HELP - Stored Procedure problem

[dbrom]

Hi guys, hopefully you can help me out with this ine.

I insert customer information in my table, and every time
for some reason the state is set to zero instead of what it
is supposed to be, say "CA".

I insert values using a stored procedure, and as you can see I use JScript. Here are the values I insert:



salutation = String(Request.Form("salutation&quot);
title = String(Request.Form("title&quot);
firstName = String(Request.Form("firstName&quot);
lastName = String(Request.Form("lastName&quot);
company = String(Request.Form("company&quot);
streetAddress = String(Request.Form("streetAddress&quot);
city = String(Request.Form("city&quot);
state = String(Request.Form("state&quot);
country = String(Request.Form("country&quot);
zip = String(Request.Form("zip&quot);
phone = String(Request.Form("phone&quot);
fax = String(Request.Form("fax&quot);
mobile = String(Request.Form("mobile&quot);
email = String(Request.Form("email&quot);
pwd = String(Request.Form("pwd&quot);



Here's my ASP code for the command object:



var conn = Server.CreateObject("ADODB.Connection"
var cmdInsertCust = Server.CreateObject("ADODB.Command"
var customerId;
conn.open("DSN","username","password"

with (cmdInsertCust)
{
ActiveConnection = conn;
CommandText = "spInsertCustomerInfo";
CommandType = 4;

Parameters.Append(CreateParameter("@salutation", 202, 1, 20, salutation));
Parameters.Append(CreateParameter("@firstName", 202, 1, 35, firstName));
Parameters.Append(CreateParameter("@lastName", 202, 1, 35, lastName));
Parameters.Append(CreateParameter("@company", 202, 1, 75, company));
Parameters.Append(CreateParameter("@title", 202, 1, 50, title));
Parameters.Append(CreateParameter("@streetAddress", 202, 1, 75, streetAddress));
Parameters.Append(CreateParameter("@city", 202, 1, 50, city));
Parameters.Append(CreateParameter("@state", 202, 1, 10, state));
Parameters.Append(CreateParameter("@country", 202, 1, 30, country));
Parameters.Append(CreateParameter("@zip", 202, 1, 10, zip));
Parameters.Append(CreateParameter("@phone", 202, 1, 15, phone));
Parameters.Append(CreateParameter("@fax", 202, 1, 15, fax));
Parameters.Append(CreateParameter("@mobile", 202, 1, 15, mobile));
Parameters.Append(CreateParameter("@email", 202, 1, 50, email));
Parameters.Append(CreateParameter("@password", 202, 1, 8, pwd));
Parameters.Append(CreateParameter("@customerId", 3, 2));

Execute();

customerId = Number(Parameters("@customerId&quot);

}


And Here's the stored procedure in SQL Server:



CREATE PROCEDURE spInsertCustomerInfo
@salutation nvarchar(20),
@firstName nvarchar(35),
@lastName nvarchar(35),
@company nvarchar(75),
@title nvarchar(50),
@streetAddress nvarchar(75),
@city nvarchar(50),
@state nvarchar(10),
@country nvarchar(30),
@zip nvarchar(10),
@phone nvarchar(15),
@fax nvarchar(15),
@mobile nvarchar(15),
@email nvarchar(50),
@password nvarchar(8),
@customerId int OUTPUT
AS

IF EXISTS (SELECT customerId
FROM dbCustomerInfo
WHERE email = @email AND lastName = @lastName)

SELECT @customerId = 0

ELSE

INSERT INTO dbCustomerInfo
(salutation,firstName,lastName,company,title,streetAddress,city,state,country,zip,phone,fax,mobile,email,password)
VALUES
(@salutation,@firstName,@lastName,@company,@title,@streetAddress,@city,@state,@country,@zip,@phone,@fax,@mobile,@email,@password)

SELECT @customerId = @@IDENTITY



If I use Response.Write(state) in my ASP code, it shows that in ASP the state is set to "CA".
However, if I pass state as an argument to the stored procedure, it is set to zero along the way.
Interestingly, if I write
Parameters.Append(CreateParameter("@state", 202, 1, 10, phone));,
for example, the state field in the database is set to the value of phone, i.e. 4567890, not zero, as is the case with passing the state value.
Parameters.Append(CreateParameter("@state", 202, 1, 10, state));,
So I kinda don't understand, why one varialble works fine and the other does not.
If you can help, that'd be awesome.


What's up with that... s-) Dmitriy
dbrom@crosswinds.net

 

[JuanitaC]

Instead of just doing a response.write on your variable in ASP, try response.Writing all of your parameters before executing the stored procedure, just to make sure the parameters are exactly right:

For Each Para in Parameters
   Response.Write Para.Name & &quot; =  &quot; & Para.Value & &quot;<br>&quot;
Next

My next suggestion if the parameters are all what you expect, is to copy from your web browser all of the parameters, then run the stored procedure from QueryAnalyzer.

I find this to be a useful way to start the debugging process. It could also be something as easy as "state" being some kind of reserved word or something. Try renaming the variable to see if that helps.

 

[dbrom]

Thanks for your response.

I figured out what the problem was. Your suggestion about simple names like "state" was right on money.
I am not aware of it, but looks like it is some kind of keyword in asp.
Never heard of this one before, but the most important thing is that everithing works now - I changed "state" to "usState".

Thanks again. ;-) Dmitriy
dbrom@crosswinds.net