Home All Groups Group Topic Archive Search About

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

microsoft.public.dotnet.framework.adonet
Author
30 Nov 2004 3:23 PM
joun

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???

Author
30 Nov 2004 4:23 PM
Cowboy (Gregory A. Beamer) - MVP
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!
***************************

Show quoteHide quote
"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???
>
>
>
>
>
Are all your drivers up to date? click for free checkup

Author
30 Nov 2004 6:19 PM
Jeff Dillon
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

Show quoteHide quote
"joun" <joun@nospam.com> wrote in message
news:Rf0rd.58489$Ni.2006399@twister1.libero.it...
> 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???
>
>
>
>
Author
30 Nov 2004 6:51 PM
joun
Yes, copy & paste.

Show quoteHide quote
"Jeff Dillon" <j***@removeemergencyreporting.com> ha scritto nel messaggio
news:O8Kgkkw1EHA.1652@TK2MSFTNGP11.phx.gbl...
> 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" <joun@nospam.com> wrote in message
> news:Rf0rd.58489$Ni.2006399@twister1.libero.it...
>> 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???
>>
>>
>>
>>
>
>
Author
30 Nov 2004 6:21 PM
Jeff Dillon
And "varchar" in an Access database?  You meant Text, correct?

Jeff
Show quoteHide quote
"joun" <joun@nospam.com> wrote in message
news:Rf0rd.58489$Ni.2006399@twister1.libero.it...
> 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???
>
>
>
>
Author
30 Nov 2004 6:51 PM
joun
yes

Show quoteHide quote
"Jeff Dillon" <j***@removeemergencyreporting.com> ha scritto nel messaggio
news:%23dTnzlw1EHA.3596@TK2MSFTNGP12.phx.gbl...
> And "varchar" in an Access database?  You meant Text, correct?
>
> Jeff
> "joun" <joun@nospam.com> wrote in message
> news:Rf0rd.58489$Ni.2006399@twister1.libero.it...
>> 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???
>>
>>
>>
>>
>
>

Bookmark and Share

Post Other interesting topics
Removing rows from a DataTable is VERY slow
Specifying format of DateTime columns read from CSV files
DataSet Memory Usage
SQLDataReader
XML Dataset
Aborting a thread corrupts my SqlConnection (?)
data adapter update using datatable that has MANY changes
how to move record forward or backward
how to find out my ADO.NET version
newbie question.