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

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

  1. joun

    joun Guest

    As suggested by Cor Ligthert, i've created a simpler sample, with the same
    problem; this is the full source code,
    so everyone can try itself:

    Access database "dati.mdb":
    Tables:
    "myTable"
    Fields:
    fNumber Numeric
    fString VarChar(50)
    No primary keys defined.
    Stored Procedures:
    "qry_Ins":
    PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
    INSERT INTO myTable ( fNumber, fString )
    VALUES ([@fNumber], [@fString]);


    C# Project: Only 1 WebForm (WebForm1.aspx)
    //////////////////////////////////////////
    // Code Start
    //////////////////////////////////////////

    private void Page_Load(object sender, System.EventArgs e)
    {

    OleDbConnection conn = new
    OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
    "Data Source=" + Server.MapPath("dati.mdb") + ";");

    conn.Open();

    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable", conn);
    DataSet ds = new DataSet();

    // The table is initially empty so ds has no rows
    da.Fill(ds, "myTable");


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

    upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
    upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");


    da.InsertCommand = upCmd;

    int i = 1;
    while (i<=20)
    {
    ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
    i++;
    }

    da.Update(ds, "myTable");

    conn.Close();
    conn = null;

    }
    //////////////////////////////////////////
    // Code End
    //////////////////////////////////////////

    This is the output in the database after 1 execution of the above code:

    fNumber fString
    1 data_1
    1 data_2
    1 data_3
    1 data_4
    1 data_5
    1 data_6
    1 data_7
    1 data_8
    1 data_9
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_1
    1 data_2

    As visible, fNumber is always 1, and fString is truncated to 6 chars.
    So, how to fix? It's a BUG???
     
    joun, Nov 30, 2004
    #1
    1. Advertising

  2. RE: [BUG?] (2) Update database using stored procedure and OleDbDataAda

    I would need to see the database and the query you are using to give you a
    pointer, as the code works fine for me. I would assume you have named the
    values incorrectly in the table or query and have a default value set in the
    database, but that is just a guess. This is what I have after running this:

    1 data_1
    2 data_2
    3 data_3
    4 data_4
    5 data_5
    6 data_6
    7 data_7
    8 data_8
    9 data_9
    10 data_10
    11 data_11
    12 data_12
    13 data_13
    14 data_14
    15 data_15
    16 data_16
    17 data_17
    18 data_18
    19 data_19
    20 data_20

    The truncation is most likely either field length or length of variable in
    the query.

    ---

    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    ***************************
    Think Outside the Box!
    ***************************

    "joun" wrote:

    > As suggested by Cor Ligthert, i've created a simpler sample, with the same
    > problem; this is the full source code,
    > so everyone can try itself:
    >
    > Access database "dati.mdb":
    > Tables:
    > "myTable"
    > Fields:
    > fNumber Numeric
    > fString VarChar(50)
    > No primary keys defined.
    > Stored Procedures:
    > "qry_Ins":
    > PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
    > INSERT INTO myTable ( fNumber, fString )
    > VALUES ([@fNumber], [@fString]);
    >
    >
    > C# Project: Only 1 WebForm (WebForm1.aspx)
    > //////////////////////////////////////////
    > // Code Start
    > //////////////////////////////////////////
    >
    > private void Page_Load(object sender, System.EventArgs e)
    > {
    >
    > OleDbConnection conn = new
    > OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
    > "Data Source=" + Server.MapPath("dati.mdb") + ";");
    >
    > conn.Open();
    >
    > OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable", conn);
    > DataSet ds = new DataSet();
    >
    > // The table is initially empty so ds has no rows
    > da.Fill(ds, "myTable");
    >
    >
    > OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
    > upCmd.CommandType = CommandType.StoredProcedure;
    >
    > upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
    > upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
    >
    >
    > da.InsertCommand = upCmd;
    >
    > int i = 1;
    > while (i<=20)
    > {
    > ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
    > i++;
    > }
    >
    > da.Update(ds, "myTable");
    >
    > conn.Close();
    > conn = null;
    >
    > }
    > //////////////////////////////////////////
    > // Code End
    > //////////////////////////////////////////
    >
    > This is the output in the database after 1 execution of the above code:
    >
    > fNumber fString
    > 1 data_1
    > 1 data_2
    > 1 data_3
    > 1 data_4
    > 1 data_5
    > 1 data_6
    > 1 data_7
    > 1 data_8
    > 1 data_9
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_2
    >
    > As visible, fNumber is always 1, and fString is truncated to 6 chars.
    > So, how to fix? It's a BUG???
    >
    >
    >
    >
    >
     
    =?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBN, Nov 30, 2004
    #2
    1. Advertising

  3. joun

    Jeff Dillon Guest

    What do you mean "output of the database". How are you printing out the
    values below? I'm assuming you've opened the database in Access, and
    widened the display grid columns?

    Jeff

    "joun" <> wrote in message
    news:Rf0rd.58489$...
    > As suggested by Cor Ligthert, i've created a simpler sample, with the same
    > problem; this is the full source code,
    > so everyone can try itself:
    >
    > Access database "dati.mdb":
    > Tables:
    > "myTable"
    > Fields:
    > fNumber Numeric
    > fString VarChar(50)
    > No primary keys defined.
    > Stored Procedures:
    > "qry_Ins":
    > PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
    > INSERT INTO myTable ( fNumber, fString )
    > VALUES ([@fNumber], [@fString]);
    >
    >
    > C# Project: Only 1 WebForm (WebForm1.aspx)
    > //////////////////////////////////////////
    > // Code Start
    > //////////////////////////////////////////
    >
    > private void Page_Load(object sender, System.EventArgs e)
    > {
    >
    > OleDbConnection conn = new
    > OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
    > "Data Source=" + Server.MapPath("dati.mdb") + ";");
    >
    > conn.Open();
    >
    > OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

    conn);
    > DataSet ds = new DataSet();
    >
    > // The table is initially empty so ds has no rows
    > da.Fill(ds, "myTable");
    >
    >
    > OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
    > upCmd.CommandType = CommandType.StoredProcedure;
    >
    > upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
    > upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
    >
    >
    > da.InsertCommand = upCmd;
    >
    > int i = 1;
    > while (i<=20)
    > {
    > ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
    > i++;
    > }
    >
    > da.Update(ds, "myTable");
    >
    > conn.Close();
    > conn = null;
    >
    > }
    > //////////////////////////////////////////
    > // Code End
    > //////////////////////////////////////////
    >
    > This is the output in the database after 1 execution of the above code:
    >
    > fNumber fString
    > 1 data_1
    > 1 data_2
    > 1 data_3
    > 1 data_4
    > 1 data_5
    > 1 data_6
    > 1 data_7
    > 1 data_8
    > 1 data_9
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_2
    >
    > As visible, fNumber is always 1, and fString is truncated to 6 chars.
    > So, how to fix? It's a BUG???
    >
    >
    >
    >
     
    Jeff Dillon, Nov 30, 2004
    #3
  4. joun

    Jeff Dillon Guest

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

    Jeff
    "joun" <> wrote in message
    news:Rf0rd.58489$...
    > As suggested by Cor Ligthert, i've created a simpler sample, with the same
    > problem; this is the full source code,
    > so everyone can try itself:
    >
    > Access database "dati.mdb":
    > Tables:
    > "myTable"
    > Fields:
    > fNumber Numeric
    > fString VarChar(50)
    > No primary keys defined.
    > Stored Procedures:
    > "qry_Ins":
    > PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
    > INSERT INTO myTable ( fNumber, fString )
    > VALUES ([@fNumber], [@fString]);
    >
    >
    > C# Project: Only 1 WebForm (WebForm1.aspx)
    > //////////////////////////////////////////
    > // Code Start
    > //////////////////////////////////////////
    >
    > private void Page_Load(object sender, System.EventArgs e)
    > {
    >
    > OleDbConnection conn = new
    > OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
    > "Data Source=" + Server.MapPath("dati.mdb") + ";");
    >
    > conn.Open();
    >
    > OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

    conn);
    > DataSet ds = new DataSet();
    >
    > // The table is initially empty so ds has no rows
    > da.Fill(ds, "myTable");
    >
    >
    > OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
    > upCmd.CommandType = CommandType.StoredProcedure;
    >
    > upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
    > upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
    >
    >
    > da.InsertCommand = upCmd;
    >
    > int i = 1;
    > while (i<=20)
    > {
    > ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
    > i++;
    > }
    >
    > da.Update(ds, "myTable");
    >
    > conn.Close();
    > conn = null;
    >
    > }
    > //////////////////////////////////////////
    > // Code End
    > //////////////////////////////////////////
    >
    > This is the output in the database after 1 execution of the above code:
    >
    > fNumber fString
    > 1 data_1
    > 1 data_2
    > 1 data_3
    > 1 data_4
    > 1 data_5
    > 1 data_6
    > 1 data_7
    > 1 data_8
    > 1 data_9
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_1
    > 1 data_2
    >
    > As visible, fNumber is always 1, and fString is truncated to 6 chars.
    > So, how to fix? It's a BUG???
    >
    >
    >
    >
     
    Jeff Dillon, Nov 30, 2004
    #4
  5. joun

    joun Guest

    yes

    "Jeff Dillon" <> ha scritto nel messaggio
    news:%...
    > And "varchar" in an Access database? You meant Text, correct?
    >
    > Jeff
    > "joun" <> wrote in message
    > news:Rf0rd.58489$...
    >> As suggested by Cor Ligthert, i've created a simpler sample, with the
    >> same
    >> problem; this is the full source code,
    >> so everyone can try itself:
    >>
    >> Access database "dati.mdb":
    >> Tables:
    >> "myTable"
    >> Fields:
    >> fNumber Numeric
    >> fString VarChar(50)
    >> No primary keys defined.
    >> Stored Procedures:
    >> "qry_Ins":
    >> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
    >> INSERT INTO myTable ( fNumber, fString )
    >> VALUES ([@fNumber], [@fString]);
    >>
    >>
    >> C# Project: Only 1 WebForm (WebForm1.aspx)
    >> //////////////////////////////////////////
    >> // Code Start
    >> //////////////////////////////////////////
    >>
    >> private void Page_Load(object sender, System.EventArgs e)
    >> {
    >>
    >> OleDbConnection conn = new
    >> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
    >> "Data Source=" + Server.MapPath("dati.mdb") + ";");
    >>
    >> conn.Open();
    >>
    >> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

    > conn);
    >> DataSet ds = new DataSet();
    >>
    >> // The table is initially empty so ds has no rows
    >> da.Fill(ds, "myTable");
    >>
    >>
    >> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
    >> upCmd.CommandType = CommandType.StoredProcedure;
    >>
    >> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
    >> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
    >>
    >>
    >> da.InsertCommand = upCmd;
    >>
    >> int i = 1;
    >> while (i<=20)
    >> {
    >> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
    >> i++;
    >> }
    >>
    >> da.Update(ds, "myTable");
    >>
    >> conn.Close();
    >> conn = null;
    >>
    >> }
    >> //////////////////////////////////////////
    >> // Code End
    >> //////////////////////////////////////////
    >>
    >> This is the output in the database after 1 execution of the above code:
    >>
    >> fNumber fString
    >> 1 data_1
    >> 1 data_2
    >> 1 data_3
    >> 1 data_4
    >> 1 data_5
    >> 1 data_6
    >> 1 data_7
    >> 1 data_8
    >> 1 data_9
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_2
    >>
    >> As visible, fNumber is always 1, and fString is truncated to 6 chars.
    >> So, how to fix? It's a BUG???
    >>
    >>
    >>
    >>

    >
    >
     
    joun, Nov 30, 2004
    #5
  6. joun

    joun Guest

    Yes, copy & paste.

    "Jeff Dillon" <> ha scritto nel messaggio
    news:...
    > What do you mean "output of the database". How are you printing out the
    > values below? I'm assuming you've opened the database in Access, and
    > widened the display grid columns?
    >
    > Jeff
    >
    > "joun" <> wrote in message
    > news:Rf0rd.58489$...
    >> As suggested by Cor Ligthert, i've created a simpler sample, with the
    >> same
    >> problem; this is the full source code,
    >> so everyone can try itself:
    >>
    >> Access database "dati.mdb":
    >> Tables:
    >> "myTable"
    >> Fields:
    >> fNumber Numeric
    >> fString VarChar(50)
    >> No primary keys defined.
    >> Stored Procedures:
    >> "qry_Ins":
    >> PARAMETERS [@fNumber] Long, [@fString] Text ( 255 );
    >> INSERT INTO myTable ( fNumber, fString )
    >> VALUES ([@fNumber], [@fString]);
    >>
    >>
    >> C# Project: Only 1 WebForm (WebForm1.aspx)
    >> //////////////////////////////////////////
    >> // Code Start
    >> //////////////////////////////////////////
    >>
    >> private void Page_Load(object sender, System.EventArgs e)
    >> {
    >>
    >> OleDbConnection conn = new
    >> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
    >> "Data Source=" + Server.MapPath("dati.mdb") + ";");
    >>
    >> conn.Open();
    >>
    >> OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM myTable",

    > conn);
    >> DataSet ds = new DataSet();
    >>
    >> // The table is initially empty so ds has no rows
    >> da.Fill(ds, "myTable");
    >>
    >>
    >> OleDbCommand upCmd = new OleDbCommand("qry_Ins", conn);
    >> upCmd.CommandType = CommandType.StoredProcedure;
    >>
    >> upCmd.Parameters.Add("@fNumber", OleDbType.Integer, 0, "fNumber");
    >> upCmd.Parameters.Add("@fString", OleDbType.VarChar, 50, "fString");
    >>
    >>
    >> da.InsertCommand = upCmd;
    >>
    >> int i = 1;
    >> while (i<=20)
    >> {
    >> ds.Tables["myTable"].Rows.Add(new object[] {i, "data_" + i});
    >> i++;
    >> }
    >>
    >> da.Update(ds, "myTable");
    >>
    >> conn.Close();
    >> conn = null;
    >>
    >> }
    >> //////////////////////////////////////////
    >> // Code End
    >> //////////////////////////////////////////
    >>
    >> This is the output in the database after 1 execution of the above code:
    >>
    >> fNumber fString
    >> 1 data_1
    >> 1 data_2
    >> 1 data_3
    >> 1 data_4
    >> 1 data_5
    >> 1 data_6
    >> 1 data_7
    >> 1 data_8
    >> 1 data_9
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_1
    >> 1 data_2
    >>
    >> As visible, fNumber is always 1, and fString is truncated to 6 chars.
    >> So, how to fix? It's a BUG???
    >>
    >>
    >>
    >>

    >
    >
     
    joun, Nov 30, 2004
    #6
    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,235
    Natty Gur
    May 2, 2004
  2. Bennett Haselton
    Replies:
    1
    Views:
    3,405
    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:
    501
    Jeff Dillon
    Nov 17, 2004
  4. joun
    Replies:
    9
    Views:
    2,266
    W.G. Ryan eMVP
    Nov 30, 2004
  5. Mike P
    Replies:
    0
    Views:
    3,352
    Mike P
    Jun 19, 2006
Loading...

Share This Page