PJ said:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open application("dtat_motor_connectionstring")
set rs = new adodb.recordset
'Set RS = Conn.Execute(' "exec spcn_update_transactions &
customer_number, customer_name, customer_address, customer_city,
customer_state, customer_zip, customer_phone, vin,
acct_number, tag, title_number, tag_type_number, branch_number,
institution_number, yr_make, state_from, type_number)
response.write rs
%>
above is my code I'm having problems with the prams can anyone help.
I'm I connecting them correctly. Is there and easier way to execute a
stored procedure. I'm trying to insert data into a SQL page. I have
to have the customer number returned to me asap for the next page.
PLEASE help me.
We need to see your stored procedure code so we can see how it is returning
the customer number (resultset? output parameter? return statement?).
I will say that you have not done your concatenation properly. Always assign
your dynamic sql statement to a variable so you can response.write it:
strSQL = "exec spcn_update_transactions &
customer_number, customer_name, customer_address, customer_city,
customer_state, customer_zip, customer_phone, vin,
acct_number, tag, title_number, tag_type_number, branch_number,
institution_number, yr_make, state_from, type_number
response.write strSQL
Set RS = Conn.Execute(strSQL,,1)
If the above code does not generate an error (it should), then the
response.write should show you exactly what is wrong.
The idea when creating a dynamic sql statement is to cause a statement to be
generated that, when written to the browser window, can be copied and pasted
verbatim from the browser window into into Query Analyzer and run without
modification. This means concatenating the variable values, not the variable
names (sql server will know nothing about your vbscript customer_number
variable) into your statement:
strSQL = "exec spcn_update_transactions " & _
customer_number & ", " & ...
In addition, you also need to concatenate in any delimiters required
(strings and datetime arguments require single quotes):
strSQL = "exec spcn_update_transactions " & _
customer_number & ", " & _
"'" & customer_name & "'," & ...
In addition, you need to worry about whether your string arguments contain
embedded delimiters, such as the name O'Malley. Trying to pass this name
without escaping that apostrophe will cause an error. To escape the
apostrophe, double it up. The Replace function makes this easy:
strSQL = "exec spcn_update_transactions " & _
customer_number & ", " & _
"'" & Replace(customer_name,"'","''") & "'," & ...
When you are done, the statement printed to the browser window using
response.write strsql should strongly resemble the statement you would have
typed into Query Analyzer to test the query:
exec spcn_update_transactions 12, 'O''Malley', ...
Personally, I would not use this dynamic sql approach for running the stored
procedure. To me, it wastes the work you went to when you created the
procedure. Exactly how I choose to run this procedure depends on how you've
coded the procedure to return the new customer number. So show us the
relevant parts of the procedure (the create procedure ...AS section, and the
snippet of code used to return the new customer number) Am I correct in
assuming customer number is an IDENTITY column?
Bob Barrows
