ShotDev.Com

ADO Connection Object This the tutorial/example  learn ADO connection object.

ShotDev Focus:
- ASP & ADO Connection Object

Open - Method for open the connection > Exsample

Dim Conn,strSQL,objRec
Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("mydatabase.mdb"),"" , ""
strSQL = "SELECT * FROM customer "
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open strSQL, Conn, 1,3

Execute - Method for execute query > Exsample

strSQL = ""
strSQL = strSQL&"DELETE FROM customer "
strSQL = strSQL&"WHERE CustomerID = '"&Request.QueryString("CusID")&"' "
Set objExec = Conn.Execute(strSQL)

BeginTrans - Method for begin using transaction > Exsample

'*** Transaction Start ***'
Conn.BeginTrans

'*** Save Form ***'
strSQL = ""
strSQL = strSQL &"INSERT INTO customer "
strSQL = strSQL &"(CustomerID,Name,Email,CountryCode,Budget,Used) "
strSQL = strSQL &"VALUES "
strSQL = strSQL &"('"&Request.Form("txtCustomerID")&"','"&Request.Form("txtName")&"','"&Request.Form("txtEmail")&"' "
strSQL = strSQL &",'"&Request.Form("txtCountryCode")&"','"&Request.Form("txtBudget")&"','"&Request.Form("txtUsed")&"') "
Set objExec = Conn.Execute(strSQL)

CommitTrans - Method for commit transaction. > Exsample

'*** Transaction Start ***'
Conn.BeginTrans

'*** Save Form ***'
strSQL = ""
strSQL = strSQL &"INSERT INTO customer "
strSQL = strSQL &"(CustomerID,Name,Email,CountryCode,Budget,Used) "
strSQL = strSQL &"VALUES "
strSQL = strSQL &"('"&Request.Form("txtCustomerID")&"','"&Request.Form("txtName")&"','"&Request.Form("txtEmail")&"' "
strSQL = strSQL &",'"&Request.Form("txtCountryCode")&"','"&Request.Form("txtBudget")&"','"&Request.Form("txtUsed")&"') "
Set objExec = Conn.Execute(strSQL)
Set objExec = Nothing

'*** Already Exist Save Form (CustomerID is PK)***'
strSQL = ""
strSQL = strSQL &"INSERT INTO customer "
strSQL = strSQL &"(CustomerID,Name,Email,CountryCode,Budget,Used) "
strSQL = strSQL &"VALUES "
strSQL = strSQL &"('"&Request.Form("txtCustomerID")&"','"&Request.Form("txtName")&"','"&Request.Form("txtEmail")&"' "
strSQL = strSQL &",'"&Request.Form("txtCountryCode")&"','"&Request.Form("txtBudget")&"','"&Request.Form("txtUsed")&"') "
Set objExec = Conn.Execute(strSQL)
Set objExec = Nothing

If Err.Number = 0 Then
'*** Commit Transaction ***'
Conn.CommitTrans
Response.write("Save Done.")
Else
'*** Rollback Transaction ***'
Conn.RollbackTrans
Response.write("Error Save ["&strSQL&"] ("&Err.Description&")")
End If

RollbackTrans - Method for rollback transaction. > Exsample

'*** Transaction Start ***'
Conn.BeginTrans

'*** Save Form ***'
strSQL = ""
strSQL = strSQL &"INSERT INTO customer "
strSQL = strSQL &"(CustomerID,Name,Email,CountryCode,Budget,Used) "
strSQL = strSQL &"VALUES "
strSQL = strSQL &"('"&Request.Form("txtCustomerID")&"','"&Request.Form("txtName")&"','"&Request.Form("txtEmail")&"' "
strSQL = strSQL &",'"&Request.Form("txtCountryCode")&"','"&Request.Form("txtBudget")&"','"&Request.Form("txtUsed")&"') "

Set objExec = Conn.Execute(strSQL)
Set objExec = Nothing

'*** Already Exist Save Form (CustomerID is PK)***'
strSQL = ""
strSQL = strSQL &"INSERT INTO customer "
strSQL = strSQL &"(CustomerID,Name,Email,CountryCode,Budget,Used) "
strSQL = strSQL &"VALUES "
strSQL = strSQL &"('"&Request.Form("txtCustomerID")&"','"&Request.Form("txtName")&"','"&Request.Form("txtEmail")&"' "
strSQL = strSQL &",'"&Request.Form("txtCountryCode")&"','"&Request.Form("txtBudget")&"','"&Request.Form("txtUsed")&"') "
Set objExec = Conn.Execute(strSQL)
Set objExec = Nothing

If Err.Number = 0 Then
'*** Commit Transaction ***'
Conn.CommitTrans
Response.write("Save Done.")
Else
'*** Rollback Transaction ***'
Conn.RollbackTrans
Response.write("Error Save ["&strSQL&"] ("&Err.Description&")")
End If

Category: ASP & ADO by admin