Well Cerebrus, your solution solved my problem! It appears the Syntax Error was the conflict of my field name with the reserved name. And command builder has still to mature to handle those issues.
I see ADO.NET is powerful but it just took me such a long while to reach to this point that i developed a hatred for it. Well now finally the code is working, i can try and give a fresh look at ADO.NET. Thanks ! You're a good bug analyst ! :) On Mar 27, 1:04 am, Cerebrus <[email protected]> wrote: > <Question style="Joker"> > Why so seriousssssss? > </Question> > > I find that you are unnecessarily frustrated and seem to be ranting at > something which has no fault in the matter. You raise many issues in > your post and I'll try to address them all. Eventually, I will tell > you how to solve the problem > > 1. There is nothing complex about the code. In fact, you have a very > straightforward bit of code, right out of ADO.NET 101. > > 2. The objects you are using are profoundly powerful compared to the > puny Recordset. I see that you have called only two useful methods > (not counting constructors). All the other method calls are > unnecessary. > > 3. I don't know if the guy who gave you the suggestion to use manual > SQL statements instead of a CommandBuilder was wise or not, but his > advice was very sound. It concurs with what I have always preached. > And if I were you, I would indeed not be using either the DataSet or > the DataAdapter if my only intention was to run an INSERT. There is > much overhead involved in their use (which is a tradeoff with their > power). An SqlCommand would be the way to go. > > <Answer style="Joker"> > Let's put a smile on that face. > </Answer> > > 4. Coming to what's wrong with your code... the answer is nothing. The > problem lies with MS Access. To be precise, the word "Size" is a > reserved keyword and you cannot use it in an SQL statement without > using the square braces. Since the CommandBuilder isn't smart enough > to know this (It's an OleDb one, not one specific to MS Access), your > autogenerated INSERT query *does* have syntax errors. Try changing the > column name or setting the DataAdapter.InsertStatement manually to > something like: > > "INSERT INTO Files ([FilePath], [Size]) VALUES (?, ?)" > > 5. What's the point of ADO.NET anyway ? No, I'm not qualified to > answer that question. Not in a forum post, in any case ! > > On Mar 26, 5:17 pm, Faraz Azhar <[email protected]> wrote: > > > > > Hello > > > I'm totally lost in ADO.NET ! I'm very experienced with VB6 + VB.NET > > along with ADO. But now im trying to develop code for ADO.NET using > > VB.NET working with Access 2007 database. > > > My simplest goal is to add a row to my table. It's giving me a > > headache. This is my code: > > > Dim conString As String = "PROVIDER=Microsoft.Ace.OLEDB.12.0;Persist > > Security Info=False; Data Source=C:\DB.Accdb" > > Dim con As OleDbConnection = New OleDbConnection(conString) > > > con.Open() > > > Dim dAdapter = New OleDbDataAdapter("SELECT * FROM [Files]", con) > > Dim dSet As New DataSet > > dAdapter.Fill(dSet) > > > Dim dTable As DataTable = dSet.Tables(0) > > > Dim cb As New OleDb.OleDbCommandBuilder(dAdapter) > > > Dim dRow As DataRow = dTable.NewRow() > > dRow("FilePath") = "C:\MyFile.txt" > > dRow("Size") = "1024" > > dTable.Rows.Add(dRow) > > > dAdapter.Update(dSet.GetChanges) > > dSet.AcceptChanges() > > > con.Close() > > > --- > > The above code gives me error "Syntax error in INSERT INTO statement." > > I tried to check the commandbuilder's commandtext property, it showed > > me this: > > "INSERT INTO Files (FilePath, Size) VALUES (?, ?)" > > > I cant believe how complex microsoft made this thing ! Earlier it was > > so easy to obtain a recordset and add rows and then call the update. > > Now I have to declare so many objects and call so many methods but > > still no success. > > > Some guy told me to use manual sql statements in > > dAdapter.InsertCommand instead of commandbuilder. My question is ... > > WHY!!? If im using manual statement to update my database, why did I > > even have to create a new row, assign data to it and add it to table. > > I couldve run a simple INSERT sql to add my data to database. > > > What is wrong with me code ? Why is it not working. I don't want to > > use manual sql for adding my rows to database.. i mean whats to point > > of ADO.NET anyway if manual is the right solution. To unsubscribe from this group, send email to dotnetdevelopment+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
