How can I build a sql query in to retrieve data from an external database?


#1

How can I build a sql query to retrieve data from an external database?

When a user selects a Username from a drop down list I would like to populate the email address text box withwith the selected users email from the Users table of my Database.

You will need to
a)Add an event to your drop down list to call the procedure you created.
b)add a connection string in your web.config
c)Add a procedure in your c# code

a) Add a event to your dropdownlist that will call the below procedure (i.e: on Selected index changed).

b)You will need to add your database information to your web.config file:

<add name="myConnectionName" connectionString="Data Source=myServerAddress;Initial Catalog=myDatabase;User ID=myUsername;Password=myPassword;" providerName="System.Data.SqlClient">

c)next you will need to add a procedure to your code like the following procedure:

C#

private void FindEmail() 
{
    try 
    { 
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionName"].ToString()); 

        try 
        {
            connection.Open();
        }
        catch (Exception e) 
        { 
            // Display error message in the form field named FRM_ERROR 
            this.FRM_ERROR.Text = e.Message;
            this.FRM_ERROR.Visible = true; 
        }
		
        SqlCommand selectcommand = connection.CreateCommand(); 
        selectcommand.CommandText = "select email from Users where username = '" + DROPDOWNLIST.SelectedValue + "'";
        SqlDataReader selectReader = Selectcommand.ExecuteReader (System.Data.CommandBehavior.CloseConnection); 
		
        if (selectReader.HasRows)
        { 
            while (selectReader.Read()) 
            { 
                this.SELECTED_EMAIL.Text = selectReader[0].ToString(); 
            }
        }
	
        connection.Close();
    } 
    catch (Exception e)
    { 
        this.FRM_ERROR.Text = e.Message;
        this.FRM_ERROR.Visible = true;
    }
}

VB

Private Sub FindEmail() 
 Try Dim connection As SqlConnection = New  SqlConnection(ConfigurationManager.ConnectionStrings(DBConnectionString).ToString) 
 Try 
 connection.Open() 
 Catch e As Exception 
  'a hidden text box need to be on the form named 
   FRM_ERRORMe.FRM_ERROR.Text = (e = + e.Message.ToString) 
   Me.FRM_ERROR.Visible = True 
 End Try 

 Dim selectcommand As SqlCommand = connection.CreateCommand 
 selectcommand.CommandText = (select email from Users where Username = ' _+ (DropDownList.SelectedValue + '))
 Dim selectReader As SqlDataReader =  selectcommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) 
 If selectReader.HasRows Then 
  While selectReader.Read 
   Me.SELECTED_EMAIL.Text = selectReader(0).ToString 
  End While 
 End If connection.Close() 
 Catch e As Exception 
 Me.FRM_ERROR.Text = (e = + e.Message.ToString) 
 Me.FRM_ERROR.Visible = True 
 End Try
End Sub