problem when insert to multiple table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem when insert to multiple table

Author

Topic

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:44:04

Hi,
Below is my code behind:
protected void btnRegister_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(GetConnectionString());

string sql = "INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES " + " (@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position); INSERT INTO Profile(UserName, Password) VALUES "+" (@UserName, @Password)";

try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;

string UserName = txtUserName.Text;
string Password = txtPassword.Text;
string Name = txtName.Text;
string Identity = txtIdentity.Text;
string Gender = ddlGender.SelectedItem.Value;
string Race = ddlRace.SelectedItem.Value;
string MaritalStatus = ddlMarital.SelectedItem.Value;
string Religion = ddlReligion.SelectedItem.Value;
string DOB = txtDOB.Value;
string Address = txtAddress.Text;
string PostalCode = txtPoscode.Text;
string City = txtCity.Text;
string State = ddlState.SelectedItem.Value;
string Nationality = txtNationality.Text;
string HomeTel = txtHomeTel.Text;
string MobileNo = txtHPTel.Text;
string DriverLicenseNo = txtLicenseNo.Text;
string DriverLicenseExpiry = txtLicenseExpiry.Value;
string Position = ddlPosition.SelectedItem.Value;

cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Identity", txtIdentity.Text);
cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Race", ddlRace.SelectedItem.Value);
cmd.Parameters.AddWithValue("@MaritalStatus", ddlMarital.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Religion", ddlReligion.SelectedItem.Value);
cmd.Parameters.AddWithValue("@DOB", Convert.ToDateTime(txtDOB.Value));
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@PostalCode", txtPoscode.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Nationality", txtNationality.Text);
cmd.Parameters.AddWithValue("@HomeTel", txtHomeTel.Text);
cmd.Parameters.AddWithValue("@MobileNo", txtHPTel.Text);
cmd.Parameters.AddWithValue("@DriverLicenseNo", txtLicenseNo.Text);
cmd.Parameters.AddWithValue("@DriverLicenseExpiry", Convert.ToDateTime(txtLicenseExpiry.Value));
cmd.Parameters.AddWithValue("@Position", ddlPosition.SelectedItem.Value);
cmd.ExecuteNonQuery();
//int s = cmd.ExecuteNonQuery();
//if (s == 1)
//{
// Response.Redirect("Login.aspx");
//}
//else
//{
// Response.Redirect("Register.aspx");
//}
}
catch (System.Data.SqlClient.SqlException ex_msg)
{
//Here will be catch elements
string msg = "Error occured while inserting";
msg += ex_msg.Message;
throw new Exception(msg);
}
finally
{
//Here will be fially elements
con.Close();
}

i need to insert to two table(employee, profile) when the register button click.this work fine when insert to employee table but nothing insert to profile table.
Can anyone help me check where is the problem or mistake???
thanks....

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:50:59

why not put both the INSERTS into a STORED PROCEDURE(with parameters) and execute the STORED PROCEDURE using the cmd.Exceute() & cmd.CommandType = CommandType.StoredProcedure?

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:52:54

quote:
Originally posted by rohitvishwakarma

why not put both the INSERTS into a STORED PROCEDURE(with parameters) and execute the STORED PROCEDURE using the cmd.Exceute() & cmd.CommandType = CommandType.StoredProcedure?

sorry,because i don't know how to use stored procedure.
(i'm new to asp.net and sql server.)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:03:47

DELETED

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:04:31

Add datatypes against the paramets I have done for @UserName VARCHAR(100) do it for others too
CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)
INSERT INTO Profile(UserName, Password) VALUES (@UserName, @Password)

END

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:12:43

modify the code as highlighted
protected void btnRegister_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(GetConnectionString());

string sql = "sp_Insert_Into_Employee_Profile ";
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;

string UserName = txtUserName.Text;
string Password = txtPassword.Text;
string Name = txtName.Text;
string Identity = txtIdentity.Text;
string Gender = ddlGender.SelectedItem.Value;
string Race = ddlRace.SelectedItem.Value;
string MaritalStatus = ddlMarital.SelectedItem.Value;
string Religion = ddlReligion.SelectedItem.Value;
string DOB = txtDOB.Value;
string Address = txtAddress.Text;
string PostalCode = txtPoscode.Text;
string City = txtCity.Text;
string State = ddlState.SelectedItem.Value;
string Nationality = txtNationality.Text;
string HomeTel = txtHomeTel.Text;
string MobileNo = txtHPTel.Text;
string DriverLicenseNo = txtLicenseNo.Text;
string DriverLicenseExpiry = txtLicenseExpiry.Value;
string Position = ddlPosition.SelectedItem.Value;

cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Identity", txtIdentity.Text);
cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Race", ddlRace.SelectedItem.Value);
cmd.Parameters.AddWithValue("@MaritalStatus", ddlMarital.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Religion", ddlReligion.SelectedItem.Value);
cmd.Parameters.AddWithValue("@DOB", Convert.ToDateTime(txtDOB.Value));
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@PostalCode", txtPoscode.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Nationality", txtNationality.Text);
cmd.Parameters.AddWithValue("@HomeTel", txtHomeTel.Text);
cmd.Parameters.AddWithValue("@MobileNo", txtHPTel.Text);
cmd.Parameters.AddWithValue("@DriverLicenseNo", txtLicenseNo.Text);
cmd.Parameters.AddWithValue("@DriverLicenseExpiry", Convert.ToDateTime(txtLicenseExpiry.Value));
cmd.Parameters.AddWithValue("@Position", ddlPosition.SelectedItem.Value);
cmd.ExecuteNonQuery();
//int s = cmd.ExecuteNonQuery();
//if (s == 1)
//{
// Response.Redirect("Login.aspx");
//}
//else
//{
// Response.Redirect("Register.aspx");
//}
}
catch (System.Data.SqlClient.SqlException ex_msg)
{
//Here will be catch elements
string msg = "Error occured while inserting";
msg += ex_msg.Message;
throw new Exception(msg);
}
finally
{
//Here will be fially elements
con.Close();
}

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 05:33:17

