ASP & MSSQL Query Examples

Here are some code examples for doing things in ASP with MSSQL (SQL Express):


Setup the Connection

'Setup the connection
Dim aConnectionString

aConnectionString = "Driver={SQL Native Client};Server=sitename\sqlexpress;Database=databasename;Uid=username;Pwd=password;"

'Connect to DB
Dim conn, R, SQL, RecsAffected

Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = adModeReadWrite
conn.ConnectionString = aConnectionString
conn.Open

Setup RecordSet & List all Entries

'select a record set
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select * from table", conn

do until rs.EOF
for each x in rs.Fields
response.write(x.name)
response.write(" = ")
response.write(x.value)
next
rs.MoveNext
loop

rs.close

Select an Entry & See if it Exists

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select * FROM tablename WHERE fieldname = 'fieldvalue'", conn

'check if login worked
dim found

if rs.BOF and rs.EOF then
'no entry found
found = false
else
'entry found
found = true
end if

Add an Entry

'add an entry
'conn.Execute "INSERT INTO tablename (fieldnames) VALUES (fieldvalues)"

Delete an Entry

'delete an entry
'conn.Execute "DELETE FROM tablename WHERE fieldname = 'fieldvalue'"

Update an entry

'update an entry
'conn.Execute "UPDATE tablename SET fieldname = 'somevalue' WHERE fieldname = 'fieldvalue'"

There's a lot more but these are some for reference purposes.