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

Discussion in 'ASP .Net' started by joun, Nov 26, 2004.

  1. joun

    joun Guest

    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.
    joun, Nov 26, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:eek:RHpd.53078$...
    > 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.
    >
    >
    >
    Darrin J. Olson, Nov 26, 2004
    #2
    1. Advertising

  3. joun

    joun Guest

    Sorry, that was an error on my message; the source code is correct.

    "Darrin J. Olson" <> ha scritto nel messaggio
    news:...
    > 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" <> wrote in message
    > news:eek:RHpd.53078$...
    >> 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.
    >>
    >>
    >>

    >
    >
    joun, Nov 26, 2004
    #3
  4. joun

    joun Guest

    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" <> ha scritto nel messaggio
    news:...
    > 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" <> wrote in message
    > news:eek:RHpd.53078$...
    >> 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.
    >>
    >>
    >>

    >
    >
    joun, Nov 26, 2004
    #4
  5. joun

    Cor Ligthert Guest

    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" <>
    >
    > "Darrin J. Olson" <>


    >> 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" <> wrote in message
    >> news:eek:RHpd.53078$...
    >>> 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.
    >>>
    >>>
    >>>

    >>
    >>

    >
    >
    Cor Ligthert, Nov 26, 2004
    #5
  6. 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" <> wrote in message
    news:jxJpd.52336$...
    > 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" <> ha scritto nel messaggio
    > news:...
    >> 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" <> wrote in message
    >> news:eek:RHpd.53078$...
    >>> 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.
    >>>
    >>>
    >>>

    >>
    >>

    >
    >
    Darrin J. Olson, Nov 26, 2004
    #6
  7. joun

    joun Guest

    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" <> ha scritto nel messaggio
    news:%23%...
    > 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" <> wrote in message
    > news:jxJpd.52336$...
    >> 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" <> ha scritto nel
    >> messaggio news:...
    >>> 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" <> wrote in message
    >>> news:eek:RHpd.53078$...
    >>>> 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.
    >>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
    joun, Nov 26, 2004
    #7
  8. 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" <> wrote in message
    news:VFKpd.52483$...
    > 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" <> ha scritto nel messaggio
    > news:%23%...
    >> 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" <> wrote in message
    >> news:jxJpd.52336$...
    >>> 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" <> ha scritto nel
    >>> messaggio news:...
    >>>> 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" <> wrote in message
    >>>> news:eek:RHpd.53078$...
    >>>>> 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.
    >>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
    Darrin J. Olson, Nov 26, 2004
    #8
  9. joun

    joun Guest

    Ok, thanks, now i'll try with another datasource..

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

    >>
    >>

    >
    >
    joun, Nov 26, 2004
    #9
  10. 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" <> wrote in message
    news:VFKpd.52483$...
    > 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" <> ha scritto nel messaggio
    > news:%23%...
    > > 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" <> wrote in message
    > > news:jxJpd.52336$...
    > >> 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" <> ha scritto nel
    > >> messaggio news:...
    > >>> 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" <> wrote in message
    > >>> news:eek:RHpd.53078$...
    > >>>> 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.
    > >>>>
    > >>>>
    > >>>>
    > >>>
    > >>>
    > >>
    > >>

    > >
    > >

    >
    >
    W.G. Ryan eMVP, Nov 30, 2004
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. amessimon

    Issues with OledbDataAdapter.Update()

    amessimon, Apr 30, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    3,200
    Natty Gur
    May 2, 2004
  2. Bennett Haselton
    Replies:
    1
    Views:
    3,341
    Bennett Haselton
    Oct 18, 2004
  3. =?Utf-8?B?SnVzdGlu?=

    Updating Database using a Stored procedure

    =?Utf-8?B?SnVzdGlu?=, Nov 16, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    482
    Jeff Dillon
    Nov 17, 2004
  4. joun
    Replies:
    5
    Views:
    2,641
  5. Mike P
    Replies:
    0
    Views:
    3,268
    Mike P
    Jun 19, 2006
Loading...

Share This Page