473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table", conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in the
Table i can see the new rows,
but all records have the same values of the first one, except for the field
CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
.........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql statement,
i.e. without calling the stored procedure,
all things works fine, but slower.

Nov 18 '05 #1
9 5246
I've just looked at it breifly, but I noticed that the values of your stored
procedure have "pCod" twice, once for the "Cod" column, and once for the
"CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table", conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in the
Table i can see the new rows,
but all records have the same values of the first one, except for the
field CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql statement,
i.e. without calling the stored procedure,
all things works fine, but slower.

Nov 18 '05 #2
Sorry, that was an error on my message; the source code is correct.

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:u0**************@TK2MSFTNGP12.phx.gbl...
I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once
for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table", conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in the
Table i can see the new rows,
but all records have the same values of the first one, except for the
field CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql
statement, i.e. without calling the stored procedure,
all things works fine, but slower.


Nov 18 '05 #3
The stored procedure is effectly
PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCodArt, pQ1, pDataUscita);

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:u0**************@TK2MSFTNGP12.phx.gbl...
I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once
for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table", conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in the
Table i can see the new rows,
but all records have the same values of the first one, except for the
field CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql
statement, i.e. without calling the stored procedure,
all things works fine, but slower.


Nov 18 '05 #4
Joun,

What do you mean with a bug, you mean in your program or an ADONET bug?

Please do not call something a bug before you are quiet sure from it.

To know if something is a bug you make a simple sample, by instance with
only two parameters, by instance @A and @B etc.

Than it is testable.

Cor

"joun" <jo**@nospam.com>

"Darrin J. Olson" <da************@NoSpan.msn.com>

I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once
for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table", conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in
the Table i can see the new rows,
but all records have the same values of the first one, except for the
field CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql
statement, i.e. without calling the stored procedure,
all things works fine, but slower.



Nov 18 '05 #5
It appears to be correct. I'd be suprised if there was something wrong with
the framework data adapter. I've done similar things to this many, many
times and haven't had any trouble.

Is it possible that the other values are not changing in your while loop?
I'm sure you've checked that, but by what I see here I think it should work
correctly?? If it was some confusion with an update command in your data
adapter, then I would think all the values would be the same, since they all
appear to have the same ID (if that's your unique identifier).

If you haven't already, my suggestion would be to step through the while
loop to ensure the values are persisting into the data table.

-Darrin
"joun" <jo**@nospam.com> wrote in message
news:jx********************@twister1.libero.it...
The stored procedure is effectly
PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCodArt, pQ1, pDataUscita);

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:u0**************@TK2MSFTNGP12.phx.gbl...
I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once
for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table", conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in
the Table i can see the new rows,
but all records have the same values of the first one, except for the
field CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql
statement, i.e. without calling the stored procedure,
all things works fine, but slower.



Nov 18 '05 #6
Yes, i've already checked the while loop, and the rows in the dataset are
correct. Besides i've tryed this:

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
changed to
upCmd.Parameters.Add("pID", OleDbType.VarChar, 10, "ID");

and now the data changes in the db, but it is truncated to 3 ciphers, i.e.
124610 is stored as 610
?????

i repeat, if i sobstitute the stored procedure with the plain sql, all
things work correcty
"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:%2******************@TK2MSFTNGP09.phx.gbl... It appears to be correct. I'd be suprised if there was something wrong
with the framework data adapter. I've done similar things to this many,
many times and haven't had any trouble.

Is it possible that the other values are not changing in your while loop?
I'm sure you've checked that, but by what I see here I think it should
work correctly?? If it was some confusion with an update command in your
data adapter, then I would think all the values would be the same, since
they all appear to have the same ID (if that's your unique identifier).

If you haven't already, my suggestion would be to step through the while
loop to ensure the values are persisting into the data table.

-Darrin
"joun" <jo**@nospam.com> wrote in message
news:jx********************@twister1.libero.it...
The stored procedure is effectly
PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCodArt, pQ1, pDataUscita);

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel
messaggio news:u0**************@TK2MSFTNGP12.phx.gbl...
I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once
for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:

PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCod, pQ1, pDataUscita);
this is my c# code:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table",
conn);
DataSet ds = new DataSet();
// The table is initially empty, so ds has no rows
da.Fill(ds, "Table");

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

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");

da.InsertCommand = upCmd;

