missing operator

[Stevee]

Hi, assistance would be appreciated, I have inherited a database and a bunch of .asp scripts, I have amended the database and have managed to get to view etc on line using aspmaker, GREAT (I thought). The asp's are all used to run on a different server I am now using a PC, WIndows, XP Pro through IIS. Having not used asp before I thought I'd have a quick look and see.
The problem I am getting is:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'PubID ='.
/asp/functions.asp, line 14

The function, The first line = 9 so line 14 = rs.Open strSQL, conn:

Function SetCurrentPub(PubID)
OpenDB
Set rs=Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID & ";"
rs.Open strSQL, conn
Session("strPubTitle")=rs("title")
Session("strPubWorkingDirectory")="C:\inetpub\wwwr oot\TiHo3\tihov32.mdb" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "Working\" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "WorkingDir\" & rs("WorkingDir")
Session("strPubID")=rs("PubID")
Session("strPubTemplate")=rs("Template")
Session("strPubTotalPages")=rs("TotalPages")
Session("strPubRectsImported")=rs("RectsImported")
CloseDB
End Function

Any assistance would be great, thanks.

Stevee

[CroCrew]

Hello Stevee,

Try this:

From:

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID & ";"
 

To:

strSQL = "SELECT * FROM tblPublications WHERE PubID = '" & PUBID & "'"
 

Hope that helps~

[CroCrew]

Or to:

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
 

If the database type is an integer for the field "PubID".

CroCrew~

[Stevee]

Thanks, I'll try them both, will advise. :-)

[Stevee]

Or to:

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
 

If the database type is an integer for the field "PubID".

CroCrew~

Well I have to assume it's an integer, it's actually a database table name, it now gives an:

Unterminated string constant
/asp/functions.asp, line 13, column 61

Columns throw me a little.

Stevee.

[CroCrew]

Hello Stevee,

Add this to your code and paste what is being printed in your browser.

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
Response.Write("[" & strSQL & "]")
Rresponse.End 
 

That will help us/me out a bit.

Thanks~

[CroCrew]

And is this the whole error that you’re getting?

:
Unterminated string constant
/asp/functions.asp, line 13, column 61

[CroCrew]

Right now I have to think that your local variable “PUBID” has some type of carriage return in it and that is why you’re getting the error. But, that is just a shot in the dark. We will know more after we see what you get back after showing us what your query string looks like by using the above Response.Write code.

CroCrew~

[Stevee]

Hello Stevee,

Add this to your code and paste what is being printed in your browser.

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
Response.Write("[" & strSQL & "]")
Rresponse.End 
 

That will help us/me out a bit.

Thanks~

Where in the code, right at the begining before everything else?

[CroCrew]

Add it before the line “rs.Open strSQL, conn”

[CroCrew]

Also after we are done debugging this do this:

Before your line

Session("strPubTitle")=rs("title")
 

add this:

    If (rs.EOF) Then
        Response.Write("No Records returned from the database!")
        Response.End
    End If
 

[Stevee]

Add it before the line “rs.Open strSQL, conn”

So like this:

Function SetCurrentPub(PubID)
OpenDB
Set rs=Server.CreateObject("ADODB.Recordset")
'strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID
Response.Write("[" & strSQL & "]")
Rresponse.End

rs.Open strSQL, conn
Session("strPubTitle")=rs("title")
Session("strPubWorkingDirectory")="C:\inetpub\wwwr oot\TiHo3\tihov32.mdb" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "Working\" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "WorkingDir\" & rs("WorkingDir")
Session("strPubID")=rs("PubID")
Session("strPubTemplate")=rs("Template")
Session("strPubTotalPages")=rs("TotalPages")
Session("strPubRectsImported")=rs("RectsImported")
CloseDB
End Function

[Stevee]

So altogether:

Function SetCurrentPub(PubID)
OpenDB
Set rs=Server.CreateObject("ADODB.Recordset")
'strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID
Response.Write("[" & strSQL & "]")
Rresponse.End

rs.Open strSQL, conn

If (rs.EOF) Then
Response.Write("No Records returned from the database!")
Response.End
End If

Session("strPubTitle")=rs("title")
Session("strPubWorkingDirectory")="C:\inetpub\wwwr oot\TiHo3\tihov32.mdb" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "Working\" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "WorkingDir\" & rs("WorkingDir")
Session("strPubID")=rs("PubID")
Session("strPubTemplate")=rs("Template")
Session("strPubTotalPages")=rs("TotalPages")
Session("strPubRectsImported")=rs("RectsImported")
CloseDB
End Function