thanks rohitvishwakarma,
it works...is it i do the same thing when i want insert to three table by just create the stored procedure and the code also change like just now???
And i've other question.
when the data insert to employee table but it not pass the profile table.(senario same like my previous post about the auto generate id like c0001 that one)

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 05:41:14

is it because this time is insert at same time so the E0001 can't pass to Profile table??
because i try declare again,it works...

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:45:36

quote:
Originally posted by yvette

is it because this time is insert at same time so the E0001 can't pass to Profile table??
because i try declare again,it works...

yes you have to use that part in between the two inserts i.e. Get the ID from previous INSERT (Employee)& put into a @employee_id and Pass it into VALUES of second INSERT of Profile table.

Modify the Procedure as highlighted:
Add datatypes against the paramets I have done for @UserName VARCHAR(100) do it for others too
CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

DECLARE @employee_id VARCHAR(50)
INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)

SELECT @employee_id = SELECT = EmplyeeNumber FROM Employee WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Profile(UserName, Password,id) VALUES (@UserName, @Password,@employee_id)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:50:31

quote:
Originally posted by yvette

thanks rohitvishwakarma,
it works...is it i do the same thing when i want insert to three table by just create the stored procedure and the code also change like just now???
And i've other question.
when the data insert to employee table but it not pass the profile table.(senario same like my previous post about the auto generate id like c0001 that one)

Just add the Third INSERT query after the first two inside the procedure
as

CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

DECLARE @employee_id VARCHAR(50)
INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)

SELECT @employee_id = SELECT = EmplyeeNumber FROM Employee WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Profile(UserName, Password,id) VALUES (@UserName, @Password,@employee_id)

-- YOUR THIRD INSERT STATEMNET HERE
END

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 05:51:45

quote:
Originally posted by rohitvishwakarma

quote:
Originally posted by yvette

is it because this time is insert at same time so the E0001 can't pass to Profile table??
because i try declare again,it works...

yes you have to use that part in between the two inserts i.e. Get the ID from previous INSERT (Employee)& put into a @employee_id and Pass it into VALUES of second INSERT of Profile table.

Modify the Procedure as highlighted:
Add datatypes against the paramets I have done for @UserName VARCHAR(100) do it for others too
CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

DECLARE @employee_id VARCHAR(50)
INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)

SELECT @employee_id = SELECT = EmplyeeNumber FROM Employee WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Profile(UserName, Password,id) VALUES (@UserName, @Password,@employee_id)

it show this error:
Msg 156, Level 15, State 1, Procedure sp_Insert_Into_Employee_Profile, Line 31
Incorrect syntax near the keyword 'SELECT'.

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 05:55:34

quote:
Originally posted by rohitvishwakarma

quote:
Originally posted by yvette

thanks rohitvishwakarma,
it works...is it i do the same thing when i want insert to three table by just create the stored procedure and the code also change like just now???
And i've other question.
when the data insert to employee table but it not pass the profile table.(senario same like my previous post about the auto generate id like c0001 that one)

is it i need to change the table name by add more _job???
is it i need to add the column name and datatype inside the create procedure b4 i insert the third query???

sorry for many "stupid" question from me...

is it i need to

Just add the Third INSERT query after the first two inside the procedure
as

CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

DECLARE @employee_id VARCHAR(50)
INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)

SELECT @employee_id = SELECT = EmplyeeNumber FROM Employee WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Profile(UserName, Password,id) VALUES (@UserName, @Password,@employee_id)

-- YOUR THIRD INSERT STATEMNET HERE
END

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:58:20

TRY THIS

CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

DECLARE @employee_id VARCHAR(50)

INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)

SELECT @employee_id = EmplyeeNumber FROM Employee WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Profile(UserName, Password,id) VALUES (@UserName, @Password,@employee_id)

END

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 06:02:03

For the Third Table: If the values for 3rd table are not present in the list add those after @Position

CREATE PROCEDURE sp_Insert_Into_Employee_Profile
(
@UserName VARCHAR(100),
@Password,
@Name,
@Identity,
@Gender,
@Race,
@MaritalStatus,
@Religion,
@DOB,
@Address,
@PostalCode,
@City,
@State,
@Nationality,
@HomeTel,
@MobileNo,
@DriverLicenseNo,
@DriverLicenseExpiry,
@Position

)
AS
BEGIN

DECLARE @employee_id VARCHAR(50)

INSERT INTO Employee (Name, IdentityNo, Gender, Race, MaritalStatus, Religion, DOB, Address, PostalCode, City, State, Nationality, HomeTel, MobileNo, DriverLicenseNo, DriverLicenseExpiry, Position) VALUES
(@Name, @Identity, @Gender, @Race, @MaritalStatus, @Religion, @DOB, @Address, @PostalCode, @City, @State, @Nationality, @HomeTel, @MobileNo, @DriverLicenseNo, @DriverLicenseExpiry, @Position)

SELECT @employee_id = EmplyeeNumber FROM Employee WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Profile(UserName, Password,id) VALUES (@UserName, @Password,@employee_id)

-- Following will insert into third table
INSERT INTO third_table_name (column1,column2,column3)
VALUES(val_1,val_2,val_3)
END

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 06:03:48

Thanks rohitvishwakarma, thanks for helping....
all works fine...

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 06:07:28

quote:
Originally posted by yvette

Thanks rohitvishwakarma, thanks for helping....
all works fine...

Always Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources