"syntax error in INSERT INTO" ms-access vb.net [duplicate]

Question

 If connection.Units = Nothing Then
            MetroFramework.MetroMessageBox.Show(Me, "You didnt Choose quantites", "Stop", MessageBoxButtons.OKCancel, MessageBoxIcon.Stop)
            'MetroFramework.MetroMessageBox.Show(Me, "Oops you didnt choose your desired quantites")
        Else
            'Try
            Dim Conn = New OleDb.OleDbConnection
            Dim str As String = "Select * From Cars"
            ' Dim dataAdapter As New OleDbDataAdapter(str, Conn)
            Dim dataSet As New DataSet()
            With Conn
                .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Stark\Documents\LaShop.mdb"
                .Open()
            End With
            Dim Sql As String
            Sql = "Insert Into Transactions (TransctID,TotalPrice,Username,Time,Discount) Values(@TranID,@price,@uname,@time,@Dsc)"
            'Dim cm = New OleDbCommand(Sql, Conn)
            'Dim cn = New OleDb.OleDbConnection
            Dim TranID As String
            Dim nd As New Random
            TranID = nd.Next & connection.Values(0)
            Using cm As New OleDbCommand(Sql, Conn)
                cm.Connection = Conn
                cm.CommandText = CommandType.Text
                cm.CommandText = Sql
                '.CommandText = "Insert Into Items (ItemID,BrandName,Category,ItemPrice,ItemName,Quantity)Values(@ID,@Bname,@cat,@price,@ItemName,@qty)"
                cm.Parameters.AddWithValue("@TranID", TranID)
                cm.Parameters.AddWithValue("@price", connection.TotalPrice)
                cm.Parameters.AddWithValue("@uname", connection.uname)
                cm.Parameters.AddWithValue("@time", TimeOfDay)
                cm.Parameters.AddWithValue("@Dsc", connection.DscAmount)

                cm.ExecuteNonQuery()
                ' MetroFramework.MetroMessageBox.Show(Me, "Your data is entered and saved")
            End Using
            Dim Dpt As New OleDbDataAdapter
            Dpt = New OleDbDataAdapter("Select Sum(TotalPrice) From Cart", Conn)
            'Dim Dpt As New OleDbDataAdapter("Select * From Items where ItemID=@I", Conn)
            'Cmd.Parameters.AddWithValue("@I", Str)
            Dim ds1 As New DataSet
            Dpt.Fill(ds1, "tp")
            'Dsctext.DataSource = ds1
            'Dsctext.DataMember = "tp"
            Results.tp.Text = ds1.Tables(0).Rows(0).Item(0)
            Dim ds As New DataSet
            Dim DataAdapter2 As New OleDbDataAdapter("select * From Cart", Conn)
            DataAdapter2.Fill(ds, "cart")
            Res1Grid.DataSource = ds
            Res1Grid.DataMember = "cart"

        End If
    End Sub

Take a read of this OleDbCommand.Parameters Property for some guidance on Parameters and OleDb based clients — JayV
– JayV, Commented Jul 6, 2018 at 11:29
Given that you are using parameters, the most likely cause seems to be use of a reserved word. Try wrapping your column and table names in brackets. — jmcilhinney
– jmcilhinney, Commented Jul 6, 2018 at 12:01

dbmitch · Accepted Answer · 2018-07-06 18:38:48Z

1

No need to post all of your code when you can just post the error line and variables used in the affected statement. The rest just confuses

As commenter @jmcilhinney says - look for reserved words and wrap in brackets

Assuming you've spelled TransactID correctly as TransctID, then in your case the obvious choice is the fieldname Time. Wrap it in square brackets

Sql = "Insert Into Transactions (TransctID,TotalPrice,Username,[Time],Discount) Values(@TranID,@price,@uname,@time,@Dsc)"

answered Jul 6, 2018 at 18:38

dbmitch

5,3964 gold badges27 silver badges39 bronze badges

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

Comments

ASH · Accepted Answer · 2018-07-06 12:12:57Z

How about this?

Insert Into MS Access:

Imports System.Data.OleDb

Public Class Form1

    Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb;"
    Private NewIdentifer As Integer = 0
    Private InsertStatement As String = "INSERT INTO Employee (LName) Values(@LName)"
    Private IdentifierStatement As String = "Select @@Identity"

    'Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Using cn As New OleDbConnection(ConnectionString)
            Using cmd As New OleDbCommand("SELECT * FROM Employee", cn)
                Dim dt As New DataTable
                cn.Open()
                Dim Reader As OleDbDataReader = cmd.ExecuteReader()
                dt.Load(Reader)
                Dim dv = dt.DefaultView
                DataGridView1.DataSource = dv
            End Using
        End Using
    End Sub

    'End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        If Not String.IsNullOrEmpty(txtLastName.Text) Then
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand(InsertStatement, cn)
                    cmd.Parameters.AddWithValue("@LName", txtLastName.Text)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = IdentifierStatement
                    NewIdentifer = CInt(cmd.ExecuteScalar())
                    Dim Row As DataRowView = CType(DataGridView1.DataSource, DataView).AddNew
                    Row("Fname") = NewIdentifer
                    Row("LName") = txtLastName.Text
                    Row.EndEdit()
                    DataGridView1.CurrentCell = DataGridView1(0, DataGridView1.RowCount - 1)
                    txtLastName.Text = ""
                End Using
            End Using
        Else
            MsgBox("Please enter a name")
        End If

    End Sub
End Class

Insert Into MS Access from DataGridView:

Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1
    Dim myDA As OleDbDataAdapter
    Dim myDataSet As DataSet

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\Ryan\Desktop\Coding\Microsoft Access\Powerful Access Files\Nwind.mdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Customers", con)
        con.Open()
        myDA = New OleDbDataAdapter(cmd)
        'Automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)

        myDataSet = New DataSet()

        myDA.Fill(myDataSet, "MyTable")
        DataGridView2.DataSource = myDataSet.Tables("MyTable").DefaultView

        con.Close()
        con = Nothing
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Validate()
        Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
    End Sub

End Class

Collectives™ on Stack Overflow

"syntax error in INSERT INTO" ms-access vb.net [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related