Error on DataAdapter Update

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

I'm having a problem updating a database with a datatable. (I'm using
VB.NET 2003 Standard.) I can't figure out the problem, because it's the
same method I've used for tons of other updates that work fine every time.
I create the oledb dataadapter by dragging the table from Server Explorer
onto the form. Then I right-click the adapter and generate the dataset.
This is a sample of the code I use to add a new record to the dataset and
then update:

\\
Dim tblTransactions as DataTable = datDataSet.Transactions

Dim frm as new frmNewTransaction
With frm
.ShowDialog
Dim NewTransaction As DataRow = tblTransactions.NewRow
NewTransaction("Transaction") = .cboTransaction.SelectedItem.ToString
If .txtCheck.Text = "" Then
NewTransaction("CheckNumber") = 0
Else
NewTransaction("CheckNumber") = CInt(.txtCheck.Text)
End If
NewTransaction("EntryDate") = .dtpDate.Value
NewTransaction("Amount") = CDbl(.txtAmount.Text)
NewTransaction("Description") = .txtDescription.Text
NewTransaction("Balanced") = False
tblTransactions.Rows.Add(NewTransaction)
End With

adpTransactions.Update(tblTransactions)
\\

I get the following error when the update method tries to execute: "Syntax
error in INSERT INTO statement"

This is the insert statement generated by the designer:
"INSERT INTO Transactions (Amount, Balanced, CheckNumber, Description,
EntryDate, Transaction) VALUES (?, ?, ?, ?, ?, ?)"

The Transactions table in the dataset has the following columns:

Amount - double
Balanced - boolean
CheckNumber - int
Description - string
EntryDate - dateTime
RegisterID - int (Primary Key)
Transaction - string

Can anyone figure out my problem? Let me know if you need any more
information.

Thanks,
Nathan
 
I expect it's because you're using "Transaction" as a column--it's a
reserved word. Use "[Transaction]" or change the name.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Aaah! I was thinking it might have something to do with a reserved word,
but I'm not familiar with what words are reserved. Thanks for the hint!

Nathan

William (Bill) Vaughn said:
I expect it's because you're using "Transaction" as a column--it's a
reserved word. Use "[Transaction]" or change the name.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Nathan said:
I'm having a problem updating a database with a datatable. (I'm using
VB.NET 2003 Standard.) I can't figure out the problem, because it's the
same method I've used for tons of other updates that work fine every time.
I create the oledb dataadapter by dragging the table from Server Explorer
onto the form. Then I right-click the adapter and generate the dataset.
This is a sample of the code I use to add a new record to the dataset and
then update:

\\
Dim tblTransactions as DataTable = datDataSet.Transactions

Dim frm as new frmNewTransaction
With frm
.ShowDialog
Dim NewTransaction As DataRow = tblTransactions.NewRow
NewTransaction("Transaction") = .cboTransaction.SelectedItem.ToString
If .txtCheck.Text = "" Then
NewTransaction("CheckNumber") = 0
Else
NewTransaction("CheckNumber") = CInt(.txtCheck.Text)
End If
NewTransaction("EntryDate") = .dtpDate.Value
NewTransaction("Amount") = CDbl(.txtAmount.Text)
NewTransaction("Description") = .txtDescription.Text
NewTransaction("Balanced") = False
tblTransactions.Rows.Add(NewTransaction)
End With

adpTransactions.Update(tblTransactions)
\\

I get the following error when the update method tries to execute: "Syntax
error in INSERT INTO statement"

This is the insert statement generated by the designer:
"INSERT INTO Transactions (Amount, Balanced, CheckNumber, Description,
EntryDate, Transaction) VALUES (?, ?, ?, ?, ?, ?)"

The Transactions table in the dataset has the following columns:

Amount - double
Balanced - boolean
CheckNumber - int
Description - string
EntryDate - dateTime
RegisterID - int (Primary Key)
Transaction - string

Can anyone figure out my problem? Let me know if you need any more
information.

Thanks,
Nathan
Click to expand...
Click to expand...
 
It's not always easy to know. They keep adding more all the time. If you
notice, the MS wizards surround all of the column names in brackets to
eliminate this problem. The list of reserved words is in BOL.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Nathan said:
Aaah! I was thinking it might have something to do with a reserved word,
but I'm not familiar with what words are reserved. Thanks for the hint!

Nathan

William (Bill) Vaughn said:
I expect it's because you're using "Transaction" as a column--it's a
reserved word. Use "[Transaction]" or change the name.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Nathan said:
I'm having a problem updating a database with a datatable. (I'm using
VB.NET 2003 Standard.) I can't figure out the problem, because it's the
same method I've used for tons of other updates that work fine every time.
I create the oledb dataadapter by dragging the table from Server Explorer
onto the form. Then I right-click the adapter and generate the dataset.
This is a sample of the code I use to add a new record to the dataset and
then update:

\\
Dim tblTransactions as DataTable = datDataSet.Transactions

Dim frm as new frmNewTransaction
With frm
.ShowDialog
Dim NewTransaction As DataRow = tblTransactions.NewRow
NewTransaction("Transaction") = ..cboTransaction.SelectedItem.ToString
If .txtCheck.Text = "" Then
NewTransaction("CheckNumber") = 0
Else
NewTransaction("CheckNumber") = CInt(.txtCheck.Text)
End If
NewTransaction("EntryDate") = .dtpDate.Value
NewTransaction("Amount") = CDbl(.txtAmount.Text)
NewTransaction("Description") = .txtDescription.Text
NewTransaction("Balanced") = False
tblTransactions.Rows.Add(NewTransaction)
End With

adpTransactions.Update(tblTransactions)
\\

I get the following error when the update method tries to execute: "Syntax
error in INSERT INTO statement"

This is the insert statement generated by the designer:
"INSERT INTO Transactions (Amount, Balanced, CheckNumber, Description,
EntryDate, Transaction) VALUES (?, ?, ?, ?, ?, ?)"

The Transactions table in the dataset has the following columns:

Amount - double
Balanced - boolean
CheckNumber - int
Description - string
EntryDate - dateTime
RegisterID - int (Primary Key)
Transaction - string

Can anyone figure out my problem? Let me know if you need any more
information.

Thanks,
Nathan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top