Carsten,
I was just ready to try your suggestion when I stumbled on to
something in a book that looked a little easier. Evidently, the
OLE command builder was using quotation marks in creating the
INSERT command and that, supposedly, was causing the syntax
error. The book used the following to solve the problem... and
it worked!
Dim cb As New OleDbCommandBuilder(da1)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
The fact that the object has properties of QuotePrefix and
QuoteSuffix tells me that this is an expected situation. I'm not
sure why the command builder just doesn't make the changes
automatically. But thanks for your input nevertheless. I will
probably still need to write my own INSERT command someday.
Roger
"CT" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Have you tried examining the INSERT statement, and perhaps
creating your
own? I've seen this before with ODBC drivers; it seems that the
CommandBuilder doesn't handle them all very well.
--
Carsten Thomsen
Enterprise Development with Visual Studio .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
"Roger Lord" <(E-Mail Removed)> wrote in message
news:Q9JPa.7912$b03.2844@lakeread03...
> I'm hoping somebody can help me with this and I hope this is
the
> correct newsgroup. I am writing a VB.NET program that accesses
a
> dBASE IV file. I can't get a subroutine to actually add a row
to
> the end of the dBASE file - I keep getting an error (using
> Try...Catch...Finally) that says "Syntax error in INSERT INTO
> statement". This I can't understand because I'm using
> OleDbCommandBuilder to create the INSERT command. The
following
> is the exact code from my program:
>
>
> Public Sub Append_Database()
>
> '---- First, set up the connection and the dataset ----
> Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &
> "Data Source=C:\LCTA;" & "Extended Properties=dBase IV"
> Dim cn As New OleDbConnection(cs)
> cn.Open()
>
> '---- Second, read in the existing data from the
server ----
> Dim myQuery As String = "SELECT * FROM MEMBERS"
> Dim da1 As New OleDbDataAdapter(myQuery, cn)
> Dim mDataSet As New DataSet()
> da1.Fill(mDataSet, "MEMBERS") 'Fill dataset with dBase
table
> Members.dbf
>
> Dim cb As New OleDbCommandBuilder(da1) 'This builds the
> INSERT, UPDATE, and DELETE commands
>
> '---- Now define what will be a new row ----
> Dim myRow As Data.DataRow
> myRow = mDataSet.Tables("MEMBERS").NewRow
> myRow("LASTNAME") = UCase(txtLastName.Text)
> myRow("FIRST") = UCase(txtFirstName.Text)
> myRow("MIDDLE") = UCase(txtMiddleInit.Text)
> myRow("STREET1") = UCase(txtAddress1.Text)
> myRow("STREET2") = UCase(txtAddress2.Text)
> myRow("TOWN") = UCase(txtCity.Text)
> myRow("STATE") = UCase(txtState.Text)
> myRow("ZIP") = txtZip.Text
> myRow("HOMETEL") = UCase(txtHomeTel.Text)
> myRow("WORKTEL") = UCase(txtWorkTel.Text)
> myRow("EXTENSION") = UCase(txtExt.Text)
> myRow("EMAIL") = txtEmail.Text
> myRow("WIFE_FIRST") = UCase(txtSpouseFirst.Text)
> myRow("WIFE_MI") = UCase(txtSpouseMI.Text)
> myRow("WIFE_LAST") = UCase(txtSpouseLast.Text)
> myRow("JOINED") = CDate(txtJoined.Text)
> myRow("LASTPAID") = CDate(txtLastContrib.Text)
> myRow("AMOUNT") = Val(txtAmount.Text)
>
> '---- Now add the new row to the dataset
> mDataSet.Tables("MEMBERS").Rows.Add(myRow)
>
> '---- Finally, update the database on the server from the
> revised dataset
> Try
> da1.Update(mDataSet, "MEMBERS")
> Catch e As OleDb.OleDbException
> MsgBox(e.Message)
> Finally
> cn.Close()
> End Try
> End Sub
>
>
>
> The exception occurs at the statement: da1.Update(mDataSet,
> "MEMBERS")
>
>
>
> Any ideas what's wrong?
>
>
>
> Thanks,
>
> Roger
>
>
>
Similar Threads
