ASP连接SQL服务器代码

<%

userAcc=request.Form("userAccount")

userPsw=request.Form("userPsw")

userName=request.Form("userName")

userSex=request.Form("userSex")

Dim StrServer,StrUid,StrSaPwd,StrDbName

StrServer=".\SQLEXPRESS" '数据库服务器名,如果数据库放在了根目录里,数据库服务器名就是(local)

StrUid="sa" '您的登录账号,采用SQL身份验证时输入的账号

StrSaPwd="12345" '您的登录密码,采用SQL身份验证时输入的密码

StrDbName="MyDatabase" '您的数据库名称

Dim Conn '数据库连接

Dim StrDSN '数据库连接字符串

Dim Rs '命令字符串

StrDSN="driver={SQL server};server="&StrServer&";uid="&StrUid&";pwd="&StrSaPwd&";database="&StrDbName

'建立和数据库master的连接

set Conn = Server.CreateObject("ADODB.Connection")

set Rs=Server.CreateObject("ADODB.RecordSet")

Conn.Open StrDSN

'********************************************************************

'********************************************************************

'向数据库中添加记录

sub recordAdd()

if userName="" or userPsw="" then

    response.Write "The User Name or Password cannot be empty!!":exit Sub

end if

dim strSql1

strSql1="select UserName from test"

rs.open strSql1,Conn,1,1

do while not rs.EOF

for i=0 to rs.Fields.Count-1

if userName=rs.Fields(i).Value then

   response.Write "The user existed,record Add Failed":exit Sub

end if

next

rs.MoveNext

loop

rs.Close

dim strSql3

strSql3="INSERT INTO test(userAccount,userPsw,userName,userSex) VALUES('"&userAcc&"','"&userPsw&"','"&userName&"','"&userSex&"')"

rs.open strSql3,Conn,1,3

response.Write "Record successfully!"

end Sub

 

Sub readdb()

dim strSql2

strSql2="select * from test"

rs.open strSql2,Conn,1,1

if rs.EOF then response.write "no record at all":exit sub

response.write "<table border=1 align='center'>"

response.write "<tr>"

for i=0 to rs.Fields.Count-1

response.write "<td><font color=blue>"&rs.Fields(i).Name&"</font></td>"

next

response.write "</tr>"

while not rs.EOF

response.write "<tr>"

for i=0 to rs.Fields.Count-1

response.write "<td>"&rs.Fields(i).Value&"</td>"

next

response.write "</tr>"

rs.MoveNext

wend

response.write "</table>"

rs.Close

end Sub

 

'释放数据库连接对象

set rs=nothing

set conn=nothing

%>