I want to bind the data in grid view from multiple tables in visual basic ..Firstly for insertion I used stored procedure and for multiple source I used inner join .. but when I clicked on submit button which to view the result it shows me blank table.. but data get stored in database..

This is my code:

public partial class Form1 : Form
{
    DataSet ds;
    SqlCommand cmd;
    SqlConnection cnct;
    SqlDataAdapter ad;

    private void Form1_Load_1(object sender, EventArgs e)
    {
        cnct = new SqlConnection();
        cnct.ConnectionString = "Data Source=SIMRAN-PC\\SQLEXPRESS;Initial Catalog=db2;Integrated Security=true";
        cnct.Open();
        //ad = new SqlDataAdapter();
        //ds = new DataSet();

        comboBox2.Items.Add("New Delhi");
        comboBox2.Items.Add("Agra");
        comboBox2.Items.Add("Shimla");
        comboBox2.Items.Add("Noida");
        comboBox2.Items.Add("Chandigarh");

    }

    private void label8_Click(object sender, EventArgs e)
    {

    }

    private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
        if (ConnectionState.Closed == cnct.State)
        {
            cnct.Open();
        }

        cmd = new SqlCommand("prc_insert", cnct);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@empname", textBox4.Text);
        cmd.Parameters.AddWithValue("@sal", textBox6.Text);
        cmd.Parameters.AddWithValue("@cty", comboBox2.Text);
        cmd.Parameters.AddWithValue("@proname", textBox5.Text);

        cmd.ExecuteNonQuery();

        bind();
    }
    public void bind()
    {
        ad = new SqlDataAdapter("select e.Emp_name, e.Salary, c.City_name, p.Project_name from emp as e inner join city as c on c.City_id = e.City_id inner join project as p on p.Emp_id = e.Emp_id",cnct);
        ds = new DataSet("link");
        ad.Fill(ds,"link");

        dataGridView1.DataSource = ds.Tables["Link"];

    }
}

}

AND MY SQL QUERIES:

create table city (City_id int primary key identity,City_name varchar(10))

create table emp (Emp_id int primary key identity,Emp_name varchar(10),Salary int,City_id int Foreign Key references city(City_id)) create table project (Project_id int primary key identity,Emp_id int foreign key references emp(Emp_id),Project_name varchar(10))

select * from project

select * from city select * from emp

create proc prd_insert

@empname varchar(10), @sal int ,@cty varchar(10) ,@proname varchar(10)

,@proid int ,@empid int As begin declare @cid int set @cid = (Select max(City_id)from city)
insert into emp(Emp_name,salary,City_id) values (@empname,@sal,@cid) insert into city(City_name,City_id) values (@cty,@cid) insert into project(Project_name,Project_id,Emp_id) values (@proname,@proid,@empid) end

select e.Emp_name,e.Salary,c.City_name,p.Project_name from emp as e inner join city as c on c.City_id= e.City_id inner join project as p on p.Emp_id =e.Emp_id;

Related posts

Recent Viewed