Hi all,

I've been having a problem adding a record to an Access database using datasets and da.update etc.

I'm trying to create a simple program that displays the records of a database table in a series of text boxes. I've managed to get my next and previous buttons working correctly and my update button works correctly too.

The problem occurs when I try to add a new record. I keep getting a "Syntax error in INSERT INTO statement" whenever I click my add button.

My database table has JobID(number), ElectricianID(number), CustomerID(number), Price(number), Date(date), Time(time), Paid(yes/no)

Eventually I will have other related tables with a similar sort of structure.

I know I could use SQL, but datasets are needed for this problem.

I would be grateful for any suggestions.

Here is my code

Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim sql As String = "SELECT * FROM Jobs"
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, con)
    Dim MaxRows As Integer = 0
    Dim CurrentRecord As Integer = 0

    Private Sub DisplayRecord()
        txtJobID.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(0)
        txtElectricianID.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(1)
        txtCustomerID.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(2)
        txtPrice.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(3)
        txtDate.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(4)
        txtTime.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(5)
        txtPaid.Text = ds.Tables("JobsTable").Rows(CurrentRecord).Item(6)
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AceElectricalDB.mdb"
        con.Open()
        da.Fill(ds, "JobsTable")
        con.Close()
        MaxRows = ds.Tables("JobsTable").Rows.Count
        DisplayRecord()
    End Sub


    Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNext.Click
        If CurrentRecord < MaxRows - 1 Then
            CurrentRecord = CurrentRecord + 1
            DisplayRecord()
        Else
            CurrentRecord = 0
            DisplayRecord()
        End If


    End Sub

    Private Sub cmdPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrev.Click
        If CurrentRecord = 0 Then
            CurrentRecord = MaxRows - 1
            DisplayRecord()
        Else
            CurrentRecord = CurrentRecord - 1
            DisplayRecord()
        End If
    End Sub

    Private Sub cmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNew.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("JobsTable").NewRow()
        dsNewRow.Item(0) = txtJobID.Text
        dsNewRow.Item(1) = txtElectricianID.Text
        dsNewRow.Item(2) = txtCustomerID.Text
        dsNewRow.Item(3) = txtPrice.Text
        dsNewRow.Item(4) = txtDate.Text
        dsNewRow.Item(5) = txtTime.Text
        dsNewRow.Item(6) = txtPaid.Text
        ds.Tables("JobsTable").Rows.Add(dsNewRow)
        da.Update(ds, "JobsTable")
        MsgBox("New Record added to the Database")

    End Sub

    Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(0) = txtJobID.Text
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(1) = txtElectricianID.Text
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(2) = txtCustomerID.Text
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(3) = txtPrice.Text
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(4) = txtDate.Text
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(5) = txtTime.Text
        ds.Tables("JobsTable").Rows(CurrentRecord).Item(6) = txtPaid.Text
        da.Update(ds, "JobsTable")
        MsgBox("Data updated")

    End Sub
End Class

First create a "insert" command.

Dim dbInsert As New OleDb.OleDbCommand

dbInsert.CommandType = CommandType.Text
dbInsert.Connection = con

Then create the parameters for your insert function.

dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "JobId"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "ElecId"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "CustId"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Price"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Date"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Time"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Paid"

dbInsert.CommandText = "INSERT INTO Table_YourTable VALUES (JobId ,ElecId, CustId, Price, Date, time, Paid)"

Now give the parameter items their values.

dbInsert.Parameters.Item("JobId").Value = txtJobID.Text
dbInsert.Parameters.Item("ElecId").Value = txtElectricianID.Text
dbInsert.Parameters.Item("CustId").Value = txtCustomerID.Text
dbInsert.Parameters.Item("Price").Value = txtPrice.Text
dbInsert.Parameters.Item("Date").Value = txtDate.Text
dbInsert.Parameters.Item("Time").Value = txtTime.Text
dbInsert.Parameters.Item("Paid").Value = txtPaid.Text

The following code will write the info to your database.

dbInsert.ExecuteNonQuery()

And also remember, I see you formated your columns in your database as number but your trying to write string values to them, this will not work rather format all your columns in your database as Text.

Hope this Helps

Edited 5 Years Ago by Animal Mother: Did not wrap my code sorry

Comments
nice teaching.
Grear help, thanks!

Thankyou very much - if I change all of my data types in my database to text it works.

I'm fairly new to this method of working with databases.

Is there a way I can maintain the data types from the text boxes?

I really do need to keep the number, date and time formats if possible.

Much thanks and appreciation!

IF you are seeking to learn database Please download the Sample notes of 5 Chepter from Kidwaresoftware. Visual Basic and databases is the name of the notes. Hope that these will give you good help.

Comments
Thankyou

Yes there is a way to keep them, format your date\time columns in your database to short date.
What you should do then is put in a date time picker on your form to insure the user can't use the wrong format when entering a date.
Use the Date\Time picker to get the value and make sure the value is Short date string.

Dim dtDate As Date = DateTimePicker1.Value.ToShortDateString

dtDate would be the value that you write to the database.

Remember to mark the thread solved whne it is please.

Enjoy

This question has already been answered. Start a new discussion instead.