[Stevee]

Or do I have to remove the line breaks?

[CroCrew]

ok like this:

Function SetCurrentPub(PubID)
OpenDB
Set rs=Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
Response.Write("[" & strSQL & "]")
Response.End
 
rs.Open strSQL, conn
 
If (rs.EOF) Then
Response.Write("No Records returned from the database!")
Response.End
End If
 
Session("strPubTitle")=rs("title")
Session("strPubWorkingDirectory")="C:\inetpub\wwwroot\TiHo3\tihov32.mdb" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "Working\" & rs("WorkingDir")
'Session("strPubWorkingDirectory")= strMyRoot & "WorkingDir\" & rs("WorkingDir")
Session("strPubID")=rs("PubID")
Session("strPubTemplate")=rs("Template")
Session("strPubTotalPages")=rs("TotalPages")
Session("strPubRectsImported")=rs("RectsImported")
CloseDB
End Function
 

[CroCrew]

After you change the code and run it let us know what you get within the “[ ]” including the brackets. Please just do a copy and paste so we can see exactly how you see it.

Thanks~

[Stevee]

I'm sorry, but what has changed?
It now doesn't get an error it goes further into it but isn't actually finding the database name (I think):

[SELECT * FROM tblPublications WHERE PubID = ]

is written on the browser window, but now the #include file - top.asp (slightly different than the header.asp) is showing.

[CroCrew]

It looks like your problem is that the value getting passed into your function has no value at all. Thus, making your SQL syntax invalid.

You need to have more error trapping on your page. Here is a little that you can start to play with.

Change your code to be this:

Function SetCurrentPub(PubID)
    OpenDB
    Set rs=Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
    rs.Open strSQL, conn
 
    If (rs.EOF) Then
        Response.Write("No records returned from the database!")
        Response.End
    End If
 
    Session("strPubTitle")=rs("title")
    Session("strPubWorkingDirectory")="C:\inetpub\wwwroot\TiHo3\tihov32.mdb" & rs("WorkingDir")
    'Session("strPubWorkingDirectory")= strMyRoot & "Working\" & rs("WorkingDir")
    'Session("strPubWorkingDirectory")= strMyRoot & "WorkingDir\" & rs("WorkingDir")
    Session("strPubID")=rs("PubID")
    Session("strPubTemplate")=rs("Template")
    Session("strPubTotalPages")=rs("TotalPages")
    Session("strPubRectsImported")=rs("RectsImported")
    CloseDB
End Function
 

Lines 7 through 10 is checking to see if any records have been returned from the database based on your query. If no records then print something on the screen and stop processing. You can extend this more to your needs.

Hope that helps~

[Stevee]

It looks like your problem is that the value getting passed into your function has no value at all. Thus, making your SQL syntax invalid.

You need to have more error trapping on your page. Here is a little that you can start to play with.

Change your code to be this:

Function SetCurrentPub(PubID)
    OpenDB
    Set rs=Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID 
    rs.Open strSQL, conn
 
    If (rs.EOF) Then
        Response.Write("No records returned from the database!")
        Response.End
    End If
 
    Session("strPubTitle")=rs("title")
    Session("strPubWorkingDirectory")="C:\inetpub\wwwroot\TiHo3\tihov32.mdb" & rs("WorkingDir")
    'Session("strPubWorkingDirectory")= strMyRoot & "Working\" & rs("WorkingDir")
    'Session("strPubWorkingDirectory")= strMyRoot & "WorkingDir\" & rs("WorkingDir")
    Session("strPubID")=rs("PubID")
    Session("strPubTemplate")=rs("Template")
    Session("strPubTotalPages")=rs("TotalPages")
    Session("strPubRectsImported")=rs("RectsImported")
    CloseDB
End Function
 

Lines 7 through 10 is checking to see if any records have been returned from the database based on your query. If no records then print something on the screen and stop processing. You can extend this more to your needs.

Hope that helps~

I will give it a go see what happens, and let you know. I was assured that all these asp scripts I have inherited worked as were, and was told it was merely a case of changing paths to the database, but it looks like I've recieved a bag of worms!

Thanks for all your help, hope you'll be around a while longer.

