By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,620 Members | 1,303 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,620 IT Pros & Developers. It's quick & easy.

error in insert into satatement

P: 1
hi
I created a button to save the details in ms_access database with oledb but when I run my project and click save button I get an error message saying that "error in insert into statement" any help please.
here is my code

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
datafile = "C:\Users\user\Documents\Database51111.accdb"
connString = provider & datafile
myConnection.ConnectionString = connString
myConnection.Open()
Dim str As String
'str = "insert into files([number],[fname],[from],[to],[date],[recom])Values(?,?,?,?,?)"
str = "INSERT INTO allocate ([IDE],[IT],[SE],[ITE],[DATEE],[FROM],[TO],[DE],[ASSTE],[QUA],[RETE],[RE],[DAM],[STAT]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
cmd.Parameters.Add(New OleDbParameter("IDE", CType(TextBox1.Text, String)))
cmd.Parameters.Add(New OleDbParameter("IT", CType(TextBox2.Text, String)))
cmd.Parameters.Add(New OleDbParameter("SE", CType(TextBox3.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ITE", CType(TextBox4.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DATEE", CType(DateTimePicker1.Value, String)))
cmd.Parameters.Add(New OleDbParameter("FROM", CType(TextBox6.Text, String)))
cmd.Parameters.Add(New OleDbParameter("TO", CType(TextBox7.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DE", CType(TextBox8.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ASSTE", CType(DateTimePicker2.Value, String)))
cmd.Parameters.Add(New OleDbParameter("QUA", CType(TextBox10.Text, String)))
cmd.Parameters.Add(New OleDbParameter("RETE", CType(DateTimePicker3.Value, String)))
cmd.Parameters.Add(New OleDbParameter("RE", CType(TextBox12.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DAM", CType(TextBox13.Text, String)))
cmd.Parameters.Add(New OleDbParameter("STAT", CType(TextBox14.Text, String)))
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
myConnection.Close()
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox6.Text = ""
Me.TextBox7.Text = ""
Me.TextBox8.Text = ""
Me.TextBox10.Text = ""
Me.TextBox12.Text = ""
Me.TextBox13.Text = ""
Me.TextBox14.Text = ""
'Exit Sub
'End With
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try


Me.Hide()
frmmain.Show()


End Sub
Sep 29 '18 #1
Share this Question
Share on Google+
1 Reply


100+
P: 299
I see a couple of things wrong here.

1) The question marks in your string should be parameters that you are replacing if the string is built the way you're trying to do so. That being said, it appears that you're trying to insert values? I'm not really sure, sorry.

2) The command you're using to add parameter values should be ".AddWithValue", not ".Add" when building a string in this manner.

I've included a couple of examples that would be more accurate.

This example builds the string in the same fashion that you're trying:
Expand|Select|Wrap|Line Numbers
  1.         Dim str As String
  2.         str = "INSERT INTO allocate([IDE],[IT],[SE],[ITE],[DATEE],[FROM],[TO],[DE],[ASSTE],[QUA],[RETE],[RE],[DAM],[STAT])VALUES(@IDE,@IT,@SE,@ITE,@DATEE,@FROM,@TO,@DE,@ASSTE,@QUA,@RETE,@RE,@DAM,@STAT)"
  3.         Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
  4.         cmd.Parameters.AddWithValue(New OleDbParameter("@IDE", TextBox1.Text.ToString()))
  5.         cmd.Parameters.AddWithValue(New OleDbParameter("@IT", TextBox2.Text.ToString()))
  6.         cmd.Parameters.AddWithValue(New OleDbParameter("@SE", TextBox3.Text.ToString()))
  7.         cmd.Parameters.AddWithValue(New OleDbParameter("@ITE", TextBox4.Text.ToString()))
  8.         cmd.Parameters.AddWithValue(New OleDbParameter("@DATEE", DateTimePicker1.Value.ToString()))
  9.         cmd.Parameters.AddWithValue(New OleDbParameter("@FROM", TextBox6.Text.ToString()))
  10.         cmd.Parameters.AddWithValue(New OleDbParameter("@TO", TextBox7.Text.ToString()))
  11.         cmd.Parameters.AddWithValue(New OleDbParameter("@DE", TextBox8.Text.ToString()))
  12.         cmd.Parameters.AddWithValue(New OleDbParameter("@ASSTE", DateTimePicker2.Value.ToString()))
  13.         cmd.Parameters.AddWithValue(New OleDbParameter("@QUA", TextBox10.Text.ToString()))
  14.         cmd.Parameters.AddWithValue(New OleDbParameter("@RETE", DateTimePicker3.Value.ToString()))
  15.         cmd.Parameters.AddWithValue(New OleDbParameter("@RE", TextBox12.Text.ToString()))
  16.         cmd.Parameters.AddWithValue(New OleDbParameter("@DAM", TextBox13.Text.ToString()))
  17.         cmd.Parameters.AddWithValue(New OleDbParameter("@STAT", TextBox14.Text.ToString()))
This example is using a manually built string. Obviously I've only used two textboxes to simulate what would need to be done:
Expand|Select|Wrap|Line Numbers
  1.         Dim str As String
  2.         str = "INSERT INTO allocate([IDE],[IT],[SE],[ITE],[DATEE],[FROM],[TO],[DE],[ASSTE],[QUA],[RETE],[RE],[DAM],[STAT])VALUES(" & TextBox1.Text.ToString() & "," & TextBox2.Text.ToString() & ")"
This example is using string variables. Same concept as the previous option but a little more clean. Again, I only used two variables:
Expand|Select|Wrap|Line Numbers
  1.         Dim str As String
  2.         Dim IDE As String = TextBox1.Text
  3.         Dim IT As String = TextBox2.Text
  4.         str = "INSERT INTO allocate([IDE],[IT],[SE],[ITE],[DATEE],[FROM],[TO],[DE],[ASSTE],[QUA],[RETE],[RE],[DAM],[STAT])VALUES(" & IDE & "," & IT & ")"
I hope this helps!
Oct 1 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.