asked on

Syntax error trying to UPDATE dbf file

HI,
I have the following code which is supposed to connect to a .dbf file and then update (i'm using test values) - first I got the error that the Jet engine couldn't find the table "WV_region.dbf", so after searching, thought it might be something to do with the long filename. So i've now included code to get the DOS 8.3 file name, but I'm gettin the error "Syntax error in UPDATE statement."

I'm about to throw the PC out of the window, so if anyone could help before I do that, I would be most grateful

Cheers
leddy

int recordsAffected = 0;
            OpenFileDialog dlg = new OpenFileDialog();
            //dlg.Filter = ".dbf";
 
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                string longfilename = dlg.FileName;
                
 
                StringBuilder buffer = new StringBuilder(256);
                GetShortPathName(longfilename, buffer, buffer.Capacity);
                string shortfilename = buffer.ToString();
 
                string fileName = shortfilename.Substring(0, shortfilename.Length - 4);
                string filePath = System.IO.Path.GetDirectoryName(dlg.FileName);
                string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=dBASE IV;User ID=Admin;Password=;";
                OleDbConnection conn = new OleDbConnection(ConnectionString);
                char c = (char)34;
                OleDbCommand cmd = new OleDbCommand("UPDATE " + shortfilename + " SET ROUNDCODE = 'test' WHERE POSTCODE = 'WV1 1AA';", conn);
 
                conn.Open();
                recordsAffected = cmd.ExecuteNonQuery();
                conn.Close();
 
                MessageBox.Show(Convert.ToString(recordsAffected));
            }

Open in new window

Fernando Soto

Hi leds769;

This line in your code
string filePath = System.IO.Path.GetDirectoryName(dlg.FileName);
Should contain the fully qualified file name of the dbf, so it should be this
string filePath = dlg.FileName;

In this line of code:
OleDbCommand cmd = new OleDbCommand("UPDATE " + shortfilename + " SET ROUNDCODE = 'test' WHERE POSTCODE = 'WV1 1AA';", conn);
The value of shortfilename should be the name of the table within the database that you want to update and not the name of the file that the database is stored on disk with.

Make those changes and see what happens.

leds769

ASKER

Hi and thanks for your response

No, if I change the filePath to the full path (inc the file name) it gives the error:
'file name here' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

The name of the file, i.e. WV_region.dbf, is the name of the table - did you see that it is a dbf file I'm working with?

cheers

leds769

ASKER

Ok, finally found the problem

The test file I was using didn't have a "ROUNDCODE" column.......grrrrrrrr
It works when there is one though.

Thanks for your replies though

leddy

Fernando Soto

I have not worked with dBase IV or FoxPro database files and I looked up the connection string and and it is
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password=;

Where Data Source is the folder name and not a file name, so you are correct.

I have found a reference on line that states that the file name is used in the SQL statement so try your update statement like this:

OleDbCommand cmd = new OleDbCommand("UPDATE " + shortfilename + " SET ROUNDCODE = 'test' WHERE POSTCODE = 'WV1 1AA';", conn);

Where shortfilename should equal the filename --> "WV_region.dbf" with extension and see what you get.

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial