ASP SQL Server连接

Question

<% 
DIM objConn 
Set objConn = Server.CreateObject("ADODB.Connection") 
objConn.ConnectionString = "Data Source=123.123.12.123,1234;Database=DatabaseName;User Id=Usernm;Password=abcd1234;" 
objConn.Open 

DIM mySQL 

mySQL = "SELECT * FROM [Users] WHERE [User ID]='1'" 

DIM objRS 
Set objRS = Server.CreateObject("ADODB.Recordset") 
objRS.open(mySQL, objConn) 

Response.Write objRS("FullName") 

objRS.Close 
Set objRS = Nothing 
objConn.Close 
Set objConn = Nothing 
%> 

我想连接到SQL Server数据库，读取数据并关闭连接。我研究过这些例子，并提出了这个问题。但它不工作。请指导我。我哪里错了？

Answer 1

Dim rs, dbConn 

Function OpenDB() 
    Set dbConn = Server.CreateObject("ADODB.Connection") 
    dbConn.ConnectionTimeout = 300 
    dbConn.CommandTimeout = 300 
    dbConn.Open "Data Source=123.123.12.123,1234;Database=DatabaseName;User Id=Usernm;Password=abcd1234;" 
End Function 

Function CloseDB() 
    Set rs = Nothing 
    if ucase(TypeName(dbConn)) = "CONNECTION" then 
     dbConn.Close 
     Set dbConn = Nothing 
    end if 
End Function 

Function OpenRecordSet(recset, tablename) 
    Call OpenDB() 
    Set recset = Server.CreateObject("ADODB.Recordset") 
    recset.Open tablename, dbConn, 0, 1 
End Function 

Function CloseRecordSet(recset) 
    Set recset = Nothing 
    Call CloseDB() 
End Function 

然后使用

<% 
Call OpenDB() 
sql = "select from mytable where this = 'that'" 
Set rs = dbConn.Execute(sql) 
if not rs.EOF then 
     ' do your stuff! 
end if 
Call CloseDB() 
%> 

http://www.shiningstar.net/articles/articles/database/datafunctions.asp?ID=AW

Answer 2

一些答案建议包装逻辑到功能没有必要为此。

这只是很多绒毛，是不需要的，只需使用ADODB.Command对象。有几百种方法可以解决这个问题，但我发现一次又一次地工作的方法是让ADODB.Command对象完成这项工作，然后使用ADODB.Recordset对象的.GetRows()方法将结果返回到Array。 通过这种方式，您可以快速关闭ADODB.Recordset和ADODB.Command对象，并可以使用Array。

Dim conn, cmd, rs, sql, data, search 

'Assume value to query comes from a Request Collection. 
search = Request("myvalue") & "" 

conn = "Data Source=123.123.12.123,1234;Database=DatabaseName;User Id=Usernm;Password=abcd1234;" 
sql = "select from mytable where this = ?" 
Set cmd = Server.CreateObject("ADODB.Command") 
With cmd 
    'No need to handle connection let ADODB.Command create and destory it. 
    .ActiveConnection = conn 
    .CommandType = adCmdText 
    .CommandText = sql 
    .Parameters.Append(.CreateParameter("@myparam", adVarWChar, adParamInput, 50)) 
    .Parameters("@myparam").Value = search 
    Set rs = .Execute() 
    If Not rs.EOF Then data = rs.GetRows() 
    Call rs.Close() 
    Set rs = Nothing 
End with 
Set cmd = Nothing 
'ADODB.Connection is closed when ADODB.Command is destroyed. 

If IsArray(data) Then 
    rows = UBound(data, 2) 
    For row = 0 To rows 
    'Return first column of the current row 
    Call Response.Write("First Column of Row " & row & " is '" & data(0, row) & "'<br />" 
    Next 
Else 
    Call Response.Write("No records") 
End If