Community resource center

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 with with 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:

<appSettings>

<add key="DBConnectionString" value="Provider=SQLOLEDB; Data Source=SQLSERVERNAME; Initial Catalog=EXTERNALDATABASE;User ID=USERNAME; Password=PASSWORD"/>

</appSettings>

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

C#

private void FindEmail()

{

     try

     {

          SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString());

          try

          {

               connection.Open();

           }

          catch (Exception e)

          { //a hidden text box need to be on the form named FRM_ERROR

               this.FRM_ERROR.Text = "e = " + e.Message.ToString();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 = " + e.Message.ToString();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_ERROR
           Me.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