CODEWITHSUNDEEP
vb.netms-accessvba
oli
oli

Reputation: 3

How do you resolve 'Syntax error in INSERT INTO Statement'?

So I have created a database and want to insert information from my form in VB to the database. I have linked and coded it however, I keep getting this error message: Syntax error in INSERT INTO Statement . I have looked at many other similar questions however none of the solutions work. Here is my code:

Imports System.Data.OleDb
Class Form1
Dim Provider As String
Dim DataFile As String
Dim ConnString As String
Dim MyConnection As OleDbConnection = New OleDbConnection
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    Provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "
    DataFile = "C:\Users\Documents\Visual Studio 2012\Projects\DatabaseTest\DatabaseTest\Database.accdb"
    ConnString = Provider & DataFile
    MyConnection.ConnectionString = ConnString
    MyConnection.Open()
    Dim Strng As String
    Strng = "INSERT INTO [Table1]([StudentName], [StudentScore] Values (?,?))"
    Dim Cmmnd As OleDbCommand = New OleDbCommand(Strng, MyConnection)
    Cmmnd.Parameters.Add(New OleDbParameter("StudentName", CType(txtName.Text, String)))
    Cmmnd.Parameters.Add(New OleDbParameter("StudentScore", CType(txtScore.Text, String)))
    Try
        Cmmnd.ExecuteNonQuery()
        Cmmnd.Dispose()
        MyConnection.Close()
        txtName.Clear()
        txtScore.Clear()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub
End Class

Any help would be much appreciated!!

Upvotes: 0

Views: 114

Answers (1)

OneFineDay
OneFineDay

Reputation: 9024

End brace in the wrong place. You also do not need to convert the Text property to a string in the parameters.

Imports System.Data.OleDb
Class Form1
  Dim Provider As String
  Dim DataFile As String
  Dim ConnString As String
  Dim MyConnection As OleDbConnection = New OleDbConnection
   Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
      Provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "
      DataFile = "C:\Users\Documents\Visual Studio 2012\Projects\DatabaseTest\DatabaseTest\Database.accdb"
      ConnString = Provider & DataFile
      MyConnection.ConnectionString = ConnString
      MyConnection.Open()
      Dim Strng As String
      Strng = "INSERT INTO [Table1]([StudentName], [StudentScore]) Values (?,?)"
      Dim Cmmnd As OleDbCommand = New OleDbCommand(Strng, MyConnection)
      Cmmnd.Parameters.Add(New OleDbParameter("StudentName", txtName.Text))
      Cmmnd.Parameters.Add(New OleDbParameter("StudentScore", txtScore.Text))
     Try
       Cmmnd.ExecuteNonQuery()
       Cmmnd.Dispose()
       MyConnection.Close()
       txtName.Clear()
       txtScore.Clear()
     Catch ex As Exception
       MsgBox(ex.Message)
     End Try
  End Sub
End Class

Upvotes: 4

Related Questions