ANmaDB functions

Database functions, commonly used in my systems.
Should be working for each SQL server or Access mdb (mostly used in my tools)
This is not complete, it is just an initial version, not tested yet


Public

Tested

My Own Work
' List of functions
ANmaDB_Insert1(TableName, FieldColumns, FieldValues)
        Inserts 1 row
ANmaDB_Update1(TableName, UpdateCol, NewValue, WhereStatement)
        Updates 1 fields from 1 row, accepts custom where
ANmaDB_DeleteX(TableName, WhereStatement)
        Deletes rows, accepts custom where
ANmaDB_SelectX(TableName, SelectColumn, WhereStatement)
        Returns whole table matching
        Select * from a table, accepts custom where condition
ANmaDB_Select1(TableName, SelectColumn, WhereStatement)
        Select certain column, accepts custom where condition
ANmaDB_CountX(TableName, WhereStatement)
        Returns count of a table, accepts custom where condition

' Helpful small functions
ANmaDB_Close()
ANmaDB_Open(DBType, MDBFile)
ANmaDB_Cmd(sSQL)
        Executes SQL command, any command with nothing needed to return -- Conn already defined
ANmaDB_TableName(TableName)
        Makes sure table name has [] around it
ANmaDB_Where(Where1)
        Makes sure where has " Where " before it condition
ANmaDB_In(FiledsList)
        Makes sure value list is enclosed with ()


Function ANmaDB_TableName(TableName)
    Rett                        = TableName
    If Left(Trim(TableName),1) <> "[" Then Rett = " [" & TableName & "] "
    ANmaDB_TableName        = Rett
End Function
Function ANmaDB_Where(Where1)
    Rett                        = " " & Where1
    If UCase(Left(Trim(Where1), 6)) <> "WHERE " Then Rett = " Where " & Where1
    ANmaDB_Where            = Rett
End Function
Function ANmaDB_In(FiledsList)
    ' Adds ( and ) to a string if not found
    Rett                        = " " & FiledsList ' Assumption that passed text has all needed spaces and brackets
    If Left(Trim(FiledsList), 1) <> "(" Then Rett = " (" & FiledsList & ") " ' If not
    ANmaDB_In                = Rett
End Function

' ########################################################################## Database
Function ANmaDB_Close()
    ' Closes connection to DB
    '    Needed to be run once, at end of page
    '
    Set Conn                    = Nothing
    Set rsDB                    = Nothing
End Function
Function ANmaDB_Open(DBType, MDBFile)
    ' Opens connection to DB
    '     Needed only once, recommended at start of page
    '
    Dim Conn
    Dim rsDB
    Err.Clear
    On Error Resume Next
    Set Conn                    = Server.CreateObject("ADODB.Connection")
    If DBType = 1 Then     ' Access mdb file
        DB3File_URL            = MDBFile ' "/Assets/DB1.mdb"
        ConnString            = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=" & Server.MapPath(DB3File_URL) & "; " & _
            "User Id=admin; " & _
            "Password=; "
        Conn.Provider        = "Microsoft.Jet.OLEDB.4.0"
        Conn.Open Server.MapPath(DB3File_URL) ' "c:/webdata/northwind.mdb"
       
        Set rsDB                = Server.CreateObject("ADODB.Recordset")
        rsDB.ActiveConnection     = ConnString
        'rsDB.Source            = "SELECT * FROM Settings Where ItemID like 0"
        rsDB.CursorType    = 0 : rsDB.CursorLocation        = 2 : rsDB.LockType                = 1
        rsDB.Open()
    ElseIf DBType = 2 Then     ' SQL Server DB
        DBServer                = ""
        DBName                = ""
        DBUser                = ""
        DBPassword            = ""
        'ConnString            = "PROVIDER=SQLNCLI11 ;SERVER=" & DBServer & ";DATABASE=" & DBName & _
        '    ";USER ID=" & DBUser & ";PASSWORD=" & DBPassword & ";"
        ConnString            = "PROVIDER={SQL Server} ;DATA SOURCE=" & DBServer & _
            ";DATABASE=" & DBName & ";USER ID=" & DBUser & ";PASSWORD=" & DBPassword & ";"
        Conn.Open ConnString
    End If
End Function

Function ANmaDB_Cmd(sSQL)
    ' Executes SQL command, any command with nothing to return -- Conn already defined
    Conn.Execute sSQL
End Function

