如何使用C＃在运行时在MS Access中创建存储的查询

Sanjeev4evr 发表于 Dev

Sanjeev4evr

我正在Windows应用程序上。在这里，我需要在MS Access 2007中在运行时创建存储的查询。（即，单击按钮）选择，插入，更新，删除，如何基于参数从表单调用查询？

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.

史蒂夫

如果要在Access数据库中创建一个新的存储查询，则只需准备一个命令并执行它即可。

例如，假设您有一个客户表，并且您想使用查询来检索单个客户的记录。

创建查询

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

要调用查询，它还是一个简单的命令，标记为 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();

上面的例子非常简单。对于更复杂的情况，您需要查看MS-Access数据定义语言区域中的参考。

编辑

句法：

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

所以这应该是您的“ 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])";

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。