Invalid procedure call or argument: 'Left' 1

[Light777]

Someone help, I've been pulling my hair out over this one. I keep getting an "Invalid procedure call or argument: 'Left'" error whenever I run this piece of code...

strSql="SELECT NAME FROM Places WHERE NAME = '" & Request.Form("StartPlace&quot & "'"
Set rs = db.Execute(strSql)

' If there are no entries in the rs then find some
PlaceString = Request.Form("StartPlace&quot
Do While rs.RecordCount = -1
Length = Len(PlaceString) - 1
PlaceString = Left(PlaceString, Length)
strSql = "SELECT NAME FROM Places WHERE NAME LIKE '" & PlaceString & "%'"
Set rs = db.Execute(strSql)
Loop

This is a simple loop meant to search for a city within the database and if it comes up with more than one match it outputs it for the user to choose which city to use. It's kinda like something you would see on expedia to get an address for a map.

There may be a simpler or better way to handle this search loop. I would appreciate any suggestions on this part as well.

 

[Chopstik]

A quick Google search turned up a couple of possibilities. It also turned up about 900 other pages on the web that have this exact same problem but the authors have left them out there anyway.

What version of IIS are you using? One suggestion was to use IIS 6.0 (there is a 6.0?), though I have to admit I'm finding a difficult time accepting that. Another was that the string you're passing is shorter than the length that you were attempting to set the value of which also seems somewhat unlikely.

Did you try to response.write your results down for PlaceString to ensure that you are getting the value you're expecting? Just a thought...

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[onpnt]

have you written PlaceString to the screen? invalid procedure calls on left() right() mid() type fucntions usually indicats a value missing in there.

on that note: you should always validate your values before calling these types of functions. I think before fixing any code you need to do that.
eg:

PlaceString = Request.Form("StartPlace&quot
If Len(PlaceString) <= 0 Then
response.write &quot;A error occured while processing value&quot;
Else
Do While rs.RecordCount = -1
Length = Len(PlaceString) - 1
PlaceString = Left(PlaceString, Length)
strSql = &quot;SELECT NAME FROM Places WHERE NAME LIKE '&quot; & PlaceString & &quot;%'&quot;
Set rs = db.Execute(strSql)
Loop

now lets think about what you have.
Do while rs count is 0 basically
get length of string - 1 (not sure why but..)
value = a portion of the value (not sure again but..)
execute a build statement
looping

let's restructure.
Why
1)you only need to execute a query once
2) the like clause is being used right but I think not efficiently
you want a place like city say (I think)
so I enter chicago and hit enter

SELECT * FROM places WHERE NAME LIKE '%icag%'
finds all cities that have the pattern icag = chicago

one execute one use of resources
so the mid() would be used here
side note: use seperate variables when manipulating a input value. debugging is far easier.

PlaceString = request.form(&quot;StartPlace&quot
Lng = cInt(Len(PlaceString) / 2) -1
'we get 3 in this case with the cInt to round up on .5 then -1

SearchCriteria = Mid(PlaceString,Lng,Lng+1)
'we get icag

now build the statement (no loops!)
strSql = &quot;SELECT * FROM Places WHERE NAME LIKE '%&quot; & SearchCriteria & &quot;%'&quot;
Set rs = db.Execute(strSql)
If rs.EOF Then
response.write &quot;Nothing found like search criteria&quot;
Else
response.write rs(&quot;NAME&quot
' or something
End If

Now, what you have is fine really but resources need to be concidered. do a loop through every record or one execute and a basic conditional if. if you had 1000 cities you an see the performance comparisons.


_____________________________________________________________________

http://www.onpntwebdesigns.com

 

[onpnt]

IIS 6.0 is on the .NET platform.


_____________________________________________________________________

http://www.onpntwebdesigns.com

 

[onpnt]

furthering my psot s bit I thought I would calrify also another validation or addition that needs to be considered.

say someone searches and the execute on the build results in more cities then one. well there's two nice ways to take that. First in saying stay with as little DB interaction as you need. If you get the values, don't go back to get more; get all of them and hold them

the first way to handle it is a select input (listbox)
so you could load a select with all returned values and have the user select the one they were looking for.

the second is a link for each or better a link to see more results found. just load a &quot;client&quot; javascript array with the values in the initial if then statement on if EOF. then if they want more call the client function to laod the names. limit the view of the names to the user making it less noisy and confusing for them. that;s what I have been successful with anyhow, but those types of methods are really developer specific.



_____________________________________________________________________

http://www.onpntwebdesigns.com

 

[tsuji]

Hello Light777,

[1] What do you think a line like this would give?

    x = left("",-1)

[2] A possible revision would be:

    PlaceString = Request.Form("StartPlace")
    If IsEmpty(PlaceString) Or IsNull(PlaceString) Then
        response.write "How do you want to hand these cases?"
    Else
        strSql="SELECT NAME FROM Places WHERE NAME = '" & PlaceString & "'"
        Set rs = db.Execute(strSql)
        Do While rs.RecordCount = -1 And Len(PlaceString)>=2
            Length = Len(PlaceString) - 1
            PlaceString = Left(PlaceString, Length)
            strSql = "SELECT NAME FROM Places WHERE NAME LIKE '" & PlaceString & "%'"
            Set rs = db.Execute(strSql)
        Loop
        If rs.RecordCount=-1 Then
            response.write "Still can't find nothing. How do you want to deal with it?"
            set rs = nothing
        End If
    End If

regards - tsuji