stored procedure in access problems

[henslecd]

Hello. I am trying to get my stored procedure in access to be called from ASP.

My stored procedure is SP_ADD_EQUIPMENT

INSERT INTO TBL_EQUIPMENT ( PROD_NUMBER, SERIAL_NUMBER, EQUIPMENT_DETAIL_ID, PO_NUMBER, COST, LOCATION_DETAIL_ID, ENTERED_BY, STATUS_AUTHORIZATION_ID )
SELECT [PNUM] AS EXPR1, [SNUM] AS EXPR2, [EDET] AS EXPR3, [PONUM] AS EXPR4, [CST] AS EXPR5, [LDET] AS EXPR6, [ENTBY] AS EXPR7, [SAUTH] AS EXPR8;

I then call it with...

objCmd.CommandText = "{Call SP_ADD_EQUIPMENT ('" & cStr(strProdNumber) & "','" & cStr(strSerialNumber) & "'," & cInt(intEquipDetail) & ",'" & cStr(strPONumber) & "','" & cStr(strCost) & "'," & cInt(intLocDetail) & "," & cInt(intEntby) & "," & cInt(intAuth) & &quot}"

And execute with...

objCmd.CommandType = adCmdText
objCmd.Execute

There error I get is...

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I use Access 2000, ASP

Thanks!

 

[jfischer]

I'm not sure you posted this question to the correct forum. The terminology you use in your description and the statement syntax for your Command Text example are based on SQL Server, not Access. Access does not have "Stored Procedures," but SQL Server does.

Now, if you really mean that you are using an Access database and you want to run a query stored in it, then there are some changes to make to your Command Text.

1) Get rid of the curly braces {}.
2) Change the "Call" statement to "EXECUTE".
3) Get rid of the parentheses around the parameter list and simply provide the parameters as a comma-separated list. Make sure there is a space between the name of the query and the first parameter.

Here's an example Command Text string to execute an Access query lookup with one Parameter defined.

objCmd.CommandText = "EXECUTE FindName '" & Request.QueryString("[Enter Name]") & "'"