March 3, 2008 at 2:30 am
This is my very first experience with sql server. I have a select statement that is pulling in a form field value from a select box as a parameter and have no idea how to do this for sql server.
here is the info:
============================
xID= CLng(Request.Form("lname"))
Set Drs = Server.CreateObject("ADODB.RecordSet")
pSql= "Select leagueplayer, ltID, approved From leagueplayers ltID= xID"
Drs.Open pSql, conn, 3, 2, 1
============================
I am familar with access and this works fine there. Would someone be willing to write this so it is appropriate for sql server, this way I can get an example of correct syntax etc.
Thanks and I appreciate any help,
March 3, 2008 at 6:52 am
If I'm reading this correctly, you have:
Select leagueplayer, ltID, approved From leagueplayers ltID= xID
What I would expect to see in a SQL Server query is something like this (cleaned up because I hate sloppy code):
SELECT l.leagueplayer,
l.ltID,
l.approved
FROM leagueplayers l
WHERE l.ltID= @xID
That assumes that you're going to pass in the xID as a parameter. If you're planning on simply doing ad hoc SQL (not the best choice) then you'd have to do it more like this:
'SELECT l.leagueplayer,
l.ltID,
l.approved
FROM leagueplayers l
WHERE l.ltID= ' + xID
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2008 at 7:20 am
I am totally with Grant. Only thing I would add is to make this into a stored Proc with xid as a parameter and call the stored from your code.
-Roy
March 3, 2008 at 8:55 am
SELECT l.leagueplayer,
l.ltID,
l.approved
FROM leagueplayers l
WHERE l.ltID= @xID
In this case is the @xID defined somewhere. I am using VB Script so I am wondering if the @ is a valid character without somehow defining it.
For Example in vbscript this would look like this
"SELECT l.leagueplayer,
l.ltID,
l.approved
FROM leagueplayers l
WHERE l.ltID=" & @xID
or
"SELECT l.leagueplayer,
l.ltID,
l.approved
FROM leagueplayers l
WHERE l.ltID= @xID"
Forgive me, I am not questioning your expertise, but merely displaying a lack of my own.
From what I have read, SQL server does not like the & and vbscript does not like the + . In all of the tutorials and articles I have read I have not seen one example with the two used together but I know it is (or probably was, with the advent of asp.net) done all of the time. I believe sql server prefers single quotes and vbscript requires double as well. Do you know of any vbscript +/& sql server guides or tutorials that may be able to guide me?
Thanks again, I appreciate the help.
March 3, 2008 at 9:09 am
Sorry, I wasn't clear. I showed how to build the SQL string in TSQL. You're correct. To build an execution string in VB, you'd use the &.
However, as Roy mentioned above, instead of building a string, you'd be better off writing a stored procedure and then calling that from your VB code. Or, barring that, writing the TSQL code using a parameter and then create a parameter collection & assign it on the VB side of things. Parameterized queries, procs or not, are better than executing ad hoc SQL strings.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2008 at 10:08 am
Thanks Grant
I didn't mention that I was working in vbscript so that was my mistake.
I must be missing something though because I am still not sure how we arrive at @xID is this just implied as a parameter to sql server or does it have to be defined previously as a parameter.
Many of the examples I have seen show a parameter "list" for lack of a better word, somewhere above the select statement. In each of thee they seem to define the parameter data type as well. Is this necessary here or were these examples of TSQL that I saw? Sorry but this is literally my first attempt at this.
Thanks again for all of your help.
March 3, 2008 at 10:37 am
create (3/3/2008)
Thanks GrantI didn't mention that I was working in vbscript so that was my mistake.
I must be missing something though because I am still not sure how we arrive at @xID is this just implied as a parameter to sql server or does it have to be defined previously as a parameter.
Many of the examples I have seen show a parameter "list" for lack of a better word, somewhere above the select statement. In each of thee they seem to define the parameter data type as well. Is this necessary here or were these examples of TSQL that I saw? Sorry but this is literally my first attempt at this.
Thanks again for all of your help.
Not a problem. These were all examples of TSQL code.
The samples where you see the parameter list would be where you use the @xID. Otherwise, if you're going to just build the string in VB, you'd refer to the variable or property where the xID value was contained (converting it to string in order to concatenate with the rest of the string, of course). I'd suggest looking at those examples. Actually, I think they substitute '?' for actual paramter names, but I might be wrong about that. I don't spend much time in the code these days.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2008 at 9:10 pm
I decided to go with an inline function and pass the parameter to it. This is a new query but very similar.
Like this:
CREATE FUNCTION league_Team (@xID int)
Returns Table
RETURN SELECT ID, teamname, leaguedesc FROM leagueteam
WHERE
ID = @xID
I am collecting the variable with Request.Form from a select box in a form. Like this:
xID= CLng(Request.Form("lname"))
I have tried Cint etc instead of CLng here but get the same error.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Conversion failed when converting the varchar value 'xID' to data type int.
Right now there are only 2 records in the db one has an ID=3 the other ID=4 so at this point I know we are getting numbers in to the initial string. ID is int Max Bytes 4 Precision 10. ID is also the primary key.
Any ideas here, I even tried casting the parameter in the function to an int to no avail. (have since altered it back to what you see above.) Any help would be appreciated.
March 4, 2008 at 5:33 am
I definately recommend you NOT use a function for this type of query. It's a straight select statement. It should be done within a stored procedure or a parameterized query.
On the other hand, to call a table valued function, you still need write it as a select statement. It's functioning as a table so it has to be treated as one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2008 at 7:19 am
xID= CLng(Request.Form("lname"))
try
pSql= "Select leagueplayer, ltID, approved From leagueplayers where ltID='" & Request.Form("lname") & "'"
after ...where ItID= is a single then double quote.
after ....Form("lname") & is a double then single then double quote.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply