CodeCharge Studio

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

Retrieve Multiple Field Values from a Database

This example shows how to read values from a database using event code. In this case, the page contains a UserInfo label. If the user_id URL parameter is not empty, the name and work phone number corresponding to the user_id value will be displayed in the UserInfo label.

1. Add the Before Show event to the Label.
2. Within the event, add the code below:

Function UserInfo_BeforeShow(Sender) 
 Dim SQL
 Dim RecordSet
 Dim UserId
 Dim UserName
 Dim WorkPhone

' Read user_id from URL 
  UserId = CCGetFromGet("user_id",  0) 

  If UserID > 0 then 
    SQL = "SELECT emp_name, phone_work FROM employees WHERE emp_id="&UserId
    ' Open the recordset
    Set RecordSet = DBConnection1.Execute(SQL)
    If DBConnection1.Errors.Count = 0 Then
      If NOT RecordSet.EOF then 
         UserName = CCGetValue(RecordSet, "emp_name")
         WorkPhone = CCGetValue(RecordSet, "phone_work")
      End if 		 
    ' Close the recordset 
      RecordSet.Close
      Set RecordSet = Nothing
    Else
      Print "SQL Execution Failed."
      DBConnection1.Errors.Clear
    End If 

  ' Show a label value 
    UserInfo.Value = UserName & ", phone: "&WorkPhone
  Else
    UserInfo.Visible = False
  End if 
End Function

function UserInfo_BeforeShow(& $sender) {
global $UserInfo;

  // Read user_id from URL 
  $UserId = CCGetFromGet("user_id", 0);
  
  if ($UserId > 0) {
    // Open connection 
    $db = new clsDBConnection1();
    $SQL = "SELECT emp_name, phone_work FROM employees WHERE emp_id=".$UserId;
    $db->query($SQL);
    $Result = $db->next_record();
    if ($Result) {
      $UserName = $db->f("emp_name");
      $WorkPhone = $db->f("phone_work");
    }
    $db->close();

    // Show a label value
    $UserInfo->SetValue($UserName .", phone: ". $WorkPhone);
  } else {
    $UserInfo->Visible = False;
  }
}

sub UserInfo_BeforeShow() {

  # Read user_id from URL 
  $UserId = CCGetFromGet("user_id", 0);
  
  if ($UserId > 0) {
    # Open connection 
    $db = clsDBConnection1->new();
    $SQL = "SELECT emp_name, phone_work FROM employees WHERE emp_id=".$UserId;
    $db->query($SQL);
    $Result = $db->next_record();
    if ($Result) {
      $UserName = $db->f("emp_name");
      $WorkPhone = $db->f("phone_work");
    }
    $db->{sth} = undef;
    $db->{dbh} = undef;

    # Show a label value
    $UserInfo->SetValue($UserName .", phone: ". $WorkPhone);
  } else {
    $UserInfo->{Visible} = 0;
  }
}

<!---UserInfo_BeforeShow --->
  <CFMODULE Template="CCGetParam.cfm" strName="user_id" def="0" outputVar="userId">
  <CFIF userId GT 0>
    <CFSET tempSQL = "SELECT emp_name, phone_work FROM employees WHERE emp_id=#userId#">
    <CFMODULE Template="CCOPENRS.cfm" strName="Temp" Connection="IntranetDB" sql="#tempSQL#">
    <CFIF queryTemp.RecordCount GT 0>
      <CFSET fldUserInfo = "#queryTemp.emp_name#, phone: #queryTemp.phone_work#">
    </CFIF>
  <CFELSE>
      <CFSET hideUserInfo = True>
  </CFIF>

//UserInfo_BeforeShow 
  // Read user_id from URL 
  long userId = Utils.convertToLong(e.getPage().getHttpGetParams().getParameter("user_id", "0")).longValue();
  if (userId > 0) {
    // Open connection 
    JDBCConnection conn = JDBCConnectionFactory.getJDBCConnection("IntranetDB");
    String sql = "SELECT emp_name, phone_work FROM employees WHERE emp_id=" +
                  conn.toSql(String.valueOf(userId), JDBCConnection.INTEGER);
    DbRow row = conn.getOneRow(sql);
    conn.closeConnection();
    String userName = "";
    String workPhone = "";
    if(row!=null) {
      userName = Utils.convertToString(row.get("emp_name"));
      workPhone = Utils.convertToString(row.get("phone_work"));
    } 

    // Show a label value 
    e.getControl().setValue(userName+", phone: "+workPhone);
  } else {
    e.getControl().setVisible(false);
  }

//UserInfo_BeforeShow 
  DataAccessObject NewDao = Settings.IntranetDBDataAccessObject;
  string UserName = "";
  string WorkPhone = "";

  if (Request.QueryString["user_id"] != null && Request.QueryString["user_id"].Length != 0) {
    SqlCommand Select = new SqlCommand("SELECT emp_name, phone_work FROM employees WHERE emp_id="+ 
      NewDao.ToSql(Request.QueryString["user_id"].ToString(),FieldType.Integer),NewDao);
 
    DataRowCollection newDr = Select.Execute().Tables[0].Rows;
    for(int i = 0; i < newDr.Count; i++){
      UserName = newDr[i]["emp_name"].ToString();
      WorkPhone = newDr[i]["phone_work"].ToString();
    }
 
    // Show a label value 
    UserInfo.Text =  UserName + ", phone: "+WorkPhone;
  }

' UserInfo_BeforeShow 
Dim NewDao As DataAccessObject = Settings.IntranetDBDataAccessObject
Dim UserName As String = ""
Dim WorkPhone As String = ""

  If Not IsNothing(Request.QueryString("user_id")) Then
	Dim [Select] As New SqlCommand("SELECT emp_name, phone_work FROM employees WHERE emp_id=" + 
	  NewDao.ToSql(Request.QueryString("user_id").ToString(), FieldType._Integer), NewDao)
   
	Dim newDr As DataRowCollection = [Select].Execute().Tables(0).Rows
	Dim i As Integer
	For i = 0 To newDr.Count - 1
		UserName = newDr(i)("emp_name").ToString()
		WorkPhone = newDr(i)("phone_work").ToString()
	Next i
   
	' Show a label value 
	UserInfo.Text = UserName & ", phone: " & WorkPhone
  End If

See Also: