Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Oct 2006
    Posts
    82
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Database & sql function

    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!

  • #2
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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.
    Code:
    strSQL = "SET NO COUNT ON; " &_
    "SELECT * FROM tblWhatever;"
    set rs = GetRS(strSQL)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •