testing out stored procedures

  • Thread starter Thread starter Parag
  • Start date Start date
P

Parag

Hi,

I have just used the upsizer wizard to change my .mdb
to .adb with msde. I tried using the stored procedures to
test it. My query is as follow:

INSERT INTO dbo.Available_Meals
SELECT Meal_desc, Cost
FROM dbo.Available_Meals
WHERE (Meal_desc = @Meal_desc) AND (Cost = @Cost)

i get promted by access to enter the meal description and
cost. I then get a message stating it executed
successfully, but when i actually check the table to see
if it inserted the values, it is not there!!!

Is there any reason that the values are not inserted??
 
Dear Parag:

You need to create a Cmd object:

Dim cmd AS ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnect = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "SPName 'Param1', 'Param2'"
cmd.Execute
Set cmd = nothing

String parameters must be string-friendly. If they contain any single
quotes, you must double them. If they aren't strings, don't put them
inside single quotes above (except dates, which will convert from
strings).

You can also use the parameters collection of the Command object to
pass the parameters, too. Make the CommandType adCmdStoredProc for
this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
And where do you specify the fields of the table into
which you are inserting?

INSERT INTO dbo.Available_Meals.Meal_desc,
dbo.Available_Meals.Cost
SELECT ...


David Atkins, MCP
 
Back
Top