About Queries in ASP 2

[daph]

Hi,

Got a question for you guys. Say I have this in my code:

strSQL = "SELECT DISTINCT MOVIES.MOVIE_CD, TITLE
FROM MOVIES ORDER BY MOVIES.MOVIE_CD DESC"

Set rsSQL = objDBConn.Execute(strSQL)
varMovieCD = rsSQL("MOVIE_CD&quot.Value

And in my table, the field MOVIE_CD is a number, is
varMovieCD a number as well? Or a string?

--

Another question: Is there a problem in doing this afterwards?

strSQLRating = "SELECT SUM(RATING) AS SUM_RATING
FROM RATINGS_MOVIES
WHERE MOVIE_CD = " & varMovieCD

Set rsSQLRating = objDBConn.Execute(strSQLRating)


Thanks for the help
Daph

 

[onpnt]

not that I see, why whats wrong

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[Gorkem]

1st Question:
If the field in the Database is an Integer, your variable will be an integer.. If its a string.. then the variable will be a string.

2nd Question:
If the MOVIE_CD field is set as an integer, then you shouldn't have any problems.. however, if it is set as a char, varchar, text or any other form of strings then you will need to supply some quotes for the value.

ie:

strSQLRating = "SELECT SUM(RATING) AS SUM_RATING
FROM RATINGS_MOVIES
WHERE MOVIE_CD = '" & varMovieCD & "'"


Hope this helps,

Gorkem.

 

[onpnt]

good points Gorkem.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[daph]

Thanks guys,

onpnt, the reason I'm asking is because I did this quite a few times on my web site and I thought maybe it was the reason why I have some errors.

I have this error popping up once in a while and it drives me crazy:

Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
/tenderpaws/testing/list.asp, line 19

What drives me crazy about it is that it works MOST of the time...but once in a while, it will appear.

Now, I KNOW that usually it means that I mispelled a field name or that maybe I assigned a value with the wrong type. As for the field name, I know it's correct. The only thing about it that I'm not sure, is if it is case-sensitive. In my table it is movie_cd and in my source it is MOVIE_CD. Besides that and the previous questions I had, I don't really know what else it could be.

Do you have any ideas? If you need more details, I wouldn't mind clarifying a few things...especially if it help me out

Thanks again for your answers.
Daph

 

[Gorkem]

Just to make sure, you should use the CINT function to convert the MOVIE_CD value..

you should also ensure that MOVIE_CD isn't NULL..

ie:

if varMOVIE_CD > 0 and varMOVIE_CD <> &quot;&quot; then
varMOVIE_CD = CINT(varMOIVE_CD)
else
response.write &quot;varMovieCD is NULL&quot;
end if

strSQLRating = &quot;SELECT SUM(RATING) AS SUM_RATING
FROM RATINGS_MOVIES
WHERE MOVIE_CD = &quot; & varMovieCD

Set rsSQLRating = objDBConn.Execute(strSQLRating)





And no.. SQL/Access isn't case sensitive.


Cheers,

Gorkem.

 

[daph]

Thanks a whole lot Gorkem,

I will try that and see if it corrects the error.
I truly appreciate it!

Daph

 

[Gorkem]

No problem at all.

Cheers,

Gorkem.