Hi guys, hopefully you can help me

[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... Dmitriy
dbrom@crosswinds.net

 

[foxbox]

Maybe STATE is a reserved word????


br
Gerard