Solved

Stored Procedure in SQL Server via ASP

Posted on 2004-09-30
3
542 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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
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…

726 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