Can't Append dBASE File

  • Thread starter Thread starter Roger Lord
  • Start date Start date
R

Roger Lord

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
 
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



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.
 
I think the cb.QuotePrefix = "]", etc. puts brackets around the field
names in the command. This is to avoid problems when field names are
also reserved words. So you probably have a field name that is a
reserved word. I had this problem when one of my field names was "number".


Roger said:
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



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
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
Click to expand...

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
Click to expand...
Click to expand...
 
Dear Sir:

You are probably correct on that. Do you have any idea which of
my field names might be a reserved word so that I avoid using
them in the future? The list of them that I am using is:

LASTNAME
FIRST
MIDDLE
STREET1
STREET2
TOWN
STATE
ZIP
HOMETEL
WORKTEL
EXTENSION
EMAIL
WIFE_FIRST
WIFE_MI
WIFE_LAST
JOINED
LASTPAID
AMOUNT

I typed in a few of them in Help's Index, but nothing in
particular came up.

Thanks,
Roger


I think the cb.QuotePrefix = "]", etc. puts brackets around the
field
names in the command. This is to avoid problems when field names
are
also reserved words. So you probably have a field name that is a
reserved word. I had this problem when one of my field names was
"number".


Roger said:
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



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
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
Click to expand...

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
Click to expand...
Click to expand...
 
¤ Dear Sir:
¤
¤ You are probably correct on that. Do you have any idea which of
¤ my field names might be a reserved word so that I avoid using
¤ them in the future? The list of them that I am using is:
¤
¤ LASTNAME
¤ FIRST
¤ MIDDLE
¤ STREET1
¤ STREET2
¤ TOWN
¤ STATE
¤ ZIP
¤ HOMETEL
¤ WORKTEL
¤ EXTENSION
¤ EMAIL
¤ WIFE_FIRST
¤ WIFE_MI
¤ WIFE_LAST
¤ JOINED
¤ LASTPAID
¤ AMOUNT
¤
¤ I typed in a few of them in Help's Index, but nothing in
¤ particular came up.

I don't see any reserved words above. I would try using the process of elimination. Remove fields
from the DataRow your are adding until you have a successful update. I would start with the Date
fields.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top