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
Re: VB.net & Microsoft Access - Adding a record problem 80 80

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

commented: Grear help, thanks! +0
commented: nice teaching. +8
Re: VB.net & Microsoft Access - Adding a record problem 80 80

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!

Re: VB.net & Microsoft Access - Adding a record problem 80 80

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.

commented: Thankyou +0
Re: VB.net & Microsoft Access - Adding a record problem 80 80

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

Re: VB.net & Microsoft Access - Adding a record problem 80 80

Impressive I must say, you have explained it really well. Great job.

Re: VB.net & Microsoft Access - Adding a record problem 80 80

I have a problem writing back to my Access Database as well. I have Access 2007 with Visual Basic 2019
I have a form (PrinterFrontend) which is attached to my Access Database (Client_Table) and I have a Combobox on my form so that I can see all the clients in the database. When I select a client their details are then visible on the form text boxes (that works as expected).
I want to add more clients to the database. I don't want to do this on my current form (PrinterFrontend) so I have made another form (Input Data) which stands alone and is not attached to my Access Database (Client_Table) .
The following code is attached to a button on my Input Data Form and supposed to enter my new Client to the database (Client_table) through the form (PrinterFrontend):
PrinterFrontend.Client_TableTableAdapter.Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text, TextBox9.Text)
PrinterFrontend.Client_TableTableAdapter.Fill(PrinterFrontend.LabelsDataSet.Client_Table)

What's happening is the new Client information is appearing with the other client information on the form (PrinterFrontend) but at the bottom of my Client list on the Combobox and not being sorted alphabetically as I would expect. When I select the new client their info is visible in the textboxes on the form (PrinterFrontend). I can add other new clients and they will appear on the form (PrinterFrontend).
If I close the my prodject and then reopen it again I have lost all the new clients I previously entered. Looking at the "Table_Form" in the database there is no sign of the Clients or ther information.
Can you please help me?
Thanks, Eric

Re: VB.net & Microsoft Access - Adding a record problem 80 80

Sorry!! Named the Database Table incorrectly.
If I close the my prodject and then reopen it again I have lost all the new clients I previously entered. Looking at the "Client_Table" in the database there is no sign of the Clients or ther information.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.