Velocity Reviews - Computer Hardware Reviews

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

Reply
Thread Tools

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

 
 
joun
Guest
Posts: n/a
 
      11-26-2004
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.



 
Reply With Quote
 
 
 
 
Darrin J. Olson
Guest
Posts: n/a
 
      11-26-2004
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" <(E-Mail Removed)> wrote in message
newsRHpd.53078$(E-Mail Removed)...
> 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.
>
>
>



 
Reply With Quote
 
 
 
 
joun
Guest
Posts: n/a
 
      11-26-2004
Sorry, that was an error on my message; the source code is correct.

"Darrin J. Olson" <(E-Mail Removed)> ha scritto nel messaggio
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> newsRHpd.53078$(E-Mail Removed)...
>> 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.
>>
>>
>>

>
>



 
Reply With Quote
 
joun
Guest
Posts: n/a
 
      11-26-2004
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" <(E-Mail Removed)> ha scritto nel messaggio
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> newsRHpd.53078$(E-Mail Removed)...
>> 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.
>>
>>
>>

>
>



 
Reply With Quote
 
Cor Ligthert
Guest
Posts: n/a
 
      11-26-2004
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" <(E-Mail Removed)>
>
> "Darrin J. Olson" <(E-Mail Removed)>


>> 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" <(E-Mail Removed)> wrote in message
>> newsRHpd.53078$(E-Mail Removed)...
>>> 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.
>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
Darrin J. Olson
Guest
Posts: n/a
 
      11-26-2004
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" <(E-Mail Removed)> wrote in message
news:jxJpd.52336$(E-Mail Removed)...
> 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" <(E-Mail Removed)> ha scritto nel messaggio
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> newsRHpd.53078$(E-Mail Removed)...
>>> 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.
>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
joun
Guest
Posts: n/a
 
      11-26-2004
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" <(E-Mail Removed)> ha scritto nel messaggio
news:%23%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:jxJpd.52336$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> ha scritto nel
>> messaggio news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> newsRHpd.53078$(E-Mail Removed)...
>>>> 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.
>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
Darrin J. Olson
Guest
Posts: n/a
 
      11-26-2004
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" <(E-Mail Removed)> wrote in message
news:VFKpd.52483$(E-Mail Removed)...
> 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" <(E-Mail Removed)> ha scritto nel messaggio
> news:%23%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:jxJpd.52336$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> ha scritto nel
>>> messaggio news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> newsRHpd.53078$(E-Mail Removed)...
>>>>> 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.
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
joun
Guest
Posts: n/a
 
      11-26-2004
Ok, thanks, now i'll try with another datasource..

"Darrin J. Olson" <(E-Mail Removed)> ha scritto nel messaggio
news:u$fWhm%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:VFKpd.52483$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> ha scritto nel
>> messaggio news:%23%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:jxJpd.52336$(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> ha scritto nel
>>>> messaggio news:(E-Mail Removed)...
>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>> newsRHpd.53078$(E-Mail Removed)...
>>>>>> 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.
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
W.G. Ryan eMVP
Guest
Posts: n/a
 
      11-30-2004
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" <(E-Mail Removed)> wrote in message
news:VFKpd.52483$(E-Mail Removed)...
> 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" <(E-Mail Removed)> ha scritto nel messaggio
> news:%23%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:jxJpd.52336$(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> ha scritto nel
> >> messaggio news:(E-Mail Removed)...
> >>> 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" <(E-Mail Removed)> wrote in message
> >>> newsRHpd.53078$(E-Mail Removed)...
> >>>> 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.
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>

> >
> >

>
>



 
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?] (2) Update database using stored procedure and OleDbDataAdapter.Update joun ASP .Net 5 11-30-2004 06:51 PM
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