charlieb01
asked on
Trouble inserting rows into Access table
Hi,
I am having a problem inserting a new row into a MS Access table
At the form level I have this code:
' constr is the connection string for the system database
' constr is defined in ProjConst.vb code
Dim conn As New OleDb.OleDbConnection(cons tr)
Dim qryJunk = "SELECT * FROM JunkTable"
Dim daJunk As New OleDb.OleDbDataAdapter(qry Junk, conn)
Dim cbJunk As New OleDb.OleDbCommandBuilder( daJunk)
Dim dtJunk As New DataTable
Dim drJunk As DataRow
Then in a sub that is called every 5 seconds by a timer i have this code:
Sub fillMeasurementTbl()
Dim junk
Static counter
daJunk.Fill(dtJunk)
counter = counter + 1
drJunk = dtJunk.NewRow
drJunk.Item("counter") = counter
drJunk.Item("junk1") = "sampleString1"
drJunk.Item("junk2") = "sampleString1"
dtJunk.Rows.Add(drJunk)
Try
daJunk.Update(dtJunk)
Catch ex As Exception
Debug.WriteLine(ex.ToStrin g)
End Try
End Sub
When I run the Exception code is:
System.Data.OleDb.OleDbExc eption: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataA dapter.Upd ate(DataRo w[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataA dapter.Upd ate(DataTa ble dataTable)
at WindowsApplication1.frmTes tDisp.fill Measuremen tTbl() in C:\tescor XX-XXXX-1\frmTestDisp.vb:l ine 306
What am I doing wrong?
Thanks,
Charlie
I am having a problem inserting a new row into a MS Access table
At the form level I have this code:
' constr is the connection string for the system database
' constr is defined in ProjConst.vb code
Dim conn As New OleDb.OleDbConnection(cons
Dim qryJunk = "SELECT * FROM JunkTable"
Dim daJunk As New OleDb.OleDbDataAdapter(qry
Dim cbJunk As New OleDb.OleDbCommandBuilder(
Dim dtJunk As New DataTable
Dim drJunk As DataRow
Then in a sub that is called every 5 seconds by a timer i have this code:
Sub fillMeasurementTbl()
Dim junk
Static counter
daJunk.Fill(dtJunk)
counter = counter + 1
drJunk = dtJunk.NewRow
drJunk.Item("counter") = counter
drJunk.Item("junk1") = "sampleString1"
drJunk.Item("junk2") = "sampleString1"
dtJunk.Rows.Add(drJunk)
Try
daJunk.Update(dtJunk)
Catch ex As Exception
Debug.WriteLine(ex.ToStrin
End Try
End Sub
When I run the Exception code is:
System.Data.OleDb.OleDbExc
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
at WindowsApplication1.frmTes
What am I doing wrong?
Thanks,
Charlie
I built a small application to try and duplicate your error. I am using OLDB, but I am using an SQL database. Here is my code:
Dim dajunk As OleDb.OleDbDataAdapter
Dim drjunk As DataRow
Dim dtjunk As DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As New OleDb.OleDbConnection("com mented out")
Dim qryJunk = "SELECT * FROM table3"
dajunk = New OleDb.OleDbDataAdapter(qry Junk, conn)
Dim cbJunk As New OleDb.OleDbCommandBuilder( daJunk)
dtjunk = New DataTable
fillMeasurementTbl()
fillMeasurementTbl()
fillMeasurementTbl()
End Sub
Sub fillMeasurementTbl()
Static counter As Integer
daJunk.Fill(dtJunk)
counter = counter + 1
drJunk = dtJunk.NewRow
drjunk.Item("col1") = counter
drjunk.Item("col2") = "sample"
drjunk.Item("col3") = "sample"
dtJunk.Rows.Add(drJunk)
Try
daJunk.Update(dtJunk)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
I was unable to reproduce your error. Not sure if it helps, but thought I'd post it just in case.
Dim dajunk As OleDb.OleDbDataAdapter
Dim drjunk As DataRow
Dim dtjunk As DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As New OleDb.OleDbConnection("com
Dim qryJunk = "SELECT * FROM table3"
dajunk = New OleDb.OleDbDataAdapter(qry
Dim cbJunk As New OleDb.OleDbCommandBuilder(
dtjunk = New DataTable
fillMeasurementTbl()
fillMeasurementTbl()
fillMeasurementTbl()
End Sub
Sub fillMeasurementTbl()
Static counter As Integer
daJunk.Fill(dtJunk)
counter = counter + 1
drJunk = dtJunk.NewRow
drjunk.Item("col1") = counter
drjunk.Item("col2") = "sample"
drjunk.Item("col3") = "sample"
dtJunk.Rows.Add(drJunk)
Try
daJunk.Update(dtJunk)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
I was unable to reproduce your error. Not sure if it helps, but thought I'd post it just in case.
BTW, my table looks like this:
CREATE TABLE [Table3] (
[col1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[col2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[col3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [Table3] (
[col1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
[col2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
[col3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
GO
ASKER
Hi,
I modified my code from the original to the following using your example. BTW, my "JunkTable" has the following fields:
Counter - this is PK type is Integer
junk1 - type is text
junk2 - type is text
When I run this I get this error:
System.Data.OleDb.OleDbExc eption: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataA dapter.Upd ate(DataRo w[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataA dapter.Upd ate(DataTa ble dataTable)
at WindowsApplication1.frmTes tDisp.fill Measuremen tTbl() in C:\tescor XX-XXXX-1\frmTestDisp.vb:l ine 333
Charlie
Below is my modified code per your comments:
At the Form Level:
Dim daJunk As OleDb.OleDbDataAdapter
Dim drJunk As DataRow
Dim dtJunk As DataTable
At Form_Load:
Dim conn As New OleDb.OleDbConnection(cons tr)
Dim qryJunk = "SELECT * FROM JunkTable"
daJunk = New OleDb.OleDbDataAdapter(qry Junk, conn)
Dim cbJunk As New OleDb.OleDbCommandBuilder( daJunk)
dtJunk = New DataTable
In the sub that is called every 5 seconds:
Sub fillMeasurementTbl()
Static counter As Integer
daJunk.Fill(dtJunk)
counter = counter + 1
drJunk = dtJunk.NewRow
drJunk.Item("Counter") = counter
drJunk.Item("junk1") = "sampleString1"
drJunk.Item("junk1") = "sampleString2"
dtJunk.Rows.Add(drJunk)
Try
daJunk.Update(dtJunk)
Catch ex As Exception
Debug.WriteLine(ex.ToStrin g)
End Try
End Sub
I modified my code from the original to the following using your example. BTW, my "JunkTable" has the following fields:
Counter - this is PK type is Integer
junk1 - type is text
junk2 - type is text
When I run this I get this error:
System.Data.OleDb.OleDbExc
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
at WindowsApplication1.frmTes
Charlie
Below is my modified code per your comments:
At the Form Level:
Dim daJunk As OleDb.OleDbDataAdapter
Dim drJunk As DataRow
Dim dtJunk As DataTable
At Form_Load:
Dim conn As New OleDb.OleDbConnection(cons
Dim qryJunk = "SELECT * FROM JunkTable"
daJunk = New OleDb.OleDbDataAdapter(qry
Dim cbJunk As New OleDb.OleDbCommandBuilder(
dtJunk = New DataTable
In the sub that is called every 5 seconds:
Sub fillMeasurementTbl()
Static counter As Integer
daJunk.Fill(dtJunk)
counter = counter + 1
drJunk = dtJunk.NewRow
drJunk.Item("Counter") = counter
drJunk.Item("junk1") = "sampleString1"
drJunk.Item("junk1") = "sampleString2"
dtJunk.Rows.Add(drJunk)
Try
daJunk.Update(dtJunk)
Catch ex As Exception
Debug.WriteLine(ex.ToStrin
End Try
End Sub
ASKER
Hi,
Does anyone have any ideas here? I am really stuck on this problem.
Thanks,
Charlie
Does anyone have any ideas here? I am really stuck on this problem.
Thanks,
Charlie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi charlieb01;
Thanks to Roger you can change your form load to this and that should now be able to inset the new row into the database.
Dim conn As New OleDb.OleDbConnection(cons tr)
Dim qryJunk = "SELECT * FROM JunkTable"
daJunk = New OleDb.OleDbDataAdapter(qry Junk, conn)
Dim cbJunk As New OleDb.OleDbCommandBuilder( daJunk)
cbJunk.QuotePrefix = "["
cbJunk.QuoteSuffix = "]"
dtJunk = New DataTable
In the subroutine fillMeasurementTbl you should add a line to accept changes to the db, something like this.
Try
daJunk.Update(dtJunk)
dtJunk.AcceptChanges()
Catch ex As Exception
You have one more problem that I can see but not being a db programmer I do not know how to get it done and that is getting the last record added and getting the value from Counter so that you can set the value of counter so that you do not try to add a record with the same value.
Fernando
Thanks to Roger you can change your form load to this and that should now be able to inset the new row into the database.
Dim conn As New OleDb.OleDbConnection(cons
Dim qryJunk = "SELECT * FROM JunkTable"
daJunk = New OleDb.OleDbDataAdapter(qry
Dim cbJunk As New OleDb.OleDbCommandBuilder(
cbJunk.QuotePrefix = "["
cbJunk.QuoteSuffix = "]"
dtJunk = New DataTable
In the subroutine fillMeasurementTbl you should add a line to accept changes to the db, something like this.
Try
daJunk.Update(dtJunk)
dtJunk.AcceptChanges()
Catch ex As Exception
You have one more problem that I can see but not being a db programmer I do not know how to get it done and that is getting the last record added and getting the value from Counter so that you can set the value of counter so that you do not try to add a record with the same value.
Fernando
ASKER
Thanks Roger.
I guess I should get myself a list of Access reserved words :)
Maybe you will be stumped by my next question about using Date & Time as PK in an Access table!
Charlie
I guess I should get myself a list of Access reserved words :)
Maybe you will be stumped by my next question about using Date & Time as PK in an Access table!
Charlie
I am not a database programmer but if I am not mistaken in order for the following command:
Dim cbJunk As New OleDb.OleDbCommandBuilder(
to build the queries correctly the table must have a primary key defined. So if you have not done so already add a primary key to the table in the database.
Fernando