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

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

 

[Guest]

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!
***************************

 

[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

 

[Jeff Dillon]

And "varchar" in an Access database? You meant Text, correct?

Jeff

 

[joun]

yes

And "varchar" in an Access database? You meant Text, correct?

Jeff

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

 

[joun]

Yes, copy & paste.

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

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