// Insert many new rows into the dataset
while (-------)
{
vett = ........
ds.Tables["Table"].Rows.Add(
new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
}

da.Update(ds, "Table");

Now the strange thing: the update process terminates successfully, in
the Table i can see the new rows,
but all records have the same values of the first one, except for the
field CodArt, correctly updated
Example:

ID Cod CodArt Q1 DataUscita
435 46669 1071-B 3 24/11/2004
435 46669 1071-N 3 24/11/2004
435 46669 1072-N 3 24/11/2004
435 46669 1073-N 3 24/11/2004
435 46669 1075-B 3 24/11/2004
435 46669 1076-N 3 24/11/2004
........

only the CodArt field is correct, all other fields are duplicated.

So where is the problem???? It is a BUG???
Thanks,
Michele

PS: if i replace the upCmd "qry_InsertData" with the entire sql
statement, i.e. without calling the stored procedure,
all things works fine, but slower.




Nov 18 '05 #7
That's strange, and understandbly frustrating. Do you think it might be
Access that's having trouble with the stored proc? Can you change the
connection to work off of another datasource to see if that's it? (Grasping
at straws.)

I'm sorry I'm not more help with this.... I'll try some tests with Access
and let you know if I find anything.

Sorry,
-Darrin
"joun" <jo**@nospam.com> wrote in message
news:VF********************@twister1.libero.it...
Yes, i've already checked the while loop, and the rows in the dataset are
correct. Besides i've tryed this:

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
changed to

upCmd.Parameters.Add("pID", OleDbType.VarChar, 10, "ID");

and now the data changes in the db, but it is truncated to 3 ciphers, i.e.
124610 is stored as 610
?????

i repeat, if i sobstitute the stored procedure with the plain sql, all
things work correcty
"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:%2******************@TK2MSFTNGP09.phx.gbl...
It appears to be correct. I'd be suprised if there was something wrong
with the framework data adapter. I've done similar things to this many,
many times and haven't had any trouble.

Is it possible that the other values are not changing in your while loop?
I'm sure you've checked that, but by what I see here I think it should
work correctly?? If it was some confusion with an update command in your
data adapter, then I would think all the values would be the same, since
they all appear to have the same ID (if that's your unique identifier).

If you haven't already, my suggestion would be to step through the while
loop to ensure the values are persisting into the data table.

-Darrin
"joun" <jo**@nospam.com> wrote in message
news:jx********************@twister1.libero.it...
The stored procedure is effectly
PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCodArt, pQ1, pDataUscita);

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel
messaggio news:u0**************@TK2MSFTNGP12.phx.gbl...
I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once
for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
> Hi all, i'm using this code to insert records into an Access table
> from asp.net, using a
> stored procedure, called qry_InsertData:
>
> PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
> [pDataUscita] DateTime;
> INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
> VALUES (pID, pCod, pCod, pQ1, pDataUscita);
>
>
> this is my c# code:
>
> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table",
> conn);
> DataSet ds = new DataSet();
> // The table is initially empty, so ds has no rows
> da.Fill(ds, "Table");
>
> OleDbCommand upCmd = new OleDbCommand("qry_InsertData", conn);
> upCmd.CommandType = CommandType.StoredProcedure;
>
> upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
> upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
> upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
> upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
> upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");
>
> da.InsertCommand = upCmd;
>
> // Insert many new rows into the dataset
> while (-------)
> {
> vett = ........
> ds.Tables["Table"].Rows.Add(
> new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});
> }
>
> da.Update(ds, "Table");
>
> Now the strange thing: the update process terminates successfully, in
> the Table i can see the new rows,
> but all records have the same values of the first one, except for the
> field CodArt, correctly updated
> Example:
>
> ID Cod CodArt Q1 DataUscita
> 435 46669 1071-B 3 24/11/2004
> 435 46669 1071-N 3 24/11/2004
> 435 46669 1072-N 3 24/11/2004
> 435 46669 1073-N 3 24/11/2004
> 435 46669 1075-B 3 24/11/2004
> 435 46669 1076-N 3 24/11/2004
> ........
>
> only the CodArt field is correct, all other fields are duplicated.
>
> So where is the problem???? It is a BUG???
> Thanks,
> Michele
>
> PS: if i replace the upCmd "qry_InsertData" with the entire sql
> statement, i.e. without calling the stored procedure,
> all things works fine, but slower.
>
>
>



Nov 18 '05 #8
Ok, thanks, now i'll try with another datasource..

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:u$****************@TK2MSFTNGP15.phx.gbl...
That's strange, and understandbly frustrating. Do you think it might be
Access that's having trouble with the stored proc? Can you change the
connection to work off of another datasource to see if that's it?
(Grasping at straws.)

I'm sorry I'm not more help with this.... I'll try some tests with Access
and let you know if I find anything.

Sorry,
-Darrin
"joun" <jo**@nospam.com> wrote in message
news:VF********************@twister1.libero.it...
Yes, i've already checked the while loop, and the rows in the dataset are
correct. Besides i've tryed this:

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
> changed to

upCmd.Parameters.Add("pID", OleDbType.VarChar, 10, "ID");

