Create Stored procedure

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

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
 
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/defaul...port/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=...8182038.62b9d52b%40posting.google.com&rnum=24

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
 
Dave

I really appreciate your help. I have modified and created as new stored
procedure. But can you tell me how to detect if a stored procedure exists
before I delete it?

Peter
 
When you create a new object in an Access database, a row is added into the
System table MSysObjects.

SELECT COUNT(*) FROM MSysObjects WHERE Name='proc_name'

should return 1 when you call ExecuteScalar(). From what I can tell, you'll
have to do this as two separate database calls. I haven't been able to get
a conditional IF EXISTS... to work. I don't know at this point if that
syntax is supported in Access.
 
Dave

I got a Message: "Can't read records£»You have no reading permission on
'MSysObjects'." after running the command. What privilege should I have to
get information from "MSysObjects"?

Thank you

Peter
 
You'll need to check that the user that you're connecting as has permission
to read the design and data of the MSysObjects table.

In Access and with your database open, go to Tools / Security / User and
Group Permissions...

Select the appropriate user, make sure "Object Type" is set to "Table" and
choose the MSysObjects table from the object list. Click "Read Data" ("Read
Design" should check automatically). Apply the changes.

If MSysObjects isn't listed you'll need to close the window, go to Tools /
Options... and choose "System Objects" from the "Show" group on the "View"
tab. Apply the changes.
 
Back
Top