...

View Full Version : Database & sql function



littlejones
12-07-2006, 08:56 PM
Can anyone help me out. I've been told I need to tidy up my code, and instead of having this sort of code in all of my pages...

Set objConn = Server.CreateObject(ADODB.Connection)
Set rs = Server.CreateObject(ADODB.RecordSet)

strSQL = "SET NO COUNT ON; " &_
"SELECT * FROM tblWhatever;"

rs.open strSQL, objConn

objConn.close
Set objConn = nothing
Set rs = nothing


I've been told I need to make a function or something that will save me writing all that code, but searching google I haven't found anything simple to follow.

If you can help that would be great!

mehere
12-07-2006, 11:10 PM
i generally create 2 functions, 1 for the connection and 1 for the recordset that i have in a file that's included on all my pages.
like this:

function dbConn()
dim Conn, RS
set Conn = Server.CreateObject("ADODB.Connection")
'SERVER CONNECTION STRING (NEW DRIVER)
DSNName = "Provider=SQLOLEDB;" & _
"Data Source=" & gvServer & ";" & _
"Initial Catalog=" & gvDBDev & ";" & _
"User Id=" & gvUser & ";" & _
"Password=" & gvPassword & ""
Conn.ConnectionString = DSNName
Conn.Open
set dbConn = Conn
end function
%>

<%
'CREATE RECORDSET
function GetRS(SQLpage)
set GetRS = Server.CreateObject("ADODB.Recordset")
with GetRS
.CursorLocation = 3
.CursorType = 3
.ActiveConnection = dbConn
.Open SQLpage
end with
end function
then to call it, i just have this on the page where i need a recordset.

strSQL = "SET NO COUNT ON; " &_
"SELECT * FROM tblWhatever;"
set rs = GetRS(strSQL)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum