Doesn't work th SqlCommand with multiple queries

Question

With one SELECT query, the code seems to add to the listbox correctly, but when I add another query, the listbox doesn't show anything anymore, and it seems that that rdr[3] does not exists (Contact has 3 columns and Numar_contact has one column (should't it be this one the rdr[3]?))

string connString = @"database=Agenda_db; Data Source=Marian-PC\SQLEXPRESS; Persist Security Info=false; Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);

try {
   conn.Open();
   SqlCommand cmd = new SqlCommand("SELECT * FROM Contact;"+ "SELECT * FROM Numar_contact", conn)

   SqlDataReader rdr = cmd.ExecuteReader();

   while (rdr.Read())
   {
       listBox1.Items.Add(rdr[0].ToString() + ' ' + rdr[1].ToString() + ' ' + rdr[2].ToString()+' '+ rdr[3].ToString());
   }

   rdr.Close();

You should know what's in rdr[index]. Have you stepped through with the debugger? — eddie_cat
– eddie_cat, Commented Jul 15, 2014 at 21:47
You should also add in using blocks to dispose properly of your SqlConnection, SqlCommand, and SqlDataReader. — Matthew Haugen
– Matthew Haugen, Commented Jul 15, 2014 at 21:50
So Contact has 3 columns and Numar_contact has one column, and you are expecting one result with 4 columns? Correct? If that is so, how does a row in Contact get associated with a row in Numar_contact? Or are you doing a cross apply. Please include in your question the actual schema for the two tables. — Scott Chamberlain
– Scott Chamberlain, Commented Jul 15, 2014 at 21:55
For the moment, the two tables have no relation between them; can't I just put in one result 3 columns from a table with one column from another table, with the same number of rows? — user3806279
– user3806279, Commented Jul 15, 2014 at 22:00
if you can not understand how to create a UNION query then look at this simple straight forward example here stackoverflow.com/questions/13677318/… — MethodMan
– MethodMan, Commented Jul 15, 2014 at 22:05

Matthew Haugen · Accepted Answer · 2014-07-15 22:29:49Z

3

Join your queries with a UNION. The way you've got it now, it'll return two results sets.

SELECT [col1], [col2] FROM Contact
UNION ALL
SELECT [col1], [col2] FROM Numar_contact

As DJ KRAZE pointed out in a comment, it might not be a bad idea to wrap this in a sproc or a TVF. But this will work too.

Edit:

I just learned via comments that the two tables are actually unrelated. In light of that, I'd be tempted to use two SqlCommands with two, distinct foreach loops. But if you're sold on this way,

SELECT id_contact, nume_contact, prenume_contact FROM Contact
UNION ALL
SELECT id_contact, numar, NULL FROM Numar_contact

This will align the data from the two tables, but where the second table doesn't have a [prenume_contact] it will select NULL. I might have mixed up the column positions here, since I don't really understand what those names are meant to represent.

Edit 2:

string connString = @"database=Agenda_db; Data Source=Marian-PC\SQLEXPRESS; Persist Security Info=false; Integrated Security=SSPI";
using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Contact", conn))
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                listBox1.Items.Add(rdr[0].ToString() + " " + rdr[1].ToString() + " " + rdr[2].ToString());
            }
        }

        using (SqlCommand cmd2 = new SqlCommand("SELECT * FROM Numar_contact", conn))
        using (SqlDataReader rdr2 = cmd.ExecuteReader())
        {
            while (rdr2.Read())
            {
                listBox1.Items.Add(rdr2[0].ToString() + " " + rdr2[1].ToString());
            }
        }
    }
    catch { }
}

Edit 3, thanks to insight from Scott Chamberlain:

On the other hand, you might want to perform a JOIN of some kind, most commonly an INNER JOIN. Note that this is an entirely different operation from any we've talked about before.

SELECT Contact.id_contact, Contact.nume_contact, Contact.prenume_contact, Numar_contact.numar
FROM Contact
INNER JOIN Numar_contact on Contact.id_contact = Numar_contact.id_contact

This will tie the two tables together, returning a record for each contact-numar_contact. Again, this is definitely not the same as doing a UNION. Make sure you're aware of the difference before you pick which you want.

Use this if your second table contains data that relates many-to-one to the first table.

edited Jul 15, 2014 at 22:29

answered Jul 15, 2014 at 21:47

Matthew Haugen

13.4k5 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

user3806279 Over a year ago

No, it shows me this error: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." !

MethodMan Over a year ago

Perhaps you should show the code where you state it doesn't work if you really want a clean way of running that code and returning the data why not create as well as execute it in a Stored Procedure.. I know it works as well just created and ran a simple test on my server

Matthew Haugen Over a year ago

That's why my initial answer included a disclaimer that it depended on the layout of your tables. I've since updated it. List the columns you expect to retrieve explicitly and this should work.

user3806279 Over a year ago

I don't get it ! I have 2 tables with no relation; first table with the columns (id_contact, nume_contact, prenume_contact) and the second table with two columns (from wich I want to get only one) (the columns are numar and id_contact); I just want to list the 3 columns from first table and the first column from second table (and in both tables, I've inserted only 2 rows)

Matthew Haugen Over a year ago

So you're adding data from two, unrelated tables into a single listbox? I'd probably pull that into two SqlCommands at that point, but I'll edit my answer.

|

Community · Accepted Answer · 2017-05-23 11:57:51Z

1

Thanks to your comment, what you are wanting to do is JOIN the tables.

SELECT Contact.id_contact, nume_contact, prenume_contact, numar
FROM Contact
INNER JOIN Numar_contact on Contact.id_contact = Numar_contact.id_contact

That will combine the two tables in to four columns where id_contact matches in both tables.

You may want a INNER JOIN or a LEFT JOIN depending on if you want rows to show up only when there is a item in the 2nd table or show up anyway and just make the 4th column DBNull.Value.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Jul 15, 2014 at 22:20

Scott Chamberlain

128k37 gold badges299 silver badges448 bronze badges

2 Comments

user3806279 Over a year ago

Error message box: "Incorrect syntax near the keyword 'JOIN' ", and after I close the messagebox, the listbox appears as empty.

Scott Chamberlain Over a year ago

Well that means you screwed up your sql. I would get your query working in Sql Management Studio first then try to use it in your program.

Preet Sangha · Accepted Answer · 2014-07-15 22:43:28Z

Yes you can.

Here is an example from the MSDN I've modified to use your code - you need to move the reader to the Next ResultSet

string connString = @"database=Agenda_db; Data Source=Marian-PC\SQLEXPRESS; Persist Security Info=false; Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Contact; SELECT * FROM Numar_contact", conn);
SqlDataReader myReader ;

int RecordCount=0; 

try
{
    myConnection.Open();
    myReader = myCommand.ExecuteReader();

    while (myReader.Read())
    {
        //Write logic to process data for the first result.
         RecordCount = RecordCount + 1;
    }
    MessageBox.Show("Total number of Contacts: " + RecordCount.ToString());

    bool moreResults = myReader.NextResult();   // <<<<<<<<<<< MOVE TO NEXT RESULTSET

    RecordCount = 0;

    while (moreResults && myReader.Read())
    {
        //Write logic to process data for the second result.
        RecordCount = RecordCount + 1;
    }
    MessageBox.Show("Total number from Numar_contacts: " + RecordCount.ToString());
}
catch(Exception ex) 
{
   MessageBox.Show(ex.ToString());
}
finally
{
    conn.Close(); // Could be replaced with  using statement too
}

Collectives™ on Stack Overflow

Doesn't work th SqlCommand with multiple queries

3 Answers 3

13 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related