help please!!! stored procedure with parameter

[youngun]

I use SQL 2000 and has a simple stored procedure
CREATE PROCEDURE [CheckEmplLogin]
@username varchar(50), @password varchar(50)
AS

select empl_id, passwd, privilege from employee where empl_id = @username and passwd = @password

return @@error
GO

and in ASP, if I type in
<%
set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection = DB_Connect
cmd.CommandText = &quot;CheckEmplLogin&quot;
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

%>
<Table Border=1>
<TR>
<TD><B>PARAMETER NAME</B></TD>
<TD><B>DATA-TYPE</B></TD>
<TD><B>DIRECTION</B></TD>
<TD><B>DATA-SIZE</B></TD>
</TR>
<% For Each param In cmd.Parameters %>
<TR>
<TD><%= param.name %></TD>
<TD><%= param.type %></TD>
<TD><%= param.direction %></TD>
<TD><%= param.size %></TD>
</TR>
<%
Next
%>

If I do this, everything works fine and I got a nice table with all the information I need for this stored procedure. But the problem is when I really try to do the code:

cmd.Parameters.Append cmd.CreateParameter(&quot;username&quot;,adVarChar,adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&quot;password&quot;,adVarChar,adParamInput)

then I got a error:

Error Type:
ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.


Can somebody please help me out??? Thank you very much!!

 

[TimLarkin]

Just a quick look, but......
You need to define the length of the varchar. look up CreateParameter in MSDN.

 

[youngun]

I did, but even when I tried

cmd.Parameters.Append cmd.CreateParameter(&quot;username&quot;,adVarChar,adParamInput,104)
cmd.Parameters.Append cmd.CreateParameter(&quot;password&quot;,adVarChar,adParamInput,50)

I still got the same error message.

Please help me, thank you!

 

[link9]

cmd.Parameters.Append cmd.CreateParameter(&quot;@username&quot;,adVarChar,adParamInput)


I think that will work --

 

[youngun]

This time I typed in
&quot;abc&quot; for username, and &quot;xyz&quot; for password

-------------------------------------------------
strUid = Request.Form(&quot;username&quot
strPwd = Request.Form(&quot;password&quot

cmd.Parameters.Append cmd.CreateParameter(&quot;@username&quot;,adVarChar,adParamInput,104)

cmd.Parameters.Append cmd.CreateParameter(&quot;@password&quot;,adVarChar,adParamInput,50)

cmd.Parameters(&quot;@username&quot.Value = strUid
cmd.Parameters(&quot;@password&quot.Value = strPwd
-------------------------------------------------

and I got an error message:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'abc'.


Can somebody help me out? This is really frustrating!

Thanx

 

[JuanitaC]

You are missing the last parameter for the CreateParameter, which is the value.

cmd.Parameters.Append cmd.CreateParameter("@password",adVarChar,adParamInput,50)

Should be

cmd.Parameters.Append cmd.CreateParameter("@password",adVarChar,adParamInput,50,strPwd)

Where strPwd is the variable containing the value for that parameter (in this case, password).

I hope this helps.

 

[youngun]

This time I tried serveral options and continue getting errors:

--- First Attempt

cmd.Parameters.Append cmd.CreateParameter(&quot;@username&quot;,adVarChar,adParamInput,104,strUid)

cmd.Parameters.Append cmd.CreateParameter(&quot;@password&quot;,adVarChar,adParamInput,50,strPwd)

and I got error
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'abc'.
&& where 'abc' is the value I type in for username.

--- Second Attempt

cmd.Parameters.Append cmd.CreateParameter(&quot;@username&quot;,adVarChar,adParamInput,104,&quot;strUid&quot

cmd.Parameters.Append cmd.CreateParameter(&quot;@password&quot;,adVarChar,adParamInput,50,&quot;strPwd&quot

and the error is:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'strUid'.


--- Third Attempt

cmd.Parameters.Append cmd.CreateParameter(&quot;@username&quot;,adVarChar,adParamInput,104,Request.Form(&quot;username&quot

cmd.Parameters.Append cmd.CreateParameter(&quot;@password&quot;,adVarChar,adParamInput,50,Request.Form(&quot;password&quot

and the error is:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'abc'.


--- Fourth Attempt

cmd.Parameters.Append cmd.CreateParameter(&quot;@username&quot;,adVarChar,adParamInput,104)

cmd.Parameters.Append cmd.CreateParameter(&quot;@password&quot;,adVarChar,adParamInput,50)

cmd.Parameters(&quot;@username&quot.Value = strUid
cmd.Parameters(&quot;@password&quot.Value = strPwd

and the eror is:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'abc'.


Do you think I have anything wrong with my SQL stored procedure? This is what I have for my stored procedure:

CREATE PROCEDURE [CheckEmplLogin]
@username varchar(104), @password varchar(50)
AS

execute('select empl_id, passwd, prvg from employee where empl_id = '+@username+' and passwd = '+@password)
GO

 

[JuanitaC]

Leave the create parameter in your asp as:

cmd.Parameters.Append cmd.CreateParameter("@username",adVarChar,adParamInput,104,strUid)

cmd.Parameters.Append cmd.CreateParameter("@password",adVarChar,adParamInput,50,strPwd)

And in your stored procedure, you are missing some quotes, your SQL string should look like this:

execute('select empl_id, passwd, prvg from employee where empl_id = ' + '''' + @username + '''' +' and passwd = ' + '''' + @password + '''')

Try that, and let me know what happens.

 

[youngun]

Thanks, JuanitaC. It now seems working. The only thing I need to do is to get the permission to let the current login be able to select from the employee table since the employee table belongs to dbo.

This actually raise up another question because I thought thru stored procedure it's ok to do whatever as long as the current login has the right to execute the stored procedure.
How come I couldn't select from dbo.EMPLOYEE when I login as &quot;empl&quot; instead of &quot;sa&quot;?

 

[JuanitaC]

I'm not sure on that one. Maybe post it in the SQLServer forum (if that is what you are using.)

I'm glad the stored procedure is working now.

 

[hyperbug]

If you call a stored procedure in that way you have to take care of security because somebody can enter a password like this: ---someword'; drop database master; select 'a---

It could crash your databases and crackers have a lot of patient to do it.