Insert multiple rows in access database using oledb parameters

Question

I am trying to insert multiple rows in a listitems to a database using parameters. But it won't give me any errors, and also won't insert any data in the table. does anyone have any ideas on this one?

  strSQL = "insert into tbltrans2 (transid,itemcode,itemname,qty,price,[total],btw) values ( ?,?,?,?,?,?,?)"
    Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\POS.mdb"), _
          cmd As New OleDbCommand(strSQL, cn)

        cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtTransId.Text
        cmd.Parameters.Add("?", OleDbType.VarChar, 10)
        cmd.Parameters.Add("?", OleDbType.VarChar, 50)
        cmd.Parameters.Add("?", OleDbType.Integer)
        cmd.Parameters.Add("?", OleDbType.Decimal)
        cmd.Parameters.Add("?", OleDbType.Decimal)
        cmd.Parameters.Add("?", OleDbType.VarChar, 50)

        cn.Open()
        For Each ls As ListViewItem In ListItems.Items
            cmd.Parameters(1).Value = ls.Tag
            cmd.Parameters(2).Value = ls.SubItems(0).Text
            cmd.Parameters(3).Value = Integer.Parse(ls.SubItems(1).Text)
            cmd.Parameters(4).Value = Decimal.Parse(ls.SubItems(2).Text)
            cmd.Parameters(5).Value = Decimal.Parse(ls.SubItems(3).Text)
            cmd.Parameters(6).Value = ls.SubItems(5).Text
        Next ls

    End Using

Hey steve, When I try that, it gives me 'syntax error in update statement' erro. Here is my code:

 Try
        strSQL = "UPDATE set instock = ? where itemcode= ?"
        Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\POS.mdb"), _
               cmd As New OleDbCommand(strSQL, cn)
            cn.Open()
            For Each ls As ListViewItem In ListItems.Items
                cmd.Parameters.Add("?", OleDbType.Integer).Value = 100
                cmd.Parameters.Add("?", OleDbType.VarChar).Value = ls.Tag
                cmd.ExecuteNonQuery()
            Next ls
            cn.Close()
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

Now, I still need help with decreasing the stock. Here is the code that I use, but it isn't working.

  strSQL = "UPDATE tblitem set instock ='instock'- ? where itemcode = ?"
            Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\POS.mdb"), _
                   cmd As New OleDbCommand(strSQL, cn)
                cn.Open()
                For Each ls As ListViewItem In SalesListItems.Items
                    If Not (ls.SubItems(1).Tag(0) = "n") Then
                        cmd.Parameters.Add("?", OleDbType.Integer).Value = ls.SubItems(1).Text
                        cmd.Parameters.Add("?", OleDbType.VarChar, 10).Value = ls.Tag
                        cmd.ExecuteNonQuery()
                    End If
                Next ls
                cn.Close()
            End Using

Sorry forget the table name after the UPDATE, answer updated — Steve
– Steve, Commented Aug 24, 2012 at 22:45

Steve · Accepted Answer · 2012-08-24 22:44:44Z

4

You are missing the execute part

For Each ls As ListViewItem In ListItems.Items 
   cmd.Parameters(1).Value = ls.Tag 
   cmd.Parameters(2).Value = ls.SubItems(0).Text 
   cmd.Parameters(3).Value = Integer.Parse(ls.SubItems(1).Text) 
   cmd.Parameters(4).Value = Decimal.Parse(ls.SubItems(2).Text) 
   cmd.Parameters(5).Value = Decimal.Parse(ls.SubItems(3).Text) 
   cmd.Parameters(6).Value = ls.SubItems(5).Text 
   cmd.ExecuteNonQuery()
Next ls

Also, are you certain on the input values coming from the subitems? If there isn't a control on their effective numeric value when you add them to the ListView, then the loop could fail with an exception when you try to convert the supposed numeric value with Parse.

EDIT: This instead for an update

strSQL = "UPDATE tblitem set instock = ? where itemcode= ?"  
Using cn As New OleDbConnection("......")   
    cmd As New OleDbCommand(strSQL, cn)  
    cmd.Parameters.Add("?", OleDbType.Integer).Value = 100
    cmd.Parameters.Add("?", OleDbType.VarChar).Value = yourItemCodeValue    
    cmd.ExecuteNonQuery()
End Using

Keep in mind that I suppose instock is a integer data type and itemcode a varchar.

edited Aug 24, 2012 at 22:44

answered Aug 24, 2012 at 13:27

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dester Dezzods Over a year ago

is it possible to use this same method to update a database? This is what I'm using currently: strSQL = String.Format(("update tblitem set inStock = '" & ls.SubItems(1).Tag - ls.SubItems(1).Text & "' where itemcode =" & ls.Tag & "")) objDal.ExecuteQuery(strSQL)

Dester Dezzods Over a year ago

ok, leave it.. How do I use parameters to update the database. like "update tblitem set instock ='value' where itemcode='value2' ?

Dester Dezzods Over a year ago

It gives me a syntax error. I have added more details in my original question.

Dester Dezzods Over a year ago

Thanks very much mate! It worked like a charm !! :D This is supposed to be very fast, right? Even if I have hundreds of records in list.

Dester Dezzods Over a year ago

Thanks mate. I did that and it worked perfect. but now I need to decrease items from stock if the transition is cancelled. I have also put my code there above in original question. Hope you could help me with this one too. thanks

|

Collectives™ on Stack Overflow

Insert multiple rows in access database using oledb parameters

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related