Help: Opening multiple SQL(back end) tables and Displaying them on C#(Front End)

[whodoes21]

Hello :D
we've been working on this simple student database where we can add, edit and delete a student record...
sooo going straight to the point I'm having problem with displaying
details from two different tables as you can see we can only open using one connection string and try opening the tables using a INNER JOIN command in SQL as you can see where I fail badly... but there's still a problem with the following namely "dtrStudentContact" which is my other table...
can you please help me with this... thanks In advance :)

private void txtStudentNumber_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                string connString = @"//generated by adding databases";
                SqlConnection connStudent = new SqlConnection(connString);
                connStudent.Open();
             //this part is where I fail badly... :(
                string sqlSelectStatement = @"select * from student a inner join student contact b on a.studentno=b.studno where a.studno= '" + @txtStudentNumber.Text + "'";
                SqlCommand cmdTxt = new SqlCommand(sqlSelectStatement, connStudent);
                SqlDataReader dtrStudent = cmdTxt.ExecuteReader();
                dtrStudent.Read();

                try
                {
                    if ((dtrStudent["Lastname"].ToString().Trim().Length != 0))
                    {
                        //ENABLE OTHER CONTROLS

                        txtLName.Enabled = true;
                        txtFName.Enabled = true;
                        txtMI.Enabled = true;
                        rdoFemale.Enabled = true;
                        rdoMale.Enabled = true;
                        cboProgram.Enabled = true;
                        cboYearLevel.Enabled = true;
                        btnSave.Enabled = true;
                        btnDelete.Enabled = true;
                    }

                    //FILL UP THOSE OBJECT BY GETTING THE DATA FROM TABLE Student
                    txtLName.Text = dtrStudent["Lastname"].ToString();
                    txtFName.Text = dtrStudent["Firstname"].ToString();
                    txtMI.Text = dtrStudent["MI"].ToString();
                    txtPERMANENT_ADDRESS = dtrStudentContact["[PERMANENT ADDRESS]"].ToString();
                    txtMAILING_ADDRESS = dtrStudentContact["[MAILING ADDRESS]"].ToString();



                    if (dtrStudent["Gender"].ToString().Trim() == "Male")
                        rdoMale.Checked = true;

                    if (dtrStudent["Gender"].ToString().Trim() == "Female")
                        rdoFemale.Checked = true;

                    cboProgram.Text = dtrStudent["Program"].ToString();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("No Record Found", "Warning! ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    //MessageBox.Show(ex.Message);

                }
                connStudent.Close();
                connStudent.Dispose();
            }
        }

[sandeepparekh9]

what error do you get??

can you clarify it further?

[whodoes21]

hmm let's see well for starters the ones declared as dtrStudentContact because it was not defined and if I did try to define it by opening the table it will not show the desired data for example if I have this inside the try-catch statement it will still not open and there will still be erros on the part where I try to show the data that is in the StudentContact table :|

string connString = @"//generated by adding databases";
                SqlConnection connStudent = new SqlConnection(connString);
                connStudent.Open();
             //this part is where I fail badly... :(
                string sqlSelectStatement = @"select * from Student Where'" + @txtStudentNumber.Text + "'";
                SqlCommand cmdTxt = new SqlCommand(sqlSelectStatement, connStudent);
                SqlDataReader dtrStudent = cmdTxt.ExecuteReader();
                dtrStudent.Read();
                
                string connString1 = @"//generated by adding databases";
                SqlConnection connStudentContact = new SqlConnection(connString1);
                connStudentContact.Open();
             
                string sqlSelectStatement = @"select * from Student Where= '" + @txtStudentNumber.Text + "'";
                SqlCommand cmdTxt1 = new SqlCommand(sqlSelectStatement, connStudentContact);
                SqlDataReader dtrStudentContact = cmdTxt.ExecuteReader();
                dtrStudent.Read();

then of course even if this works... this will be impractical that's why we were asked to use one opening/reading connection and use an inner join command but then we can still use this

[Momerath]

@"select * from student a inner join student contact b on a.studentno=b.studno where a.studno= ...

From this is looks like you have two tables 'student' and 'student contact' that you are trying to join. If so, SQL does not like spaces in the names of tables so you'd have to change this to

@"select * from student a inner join [student contact] b on a.studentno=b.studno where a.studno=

so it will recognize it.

[whodoes21]

sorry for that slip-up but the two tables are actually named Student and StudentContact
my Problem is with these lines

txtPERMANENT_ADDRESS = dtrStudentContact["[PERMANENT ADDRESS]"].ToString();
txtMAILING_ADDRESS = dtrStudentContact["[MAILING ADDRESS]"].ToString();

since dtrStudentContact wasn't really initialized and those two are not inside the table 'Student'

:(

right now I'm trying the other code where in I'll be opening another connection for StudentContact but it says that Index Out of Range Exeption was unhandled

and it pretty much looks like this

string connString15 = @"connection string";
                SqlConnection connStudentContact = new SqlConnection(connString15);
                connStudentContact.Open();


                string sqlSelectStatement15 = @"select * from StudentContact Where STUDNO= '" + @txtSTUDNO.Text + "'";
                SqlCommand cmdTxt15 = new SqlCommand(sqlSelectStatement15, connStudentContact);
                SqlDataReader dtrStudentContact = cmdTxt15.ExecuteReader();
                dtrStudentContact.Read();

               
                txtPERMANENT_ADDRESS.Text = dtrStudentContact["[PERMANENT ADDRESS]"].ToString();
                txtMAILING_ADDRESS.Text = dtrStudentContact["[MAILING ADDRESS]"].ToString();
                txtTELEPHONE.Text = dtrStudentContact["TELEPHONE"].ToString();
                txtEMAIL.Text = dtrStudentContact["EMAIL"].ToString();