RecordSet Filters with variable names

Results 1 to 5 of 5

Thread: RecordSet Filters with variable names

  1. #1
    warren Boe Guest

    Default RecordSet Filters with variable names

    I am trying to filter a recordset using a value from a form that has been posted to the page in question. I can filter the recordset with constant data but not the variable that comes from a form. Here is the code I am trying to develop. Any help would be appreciated.<BR><BR>&#060;% <BR>Dim MyConn, MyRS, MySQL, strfname, strlname, struserid, struserpw, stremail %&#062; <BR><BR>&#060;% strFName=Request.form("First_Name")%&#062;<BR>&#06 0;% strLName=Request.form("Last_Name")%&#062;<BR>&#060 ;% struserID=Request.form("userID")%&#062;<BR>&#060;% strUserPW=Request.form("UserPW")%&#062;<BR>&#060;% stremail=Request.form("email")%&#062;<BR>&#060;% <BR>Set MyRS = Server.CreateObject("ADODB.Recordset")<BR>MySQL = "Select * from users;"<BR>MyRS.Open "Select * from Users", MyConn, adopenstatic, adlockoptimistic<BR><BR>MyRS.filter = "UserID = &#039"&struserID&"&#039 "<BR><BR>Thanks<BR>

  2. #2
    Tom Anderson Guest

    Default RE: RecordSet Filters with variable names

    2 things.<BR><BR>1. try a response.write struserID and see what is acutally getting passed. There is a good possibility that you are not getting what you think you are getting.<BR><BR>2. is userID a number field? if so, you are setting your field like a string.<BR>MyRS.filter = "UserID = &#039"&struserID&"&#039 "<BR>should be like:<BR>MyRS.filter = "UserID = "&struserID

  3. #3
    Warren Boe Guest

    Default RE: RecordSet Filters with variable names

    StruserID is a string and I know what is being passed and it is correct. I wonder if you can use a variable in a filter statement.

  4. #4
    Tom Anderson Guest

    Default RE: RecordSet Filters with variable names

    I know you can pass a variable to a filter, I&#039ve done it. Other than that, I don&#039t know what to tell. When I have problems like this, it usually ends up being something very simple that I missed. The only other thing that I could think of is you said struserID is a string, are you sure that userID is a string in you DB? If it is, I&#039m at loss.

  5. #5
    Join Date
    Dec 1969

    Default Can I ask Why Filters?

    Is there some reason you want to use a filter on the RecordSet instead of using a query to restrict the records you get back?<BR><BR>You *do* realize that if your recordset has 10,000 records, then your code will move ALL 10,000 from the DB to your RecordSet object...for you to then filter down to one record.<BR><BR>HORRIBLY inefficient compared to just doing a WHERE field=value query in your SQL.<BR><BR>Do you have some reason to *need* all the records, after you are done filtering.<BR><BR>As for the filter not liking a variable: The filter has not got the foggiest notion that you are even USING a variable. The filter gets a STRING. Period. If you are really worried about it, do<BR><BR>&#060;%<BR>filterValue = "UserID = &#039" & strUserID & "&#039"<BR>MyRS.filter = filterValue<BR>%&#062;<BR><BR>A string expression in VBS is 100% the same as a string, so far as any method or property using the expression value is concerned.<BR><BR>I would push you back: You say "...I can filter the recordset with constant data...". So show us an example of that which works.<BR><BR>Now show us an example where you use the Request.Form, using the intermediate variable "filterValue" as shown above, but *ALSO* put in some debugging code:<BR><BR>&#060;%<BR>filterValue = "UserID = &#039" & strUserID & "&#039"<BR>Response.Write "filter value is [" & filterValue & "]&#060;HR&#062;"<BR>MyRS.filter = filterValue<BR>%&#062;<BR><BR>and let us see if, indeed, the two filter values being used *are* 100% the same. Hokay? Please?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts