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


ANmaDB_Insert1(TableName, FieldColumns, FieldValues)
  Inserts 1 row
ANmaDB_Update1(TableName, UpdateCol, NewValue, WhereStatement)
  Updates 1 fields from 1 row, accepts custom where
ANmaDB_Delete1(TableName, WhereStatement)
  Deletes 1 row, accepts custom where
ANmaDB_Select1(TableName, SelectColumn, WhereStatement)
  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)
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_Delete1(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_Select1(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 114 Downloads 59

'access', 'db', 'mdb', 'sql', 'database', 'open', 'close', 'select', 'from', 'insert', 'update', 'delete', 'in', 'ANmaDB', 'set', 'table', 'field', 'column', 'value', 'row', 'col'

ANmarAmdeen
486
Attachments
Database Classic ASP
Revisions

v1.0