CODEWITHSUNDEEP
c#asp.netsql-server
Adel Khatem
Adel Khatem

Reputation: 319

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

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

Upvotes: 0

Views: 397

Answers (3)

Rakhesh
Rakhesh

Reputation: 11

"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!

Upvotes: 0

Vikram
Vikram

Reputation: 197

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

Upvotes: 0

JKerny
JKerny

Reputation: 548

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

Upvotes: 1

Related Questions