Dave
Thu Dec 02 22:06:39 CST 2004
After much searching and reading conflicting articles about whether or not
stored procedures could be used in an mdb file, I FINALLY found an example
that works in Access 2000 and later (*Bernie, I stand corrected. It still
needs the OleDb namespace classes though*). Documentation on this is pretty
limited. In fact, BOL in Access 2003 even states that stored procedures are
hosted in a SQL Server database and called through a pass-through query.
Typically stored procedures are hosted in SQL Server (or Oracle, etc...) and
executed via a pass-through query in Access. The example from MSDN shows
how to create the procedure in Access (via a VB module). I hacked out a
quick trial in C# and it worked nicely. I haven't done anything with the
procedure inside of Access aside from bind it to a form (which prompted for
the parameter value upon opening).
Here's a link to the MSDN article:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q202/1/16.asp&NoWebContent=1
...and another recent newsgroup posting about this very subject (actually,
it's where I got the MSDN link):
http://groups.google.com/groups?hl=en&lr=&c2coff=1&threadm=6a68ace3.0408182038.62b9d52b%40posting.google.com&rnum=24&prev=/groups%3Fq%3D%2522create%2Bprocedure%2522%2BAccess%26start%3D20%26hl%3Den%26lr%3D%26c2coff%3D1%26selm%3D6a68ace3.0408182038.62b9d52b%2540posting.google.com%26rnum%3D24
The code below uses one of my testing databases that I tested this against.
I'm sure you can modify it to suit your needs. Obviously, this was just
quickly thrown together and doesn't really follow any type of best
practices.
using (OleDbConnection cn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\inetpub\wwwroot\recipemanager\recipe.mdb"))
{
cn.Open();
try
{
// Drop the procedure if it exists
OleDbCommand cmdDrop = new OleDbCommand("DROP PROCEDURE
sp_Ingredient2", cn);
cmdDrop.CommandType = CommandType.Text;
cmdDrop.ExecuteNonQuery();
}
catch
{
// Ignore any errors
}
try
{
// Create the procedure
OleDbCommand cmdCreate = new OleDbCommand("CREATE PROCEDURE
sp_Ingredient2(prmIngredientID int) AS SELECT * FROM Ingredient WHERE
IngredientID = prmIngredientID", cn);
cmdCreate.CommandType = CommandType.Text;
cmdCreate.ExecuteNonQuery();
}
catch
{
// Ignore any errors
}
// Create the command object and set its parameter
OleDbCommand cmd = new OleDbCommand("sp_Ingredient2", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OleDbParameter("prmIngredientID",
OleDbType.Integer));
cmd.Parameters["prmIngredientID"].Value = 18;
// Execute the reader and iterate through the results...should only
return one row
using(OleDbDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
// Write the ID and Ingredient Name
Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
reader.GetString(1));
}
}
}
HTH
Dave Fancher
http://davefancher.blogspot.com
"Peter" <zlxmqyt@sina.com> wrote in message
news:%23J9%23CkN2EHA.3336@TK2MSFTNGP11.phx.gbl...
> Hi, there
>
> I want to create a Stored procedure for my Access database using the code
> below.
>
> ' Conn is an opened connection to my Access database
> Dim cmd As OleDbCommand
> cmd = Conn.CreateCommand
> cmd.CommandType = CommandType.Text
>
> cmd.CommandText = _
> "CREATE PROCEDURE udpGetSampleIDByName" & vbCrLf & _
> "@zSampleName VarChar(64)" & vbCrLf & _
> "AS " & vbCrLf & _
> "Select zSampleID FROM TSamples " & _
> "Where zSampleName = @zSampleName "
>
> cmd.ExecuteNonQuery()
>
> After running, I got a error message: "invalid SQL Syntax£ºneeded symbol
> AS¡£"
> It goes well when I create SQL Server stored procedure using SQLCommand .
> Now I turn to OleDB and the error occurs.
> Anybody can help me?
>
> Peter
>
>