and now the data changes in the db, but it is truncated to 3 ciphers,
i.e. 124610 is stored as 610
?????

i repeat, if i sobstitute the stored procedure with the plain sql, all
things work correcty
"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel
messaggio news:%2******************@TK2MSFTNGP09.phx.gbl...
It appears to be correct. I'd be suprised if there was something wrong
with the framework data adapter. I've done similar things to this many,
many times and haven't had any trouble.

Is it possible that the other values are not changing in your while
loop? I'm sure you've checked that, but by what I see here I think it
should work correctly?? If it was some confusion with an update command
in your data adapter, then I would think all the values would be the
same, since they all appear to have the same ID (if that's your unique
identifier).

If you haven't already, my suggestion would be to step through the while
loop to ensure the values are persisting into the data table.

-Darrin
"joun" <jo**@nospam.com> wrote in message
news:jx********************@twister1.libero.it...
The stored procedure is effectly
PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCodArt, pQ1, pDataUscita);

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel
messaggio news:u0**************@TK2MSFTNGP12.phx.gbl...
> I've just looked at it breifly, but I noticed that the values of your
> stored procedure have "pCod" twice, once for the "Cod" column, and
> once for the "CodArt" column? Is this correct?
>
> "joun" <jo**@nospam.com> wrote in message
> news:oR*********************@twister2.libero.it...
>> Hi all, i'm using this code to insert records into an Access table
>> from asp.net, using a
>> stored procedure, called qry_InsertData:
>>
>> PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
>> [pDataUscita] DateTime;
>> INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
>> VALUES (pID, pCod, pCod, pQ1, pDataUscita);
>>
>>
>> this is my c# code:
>>
>> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table",
>> conn);
>> DataSet ds = new DataSet();
>> // The table is initially empty, so ds has no rows
>> da.Fill(ds, "Table");
>>
>> OleDbCommand upCmd = new OleDbCommand("qry_InsertData", conn);
>> upCmd.CommandType = CommandType.StoredProcedure;
>>
>> upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
>> upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
>> upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
>> upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
>> upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");
>>
>> da.InsertCommand = upCmd;
>>
>> // Insert many new rows into the dataset
>> while (-------)
>> {
>> vett = ........
>> ds.Tables["Table"].Rows.Add(
>> new object[] {vett[0], vett[1], vett[2], vett[3], vett[4],
>> vett[5]});
>> }
>>
>> da.Update(ds, "Table");
>>
>> Now the strange thing: the update process terminates successfully, in
>> the Table i can see the new rows,
>> but all records have the same values of the first one, except for the
>> field CodArt, correctly updated
>> Example:
>>
>> ID Cod CodArt Q1 DataUscita
>> 435 46669 1071-B 3 24/11/2004
>> 435 46669 1071-N 3 24/11/2004
>> 435 46669 1072-N 3 24/11/2004
>> 435 46669 1073-N 3 24/11/2004
>> 435 46669 1075-B 3 24/11/2004
>> 435 46669 1076-N 3 24/11/2004
>> ........
>>
>> only the CodArt field is correct, all other fields are duplicated.
>>
>> So where is the problem???? It is a BUG???
>> Thanks,
>> Michele
>>
>> PS: if i replace the upCmd "qry_InsertData" with the entire sql
>> statement, i.e. without calling the stored procedure,
>> all things works fine, but slower.
>>
>>
>>
>
>



Nov 18 '05 #9
Try it with one as the loop counter - use ? instead of named params - all
the typical good stuff.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"joun" <jo**@nospam.com> wrote in message
news:VF********************@twister1.libero.it...
Yes, i've already checked the while loop, and the rows in the dataset are
correct. Besides i've tryed this:

upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
changed to upCmd.Parameters.Add("pID", OleDbType.VarChar, 10, "ID");

and now the data changes in the db, but it is truncated to 3 ciphers, i.e.
124610 is stored as 610
?????

i repeat, if i sobstitute the stored procedure with the plain sql, all
things work correcty
"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel messaggio
news:%2******************@TK2MSFTNGP09.phx.gbl...
It appears to be correct. I'd be suprised if there was something wrong
with the framework data adapter. I've done similar things to this many,
many times and haven't had any trouble.

Is it possible that the other values are not changing in your while

loop? I'm sure you've checked that, but by what I see here I think it should
work correctly?? If it was some confusion with an update command in your
data adapter, then I would think all the values would be the same, since
they all appear to have the same ID (if that's your unique identifier).

If you haven't already, my suggestion would be to step through the while
loop to ensure the values are persisting into the data table.

-Darrin
"joun" <jo**@nospam.com> wrote in message
news:jx********************@twister1.libero.it...
The stored procedure is effectly
PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
[pDataUscita] DateTime;
INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
VALUES (pID, pCod, pCodArt, pQ1, pDataUscita);

"Darrin J. Olson" <da************@NoSpan.msn.com> ha scritto nel
messaggio news:u0**************@TK2MSFTNGP12.phx.gbl...
I've just looked at it breifly, but I noticed that the values of your
stored procedure have "pCod" twice, once for the "Cod" column, and once for the "CodArt" column? Is this correct?

"joun" <jo**@nospam.com> wrote in message
news:oR*********************@twister2.libero.it...
> Hi all, i'm using this code to insert records into an Access table from> asp.net, using a
> stored procedure, called qry_InsertData:
>
> PARAMETERS [pID] Long, [pCod] Long, [pCodArt] Text(20), [pQ1] Long,
> [pDataUscita] DateTime;
> INSERT INTO Table ( ID, Cod, CodArt, Q1, DataUscita )
> VALUES (pID, pCod, pCod, pQ1, pDataUscita);
>
>
> this is my c# code:
>
> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table",
> conn);
> DataSet ds = new DataSet();
> // The table is initially empty, so ds has no rows
> da.Fill(ds, "Table");
>
> OleDbCommand upCmd = new OleDbCommand("qry_InsertData", conn);
> upCmd.CommandType = CommandType.StoredProcedure;
>
> upCmd.Parameters.Add("pID", OleDbType.Integer, 0, "ID");
> upCmd.Parameters.Add("pCod", OleDbType.Integer, 0, "Cod");
> upCmd.Parameters.Add("pCodArt", OleDbType.VarChar, 20, "CodArt");
> upCmd.Parameters.Add("pQ1", OleDbType.Integer, 0, "Q1");
> upCmd.Parameters.Add("pDataUscita", OleDbType.Date, 0, "DataUscita");
>
> da.InsertCommand = upCmd;
>
> // Insert many new rows into the dataset
> while (-------)
> {
> vett = ........
> ds.Tables["Table"].Rows.Add(
> new object[] {vett[0], vett[1], vett[2], vett[3], vett[4], vett[5]});> }
>
> da.Update(ds, "Table");
>
> Now the strange thing: the update process terminates successfully, in
> the Table i can see the new rows,
> but all records have the same values of the first one, except for the
> field CodArt, correctly updated
> Example:
>
> ID Cod CodArt Q1 DataUscita
> 435 46669 1071-B 3 24/11/2004
> 435 46669 1071-N 3 24/11/2004
> 435 46669 1072-N 3 24/11/2004
> 435 46669 1073-N 3 24/11/2004
> 435 46669 1075-B 3 24/11/2004
> 435 46669 1076-N 3 24/11/2004
> ........
>
> only the CodArt field is correct, all other fields are duplicated.
>
> So where is the problem???? It is a BUG???
> Thanks,
> Michele
>
> PS: if i replace the upCmd "qry_InsertData" with the entire sql
> statement, i.e. without calling the stored procedure,
> all things works fine, but slower.
>
>
>



Nov 18 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mage | last post by:
Hello! create or replace function trigger_keywords_maintain() returns trigger as $$ return 'MODIFY' $$ language plpythonu; update table set id = id where id = 7; ERROR: invalid input...
9
by: David Berman | last post by:
I'm having a problem with an update operation in a stored procedure. It runs so slowly that it is unusable, unless I comment a part out in which case it is very fast. However, I need the whole...
2
by: Joe Fetters via .NET 247 | last post by:
Have googled and read the VS.NET documentation can't seem to getthe answer to the following. Environment: Framework 1.1 VB.NET WinForm Access database Using all automagic tools (DataAdapter...
5
by: joun | last post by:
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"...
3
by: marcmc | last post by:
Have you ever heard of a bug in .Net that uses the below to execute a sProc and only executes half the sProc before ducking out(without error) with a return value of NULL Here's the clincher, I...
2
by: explode | last post by:
I made nova oledbdataadapter select update insert and delete command and connection veza. dataset is Studenti1data, I made it by the new data source wizard,and made datagridview and bindingsource...
5
by: explode | last post by:
I made a procedure Public Sub Novo(ByVal nova1 As String, ByVal nova2 As String) that creates a new oledbDataAdapter with insert update select and delete commads. I also added that commands can...
11
by: raylopez99 | last post by:
Keep in mind this is my first compiled SQL program Stored Procedure (SP), copied from a book by Frasier Visual C++.NET in Visual Studio 2005 (Chap12). So far, so theory, except for one bug...
9
by: Peter Duniho | last post by:
Is there a straightfoward API in .NET that allows for inspection of a database? That is, to look at the structure of the database, without knowing anything in advance about it? For example,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.