SQL and ASP

---

SQL and ASP:
Here I have listed same important SQL-Statement.
and same functions for connect to Database.
By my samples I don't use:
follow ADO Command:
-add
-update
...
only SQL (is faster and better to read)
I dont't check why so much programmer use not the SQL-Staetment

Original Author: Hohl David

Code

'SELECT with Array
Dim arrstrZipcode()
Dim i
Set rec = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT zipcode FROM ZIPCODES WHERE Countrycode = 'AUT'"
set rec = objConn.Execute(strSQL)
if not rec.eof then
while not rec.eof
redim preserve arrstrZipcode(i)
arrstrZipcode(i) = rec.fields("zipcode")
rec.movenext
i = i + 1
wend
else
strError = "Nothing found"
end if
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö
'UPDATE
strFirstname = ReplaceUnletters(Request.form("txtFirstname"))
iAdressID = Request.Form("txtAdressID")
Set rec = Server.CreateObject("ADODB.Recordset")
strSQL = "Update ADDRESS SET " & _
"Firstname = '" & strFirstname & "' " & _
"WHERE ZipcodeID = " & iAdressID
set rec = objConn.Execute(strSQL)
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö
'INSERT
strFirstname = ReplaceUnletters(Request.form("txtFirstname"))
strLastname = ReplaceUnletters(Request.form("txtFirstname"))
Set rec = Server.CreateObject("ADODB.Recordset")
strSQL = "INSERT INTO ADDRESS (Firstname, Lastname) VALUES ( " & _
"'" & strFirstname & "', " & _
"'" & strLastname & "')"
set rec = objConn.Execute(strSQL)
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö
'DELETE
iAddressID = Request.querystring("AdID")
Set rec = Server.CreateObject("ADODB.Recordset")
strSQL = "DELETE FROM ADDRESS WHERE AddressID = " & iAddressID
set rec = objConn.Execute(strSQL)
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö
' FUNCTIONS
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö

function OpenDatabase()
Dim strDatabaseIP, strPW, strUID
strDatabaseIP = "150.150.100.210"
strPW = ""
strUID = "sa"
    'SQL-Server
strConn = "Provider=SQLOLEDB.1;SERVER=" & strDatabaseIP & ";DATABASE=ADDRESS;UID=" & strUID & ";PWD=" & strPW
'MY SQL
strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & strDatabaseIP & ";DATABASE=Address;UID=" & strUID & ";PWD=" & strPW & ";OPTION=35;"
set objConn = server.CreateObject("ADODB.Connection")
objConn.Open(strConn)
end function
function CloseDatabase()
objConn.close
Set objConn = Nothing
end function
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö
Public Function ReplaceUnLetters(ByVal strReplace)
strReplace = Replace(strReplace, "'", "''")
ReplaceUnLetters = strReplace
End Function
' ÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇöÔÇö

Function ReplaceNonSpace(strText)
'Here I want replace HTMLSPACE WITH normal space, but the insert into the Planet Source code replace the HTML TAG excuse
ReplaceNonSpace = Replace(strText, "?á", " " )
End Function


About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 89 times

Categories

ASP/ HTML

Attachments

No attachments for this post

Comments

No comments have been added for this post.

You must be logged in to make a comment.