To start a "Request For Order", users can do a couple of things.
1. Go to our vendor table and click a vendor link which will send the "key" field for that record to the RFO and automaticly fill in user information. The key field is a auto generated number.
2. Enter either the organization's FEIN (Federal ID)which will send the "fein" files format= 12-3456789 to the RFO for lookup.
3. Enter a University ID number for that organization "uiid"...format= People: 123456789 Organization: @12345678

my RFO works great for everything unless I type in the vendor uiid (@12345678)...I get the error message 'No value given for one or more required parameters.' FUNNY THING...it works fine for People uiid's (123456789)

sSQL = "SELECT * " & _
  " FROM vendor" & _
  " WHERE fein = '" & Request.Form("search_string") & "'" & _
  " OR uiid = '" & Request.Form("search_string") & "'" & _
  " OR key = " & Request.Form("search_string")
Set rs = Connect.Execute(sSQL)


however if I make this small change it works fine for Organization uiin's (@12345678) but of course it now does not work when sending a "key" field after the user clicks on a vendor link.

sSQL = "SELECT * " & _
  " FROM vendor" & _
  " WHERE fein = '" & Request.Form("search_string") & "'" & _
  " OR uiid = '" & Request.Form("search_string") & "'"
Set rs = Connect.Execute(sSQL)

Does anyone understand this?

Hi there,

Are you using SQL Server?

In SQL Server, parameters in Stored Procedures start with an @ symbol. So, I think your @12345678 is interpreted as a variable with the name 12345678.

What datatype is key in the database? It looks like a text type (char, varchar etc), because of the @. In that case, you'll need to enclose its value in apostrophes as well:

" OR key = '" & Request.Form("search_string") & "'"

Cheers,

Imar



---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

when I enclose the Request.Form for the key field...my page does not work when sending a key threw. It's a AutoNumber Long Integer

Maybe I should write an If...if it coming from the vendor lookup use
sSQL = "SELECT * " & _
  " FROM vendor" & _
  " WHERE fein = '" & Request.Form("search_string") & "'" & _
  " OR uiid = '" & Request.Form("search_string") & "'"
Set rs = Connect.Execute(sSQL)

else use
 sSQL = "SELECT * " & _
  " FROM vendor" & _
  " WHERE key = " & Request.Form("search_string")
Set rs = Connect.Execute(sSQL)

Ill bet that will work...?

How can an auto number contain an @ symbol??

Or should people enter a key without the @? In that case, your last solution is close. How about this:

If Left(Request.Form("search_string"), 1) = "@" Then
  ' First char is an @
  sSQL = "SELECT * " & _
    " FROM vendor" & _
    " WHERE fein = '" & Request.Form("search_string") & "'" & _
    " OR uiid = '" & Request.Form("search_string") & "'"
Else
  sSQL = "SELECT * " & _
    " FROM vendor" & _
    " WHERE key = " & Request.Form("search_string")
End If


Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I'm sorry the "@number" is a user ID number
the autonumber is the key field.
The reason the "@number" in not the key field is because organizations may have more than one address needing more than one entry.

User can enter
Person UID = "123456789"
Business UID = "@12345678"
or
User can click on link in vendor table
Key field is sent

Well, either way, examine the value the user entered. If it contained an @, treat it as a string, and use it for string type column, and enclose its value in apostrophes.

If it doesn't contain an @, treat it as a number. You can still pass it to string type columns, enclosed in apostrophes, but now you can also pass it to (Auto)number column type, without the apostrophes


Does that make sense?

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I used your code...and of course it works.
Thank you again Imar;)