Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > [BUG?] (2) Update database using stored procedure and OleDbDataAdapter.Update

Reply
Thread Tools

[BUG?] (2) Update database using stored procedure and OleDbDataAdapter.Update

 
 
joun
Guest
Posts: n/a
 
      11-30-2004
As suggested by Cor Ligthert, i've created a simpler sample, with the same
problem; this is the full source code,
so everyone can try itself:

Access database "dati.mdb":
Tables:
"myTable"
Fields:
fNumber Numeric
fString VarChar(50)
No primary keys defined.
Stored Procedures:
"qry_Ins":
PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
INSERT INTO myTable ( fNumber, fString )
VALUES ([@fNumber], [@fString]);


C# Project: Only 1 WebForm (WebForm1.aspx)
//////////////////////////////////////////
// Code Start
//////////////////////////////////////////

private void Page_Load(object sender, System.EventArgs e)
{

OleDbConnection conn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + Server.MapPath("dati.mdb") + ";");

conn.Open();

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable", conn);
DataSet ds = new DataSet();

// The table is initially empty so ds has no rows
da.Fill(ds, "myTable");


OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
upCmd.CommandType = CommandType.StoredProcedure;

upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");


da.InsertCommand = upCmd;

int i = 1;
while (i<=20)
{
ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
i++;
}

da.Update(ds, "myTable");

conn.Close();
conn = null;

}
//////////////////////////////////////////
// Code End
//////////////////////////////////////////

This is the output in the database after 1 execution of the above code:

fNumber fString
1 data_1
1 data_2
1 data_3
1 data_4
1 data_5
1 data_6
1 data_7
1 data_8
1 data_9
1 data_1
1 data_1
1 data_1
1 data_1
1 data_1
1 data_1
1 data_1
1 data_1
1 data_1
1 data_1
1 data_2

As visible, fNumber is always 1, and fString is truncated to 6 chars.
So, how to fix? It's a BUG???




 
Reply With Quote
 
 
 
 
=?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBNVlA=?=
Guest
Posts: n/a
 
      11-30-2004
I would need to see the database and the query you are using to give you a
pointer, as the code works fine for me. I would assume you have named the
values incorrectly in the table or query and have a default value set in the
database, but that is just a guess. This is what I have after running this:

1 data_1
2 data_2
3 data_3
4 data_4
5 data_5
6 data_6
7 data_7
8 data_8
9 data_9
10 data_10
11 data_11
12 data_12
13 data_13
14 data_14
15 data_15
16 data_16
17 data_17
18 data_18
19 data_19
20 data_20

The truncation is most likely either field length or length of variable in
the query.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"joun" wrote:

> As suggested by Cor Ligthert, i've created a simpler sample, with the same
> problem; this is the full source code,
> so everyone can try itself:
>
> Access database "dati.mdb":
> Tables:
> "myTable"
> Fields:
> fNumber Numeric
> fString VarChar(50)
> No primary keys defined.
> Stored Procedures:
> "qry_Ins":
> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
> INSERT INTO myTable ( fNumber, fString )
> VALUES ([@fNumber], [@fString]);
>
>
> C# Project: Only 1 WebForm (WebForm1.aspx)
> //////////////////////////////////////////
> // Code Start
> //////////////////////////////////////////
>
> private void Page_Load(object sender, System.EventArgs e)
> {
>
> OleDbConnection conn = new
> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
> "Data Source=" + Server.MapPath("dati.mdb") + ";");
>
> conn.Open();
>
> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable", conn);
> DataSet ds = new DataSet();
>
> // The table is initially empty so ds has no rows
> da.Fill(ds, "myTable");
>
>
> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
> upCmd.CommandType = CommandType.StoredProcedure;
>
> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
>
>
> da.InsertCommand = upCmd;
>
> int i = 1;
> while (i<=20)
> {
> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
> i++;
> }
>
> da.Update(ds, "myTable");
>
> conn.Close();
> conn = null;
>
> }
> //////////////////////////////////////////
> // Code End
> //////////////////////////////////////////
>
> This is the output in the database after 1 execution of the above code:
>
> fNumber fString
> 1 data_1
> 1 data_2
> 1 data_3
> 1 data_4
> 1 data_5
> 1 data_6
> 1 data_7
> 1 data_8
> 1 data_9
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_2
>
> As visible, fNumber is always 1, and fString is truncated to 6 chars.
> So, how to fix? It's a BUG???
>
>
>
>
>

 
Reply With Quote
 
 
 
 
Jeff Dillon
Guest
Posts: n/a
 
      11-30-2004
What do you mean "output of the database". How are you printing out the
values below? I'm assuming you've opened the database in Access, and
widened the display grid columns?

Jeff

"joun" <(E-Mail Removed)> wrote in message
news:Rf0rd.58489$(E-Mail Removed)...
> As suggested by Cor Ligthert, i've created a simpler sample, with the same
> problem; this is the full source code,
> so everyone can try itself:
>
> Access database "dati.mdb":
> Tables:
> "myTable"
> Fields:
> fNumber Numeric
> fString VarChar(50)
> No primary keys defined.
> Stored Procedures:
> "qry_Ins":
> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
> INSERT INTO myTable ( fNumber, fString )
> VALUES ([@fNumber], [@fString]);
>
>
> C# Project: Only 1 WebForm (WebForm1.aspx)
> //////////////////////////////////////////
> // Code Start
> //////////////////////////////////////////
>
> private void Page_Load(object sender, System.EventArgs e)
> {
>
> OleDbConnection conn = new
> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
> "Data Source=" + Server.MapPath("dati.mdb") + ";");
>
> conn.Open();
>
> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

conn);
> DataSet ds = new DataSet();
>
> // The table is initially empty so ds has no rows
> da.Fill(ds, "myTable");
>
>
> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
> upCmd.CommandType = CommandType.StoredProcedure;
>
> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
>
>
> da.InsertCommand = upCmd;
>
> int i = 1;
> while (i<=20)
> {
> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
> i++;
> }
>
> da.Update(ds, "myTable");
>
> conn.Close();
> conn = null;
>
> }
> //////////////////////////////////////////
> // Code End
> //////////////////////////////////////////
>
> This is the output in the database after 1 execution of the above code:
>
> fNumber fString
> 1 data_1
> 1 data_2
> 1 data_3
> 1 data_4
> 1 data_5
> 1 data_6
> 1 data_7
> 1 data_8
> 1 data_9
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_2
>
> As visible, fNumber is always 1, and fString is truncated to 6 chars.
> So, how to fix? It's a BUG???
>
>
>
>



 
Reply With Quote
 
Jeff Dillon
Guest
Posts: n/a
 
      11-30-2004
And "varchar" in an Access database? You meant Text, correct?

Jeff
"joun" <(E-Mail Removed)> wrote in message
news:Rf0rd.58489$(E-Mail Removed)...
> As suggested by Cor Ligthert, i've created a simpler sample, with the same
> problem; this is the full source code,
> so everyone can try itself:
>
> Access database "dati.mdb":
> Tables:
> "myTable"
> Fields:
> fNumber Numeric
> fString VarChar(50)
> No primary keys defined.
> Stored Procedures:
> "qry_Ins":
> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
> INSERT INTO myTable ( fNumber, fString )
> VALUES ([@fNumber], [@fString]);
>
>
> C# Project: Only 1 WebForm (WebForm1.aspx)
> //////////////////////////////////////////
> // Code Start
> //////////////////////////////////////////
>
> private void Page_Load(object sender, System.EventArgs e)
> {
>
> OleDbConnection conn = new
> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
> "Data Source=" + Server.MapPath("dati.mdb") + ";");
>
> conn.Open();
>
> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

conn);
> DataSet ds = new DataSet();
>
> // The table is initially empty so ds has no rows
> da.Fill(ds, "myTable");
>
>
> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
> upCmd.CommandType = CommandType.StoredProcedure;
>
> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
>
>
> da.InsertCommand = upCmd;
>
> int i = 1;
> while (i<=20)
> {
> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
> i++;
> }
>
> da.Update(ds, "myTable");
>
> conn.Close();
> conn = null;
>
> }
> //////////////////////////////////////////
> // Code End
> //////////////////////////////////////////
>
> This is the output in the database after 1 execution of the above code:
>
> fNumber fString
> 1 data_1
> 1 data_2
> 1 data_3
> 1 data_4
> 1 data_5
> 1 data_6
> 1 data_7
> 1 data_8
> 1 data_9
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_1
> 1 data_2
>
> As visible, fNumber is always 1, and fString is truncated to 6 chars.
> So, how to fix? It's a BUG???
>
>
>
>



 
Reply With Quote
 
joun
Guest
Posts: n/a
 
      11-30-2004
yes

"Jeff Dillon" <(E-Mail Removed)> ha scritto nel messaggio
news:%(E-Mail Removed)...
> And "varchar" in an Access database? You meant Text, correct?
>
> Jeff
> "joun" <(E-Mail Removed)> wrote in message
> news:Rf0rd.58489$(E-Mail Removed)...
>> As suggested by Cor Ligthert, i've created a simpler sample, with the
>> same
>> problem; this is the full source code,
>> so everyone can try itself:
>>
>> Access database "dati.mdb":
>> Tables:
>> "myTable"
>> Fields:
>> fNumber Numeric
>> fString VarChar(50)
>> No primary keys defined.
>> Stored Procedures:
>> "qry_Ins":
>> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
>> INSERT INTO myTable ( fNumber, fString )
>> VALUES ([@fNumber], [@fString]);
>>
>>
>> C# Project: Only 1 WebForm (WebForm1.aspx)
>> //////////////////////////////////////////
>> // Code Start
>> //////////////////////////////////////////
>>
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>>
>> OleDbConnection conn = new
>> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
>> "Data Source=" + Server.MapPath("dati.mdb") + ";");
>>
>> conn.Open();
>>
>> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

> conn);
>> DataSet ds = new DataSet();
>>
>> // The table is initially empty so ds has no rows
>> da.Fill(ds, "myTable");
>>
>>
>> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
>> upCmd.CommandType = CommandType.StoredProcedure;
>>
>> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
>> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
>>
>>
>> da.InsertCommand = upCmd;
>>
>> int i = 1;
>> while (i<=20)
>> {
>> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
>> i++;
>> }
>>
>> da.Update(ds, "myTable");
>>
>> conn.Close();
>> conn = null;
>>
>> }
>> //////////////////////////////////////////
>> // Code End
>> //////////////////////////////////////////
>>
>> This is the output in the database after 1 execution of the above code:
>>
>> fNumber fString
>> 1 data_1
>> 1 data_2
>> 1 data_3
>> 1 data_4
>> 1 data_5
>> 1 data_6
>> 1 data_7
>> 1 data_8
>> 1 data_9
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_2
>>
>> As visible, fNumber is always 1, and fString is truncated to 6 chars.
>> So, how to fix? It's a BUG???
>>
>>
>>
>>

>
>



 
Reply With Quote
 
joun
Guest
Posts: n/a
 
      11-30-2004
Yes, copy & paste.

"Jeff Dillon" <(E-Mail Removed)> ha scritto nel messaggio
news:(E-Mail Removed)...
> What do you mean "output of the database". How are you printing out the
> values below? I'm assuming you've opened the database in Access, and
> widened the display grid columns?
>
> Jeff
>
> "joun" <(E-Mail Removed)> wrote in message
> news:Rf0rd.58489$(E-Mail Removed)...
>> As suggested by Cor Ligthert, i've created a simpler sample, with the
>> same
>> problem; this is the full source code,
>> so everyone can try itself:
>>
>> Access database "dati.mdb":
>> Tables:
>> "myTable"
>> Fields:
>> fNumber Numeric
>> fString VarChar(50)
>> No primary keys defined.
>> Stored Procedures:
>> "qry_Ins":
>> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
>> INSERT INTO myTable ( fNumber, fString )
>> VALUES ([@fNumber], [@fString]);
>>
>>
>> C# Project: Only 1 WebForm (WebForm1.aspx)
>> //////////////////////////////////////////
>> // Code Start
>> //////////////////////////////////////////
>>
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>>
>> OleDbConnection conn = new
>> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
>> "Data Source=" + Server.MapPath("dati.mdb") + ";");
>>
>> conn.Open();
>>
>> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

> conn);
>> DataSet ds = new DataSet();
>>
>> // The table is initially empty so ds has no rows
>> da.Fill(ds, "myTable");
>>
>>
>> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
>> upCmd.CommandType = CommandType.StoredProcedure;
>>
>> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
>> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
>>
>>
>> da.InsertCommand = upCmd;
>>
>> int i = 1;
>> while (i<=20)
>> {
>> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
>> i++;
>> }
>>
>> da.Update(ds, "myTable");
>>
>> conn.Close();
>> conn = null;
>>
>> }
>> //////////////////////////////////////////
>> // Code End
>> //////////////////////////////////////////
>>
>> This is the output in the database after 1 execution of the above code:
>>
>> fNumber fString
>> 1 data_1
>> 1 data_2
>> 1 data_3
>> 1 data_4
>> 1 data_5
>> 1 data_6
>> 1 data_7
>> 1 data_8
>> 1 data_9
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_1
>> 1 data_2
>>
>> As visible, fNumber is always 1, and fString is truncated to 6 chars.
>> So, how to fix? It's a BUG???
>>
>>
>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How to update data using Stored Procedure? Alexey Smirnov ASP .Net 0 12-11-2008 06:58 PM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
[BUG?] Update database using stored procedure and OleDbDataAdapter.Update joun ASP .Net 9 11-30-2004 04:57 AM
Updating Database using a Stored procedure =?Utf-8?B?SnVzdGlu?= ASP .Net 3 11-17-2004 12:01 AM
How to modify default Stored Procedure template in VS.NET database project ES ASP .Net 2 08-20-2004 08:19 PM



Advertisments