Error updating access db

Code:

---

Dim dt As New DataTable 

02        Dim ds As New DataSet 

03   

04        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Test.accdb"

05   

06        con.Open() 

07        MsgBox("here") 

08        ds.Tables.Add(dt) 

09   

10        Dim da As New OleDbDataAdapter 

11   

12        da = New OleDbDataAdapter("SELECT * FROM Table1", con) 

13   

14        da.Fill(dt) 

15   

16   

17        Dim newRow As DataRow = dt.NewRow 

18   

19        newRow.Item(1) = Item1 

20        newRow.Item(2) = Item2 

21        newRow.Item(3) = Item3 

22        newRow.Item(4) = Item4 

23        newRow.Item(5) = Item5 

24        newRow.Item(6) = Item6 

25        newRow.Item(7) = Item7 

26        newRow.Item(8) = Item8 

27        newRow.Item(9) = Item9 

28        newRow.Item(10) = Item10 

29        newRow.Item(11) = Item11 

30        newRow.Item(12) = Item12 

31        newRow.Item(13) = Item13 

32        newRow.Item(14) = Item14 

33        newRow.Item(15) = Item15 

34        newRow.Item(16) = Item16 

35        newRow.Item(17) = Item17 

36        newRow.Item(18) = Item18 

37        newRow.Item(19) = Item19 

38        newRow.Item(20) = Item20 

39        newRow.Item(21) = Item21 

40        newRow.Item(22) = Item22 

41        newRow.Item(23) = Item23 

42        newRow.Item(24) = hms 

43        newRow.Item(25) = ymd 

44        newRow.Item(26) = SingleElapsedTime.Text 

45   

46   

47        dt.Rows.Add(newRow) 

48        Dim cb As New OleDbCommandBuilder(da) 

49        da.Update(dt) 

50        MsgBox("Record successfully saved", vbInformation) 

51        con.Close()

---

I am getting a error at da.Update(dt) and I am unsure why as I ran it with about 10 items and it ran perfect very unsure why now. Here is the error. OleDbException was unhandled : Syntax error in INSERT INTO statement. Thanks for any advice or help.

i think it is because in your sql statement i dont see in your code that you have an update statement

Please explain this is my first time ever doing this and i've kinda just peiced everything together

You've got SELECT * FROM Table1. You don't provide us with field names since you're assigning them by ordinal value but as a guess I'd assume one of your columns is named using a reserved word in Access. Explicitly type out the fields in your select query and put the offending fields in brackets to for them to be interpreted as an identifier.

Code:

---

SELECT UserName, [Password] FROM Table1

---

Code:

---

        newRow.Item("Caller") = Caller
        newRow.Item("Associate Name") = Associate
        newRow.Item("Store Number") = "1"
        newRow.Item("Number of Rings") = Ring
        newRow.Item("Time on Hold") = HoldTime
        newRow.Item("Greeting: 3 or fewer rings") = GreetingRings
        newRow.Item("Greeting: Asked for your name") = GreetingAskName
        newRow.Item("Greeting: Offered his/her name") = GreetingOfferedName
        newRow.Item("Greeting: Mentioned TIRE PROS in the greeting") = GreetingTirePros
        newRow.Item("Greeting: Associated acted like they are glad") = GreetingGlad
        newRow.Item("Hold for longer than 1 minute") = holdUpdate
        newRow.Item("Ask for the type of car AND look up the size") = LookupSize
        newRow.Item("Ask appropriate questions about the type of driving") = DailyDriving
        newRow.Item("1st Price Mentioned") = SingleTirePrice
        newRow.Item("1st OTD Price Mentioned") = SingleTireOutDoorPrice
        newRow.Item("Tire Brand") = TireBrand
        newRow.Item("Tire Model") = TireModel
        newRow.Item("Offered several tire choices and prices") = SeveralChoices
        newRow.Item("Did they offer financing options") = Financing
        newRow.Item("Mentioned benefits of the location") = Benefits
        newRow.Item("Appointment") = Appointment
        newRow.Item("How long does it take to put them on") = InstallTime
        newRow.Item("Associate Score") = AssociateScore
        newRow.Item("Time Completed") = hms
        newRow.Item("Completed Date") = ymd

---

I have everything named here and I had an issue with a reserved word at first but I already fixed that at my Date.

I count 25 columns and you're attempting to feed them 26 values?

I deleted Item(26)

Ok. So now, unless you have an autonumber in column 0, subtract 1 from all the index values and we might be getting somewhere.

I have an autonumber and i let access handle that. Haven't had a problem with that. Like i said in begining with only 10 columns it was running okay for a "test" run. The only thing i can think of is that its somewhere in either the column name or... maybe the data type, but from what I understand any data can go into a text column right?

Well not 'anything'. It has to be possible to represent it as text in some way.

What I would suggest you do to check is feed the new values to a datagridview control rather than attempt to update the database so you can check whether the items are in the order and places you expect them to be.

Since you said it was working with only 10 columns then for testing I would suggest putting a break point at the "Update" call and check the values that you are assigning all these fields. Also make sure that the type of variable and field match, like you can't assign a string to a numeric. In the project properties under "Compile" set "Option Strict" on. This is always a good idea.

btw - are those really you field names? That could make your life really difficult. IMHO

You might want to look at the SQL that the CommandBuilder is generating. One thing I note is that lots of your fields have spaces, which means that they have to be wrapped in square braces, too. That should be handled right, but is it? Getting a look at the SQL generated from the CommandBuilder isn't necessarily obvious. You might think that the da.UpdateCommand would have that, but it doesn't. I think you would need to use the CommandBuilder.GetUpdateCommand.CommandText to see the SQL being generated. That SQL would likely be somewhat instructive.

Thank you both i will try both and update.

I changed the source table and didn't rename the fields just kept them Field1, Field2, etc. and it works fine. I think you both are right, the field names were the issue I appreciate the help! Thanks