Insert in to Access Database

cabrio

New member
Joined
Feb 6, 2012
Messages
2
Programming Experience
Beginner
I need some help here...pulling out my hair...

what happens is that I can update, view, edit and search all my records, however, when I want to add a new record, the below executes perfectly, but the record never shows in the database.

Access 97 database

VS2010

here is my code...

Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Inventory").NewRow()
With dsNewRow
con.Open()
.Item("DateAdded") = txtDateAdded.Text
.Item("StockNum") = txtStockNum.Text
.Item("SerialNum") = txtSerialNum.Text
.Item("Manufacturer") = txtManufacturer.Text
.Item("Model") = txtModel.Text
.Item("Category") = cmbCategory.Text
.Item("Price") = Convert.ToDouble(Mid(txtPrice.Text, 2, (Len(txtPrice.Text) - 1)))
.Item("Qty") = txtQty.Text
.Item("Status") = cmbStatus.Text
.Item("Description") = txtDescription.Text
.Item("Hours") = Convert.ToInt32(txtHours.Text.Trim())
.Item("Year") = txtYear.Text
.Item("Pictures") = Convert.ToInt32(cmbPictures.Text.Trim())
.Item("HP") = Convert.ToInt32(txtHP.Text.Trim())
.Item("Available") = chkAvailable.Checked
.Item("ForRent") = chkForRent.Checked
.Item("ForSale") = chkForSale.Checked
.Item("Consignment") = chkConsignment.Checked
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("Inventory").Rows.Add(dsNewRow)
da.Fill(ds)
ds.AcceptChanges()
da.Update(ds, "Inventory")
con.Close()
MsgBox("New Record added to the Database")


Alos found out if I comment out the "ds.AcceptChanges()"
then I recieve a Syntax error in INSERT INTO statement. on this line - da.Update(ds, "Inventory")
if that helps anyone answer this

 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
You don't need to call AcceptChanges at all but, if you ever do call it, it would NEVER be BEFORE calling Update. AcceptChanges says that all changes have been accepted, but if you haven't saved them yet then they can't have been accepted. The call to Update implicitly calls AcceptChanges AFTER all the changes have been successfully saved.

The most likely reason for the syntax error is a column name that is a reserved word. Put this line before your Update call:
Messagebox.Show(cb.GetInsertCommand().CommandText)
to see what your actual SQL looks like. Now set the QuotePrefix and QuoteSuffix properties of the command builder to "[" and "]" respectively and run it again and see how that changes the code to get around that issue.
 

cabrio

New member
Joined
Feb 6, 2012
Messages
2
Programming Experience
Beginner
Thanks for the command to print the current query. Figured it out last night, and it was exactly what you said. My feild "Year" is a reserved word I guess...duh. Changed it to "Yr" and all is flowing perfectly now.\
Thanks to jmcilhinney.
 
Top Bottom