Function ANmaDB_Insert1(TableName, FieldColumns, FieldValues)
    ' Inserts 1 row in DB
    '    SQL47                    = "Insert Into [Blog] (ID, Title, Body, ImageURL, Tags, UserID, DateAdded, Status) " & _
    '        "Values(" & Edit_or_New & " , '" & BlogPost1 & "' , " & _
    '        "'" & BlogPost4 & "' , '" & BlogPost7 & "' , '" & BlogPost5 & "' , '" & BlogPost2 & "' , " & _
    '        BlogPost3 & " , " & BlogPost8 & ") "
    '
    tTable                    = ANmaDB_TableName(TableName)
    AllColumns                = ANmaDB_In(FieldColumns) ' Assumption that passed text has all needed spaces and brackets
    AllValues                = ANmaDB_In(FieldValues)
    SQL47                        = "Insert Into " & tTable & AllColumns & " Values" & AllValues
    ANmaDB_Cmd SQL47
End Function

Function ANmaDB_Update1(TableName, UpdateCol, NewValue, WhereStatement)
    ' Updates 1 value in a table
    '         "Update [Blog] Set Status = " & NewStat & " Where ID = " & PostID
    ' Can also be ...
    '         "Update [Blog] Set Status = " & NewStat & " Where ID in (1,2,3,4,5) "
    tTable                    = ANmaDB_TableName(TableName)
    Stt1                        = ANmaDB_Where(WhereStatement)
    SQL47                        = "Update " & tTable & " Set " & UpdateCol & " = " & NewValue & Stt1
    Response.Write SQL47
    ANmaDB_Cmd SQL47
End Function

Function ANmaDB_DeleteX(TableName, WhereStatement)
    ' Deletes row(s) based on a where
    '         "Delete From [Fwds] Where FwdID = '" & FwdPost1 & "' "
    '    Can also be ...
    '         "Delete From [Fwds] Where FwdID in (1,2,3,4)"
    tTable                    = ANmaDB_TableName(TableName)
    Stt1                        = ANmaDB_Where(WhereStatement)
    SQL47                        = "Delete From " & tTable & Stt1
    ANmaDB_Cmd SQL47
End Function

Function ANmaDB_SelectX(TableName, SelectColumn, WhereStatement)
    ' Executes select SQL and return full table as string
    '
    Rett                        = ""
    tTable                    = ANmaDB_TableName(TableName)
    Stt1                        = ANmaDB_Where(WhereStatement)
    rsDB.Source                = "SELECT * FROM " & tTable & Stt1
    rsDB.CursorType        = 0 : rsDB.CursorLocation        = 2 : rsDB.LockType                = 1
    rsDB.Open()
    Do Until rsDB.Eof
        It1                    = rsDB.Fields.Item("SettingName").Value
        It2                    = rsDB.Fields.Item("SettingValue").Value
        If It1 = "Something DB" then
            ItemName            = It1
            ItemViews        = It2
        End If
        rsDB.MoveNext
    Loop
    rsDB.Close
    ANmaDB_SelectX            = Rett
End Function

Function ANmaDB_Select1(TableName, SelectColumn, WhereStatement)
    ' Executes a select to return only 1 value for 1 column
    '    Can pass a where if needed
    Rett                        = ""
    tTable                    = ANmaDB_TableName(TableName)
    Stt1                        = ANmaDB_Where(WhereStatement)
    rsDB.Source                = "SELECT " & SelectColumn & " FROM " & tTable & Stt1
    rsDB.CursorType        = 0 : rsDB.CursorLocation        = 2 : rsDB.LockType                = 1
    rsDB.Open()
    If Not rsDB.Eof Then Rett = rsDB.Fields(0).Value
    rsDB.Close
    ANmaDB_Select1            = Rett
End Function

Function ANmaDB_CountX(TableName, WhereStatement)
    ' Executes a select to return only 1 value, like a count of table
    '    Can pass a where if needed
    Rett                        = ""
    tTable                    = ANmaDB_TableName(TableName)
    Stt1                        = ANmaDB_Where(WhereStatement)
    rsDB.Source                = "SELECT Count(*) FROM " & tTable & Stt1
    rsDB.CursorType        = 0 : rsDB.CursorLocation        = 2 : rsDB.LockType                = 1
    rsDB.Open()
    If Not rsDB.Eof Then Rett = rsDB.Fields(0).Value
    rsDB.Close
    ANmaDB_CountX            = Rett
End Function

Views 2101 Downloads 762

Classic ASP Database
ANmarAmdeen
791
Attachments
Revisions

v4.0