使用oledb参数在Access数据库中插入多行 [英] Insert multiple rows in access database using oledb parameters

问题描述

我正在尝试使用参数将列表项中的多行插入数据库. 但这不会给我任何错误，也不会在表中插入任何数据. 有人对此有任何想法吗?

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

推荐答案

您缺少执行部分

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 

还可以确定子项的输入值吗?如果将它们添加到ListView时没有对其有效数字值的控制，则当您尝试使用Parse转换假定的数字值时，循环可能会因异常而失败.

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.

这是为了进行更新

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    

请记住，我想库存是整数数据类型，而itemcode是varchar.

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

这篇关于使用oledb参数在Access数据库中插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！