StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInsert multiple rows in access database using oledb parameters
text
Body
copied!<p>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?</p> <pre><code> 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 </code></pre> <p>Hey steve, When I try that, it gives me 'syntax error in update statement' erro. Here is my code:</p> <pre><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 </code></pre> <p>Now, I still need help with decreasing the stock. Here is the code that I use, but it isn't working.</p> <pre><code> 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload