Adel Khatem Adel Khatem - 2 months ago 12
ASP.NET (C#) Question

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

Answer

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

Comments