How to create Stored Queries in MS Access at runtime using C#

Question

I am working on windows application. Here I need to create stored queries at runtime in MS Access 2007. (i.e on button click) for the Select, Insert, Update, delete, How to call the queries from the form based on the parameters ?

cmmd.CommandText = "CREATE TABLE tblCustomers ([CustomerID] AUTOINCREMENT PRIMARY KEY, CustomerName Text(50), [CategoryID] Long REFERENCES tblCategories (CategoryID), [IsActive] YesNo, [ModifiedBy] Long REFERENCES tblUsers (UserID), [ModifiedDate] Date)";
cmmd.ExecuteNonQuery();

//Above Create table tblCustomers executed successfully,  

cmmd.CommandText = @"CREATE PROCEDURE prAddCustmer (CustName Text(50), CatID Long, Inact No, ModBY Long, ModDate date ) AS INSERT INTO tblCustomers  (CustomerName , CategoryID, Inactive, ModifiedBy, ModifiedDate) VALUES(CustName, [CatID], [Inac], [ModBy], [ModDate]);";
cmmd.ExecuteNonQuery();
//But got error in Create Procedure prAddCustomers, Please find error in the Stored query and suggest the correct answer 
//Syntax error in PARAMETER clause.

Your question is not clear. Do you want to call a query already existing in your db, or do you want to create a query in your db ? — Steve
– Steve, Commented Mar 28, 2014 at 7:31
I need to create stored query first and then I should call it from other instance. first I need to create database and its objects like tables, stored queries first. Now I have created database and tables, and left with stored queries. — Sanjeev4evr
– Sanjeev4evr, Commented Mar 28, 2014 at 7:38

Steve · Accepted Answer · 2014-03-28 11:14:04Z

5

If you want to create a new stored query in your Access database you should simply prepare a command and execute it.

For example, supposing you have a customer table and you want to retrieve the record of a single customer using a query.

To create the query

 string cmdText = @"CREATE PROCEDURE Customer_SelectOne (custID Long) as
     SELECT * FROM Customers WHERE IDCustomer = [custID]";
 OleDbCommand cmd = new OleDbCommand(cmdText, connection);
 cmd.ExecuteNonQuery();

To call the query, it is again a simple command flagged as CommandType = CommandType.StoredProcedure

 string cmdText = "Customer_SelectOne";
 OleDbCommand cmd = new OleDbCommand(cmdText, connection);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("custID", customerID);
 OleDbDataReader reader = cmd.ExecuteReader();

The example above is really simple. For more complex scenario, you need to look at the reference in the area of MS-Access Data Definition Language.

EDIT

Syntax:

CREATE PROCEDURE ProcName 
                 (optional list of parameters with type and size for Text)
                    AS
                 (Valid SQL Statement using the optional parameters list)

so this should be your 'prAddCustomers'

cmmd.CommandText = @"CREATE PROCEDURE prAddCustomers 
                     (CustName Text(50), 
                      CatID Long, 
                      IsActive BIT, 
                      ModBY Long, 
                      ModDate DATETIME )
                    as
                    INSERT INTO tblCustomers 
                      (CustomerName, CategoryID, IsActive, ModifiedBy, ModifiedDate) 
                    VALUES([CustName], [CatID], [IsActive], [ModID],[ModDate])";

edited Mar 28, 2014 at 11:14

answered Mar 28, 2014 at 7:42

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sanjeev4evr Over a year ago

Hi @Steve, I have added insert statement which has errors, Please review the question once again and suggest how to resolve it.

Steve Over a year ago

This is a different problem. Your SQL Syntax is wrong. Try to write the same query in MS-Access and copy/paste in your code. I see also that you don't pass correctly the parameters to your query. (They should go with type before the AS) Look carefully to my example

Sanjeev4evr Over a year ago

I have edited the code and got "Syntax error in PARAMETER clause". Please tell where is the error ?

Steve Over a year ago

The datatype for YesNo fields is called BIT

Steve Over a year ago

Also not sure about DATE type. It should be DATETIME

|

Collectives™ on Stack Overflow

How to create Stored Queries in MS Access at runtime using C#

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related