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