473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

conn.execute

PJ
<%
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.
Jul 19 '05 #1
1 15417
PJ wrote:
<%
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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: M | last post by:
Hi Folks, I don't seem to be able to get DB::execute to work using an ODBC datasource and a MS-Access Database. Can anyone tell me if I am doing something wrong, or is there a bug in the DB...
4
by: Jill Graham | last post by:
Hi, I'm using an access database and found following problem : I have a table X with 2 records : record 1 & record 2. I execute following procedure with 2 steps : 1. The procedure deletes...
2
by: Matt | last post by:
I want to exexute stored procedure in ASP, but it has error "Microsoft VBScript compilation (0x800A0401) Expected end of statement" on line (1). The stored procedure "sp_emp" contain "select *...
2
by: Colin Steadman | last post by:
What is the best way to control is a transaction? I've done a little test and both of the examples below seem to do the job, but I was wondering which is the better method, this: If err.number <>...
2
by: michaaal | last post by:
I feel a bit silly asking this because I use this code all the time, but what does the ",,129" mean? Are there other parameters that one might use (I only do fairly simple SQL commands). ...
6
by: Des Perado | last post by:
I have just tried this, and it works: Global.asa contains: sub Session_OnStart set Session("conn")=Server.CreateObject("ADODB.Connection") dsntemp="driver={sql Server}; server=master1;...
2
by: Empire City | last post by:
Set Conn = Server.CreateObject( "ADODB.Connection" ) Conn.Open( "DSN = mydsn; UID = myuserid; PWD = mypassword;" ) sql = "select * from customers" Set RS = Conn.Execute( sql ) Preferably with a...
7
by: Risen | last post by:
Hi,all, I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE ....." in vb.net 2003. But it always shows error. If any body can tell me how to execute sql command as above?...
1
by: j2ibeo | last post by:
Hi, I was wondering about this statement Rs=conn.execute("Select * from table where id=232") what is the result of Rs if id 232 is not found? And how do I catch this exception like : If not...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.