Click here to Skip to main content
15,891,253 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
the following is the code:
C#
OleDbConnection database;

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=PCAN.mdb";
database = new OleDbConnection(connectionString);
database.Open();

string query = @

"IF EXISTS (SELECT * FROM VEHICLE_SPEED)
 BEGIN
 INSERT INTO VEHICLE_SPEED (SPD, SPEED_VALUE) VALUES (@SPD, @SPEED_VALUE)   
 END
 ELSE 
 BEGIN
 CREATE TABLE VEHICLE_SPEED (SPD INT, SPEED_VALUE NVARCHAR(10))   
 INSERT INTO VEHICLE_SPEED (SPD, SPEED_VALUE) VALUES (@SPD, @SPEED_VALUE)
 END";
 
 int number = 0;
 OleDbCommand SQLQuery2 = new OleDbCommand(query, database);
 // ... other parameters
 SQLQuery2.Parameters.AddWithValue("@SPD", number++);
 SQLQuery2.Parameters.AddWithValue("@SPEED_VALUE", txtVehSpd.Text);
 SQLQuery2.ExecuteNonQuery();


somehow, there is an error "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." when the code run.

Any database programmers, pls help. thanks!
Posted

MS Access database engine (Jet.OleDb.4.0) does not support such of statements!

You have to use OleDbConnection.GetSchema Method (String)[^]
Example: Check if a table or field exists in a database[^]
 
Share this answer
 
Hi pohcb_sonic,

IF EXISTS checks for the existence of recordset from your table. Looking at your code, you are creating a table if your condition returns falls. I think you should replace your
SQL
IF EXISTS (SELECT * FROM VEHICLE_SPEED)

with
SQL
IF OBJECT_ID ('VEHICLE_SPEED') IS NOT NULL


This checks if the object exist or not.

Thanks.

[Comment]Please, use proper formatting[/Comment]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900