General questions about SQL db connection using ASP pages.

[Brave]

Hi,

I normally work with MS Access databases using ASP pages. I have been
given the opprotunity to work on some available SQL server space, and
wanted to ask a couple of questions. The following is a simple update
query that I use. It is an ASP page that connects to an MS Access Db:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
myDSN = "DRIVER={Microsoft Access Driver (*.mdb)};"
myDSN = myDSN & "DBQ=\\xxxxx\xxxxx\xxxxx\database003.mdb"
mySQL="UPDATE SKUList SET SKUList.Department='Furniture' WHERE
SKU='123456'"
Conn.Open(myDSN)
Conn.Execute(mySQL)
Conn.Close
Set Conn = Nothing
%>

I was wondering if someone could tell me (or better yet show me) how
this code would look in a SQL server enviroment. I have no information
on the server itself as the database will be setup for me, and I will
just need to create ASP pages to connect.

Any information, SQL code samples, or even a good online resource to
help me get started.
I think once I see a a couple of codes examples, then it will fall into
place for me.

Thanks for your time.

 

[Bob Barrows [MVP]]

Hi,

I normally work with MS Access databases using ASP pages. I have been
given the opprotunity to work on some available SQL server space, and
wanted to ask a couple of questions. The following is a simple update
query that I use. It is an ASP page that connects to an MS Access Db:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
myDSN = "DRIVER={Microsoft Access Driver (*.mdb)};"
myDSN = myDSN & "DBQ=\\xxxxx\xxxxx\xxxxx\database003.mdb"

I prefer to use the native OLE DB provider:
myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\xxxxx\xxxxx\xxxxx\database003.mdb"

mySQL="UPDATE SKUList SET SKUList.Department='Furniture' WHERE
SKU='123456'"
Conn.Open(myDSN)
Conn.Execute(mySQL)
Conn.Close
Set Conn = Nothing
%>

I was wondering if someone could tell me (or better yet show me) how
this code would look in a SQL server enviroment. I have no information
on the server itself as the database will be setup for me, and I will
just need to create ASP pages to connect.

Very similar. The connection string will change, and how it looks will
depend on whether your server is set up with Windows or Mixed authority.
See:
http://www.aspfaq.com/show.asp?id=2126

I will say that if you plan to continue building sql statements in vbscript
and passing them to the database, instead of using parameterized stored
procedures (whih is also possible in Access). you will miss out on many of
the optimizations that make moving to SQL Server desirable. See:

http://tinyurl.com/jyy0,
as well as:
http://groups.google.com/groups?hl=...=1&[email protected]
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Any information, SQL code samples, or even a good online resource to
help me get started.
I think once I see a a couple of codes examples, then it will fall
into place for me.

www.aspfaq.com

 

[Brave]

Thank you for taking the time to reply. I will look at the website you
have suggested.