How to assign the result of SQL to variable?

[sabavno]

Hi

How would I assign the result of the query to the variable in ASP?

Example:

intTransit = "Select Transit from Transits Where EmployeeID=" & intEmployeeID

 

[Tarwn]

You will have to execute this statement using one of three objects: ADODB.Connection, ADODB.Command, or ADODB.Recordset
w3schools has very good references for all three in their ADO section, as well as examples of each of these. The basic idea is to use the object to execute the query and then set a recordset object = to the executing statement. When using the Recordset itself to execute (.Open) the query the results will automatically be entered into the recordset object.
w3schools links:

http://www.w3schools.com/ado/default.asp

- ADO Introduction
The links on the left side of the page should provide you with everything you need.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[sabavno]

What I need to do is to look up the values of the specific fields based on the value entered by the user.

intTransit = "select transit from transits where EmpID=" & userinput
strName= "select name from transits where EmpID=" & userinput


to retrieve each value, would I have to open a different recordset?

 

[Tarwn]

No, you could create a single SQL statement that would pull in a recordset containing all of your information:
in this case - sqlString = "SELECT transit, name FROM transits WHERE EmpID = " & userinput

This is still a simple string though. You actually have to make a connection to the database and send/execute the query before you will get results back that you can assign to individual variables.

Simple select stataments take the form of:

SELECT fieldname[,fieldname,fieldname....] FROM tablename[,tablename,tablename] [WHERE condition=value[AND/OR condition2=value2 ...]]

You can also replace the fieldname portion with a * which will allow you to select all the fields from the records that match your criteria.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)