CodeCharge Studio

All ASP PHP Perl ColdFusion Java C#.NET VB.NET

Execute Custom SQL

This example shows how to execute a custom SQL statement. In this case, we have a Record form called Tasks which is based on the tasks database table. When a record is updated using the Record form, an SQL statement is executed to insert a new entry into the reports database table.

1. Add the After Execute Update event to the record.
2. Within the event, add the code below:

Function Tasks_DataSource_AfterExecuteUpdate(Sender)
Dim SQL
Dim Connection
Dim ErrorMessage

  Set Connection = New clsDBConnection1
  Connection.Open
  SQL = "INSERT INTO report (report_task_id,report_creator) "&_ 
        "VALUES ("& Connection.ToSQL(CCGetFromGet("task_id",0),ccsInteger) &","& Connection.ToSQL(CCGetUserID(),ccsInteger) &")"
  Connection.Execute(SQL)
  ErrorMessage = CCProcessError(Connection)
  Connection.Close
  Set Connection = Nothing
  On Error Goto 0

End Function

function Tasks_DataSource_AfterExecuteUpdate(& $sender) {

  $db = new clsDBConnection1();
  $SQL = "INSERT INTO report (report_task_id,report_creator) ". 
         "VALUES (". $db->ToSQL(CCGetFromGet("task_id",0),ccsInteger) .",". $db->ToSQL(CCGetUserID(),ccsInteger) .")";

  $db->query($SQL);
  $db->close();
  
}

sub Tasks_DataSource_AfterExecuteUpdate() {

  $db = clsDBConnection1->new();
  my $SQL = "INSERT INTO report (report_task_id,report_creator) ". 
            "VALUES (". $db->ToSQL(CCGetFromGet("task_id",0),$ccsInteger) .",". $db->ToSQL(CCGetUserID(),$ccsInteger) .")";
  $db->query($SQL);
  $db->{sth} = undef;
  $db->{dbh} = undef;
  
}

<!---Tasks_AfterExecuteUpdate --->

  <CFIF strOperation EQ "Update" AND NOT endOperationTasks>
    <CFSET strSQL="INSERT INTO report (report_task_id,report_creator) VALUES (">
    <CFMODULE Template="CCGetParam.cfm" strName="task_id" def="0">
    <CFMODULE Template="CCToSQL.cfm" Value="#CCGetParam#" Type="#ccsInteger#">
    <CFSET strSQL=strSQL & CCToSQL & ",">
    <CFMODULE Template="CCToSQL.cfm" Value="#Session.UserID#" Type="#ccsInteger#">
    <CFSET strSQL=strSQL & CCToSQL & ")">
    <CFModule Template="CCExecuteSQL.cfm" sql="#strSQL#" Connection="Connection1">
  </CFIF>
  

//Tasks_AfterExecuteUpdate 

  JDBCConnection conn = JDBCConnectionFactory.getJDBCConnection("Connection1");
  String sql = "INSERT INTO report (report_task_id,report_creator) VALUES (" + 
    conn.toSql(e.getPage().getHttpGetParameter("task_id","0"),JDBCConnection.INTEGER)+
    "," + conn.toSql(Utils.getUserId(e.getPage()),JDBCConnection.INTEGER) + ")";
  conn.executeUpdate(sql);
  conn.closeConnection();
  

'Tasks_AfterExecuteUpdate

  Dim Request As HttpRequest = HttpContext.Current.Request
  Dim NewDao As DataAccessObject = Settings.Connection1DataAccessObject
  Dim Sql As String = "INSERT INTO report (report_task_id,report_creator) " & _ 
        "VALUES  ("& NewDao.ToSql(Request.QueryString("task_id"),FieldType._Integer) &","& _
                   NewDao.ToSql(DBUtility.UserId.ToString(),FieldType._Integer) &")"
  NewDao.RunSql(Sql)
  

//Tasks_AfterExecuteUpdate

  System.Web.HttpContext context=System.Web.HttpContext.Current;
  DataAccessObject NewDao=Settings.Connection1DataAccessObject;
  string Sql = "INSERT INTO report (report_task_id,report_creator) " + 
     "VALUES ("+ NewDao.ToSql(context.Request.QueryString["task_id"],FieldType.Integer) +","+ 
              NewDao.ToSql(DBUtility.UserId.ToString(),FieldType.Integer) +")";
  NewDao.RunSql(Sql);
  

See Also: