none
Update table with tow colum RRS feed

  • Question

  • I have Access DB and Table2 have many coulm

    ID,Vendor,Prudect,Price,PriceJ,PriceM,K,HP

    I need to update Price , PriceJ,PriceM , K , and HP by choosingg Vendor and Prudect

    I try this

    Dim SavInto As New OleDb.OleDbCommand
            Dim ConStr As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                Application.StartupPath & "\mydata.mdb"
            Dim Conn As New OleDbConnection(ConStr)
            SavInto.Connection = Conn
            SavInto.CommandType = CommandType.Text
            SavInto.CommandText = "UPDATE Table2 set Price='" & Trim(TextBox9.Text) & "',PriceJ='" & Trim(TextBox10.Text) & "',PriceM= '" & Trim(TextBox11.Text) & "',k='" & Trim(TextBox12.Text) & "' WHERE Vendor='" & Trim(ComboBox3.Text) And "' Prudect='" & Trim(ComboBox4.Text) & "'"
            Conn.Open()
            SavInto.ExecuteNonQuery()

    but  I have error message

    {"Conversion from string "UPDATE Table2 set Price='100',Pr" to type 'Long' is not valid."}

    How I can do that


    I must Win

    Monday, March 5, 2012 10:38 AM

Answers

  • I convert your code to vb.net but I have some error

    I solve my problem with this code

    Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Application.StartupPath & "\mydata.mdb")
            Dim SavInto As New OleDb.OleDbCommand
            SavInto.Connection = Conn
            SavInto.CommandType = CommandType.Text
            SavInto.CommandText = "UPDATE Table2 set Price='" & Trim(TextBox9.Text) & "',PriceJ='" & Trim(TextBox10.Text) & "',PriceM= '" & Trim(TextBox11.Text) & "',k='" & Trim(TextBox12.Text) & "' WHERE Vendor='" & Trim(ComboBox3.Text) & "' And Prudect='" & Trim(ComboBox4.Text) & "'"
            Conn.Open()
            SavInto.ExecuteNonQuery()
            Conn.Close()

    thank you for your replay

    I used MS Access DB

    Vendor is string

    prudect is string

    Price , PriceJ,PriceJ,K,HP is number


    I must Win

    • Marked as answer by Kosay Hatem Thursday, March 8, 2012 3:06 PM
    Thursday, March 8, 2012 3:06 PM

All replies

  • Hi,

    for numerical types: you don't need to enclose them in single quotes:

    SavInto.CommandText = "UPDATE Table2 set Price= " & Trim(TextBox9.Text) & ",PriceJ= " & Trim(TextBox10.Text) & " , ...


    Regards, Nico

    Monday, March 5, 2012 2:32 PM
  • I remove  single quotes but the same error message !!!!

    I must Win

    Tuesday, March 6, 2012 4:32 PM
  • Hi Kosay Hatem,

    Welcome to MSDN Forum.

    Based on the issue, the 'Price', 'PriceJ', 'PriceM' properties are number type in the database, but TextBox.Text will return a string type value, so the exception will be thrown. I'm not clear about all the properties type of your table, I created a table in Access database like this,

    Table2(Vendor[Text], Prudect[Text], Price[Number], PriceJ[Number], PriceM[Number], K[Text], HP[Text])

    then, I update the Price, PriceJ, PriceM and K by choosing Vendor and Prudect like this,

    using(OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\v-alll\Documents\Database3.accdb"))
                {
                    OleDbCommand cmd = new OleDbCommand();
                    string upgradeCommand = "update Table2 set Price=@price, PriceJ=@priceJ,PriceM=@priceM,K=@k,HP=@hp where Vendor=@vendor and Prudect=@prudect";
                    cmd.CommandText = upgradeCommand;
                    cmd.Connection = con;
                    OleDbParameter parPrice = new OleDbParameter("@price", Convert.Decimal(TextBox9.Text.Trim()));
                    OleDbParameter parPrJ = new OleDbParameter("@priceJ", Convert.Decimal(TextBox10.Text.Trim()));
                    OleDbParameter parPrM = new OleDbParameter("@priceM", Convert.Decimal(TextBox11.Text.Trim()));
                    OleDbParameter parK = new OleDbParameter("@k", TextBox12.Text.Trim());
                    OleDbParameter parVendor = new OleDbParameter("@vendor",TextBox3.Text.Trim());
                    OleDbParameter parPrudect = new OleDbParameter("@prudect", TextBox4.Text.Trim());
                    cmd.Parameters.Add(parPrice);
                    cmd.Parameters.Add(parPrJ);
                    cmd.Parameters.Add(parPrM);
                    cmd.Parameters.Add(parK);
                    cmd.Parameters.Add(parVendor);
                    cmd.Parameters.Add(parPrudect);
    
                    con.Open();
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("Upgrade successfully!");
                    Console.Read();

    I used parameterized query, not concatenation of string, it is more safe.

    Best Regards

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 7, 2012 3:06 AM
    Moderator
  • I convert your code to vb.net but I have some error

    I solve my problem with this code

    Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Application.StartupPath & "\mydata.mdb")
            Dim SavInto As New OleDb.OleDbCommand
            SavInto.Connection = Conn
            SavInto.CommandType = CommandType.Text
            SavInto.CommandText = "UPDATE Table2 set Price='" & Trim(TextBox9.Text) & "',PriceJ='" & Trim(TextBox10.Text) & "',PriceM= '" & Trim(TextBox11.Text) & "',k='" & Trim(TextBox12.Text) & "' WHERE Vendor='" & Trim(ComboBox3.Text) & "' And Prudect='" & Trim(ComboBox4.Text) & "'"
            Conn.Open()
            SavInto.ExecuteNonQuery()
            Conn.Close()

    thank you for your replay

    I used MS Access DB

    Vendor is string

    prudect is string

    Price , PriceJ,PriceJ,K,HP is number


    I must Win

    • Marked as answer by Kosay Hatem Thursday, March 8, 2012 3:06 PM
    Thursday, March 8, 2012 3:06 PM