SQL Statment problem 2

[guineamation]

Hi All,
when trying to execute this SQL Statment:

MySQL = "SELECT M_FORMAT, M_FULLNAME"
MySQL = MySQL & " FROM TBL_MACHINA"
MySQL = MySQL & " WHERE M_FULLNAME Like ""*"& request.QueryString("frm_Type") &"*"""

i get this error:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

the same statement works perfectly when i execute it directly in access, why oh why?!

 

[Sheco]

Always the first thing I try when debugging this sort of problem is to use a Response.Write

Add something like this:

Response.Write "<BR>" & MySQL & "<BR>" 
Response.End

A lot of time when this happens to me the problem is that my SQL is not what I imagine it to be...

 

[bslintx]

first to make it easir to read, concatenate your sting w/ & _ and replace wildcard w/ % for like clause

MySQL = "SELECT M_FORMAT, M_FULLNAME"
MySQL = MySQL & " FROM TBL_MACHINA"
MySQL = MySQL & " WHERE M_FULLNAME Like ""*"& request.QueryString("frm_Type") &"*"""

to:

' db_connection
  Set objConn = Server.CreateObject("ADODB.Connection")
  Set objRS = Server.CreateObject("ADODB.Recordset") 
  FilePath = Server.MapPath("your_database.mdb")
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
  objConn.Open strConn 

' default for querystring is w/o querystring
' for Request.Form you need the .Form
' Dim variable

Dim frm_Type
frm_Type = Request("frm_Type")

' Assuming frm_Type = to string then you need '' surrounding
' the requested form value to look up in database

MySQL = "SELECT M_FORMAT, M_FULLNAME " & _
        "FROM TBL_MACHINA " & _
        "WHERE M_FULLNAME LIKE'%" & frm_Type & "%';"


objRS.Open MySQL, objConn

If objRS.EOF Then
  Response.Write "No records found"
Else
  Response.Write objRS("M_FULLNAME")
End If

objRS.Cloe
Set objRS = Nothing

objConn.Close
SET objConn = Nothing

 

[bslintx]

correction, since there COULD be more than 1 w/ same full name create a loop to get all..

change

If objRS.EOF Then
  Response.Write "No records found"
Else
  Response.Write objRS("M_FULLNAME")
End If

to

If objRS.EOF Then
  Response.Write "No records found"
Else
  Do While Not objRS.EOF
  Response.Write objRS("M_FULLNAME")
  objRS.MoveNext
  Loop

  objRS.Close
 
End If

 

[bslintx]

as sheco mentioned w/ a good debugging tool...this error usually indicates a column that's been misspelled or a table hence why it can't look up because there aren't any paremeters to look up...so make sure your SQL statement prints out crectly using his debug method

 

[guineamation]

the response.write didn't work because of the error, but the modifications suggested by bslintx did the trick.

Thanks!

 

[Sheco]

I'm glad that it worked out for you.

I should have clarified that the place to put the Response.Write is AFTER you create the SQL string but BEFORE you actually use it to Open a recordset.

It is a good general purpose technique, maybe it will help you at some point in the future.

 

[bslintx]

bummer sheco and myself missed that one...as he said when debugging sql statement simply comment out the open statement and the code that contains the objRS that are going to be listed so ONLY your Response.Write MySQL is the only thing being processed...it it an invaluable too...and in the future many leave this debug tool IN the code but comment it out to debug future problems, ie;

MySQL = "SELECT M_FORMAT, M_FULLNAME " & _
        "FROM TBL_MACHINA " & _
        "WHERE M_FULLNAME LIKE'%" & frm_Type & "%';"

' Debug sql statement, comment out open statement
' Response.Write MySQL

objRS.Open MySQL, objConn

If objRS.EOF Then
  Response.Write "No records found"
Else
  Do While Not objRS>EOF
  Response.Write objRS("M_FULLNAME")
  objRS.MoveNext
  Loop
End If

glad it worked for you!

 

[Sheco]

I use Response.End to halt execution of the script immediately after I write out the values.

Sometimes there is a second or third error in the remaining code. Sometimes it is a trickle-down effect caused by the first error. So I just cut the execution off and see what I've got in my variables.

 

[bslintx]

even better sheco...thanks...i myself will use that now....thatnks for the tip! now i won't have to comment out the whole dame code....ironically i didn't know about the response.write would do that...so since i didn't get a star;-( ...here's one for you....it was worth it because don't laugh...i comment out the code...this saves me big time...ty ty ty

 

[Sheco]

".. i didn't know about the response.write would do that"

You mean Response.End ???

Also I'll give you a star 'cause you fixed his problem.

I hope all these stars don't look incestuous.

 

[bslintx]

thanks sheco...i saw that typo but i figured it would be implied i meant end ;-) i suggested to the forum staff to have a way to "edit" posts, instead of reposting a post to cover a edit problem...because it simply fills up too fast!...Also, no I don't think it's incestuous(interconnected) but more like deserving...i don't give out stars like candy as you can see by my profile...i honestly will use your response.end in the future and did not use before where now i can honestly save a headache using this....bottomline your star was well-deserved..and thanks again for another useful tip!