ADO.NET/Access DB Insertion Exception

ADO.NET/Access DB Insertion Exception

• Alexandre Mineev MSFT

  David,

  Thank you for the suggestions. I've done those things a million times.

  The connection is good. I can read anything I want. Everything works well except updates and I've tried tons of permutations.

  I will impliment the suggestions you've made. I've had the brackets in earlier and I took them out. During the testing phase the the table name have universally been one word like "green" and "blue" and still fails.

  I will say this. I programmed in DAO for years and never had the slightest problem. I recognize a need to learn ADO.NET but this sql stuff is torturous.

  renee

  
  
  

• Ted Strom

   

  In this domain I don't know a lot! I'm a very experienced programmer and I've always avoided databases.

  This program does far more reading than writing and this is the first write that it does.

   

  But I've tried it with the command builder and without the select statement.

  When I try it without the Select Statement the exception is that I don't have one. When I don't have an insert command it gives me the xception that I reported earlier: "Syntax error in insert statement. When I add the insert statement, it does the same thing.

   Other command statements do not rely on the command builder they are hard coded.

  Pardon my silliness... but these SQL commands are just so alien to me.

   

  
  
  

• dborgohain

  I ran several tests today. I created a new database and ran your program.

  It worked exactly fine.

  Then I copied my table into it. At the update, I got the Insert error. Apparently the insert error comes from the existence of the fields having more complex data types OR not filling in all of the fields in the datarow prior to appending it.

  I'll continue to investigate this.

  
  
  

• Demchuk

  Hi

  The question marks in the command text are place holders for the values that you will assign your data too.

  However, I thought I would offer up a couple of ways to write data to your database. The first uses the CommandBuilder and the second uses the Command object alone which is a method I personally prefer but is not set in stone.

  The database I am writing to contains one table (Table1) with three fields (RecordNum, RecordType1 and Category). The RecordNum field is a primary key with AutoNumber set, the RecordType1 is a standard Text field and the Category field is a memo field.

  CommandBuilder Sample

  'Connection to database
  Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" _
  & "Data Source=C:\Temp\Test.mdb")

  Dim adapter As New OleDbDataAdapter("SELECT * FROM Table1", connection)
  Dim table As New DataTable

  'Populate the table with data from the SELECT statement.
  adapter.Fill(table)

  'Create a new CommandBuilder based on the DataAdapter.
  Dim builder As New OleDbCommandBuilder(adapter)

  'Create a new DataRow and assign it a new row from the DataTable.
  Dim row As DataRow = table.NewRow

  'Write values to the fields in the DataRow
  row("RecordType1") = "Test Record Type"
  row("Category") = "Test Category"

  'Add the DataRow to the table and use the adapter's update method to write the data
  'to the database.
  table.Rows.Add(row)
  adapter.Update(table)
  

  Command Sample

  'Connection to database
  Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" _
  & "Data Source=C:\Temp\Test.mdb")

  'Create a command object and assign the INSERT statement to the CommandText
  'property.
  Dim command As New OleDbCommand

  With command
  .CommandText = "INSERT INTO Table1 (RecordType1, Category) VALUES ('Value for Record Type 1', 'Value for Category')"
  .CommandType = CommandType.Text
  .Connection = connection

  'The ExecuteNonQuery method requires an open connection
  connection.Open()
  .ExecuteNonQuery() 'This method returns the number of rows affected if required
  connection.Close()
  End With
  

  HTH

  
  

• Sylvia Friedl

  Hi Renee

  I understand what you mean. I used to love DAO back in the VB3/4 days, then it was RDO then ADO and now ADO.NET. But too be truthful, ADO.NET I appreciate far more than I did RDO and ADO.

  Anyway, I have just been re-reading this thread and I am curious as to why you have opted to use the CommandBuilder. I'm not saying that you shouldn't it's just that it does impose a number of restrictions. Firstly, you must specify a SELECT statement for the CommandBuilder to work and the table that you are selecting data from must include a column with a primary key. Of course, most of your tables will utilise a primary key but if you are just needing to add a new record to a table or update a record, I see the extra step of specifying a SELECT command more of a hindrance.

  Have you looked into using the OleDbCommand object on it's own to execute SQL queries against your database You will probably find this more intuitive than trying to get the CommandBuilder to do the work for you and it will also give you more control.

  
  

• Josh Korn at Diligentsia

  David,

  I have a hunch that there is a problem in the table itself. Your program worked fine with a table created by Access. I've followed recommendations to create and intialize tables with ADOX.

  When I copied my table via cut and past via Access, your program fails.

  This leaves me to believe that the problem is not in the software ADO.NET code but with tables created by ADOX.

  I'm working on this now. I'm using tried and true software methodolgy. Simplify the problem and isolate it.

  I should have more information soon.

  
  
  

• NOX

  Hi

  Just a couple of suggestions that may or may not help.

  What is the value of table.TableName. If this table contains spaces then it will need to be enclosed within square brackets. In fact, I would be inclined to put the table within square brackets anyway to avoid the potential for error.

  I also noticed that there is no space between the closing parenthesis and the VALUES keyword. This probably won't cause a problem but there should still be a space there.

  "INSERT INTO [" & table.TableName & _

  "] (RecordNo,RecordType1,Category,Descriptor,Level,SubLevel,DocumentDescriptor,Document) " & _

  "VALUES (@RecordNo,@RecordType1,@Category,@Descriptor,@Level,@SubLevel,@DocumentDescriptor,@Document)")
  

  HTH

  
  

• Christopher Ireland

  Goodness,

  Rearranged some things

  I just read that the select command has to give issued to the adapter prior to the execution of the command builder, so I made these changes:

  Public Function InsertRecord(ByVal NewRow As DataRow, ByVal Table As DataTable, Optional ByVal Update As Boolean = True) As Boolean

  'works

  NewRow(DataRecords.ciRecordNum) = Table.Rows.Count ' Autoincrement

  'Table.Rows.InsertAt(NewRow, Table.Rows.Count)

  If Update Then

  Adapter.SelectCommand = New OleDbCommand("Select * from [" & Table.TableName & "]", con)

  Using CmdBuilder As New OleDbCommandBuilder(Adapter)

  Try

  GetCategoryCommand(Adapter, Table)

  con.Open()

  Adapter.Fill(Table)

  Table.Rows.Add(NewRow)

  Dim a As String = CmdBuilder.GetInsertCommand.CommandText()

  Adapter.Update(Table)

  con.Close()

  Dataset.AcceptChanges()

  Return True

  Catch e As Exception

  MsgBox(e.Message, MsgBoxStyle.Exclamation, "ADONET.InsertRecord")

  Return False

  End Try

  End Using

  Else

  Return True

  End If

  End Function

  Then I looked at my insert statement which looks like this.

  a = "INSERT INTO Bluish (RecordNo, RecordType1, Category, Descriptor, Level, Sublevel, DocumentDescriptor, Document) VALUES ( , , , , , , , )"

  

  
  
  

• cmazur

  Hi Renee

  What is the exact structure of the table you are trying to update Maybe you are using a reserved keyword as a field name. I don't believe you will get many problems with complex field types using Access save for BLOB fields.

  Did you have ago with the second example (using only the Command Object)

  
  

• Leonid B

  So I tried this.....

  Public Function InsertRecord(ByVal NewRow As DataRow, ByVal Table1 As DataTable, Optional ByVal Update As Boolean = True) As Boolean

  Dim adapter As New OleDbDataAdapter("Select * from [" & Table1.TableName & "]", con)

  Dim table As New DataTable

  adapter.Fill(table)

  Dim builder As New OleDbCommandBuilder(adapter)

  Dim row As DataRow = table.NewRow

  table.Rows.Add(row)

  adapter.Update(table)

  End Function

  It's just about exactly what you did. The connection is already open. I could not use my own prefilled in data row because there was an exception "Datarow belongs to another table". yet it's exactly the same table as I have here.

  So ok, I allocated a new datarow and did not fill it in. I just allocated it.

  At the update, I got exactly the same error. "Syntax error on insert satement."

  
  
  

