CODEWITHSUNDEEP
c#mysqlsql-server
Arief Arfiansyah
Arief Arfiansyah

Reputation: 39

Joining 2 tables with the same primary key SQL Server to C#

whats up guys, i have some trouble with this code i hope u guys can figured out

I have 2 Tables ( Customer & Registration ) Using SQL Server

Customer

  1. Id
  2. Name
  3. etc

Registration

  1. Id
  2. Date

i working on my C# Program, i want to show them into a listview but i have error here is my code : 

public void listdata()
    {
        SqlDataReader reader = null;

        listView1.Items.Clear();
        listView1.Columns.Clear();
        listView1.Columns.Add("ID", 55, HorizontalAlignment.Center);
        listView1.Columns.Add("Tanggal Registrasi", 150, HorizontalAlignment.Center);
        listView1.Columns.Add("Nama Pemohon", 150, HorizontalAlignment.Center);

        System.Data.SqlClient.SqlConnection conn = konn.GetConn();
        try
        {
            conn.Open();
            string sql = "select*from Ms_Register a join Ms_Coba b on a.id = b.id where id='" + textBox1.Text + "'";
            SqlCommand command = new SqlCommand(sql, conn); 
            command.ExecuteNonQuery();
            conn.Close();

            //Check
            reader = command.ExecuteReader();


            while (reader.Read())
            {
                ListViewItem item1 = new     ListViewItem(reader["id"].ToString(), 0);
                item1.SubItems.Add(reader["tanggal"].ToString());
                item1.SubItems.Add(reader["nama"].ToString());

                listView1.Items.Add(item1);
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }
        finally
        {
            conn.Close();
        }

    }

The both tables have same id and the error says "Ambiguous column name "id" " anyone have the way to solve this ? Thank you

Upvotes: 0

Views: 574

Answers (2)

mikeb
mikeb

Reputation: 11267

select * from Ms_Register a join Ms_Coba b on a.id = b.id where id='" + textBox1.Text + "'"

Should be either WHERE a.id or WHERE b.id, does not matter which in this case since they are identical

This is really bad code, though, you should be sanitizing textBox1's value by using parameters for your query. Your code is vulnerable to SQL injection.

https://en.wikipedia.org/wiki/SQL_injection

Upvotes: 2

Karthika
Karthika

Reputation: 110

Your query was wrong, Change it as follows:

string sql = "select * from Ms_Register a join Ms_Coba b on a.id = b.id where a.id='" + textBox1.Text + "'";

Because both table contains field name as same, so we have to mention which table field to be choose on where clause

Upvotes: 0

Related Questions