How do I reference web form fields in a stored procedure?

[Qmoto]

This is my first foray into stored procedures and I'm getting the following error:

[COLOR=red]Server: Msg 128, Level 15, State 1, Procedure MultiTableInsert, Line 23[/color]
The name 'req_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Here's the piece of the code causing the error:
INSERT INTO dbo.request_id (req_id, req_type, dep_name, ...)
  VALUES (req_id, req_type, dep_name, ...)

I've figured out why I'm getting the error...because the VALUES I'm trying to insert match the column names of the table I'm inserting into... but I havent figured out how to get the data from my form into the VALUES section of the code.

How do I reference that?
VALUES (form.texbox1.req_id)
VALUES (textbox1.req_id)
???

 

[TheBugSlayer]

The form you are showing in your question is the correct one:

INSERT INTO dbo.request_id (req_id, req_type, dep_name, ...)
  VALUES (form.textbox1.req_id, ...)

 

[jbenson001]

You have to setup Input parameters in the Stored Procedure. Then when you call the SP from your app, you set values in the parameters then pass the parameters to your SP.

Since you are new, first look at how to set up parameters in a SP in Books On Line. (Create Procedure)

Jim

 

[Qmoto]

Thanks for the quick reply!

BugSlayer:
So am I correct in thinking that inserting a value from a radio button or a dropdown would be the same?

VALUES (form.radio1.req_id, form.select.req_type)

What about asp dropdowns?

jbenson001:
Thanks, I'll take a look at the Input Parameters. Can I set the parameter to a form variable? If so how?

Thanks again,
Steve

 

[jbenson001]

What is your front end application you are using?

 

[Qmoto]

A VB.NET webpage, hopefully moving to php in the future.

 

[jbenson001]

If you are using a SQL Server stored procedure, you cannot use the syntax provided by TheBugSlayer. That looks like something in Access I think, but definately not SQL Server.

 

[travisbrown]

You have to create a stored procedure call in your webpage (ASP? PHP?) and pass the form variables into the SP. Google will give you pretty good results for tutorials for your scripting language of choice. Dreamweaver, etc will prompt you for all the parameters if you use their built in behaviours.

For example, asp:

http://support.microsoft.com/kb/q164485/

4guysfromrolla and asp101 will have other examples.