• Alexander Stevenson

  Hi David,

  I'm using command builder because I am required to write a record.

  I will never do a query on this database. When I get it so I can write, I intend to use it exactly as I did with DAO, record by record.  It's a database for documents eactly as we are writing now. It will have no relationships exactly the hierachy of the database itself.  I'll read it record by record. But I don't have any intention of doing SQL anything. I'm just doing this to use ADO.NET and it does have some really nice features in memory. It's just that doing actually IO is a bear.

  All of my tables have a primary keys and they can be clearly seen in Access. The primary Key is the first field which is Recordnum. I'll never use the primary key but it's there for the benefit of the architecture.

  If you'll notice the code does use a select statement prior to the call to the construction of the Insert command.

  I am curious about a couple of things as far as datatypes are concerned. Other than the memo datatype used by Access 2000 and above (this file is in 2002-2003) format, are there any variable length text fields I'm using adVarWChar. Are they variable length in actuality Can I write large strings to them I noticed that the implication was in the insert statement that that there is a length field. What should I set that to

   

  
  
  

• SeanKornish

  You do know that once you have created the command builder, you dont have to explicitly set the insert. update, and delete commands, right The command builder does that for you.
  
  

• LegolasXVI

  Public Function InsertRecord(ByVal NewRow As DataRow, ByVal Table As DataTable, Optional ByVal Update As Boolean = True) As Boolean

  'works

  NewRow(DataRecords.ciRecordNum) = Table.Rows.Count ' Autoincrement

  'Table.Rows.InsertAt(NewRow, Table.Rows.Count)

  If Update Then

  Using CmdBuilder As New OleDbCommandBuilder(Adapter)

  Try

  Adapter.SelectCommand = New OleDbCommand("Select * from [" & Table.TableName & "]", con)

  GetCategoryCommand(Adapter, Table)

  con.Open()

  Table.Rows.Add(NewRow)

  Adapter.Update(Table)

  con.Close()

  Dataset.AcceptChanges()

  Return True

  Catch e As Exception

  MsgBox(e.Message, MsgBoxStyle.Exclamation, "ADONET.InsertRecord")

  Return False

  End Try

  End Using

  Else

  Return True

  End If

  End Function

  Here is the code for the insert command.

  Public Sub GetCategoryCommand(ByVal Adapter As OleDbDataAdapter, ByVal table As DataTable)

  Adapter.InsertCommand = New OleDbCommand("INSERT INTO " & table.TableName & _

  "(RecordNo,RecordType1,Category,Descriptor,Level,SubLevel,DocumentDescriptor,Document)" & _

  "VALUES(@RecordNo,@RecordType1,@Category,@Descriptor,@Level,@SubLevel,@DocumentDescriptor,@Document)")

  Adapter.InsertCommand.Parameters.Add("@RecordNo", OleDbType.Integer, 4, "RecordNo")

  Adapter.InsertCommand.Parameters.Add("@RecordType1", OleDbType.Integer, 4, "RecordType1")

  Adapter.InsertCommand.Parameters.Add("@Category", OleDbType.VarWChar, 0, "Category")

  Adapter.InsertCommand.Parameters.Add("@Descriptor", OleDbType.Integer, 4, "Descriptor")

  Adapter.InsertCommand.Parameters.Add("@Level", OleDbType.Integer, 4, "Level")

  Adapter.InsertCommand.Parameters.Add("@SubLevel", OleDbType.Integer, 4, "SubLevel")

  Adapter.InsertCommand.Parameters.Add("@DocumentDescriptor", OleDbType.VarWChar, 8, "DocumentDescriptor")

  Adapter.InsertCommand.Parameters.Add("@Document", OleDbType.LongVarWChar, 0, "Document")

  End Sub

  Phhhhhhhhewwww, this isn’t code that even a mother could love. As far as databases are concerned, Fortran II as more fun. I stall have an exception from a syntax error.

  
  
  

• sieweng

  David.....

  I really liked the first example.

  All you are doing is appending a record on a table. That's what I am doing.

  I have created the record from the database and the record (datarow) has already been filled in and populated.

  I'll emulate the code you've supplied. And see what happens.

  I just want to be able to read and write. That's all. I'm never ever going to a query.

  Thank you for the code and please stayed tuned.

  Renee

  
  
  
• ADO.NET/Access DB Insertion Exception