Stored Procedure in SQL Server via ASP

I receive the following error message when trying to execute my code:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Syntax error or access violation

/loginaction.asp, line 125

My global.asa file:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart
      application("DBConn")="Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=carpentersfund;Data Source=BILLS-SERVER;PASSWORD=;"
      application("SSLPath")="c:\cf_wip\members"
End Sub

</SCRIPT>      

The problem portion of my ASP script:

<%
dim RequestUserDataSource
dim Cmd
dim RS
dim UsrParm
dim PwdParm
dim sSQL

Set RequestUserDataSource=Server.CreateObject("ADODB.connection")
RequestUserDataSource.Open(application("DBConn"))

sSQL="[NoZeroes}"

Set Cmd=Server.CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = RequestUserDataSource
Cmd.CommandText = sSQL

Set UsrParm=Cmd.CreateParameter("username",adChar,adParamInput,50)
Set PwdParm=Cmd.CreateParameter("password",adChar,adParamInput,50)

UsrParm.Value = Request.Form("Username")
PwdParm.Value = Request.Form("Password")

Cmd.Parameters.Append UsrParm
Cmd.Parameters.Append PwdParm

Set RS=Cmd.Execute(,,adCmdStoredProc)

(Line 125 is the above line)

The Stored Procedure:

CREATE PROCEDURE  NoZeroes
@username char,
@password char
 AS
SELECT Username, Password
FROM Users
WHERE Username = Convert (numeric, @username) AND Password = @password
GO

I have included adovbs.inc at the top of the page.

Chips, dip, and beer, plus 500 points to the winner.

I noticed that I had a syntax error in the sSQL= "[NoZeroes}" line above.  It now reads:

sSQL="[NoZeroes]"

Now I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e09'

EXECUTE permission denied on object 'NoZeroes', database 'carpentersfund', owner 'dbo'

/loginaction.asp, line 125

AARRRGGGHHH!!!
thewyzzardAsked:
Who is Participating?
 
LlamaJoeConnect With a Mentor Commented:
you are using integrated security in your connection string, so if you have security enabled (basic or windows authentication) it will pass those securities to SQL.  Otherwise it is trying to connect as the anonymous iusr_machinename account.  

Change your connection string to a sql username and password and try again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.