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
' 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
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 2,616
Downloads 948
CodeID
DB ID