Error updating access db

[jbailey01]

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.

[marniel647]

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

[jbailey01]

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

[MattP]

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.

SELECT UserName, [Password] FROM Table1

[jbailey01]

        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.

[dunfiddlin]

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

[jbailey01]

I deleted Item(26)

[dunfiddlin]

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

[jbailey01]

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?

[dunfiddlin]

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.

[wes4dbt]

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

[Shaggy Hiker]

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.

[jbailey01]

Thank you both i will try both and update.

[jbailey01]

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