How to insert data into two SQL Server tables in asp.net

Question

I have two tables, the first table is Course and this table contains three columns Course_ID, Name_of_course, DeptID; and the second table is Department and it has three columns DeptID, DepName, College.

I put a GridView to display the data that I will add it. But when I write the command to insert the data in both tables the data don't add. I used this command

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    try
    {
        GridViewRow r = GridView1.SelectedRow;

        Dbclass db = new Dbclass();
        string s = "";

        DataTable dt = db.getTable(s);
        ddcollege.SelectedValue = dt.Rows[0]["College"].ToString();
        dddept.SelectedValue = dt.Rows[1]["DepName"].ToString();
        tbid.Text = r.Cells[0].Text;
        tbcourse_name.Text = r.Cells[1].Text;
        lblid.Text = tbid.Text;
        lberr.Text = "";
    }
    catch (Exception ex)
    {
        lberr.Text = ex.Message;
    }
}

protected void btadd_Click(object sender, EventArgs e)
{
    try
    {
        if (tbid.Text == "")
        {
                lberr.Text = "Please input course id";
                return;
        }

        if (tbcourse_name.Text == "")
        {
                lberr.Text = "Please input course name";
                return;
        }

        string s = "Insert into Course(Course_ID,Name_of_course) values ('" + tbid.Text + "','" + tbcourse_name.Text + "')";

        s = "INSERT INTO Department (DepName,College,DeptID) VALUES ('"+dddept.SelectedValue+"','"+ddcollege.SelectedValue+"','"+tbdeptID.Text+"')";
        Dbclass db = new Dbclass();

        if (db.Run(s))
        {
                lberr.Text = "The data is added";
                lblid.Text = tbid.Text;
        }
        else
        { 
                lberr.Text = "The data is not added";
        }

        SqlDataSource1.DataBind();
        GridView1.DataBind();
    }
    catch (Exception ex)
    {
            lberr.Text = ex.Message;
    }
}

Here is the Dbclass code:

public class Dbclass
{
    SqlConnection dbconn = new SqlConnection();

    public Dbclass()
    {
        try
        {
            dbconn.ConnectionString = @"Data Source=Fingerprint.mssql.somee.com;Initial Catalog=fingerprint;Persist Security Info=True;User ID=Fingerprint_SQLLogin_1;Password=********";
            dbconn.Open();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

    //----- run insert, delete and update
    public bool Run(String sql)
    {
        bool done= false;

        try
        {
            SqlCommand cmd = new SqlCommand(sql,dbconn);
            cmd.ExecuteNonQuery();
            done= true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        } 
        return done;
    }

    //----- run insert, delete and update
    public DataTable  getTable(String sql)
    {
        DataTable done = null;

        try
        {
                SqlDataAdapter da = new SqlDataAdapter(sql, dbconn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        return done;
    }
}

Thank you all

The logic is inside Dbclass; it is hard to tell without seeing it. — Win
– Win, Commented Sep 10, 2016 at 5:05

JKerny · Accepted Answer · 2016-09-10 05:18:25Z

1

The main thing I can see is you are assigning two different things to your "s" variable.

At this point db.Run(s) the value is "Insert into Department etc" and you have lost the first sql string you assigned to "s"

Try:

string s = "Insert into Course(Course_ID,Name_of_course) values ('" + tbid.Text + "','" + tbcourse_name.Text + "')";
        s += "INSERT INTO Department (DepName,College,DeptID) VALUES ('"+dddept.SelectedValue+"','"+ddcollege.SelectedValue+"','"+tbdeptID.Text+"')";

Notice the concatenation(+=). Otherwise as mentioned above using a stored procedure or entity framework would be a better approach. Also try to give your variables meaningful names. Instead of "s" use "insertIntoCourse" or something that describes what you are doing

answered Sep 10, 2016 at 5:18

JKerny

5481 gold badge3 silver badges20 bronze badges

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

2 Comments

Adel Khatem Over a year ago

it's working now, but the data does not display in GridView. Also, that data that I add in DeptID column only added in Department table, and DeptID in course table still null. is there a way when I add any data to deptID column it automatically add in both tables

JKerny Over a year ago

Get the query to work in SQL management studio first then it should be easy to bring it back into your code and add some variables. I would look at stored procedures though because strings of SQL statements are not used much anymore

Vikram · Accepted Answer · 2016-09-10 10:54:12Z

0

When a value is inserted into a table(Table1) and and value has to be entered to into another table(Table2) on insertion of value to Table1, you can use triggers.

https://msdn.microsoft.com/en-IN/library/ms189799.aspx

answered Sep 10, 2016 at 10:54

Vikram

1972 silver badges6 bronze badges

Comments

Rakhesh · Accepted Answer · 2016-09-10 11:18:52Z

0

"tbdeptID.Text" is giving you the department Id only right? You should be able to modify your first statement

string s = "Insert into Course(Course_ID,Name_of_course,) values ('" + tbid.Text + "','" + tbcourse_name.Text + "',)";

Please start running SQL Profiler, it is a good tool to see what is the actual query getting executed in server!

answered Sep 10, 2016 at 11:18

Rakhesh

113 bronze badges

Collectives™ on Stack Overflow

How to insert data into two SQL Server tables in asp.net

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related