"simple" SQL db stored function recordset call . . .

[freejoe76]

Hi,

I've been bugging my brain for hours on this and can't come up with a solution.

The situation seems simple: take a request.form variable and return a record from the SQL database using the variable as the key.

Hasn't been so much, though. This is what I have so far:

Dim DataConn, adoCmd, SQL, emailAd, rs 'database-related variable definitions

Set rs = Server.CreateObject("ADODB.Recordset")

Set DataConn = Server.CreateObject("ADODB.Connection")

Set adoCmd = Server.CreateObject("ADODB.Command")

DataConn.Open("Provider=SQLOLEDB; Network Library=dbmssocn; Data Source=; Initial Catalog=;UID=;PWD=")

emailAd = request.form("emailAddress")


With adoCmd
.ActiveConnection = DataConn
.CommandType = adCmdStoredProc
.CommandText = "spRetrieveBillingInfo"
.Parameters.Append .CreateParameter("emailAddress", adVarChar, adParamInput, 80, "test")
End With

Set rs = adoCmd.Execute

But when I call a rs.fields("nameFirst").value later in the page, I get a

ADODB.Field error '800a0bcd'
error.

And for reference, this is the stored procedure:


CREATE PROCEDURE spRetrieveBillingInfo
@emailAddress varchar(80) = null
AS

SELECT * FROM tblCustomer WHERE emailAddress = '@emailAddress'
GO



.... I believe this is the closest I've come to a solution, but it's no means the first method I've tried. I'm still real new to ASP, so any and all help is much much much appreciated.

Thanks
Joe

[Angelika]

<%

Dim DataConn, adoCmd, SQL, emailAd, rs 'database-related variable definitions

set DataConn=server.CreateObject("ADODB.Connection")
DataConn.Open("Provider=SQLOLEDB; Network Library=dbmssocn; Data Source=; Initial Catalog=;UID=;PWD=")

set Rs = server.CreateObject("adodb.recordset")


emailAd = request.form("emailAddress")

strSQL="SELECT * FROM tblCustomer WHERE emailAddress = '"+emailAd+"'"
Rs.Open strSQL,DataConn

var1 =Rs("Field_Name1")
var2=Rs("Field_Name2")

rs.close

%>

[freejoe76]

Hi Angelika,

Thanks for the suggestion,

unfortunately I've tried that already, and it returns this error:

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

SELECT permission denied on object 'tblCustomer', database 'db', owner 'dbo'



. . . .
joe

[Angelika]

This most likely because Execute, or EXEC, permission has not been granted to the SQL Server login making the connection to the database. Execute permission can also be lost when databases are imported or migrated from other servers. In Enterprise Manager, right click on the stored procedure in question, then navigate to All Tasks, Manage Permissions… in the popup menu. Check the EXEC checkbox for the appropriate login, then click Apply.

[freejoe76]

You were absolutely right about the table permissions -- but now it still returns the same error I had before:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I've verified the request.form("emailAddress") returns the correct value,
The db field emailAddress is spelled correctly . . . . I'm stumped.

[Angelika]

emailAd = trim(request.form("emailAddress"))

strSQL="SELECT * FROM tblCustomer WHERE emailAddress = '"+emailAd+"'"
Rs.Open strSQL,DataConn

IF Not Rs.EOF="True" Then
var1 =Rs("Field_Name1")
var2=Rs("Field_Name2")
End IF

rs.close

[freejoe76]

weird,
it still can't find the record. It's fine when I hard-code ( example sql = "SELECT * FROM tblCustomer WHERE emailAddress = 'test ' " ) a value in, but it must be something about the emailAd variable that's messing with it.

[freejoe76]

NEVERMIND!

The problem:
I forgot to take out the spaces between the single and the double quotes in the sql query

THANK YOU SO MUCHA ANGELIKA