Solved

Stored Procedure in SQL Server via ASP

Posted on 2004-09-30
3
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…

696 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