SQL server 2000 and ASP.NET

[Thomas Storey]

Hi all,

For the last couple of years I have been using regular ASP to code my web
pages, but now I would like to get into ASP.NET

Ive been playing around a little but have not been able to figure out how to
connect to an SQL 2000 server and run a bunch of SELECT, DELETE, and UPDATE
queries against it.

In the old days, I would do something like this:

dim objDB
set objDB = server.createobject("ADODB.Connection")
objDB.Open "insert connection string"

dim objRS
set objRS = server.createobject("ADODB.Recordset")

And then either do a select, delete or update query by typing:

objRS.Open "sql query"

When extracting data from the database after doing a select query, I would
type:

variable = objRS.Fields("field name")

However, I have not been able to figure out how to do the same thing using
ASP.NET.

Can anyone give me a code sample to do something the same as the above, only
in ASP.NET?

All of the examples I have seen on the internet use some datagrid and
display all of the data on a web page, but I just want to extract certain
rows and then certain columns.

Cheers.

 

[Mark Fitzpatrick]

Try starting here instead:
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

That should give you an idea of ADO.Net. Also, find a good ADO.Net book as
well since ADO and ADO.Net are like night and day. There are no more
recordsets, they've been replace (sort of) by DataReaders. A DataReader is a
forward only collection of results from the server that pulls the results
from the server as a row is requested. There is also a DataSet, which
contains DataTables. DataTables can store the results from a query and can
even have a relationship with other DataTables. Basically, it's a whole new
world but it does have huge, huge possibilities.

Another thing about ADO.Net is it's there are specific libraries for access
depending upon the technology. For example; there's a
System.Data.OleDBClient series of objects to connect to OleDB compliant
databases, and then there's a System.Data.SqlClient namespace containing
ADO.Net objects to connect to SQL Servers. There are connection objects in
both, but their names according to which access technology you use (like a
SqlConnection or SqlCommand object).

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

 

[Thomas Storey]

Thanks for your reply.

I managed to get it working

This is the code I use. I have only tried it once, very shortly because i
ended up going to bed after I figured it out:

I first import System.Data

<%@ Import namespace="System.Data" %>

Then I use this code to read variables from the database:

dim cmd
dim sSql
dim cnn as new SqlClient.SqlConnection("connection string")
dim dr as SqlClient.SqlDataReader

cnn.Open()

sSql = "sql query"

cmd = new SqlClient.SqlCommand(sSql, cnn)

dr = cmd.ExecuteReader()

variable = dr.Item("column name")

It works a charm

I will now be working to perform insert, update and delete queries, though
this should be as simple as removing the last two lines of the code above
and placing the relevant query in the sSql string.