Stevee

[CroCrew]

We are all here for ya 24/7 Stevee.

Happy coding~

[Stevee]

When I take out the:

Response.Write("[" & strSQL & "]")
Response.End

you had me put in earlier then I get:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'PubID ='.
/asp/functions.asp, line 18

When I put it in I get the

Included file - top.asp and then start
[SELECT * FROM tblPublications WHERE PubID = ]

Stevee.

[Stevee]

All it's actually doing is printing the 4th line:

strSQL = "SELECT * FROM tblPublications WHERE PubID = " & PUBID

Stevee

[Stevee]

Hate being stoopid but why doesn't it work when I now take out the 2 lines you gave me earlier:

Response.Write("[" & strSQL & "]")
Response.End

Stevee.

[Stevee]

We are all here for ya 24/7 Stevee.

Happy coding~

Thanks for tonight, hope to catch you tomorrow.

Stevee

[CroCrew]

Hello Stevee,

“Response” is an object that is used to send output to the user from the server.
“Write” and “End” are both methods that are used within the object.

When we use “Response.Write” in our code is when we want to paint something on to the screen. Example is Response.Write(“Hello World”) Any string value within the brackets will get painted on the screen. In this case the two words Hello World would get painted. We also can build a string value with in the brackets like this; Response.Write(“Hello World” & “ How are you?”) this would paint Hello World How are you? on the screen. We can also build a string out of variables too like this Response.Write(MyVariable) if the value of MyVariable was MyVariable=”Coding is fun” then Coding is fun would get painted on the screen.

When we use “Response.End” in our code is when we don’t want any more code to be processed. “Response.End” will stop the processing of a script at that point.

I had you put these two lines

Response.Write("[" & strSQL & "]")
Response.End
 

in your code so I could verify that the value of “PUBID” was getting passed in to your function. How I determined this is because I had you paint/write out the value for the string “strSQL”. If the local variable “PUBID” had a value we would have seen it painted on the screen with the rest of your query.

Bottom line is somewhere on your page there is a line that is calling your function and not passing in a value for “PUBID”. Also, you should places more conditional/error trapping on your page. You will understand more on why you should probably after you’re done with your project.

Still, we are here to help you so post questions whenever you need help. Just try not to bleed multiple questions into one post. That way you will get answers faster and people tend to hate reading long threads.

Good luck and happy coding~

[Stevee]

Hello Stevee,

“Response” is an object that is used to send output to the user from the server.
“Write” and “End” are both methods that are used within the object.

When we use “Response.Write” in our code is when we want to paint something on to the screen. Example is Response.Write(“Hello World”) Any string value within the brackets will get painted on the screen. In this case the two words Hello World would get painted. We also can build a string value with in the brackets like this; Response.Write(“Hello World” & “ How are you?”) this would paint Hello World How are you? on the screen. We can also build a string out of variables too like this Response.Write(MyVariable) if the value of MyVariable was MyVariable=”Coding is fun” then Coding is fun would get painted on the screen.

When we use “Response.End” in our code is when we don’t want any more code to be processed. “Response.End” will stop the processing of a script at that point.

I had you put these two lines

Response.Write("[" & strSQL & "]")
Response.End
 

in your code so I could verify that the value of “PUBID” was getting passed in to your function. How I determined this is because I had you paint/write out the value for the string “strSQL”. If the local variable “PUBID” had a value we would have seen it painted on the screen with the rest of your query.

Bottom line is somewhere on your page there is a line that is calling your function and not passing in a value for “PUBID”. Also, you should places more conditional/error trapping on your page. You will understand more on why you should probably after you’re done with your project.

Still, we are here to help you so post questions whenever you need help. Just try not to bleed multiple questions into one post. That way you will get answers faster and people tend to hate reading long threads.

Good luck and happy coding~

Thanks for that, trying to find the function that should pass the PUBID value, I have checked the paths and they all seem fine, any ideas?

Steve.

[CroCrew]

I would do a “search” for the word “SetCurrentPub” with the text editor that you’re using. If you can’t find it start checking all of the include files too.

Now when you do find it use the Response.Write on the variable that is getting passing to see if has a value.

Welcome to the wonderful world of debugging someone else’s code.

Good luck~

[Stevee]

Thanks for that, I'll give it a go.

I'm sure I'll be back soon, hope that's ok.