Solved

Stored Procedure in SQL Server via ASP

Posted on 2004-09-30
3
535 Views
Last Modified: 2012-05-05
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!!!
0
Comment
Question by:thewyzzard
3 Comments
 
LVL 5

Accepted Solution

by:
LlamaJoe earned 500 total points
ID: 12335827
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now