Topic: Asp 3.0 & Ms Sql

I'm not sure if i'm allow to post here but here goes.

i'm a newbie to SQL and ran into a little problem. Not sure if it's a sql or asp problem.

Here is the error

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Syntax error or access violation

The code is all inline sql and it has acess to the table. Here is the sql:

SELECT DISTINCT dbo.tblEvents.EventID, dbo.tblEvents.EventTitle, dbo.tblEvents.EventTeaserShort, dbo.tblEventDates.EventDate, dbo.tblEvents.EventTimeStart FROM dbo.tblEvents INNER JOIN dbo.tblEventDates ON dbo.tblEvents.EventID = dbo.tblEventDates.EventID WHERE tblEventDates.EventDate = '5/10/2006' Order By dbo.tblEventDates.EventDate, dbo.tblEvents.EventTimeStart

It works when i tested it in sql server.

so i'm thinking it's an ASP code issue. I'm using ASP 3.0 don't know .Net yet.

Here is the ASP Code:

Function GetRecords()

'on error resume next
    Set m_rsEvents = server.CreateObject("Adodb.Recordset")
       
    Dim cmd
    Dim strQuery

    strQuery = "SELECT DISTINCT  dbo.tblEvents.EventID, dbo.tblEvents.EventTitle, dbo.tblEvents.EventTeaserShort, dbo.tblEventDates.EventDate, dbo.tblEvents.EventTimeStart "
    strQuery = strQuery & "FROM  dbo.tblEvents INNER JOIN dbo.tblEventDates ON dbo.tblEvents.EventID = dbo.tblEventDates.EventID WHERE "
    strQuery = strQuery & MakeWhereClause
    strQuery = strQuery & " Order By dbo.tblEventDates.EventDate, dbo.tblEvents.EventTimeStart"
       
    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = oConn
    cmd.CommandType = adCmdText
    cmd.CommandText = strQuery

    Set m_rsEvents = GetReadOnlyRecordset(cmd)

    Set cmd = Nothing
End Function

Any help would be great!
Thanks
Mark

Re: Asp 3.0 & Ms Sql

Mark - First, this is a perfect place to post a question like this. 

Second, I would recommend using line continuation when building your sql string.  Strings are immutable and using the
strQuery = strQuery & ...

uses more overhead and memory than using

strQuery = "select * " & _
"from mytable " & _
"where id = 1"

Third, what line does the error occur on?  Does the error occur in the function you have listed here or in the GetReadOnlyRecordset function you are passing the command object to?  Does this article help at all? http://www.aspfaq.com/show.asp?id=2400 Do a find in your browser and search for access violation.

Re: Asp 3.0 & Ms Sql

One thing I try to sort out possible sql problems is to write the sql string to the browser where I can look at it and copy/paste into Query Analyzer. 

Response.Write(strQuery)
Response.End

-Eric

Re: Asp 3.0 & Ms Sql

I did write it out to the browser so i know the function that defindes the where clause is working and the sql string does validate in sql.

Re: Asp 3.0 & Ms Sql

Here is the line error:

/Includes/dbfunctions1.asp, line 89

so the sql is passing over to the getreadonlyrecords function and some how messing up there would be my guess but i'm still not sure why because it validates in sql

Re: Asp 3.0 & Ms Sql

If you post the sql string as output by Response.Write and the code within GetReadOnlyRecordset, I'll take a look at it.  It might be tomorrow though.

-Eric

Re: Asp 3.0 & Ms Sql

strQuery = "SELECT DISTINCT  dbo.tblEvents.EventID, dbo.tblEvents.EventTitle, dbo.tblEvents.EventTeaserShort, dbo.tblEventDates.EventDate, dbo.tblEvents.EventTimeStart "
    strQuery = strQuery & "FROM  dbo.tblEvents INNER JOIN dbo.tblEventDates ON dbo.tblEvents.EventID = dbo.tblEventDates.EventID WHERE "
    strQuery = strQuery & MakeWhereClause
    strQuery = strQuery & " Order By dbo.tblEventDates.EventDate, dbo.tblEvents.EventTimeStart"


and here is the GetReadonlyRecordset


  Public Function GetReadOnlyRecordset(ByVal cmd)
       
           
        'Dim cmd
        Dim rs
        Dim rsClone
           
        'Set cmd = Server.CreateObject("ADODB.Command")
       
        'cmd.ActiveConnection = cn
        cmd.CommandType =adCmdStoredProc
        'cmd.CommandText = strCommandText
             
        Set rs = Server.CreateObject("ADODB.Recordset")
        rs.CursorType = adOpenForwardOnly
        rs.CursorLocation = adUseClient
        rs.LockType = adLockReadOnly   
        'response.Write strcommandtext
        'response.end
        rs.Open cmd
       
       
        Set cmd.ActiveConnection = Nothing
        Set cmd = Nothing
        

        Set rsClone = rs.Clone
        Set rsClone.ActiveConnection = Nothing
             
        rs.Close()
        Set rs = Nothing
           
        Set GetReadOnlyRecordset = rsClone
             
    End Function

thanks for the help.

Mark

Re: Asp 3.0 & Ms Sql

'cmd.ActiveConnection = cn
        cmd.CommandType =adCmdStoredProc
        'cmd.CommandText = strCommandText

you are passing a string, but telling the connection/command to expect a stored procedure

Re: Asp 3.0 & Ms Sql

I think Chuck hit the nail on the head.  Another thing to check is to make sure you are including the constants file (adovbs.inc) so that all of your constants (adOpenForwardOnly, etc) are defined.

-Eric