I am writing a form application. User inputs his name, email, address etc into text boxes as if he was ordering a package. If the user has already made an order once I want to make it possible for the user to enter his email into the text box and based on his email fill out all the other personal information needed for the package.

The trouble I am having is that his data is in two different tables. The data which is in customer table (his first and last name) I have successfully retrieved, but the data in the table address I don't know how to get.

Here is the code:

{
        try
        {
            var connection = getConnection();

            var command = new SqlCommand
            {
                Connection = connection,
                CommandText = "SELECT * FROM Customer WHERE Email = @Email"
            };

            command.Parameters.Clear();
            command.Parameters.AddWithValue("@Email", mailBox.Text);

            connection.Open();

            reader = command.ExecuteReader(CommandBehavior.SingleRow);

            if (reader.Read())
            {
                fnameBox.Text = reader["fname"].ToString();
                lnameBox.Text = reader["lname"].ToString();

                command.CommandText = "SELECT * FROM address WHERE customerID= "  reader["customerID"].ToString();

                stateBox.Text = reader["state"].ToString();  //part where the error happens
                cityBox.Text = reader["city"].ToString();
                addressBox.Text = reader["address"].ToString();
                zipBox.Text = reader["zip"].ToString();

                int result = command.ExecuteNonQuery();
                connection.Close();

                if (result > 0)
                {
                    MessageBox.Show("Success");
                }
                else
                {
                    MessageBox.Show("Error");
                }
            }
            else
            {
                MessageBox.Show("E-mail entered doesn't exist");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
} ```

CodePudding user response：

1. Look into using something like EF in future, will clean such things up, but appreciate this is probably not feasible for what you're doing here.
2. You should get related data from multiple tables via SQL Joins (look into LEFT JOIN and INNER JOIN)
3. Your problem is caused by the result set not having a state field, which in turn is caused by your not actually executing your SELECT * FROM address query - you are setting the command text but doing nothing further. You need to create another DataReader for the second query and read those results.

Overall there's a lot of stuff to improve, but you're clearly at an early state in learning this so that's fine for now....

CodePudding user response：

you didn't finish to read the first result and after this trying to get the second one

            command.Parameters.Clear();
            command.Parameters.AddWithValue("@Email", mailBox.Text);
           
            var customerID=0;
            var success=false;

            connection.Open();

            var reader1 = command.ExecuteReader();
              
            if (reader1.Read())
            {
               sucess=true;
                fnameBox.Text = reader1["fname"].ToString();
                lnameBox.Text = reader1["lname"].ToString();
                customerID=  Convert.ToInt32( reader1["customerID"].ToString());
            reader1.Close();
            }

      if( sucess)
       { 
              command.CommandText = "SELECT * FROM address WHERE customerID = @CustomerID";
             command.Parameters.Clear();
            command.Parameters.AddWithValue("@CustomerID", customerID);
            var reader2 = command.ExecuteReader();

            sucess=false;
            if (reader2.Read())
            {
                sucess=true;    
                stateBox.Text = reader2["state"].ToString();  
                cityBox.Text = reader2["city"].ToString();
                addressBox.Text = reader2["address"].ToString();
                zipBox.Text = reader2["zip"].ToString();
               reader2.Close();
             }
            if (success)
                {
                    MessageBox.Show("Success");
                }
                else
                {
                    MessageBox.Show(" address select Error");
                }
      }
       else
      {
                MessageBox.Show("E-mail entered doesn't exist");
      }
                connection.Close();