How To Insert Data With INNER JOIN With 3 TABLE

C# Corner

Guest User

4y979SQL Server

I Will Attach DataBase File Check And Send ME Code

Table CheckOut Not Inster Only UPDATE

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
you have an extra column sorttoOrder, you need to insert that column too
Create procedure SpCheckInCheckOut
@Name Varchar(250)='',
@Woker Varchar(250)='',
@Cnic Varchar(250)='',
@CardNumber Varchar(250)='',
@FlatNumber Varchar(250)='',
@Designation Varchar(250)='',
@CheckinID INT=0,
@CheckIn DateTime,
@CheckOutID INT=0,
@CheckOut DateTime,
@Action Varchar(7)=''
AS
BEGIN
IF(@Action='Checkin')
BEGIN
INSERT INTO Employee (Name,Woker,Cnic,CardNumber,FlatNumber,Designation)
VALUES (@Name,@Woker,@Cnic,@CardNumber,@FlatNumber,@Designation);
DECLARE @Wrkid int = 0;
SET @Wrkid=@Scope_IDENTITY(),
INSERT INTO CheckIn(WokerID,CheckinID,[Check-In],sortoder)VALUES (@Wrkid,@CheckinID,@CheckIn,0)
INSERT INTO CheckOut(WokerID,CheckOutID,[Check-Out],sortoder)VALUES(@Wrkid,0,NULL,0)
END
IF(@Action='CheckOut')
BEGIN
UPDATE CheckOut SET CheckOutID=@CheckOutID,[Check-Out]=@CheckOut;
END
END
Sachin Singh4y ago
Use the SP from my recent answer (2 vote up)
Guest User4y ago
1. private void BtnSubmit_Click(object sender, EventArgs e)
2. {
3. SqlConnection connection = new SqlConnection(myconn);
5. if (string.IsNullOrEmpty(txtName.Text))
6. {
7. MessageBox.Show("Please Enter Worker Name !!!","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
8. }
9. else if (string.IsNullOrEmpty(txtCnic.Text))
10. {
11. MessageBox.Show("Please Enter A CNIC-NO !!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
12. }
13. else
14. {
15. try
16. {
19. SqlCommand cmd = new SqlCommand("SpCheckInCheckOut", connection);
20. connection.Open();
21. cmd.Parameters.AddWithValue("@Name", ddlName.Text);
22. cmd.Parameters.AddWithValue("@Action", "Checkin");
23. cmd.Parameters.AddWithValue("@Worker", txtWorker.Text);
24. cmd.Parameters.AddWithValue("@Cnic", txtCnic.Text);
25. cmd.Parameters.AddWithValue("@CardNumber", txtCardNumber.Text);
26. cmd.Parameters.AddWithValue("@FlatNumber", txtFlatNumber.Text);
27. cmd.Parameters.AddWithValue("@Designation", txtDesignation.Text);
28. cmd.Parameters.AddWithValue("@CheckIn", dtpTimeIn.Text);
29. cmd.Parameters.AddWithValue("@CheckOut", dtpTimeOut.Text);
30. int NumRes = cmd.ExecuteNonQuery();
31. if (NumRes > 0)
32. {
33. MessageBox.Show("Record Saved Successfully !!!");
34. }
35. else
36. MessageBox.Show("Please Try Again !!!");
37. }
38. catch (Exception ex)
39. {
40. MessageBox.Show("Error:- " + ex.Message);
41. }
42. connection.Close();
43. }
45. }
1. USE [StaffDB]
2. GO
3. /****** Object: StoredProcedure [dbo].[SpCheckInCheckOut] Script Date: 6/22/2022 11:39:33 AM ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8. ALTER procedure [dbo].[SpCheckInCheckOut]
9. @Name Varchar(250)='',
10. @Woker Varchar(250)='',
11. @Cnic Varchar(250)='',
12. @CardNumber Varchar(250)='',
13. @FlatNumber Varchar(250)='',
14. @Designation Varchar(250)='',
15. @CheckinID INT=0,
16. @CheckIn DateTime,
17. @CheckOutID INT=0,
18. @CheckOut DateTime,
19. @Action Varchar(7)=''
20. AS
21. BEGIN
22. IF(@Action='Checkin')
23. BEGIN
24. INSERT INTO Worker(FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation)
25. VALUES (@Name,@Woker,@Cnic,@CardNumber,@FlatNumber,@Designation);
26. DECLARE @Wrkid int = 0;
27. SET @Wrkid=SCOPE_IDENTITY();
28. INSERT INTO tblCheckIn(WorkerID,CheckinID,CheckIn)VALUES (@Wrkid,@CheckinID,@CheckIn)
29. INSERT INTO tblCheckOut(WorkerID,CheckOutID,CheckOut)VALUES(@Wrkid,0,NULL)
30. END
31. IF(@Action='CheckOut')
32. BEGIN
33. UPDATE tblCheckOut SET CheckOutID=@CheckOutID,CheckOut=@CheckOut;
34. END
35. END

Sachin Singh4y ago

Yes, columns are fewer, modify SP and table as

Create table tblCheckin
(
CheckInID int identity not null,
CheckIn datetime null,
sortoder int not null
CONSTRAINT pk_tblCheckin PRIMARY KEY (CheckInID)
);
Create table tblCheckout
(
CheckOutID int identity not null,
CheckOut datetime null,
sortoder int not null
CONSTRAINT pk_tblCheckout PRIMARY KEY (CheckOutID)
);
Create table tblWoker
(
Empid int IDENTITY(1,1) not null,
Name varchar(300) not null,
Worker varchar(300) not null,
Cnic varchar(300) not null,
CardNumber varchar(300) not null,
FlatNumber varchar(300) not null,
Designation varchar(300) not null,
CheckInID INT NULL,
CheckOutID INT NULL,
CONSTRAINT pk_tblWoker PRIMARY KEY (Empid),
CONSTRAINT fk_tblCheckin FOREIGN KEY (CheckInID) REFERENCES tblCheckin (CheckInID),
CONSTRAINT fk_tblCheckout FOREIGN KEY (CheckOutID) REFERENCES tblCheckout (CheckOutID),
);

CREATE PROCEDURE spEmployee
(
@Name Varchar(250),
@Worker Varchar(250),
@Cnic Varchar(250),
@CardNumber Varchar(250),
@FlatNumber Varchar(250),
@Designation Varchar(250),
@CheckinID INT=0,
@CheckIn DateTime null,
@CheckOutID INT=0,
@CheckOut DateTime null,
@Action Varchar(25)
)
AS
BEGIN
IF @Action='SaveData'
BEGIN
Declare @chkInId INT=0;
Declare @chkOutId INT=0;
INSERT INTO tblCheckin(CheckIn,sortoder)VALUES(@CheckIn,0)
SET @chkInId=@Scope_Identity();
INSERT INTO tblCheckout(CheckOut,sortoder)VALUES(@CheckOut,0)
SET @chkOutId=@Scope_Identity();
INSERT INTO tblWoker(Name,Worker,Cnic,CardNumber,FlatNumber,Designation,CheckInID,CheckOutID )
VALUES (@Name,@Worker,@Cnic,@CardNumber,@FlatNumber,@Designation,@chkInId,@chkOutId);
END
IF @Action = 'UpdateData'
BEGIN
UPDATE tblCheckout SET CheckOut=@CheckOut where CheckOutId=@CheckOutId;
END
END

Guest User4y ago

CREATE PROCEDURE spEmployee
(
@Name Varchar(250),
@Worker Varchar(250),
@Cnic Varchar(250),
@CardNumber Varchar(250),
@FlatNumber Varchar(250),
@Designation Varchar(250),
@CheckinID INT=0,
@CheckIn DateTime null,
@CheckOutID INT=0,
@CheckOut DateTime null,
@Action Varchar(25)
)
AS
BEGIN
IF @Action='SaveData'
BEGIN
INSERT INTO tblWoker(Name,Worker,Cnic,CardNumber,FlatNumber,Designation)
VALUES (@Name,@Worker,@Cnic,@CardNumber,@FlatNumber,@Designation);
DECLARE @Wrkid int = 0;
SET @Wrkid=SCOPE_IDENTITY()
INSERT INTO tblCheckin(CheckInID,CheckIn)VALUES(@Wrkid,@CheckinID,@CheckIn,0)
INSERT INTO tblCheckout(CheckOutID,CheckOut)VALUES(@Wrkid,@CheckOutID,0,NULL,0)
END
IF @Action = 'UpdateData'
BEGIN
UPDATE tblCheckout SET CheckOutID=@CheckOutID,CheckOut=@CheckOut;
END
END

Msg 110, Level 15, State 1, Procedure spEmployee, Line 23 [Batch Start Line 0]

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Guest User4y ago

Msg 257, Level 16, State 3, Procedure SpCheckInCheckOut, Line 21 [Batch Start Line 0]

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Sachin Singh4y ago

follow this

Create table tblCheckin
(
CheckInID int not null,
CheckIn datetime null,
sortoder int not null
CONSTRAINT pk_tblCheckin PRIMARY KEY (CheckInID)
);
Create table tblCheckout
(
CheckOutID int not null,
CheckOut datetime null,
sortoder int not null
CONSTRAINT pk_tblCheckout PRIMARY KEY (CheckOutID)
);
Create table tblWoker
(
Empid int IDENTITY(1,1) not null,
Name varchar(300) not null,
Worker varchar(300) not null,
Cnic varchar(300) not null,
CardNumber varchar(300) not null,
FlatNumber varchar(300) not null,
Designation varchar(300) not null,
CheckInID INT NULL,
CheckOutID INT NULL,
CONSTRAINT pk_tblWoker PRIMARY KEY (Empid),
CONSTRAINT fk_tblCheckin FOREIGN KEY (CheckInID) REFERENCES tblCheckin (CheckInID),
CONSTRAINT fk_tblCheckout FOREIGN KEY (CheckOutID) REFERENCES tblCheckout (CheckOutID),
);

Guest User4y ago

Create table tblCheckin
(
CheckInID int not null,
CheckIn datetime,
sortoder int not null
CONSTRAINT pk_tblCheckin PRIMARY KEY (CheckInID)
);
Create table tblCheckout
(
CheckOutID int not null,
CheckOut datetime,
sortoder int not null
CONSTRAINT pk_tblCheckout PRIMARY KEY (CheckOutID)
);
Create table tblWoker
(
Empid int IDENTITY(1,1) not null,
Name varchar(300) not null,
Worker varchar(300) not null,
Cnic varchar(300) not null,
CardNumber varchar(300) not null,
FlatNumber varchar(300) not null,
Designation varchar(300) not null,
CONSTRAINT pk_tblWoker PRIMARY KEY (Empid),
CONSTRAINT fk_tblCheckin FOREIGN KEY (CheckInID) REFERENCES tblCheckin (CheckInID),
CONSTRAINT fk_tblCheckout FOREIGN KEY (CheckOutID) REFERENCES tblCheckout (CheckOutID),
);

Error In HeighLigt What Wrong Code

Sachin Singh4y ago

You have two mistakes,

copy paste this

Create procedure SpCheckInCheckOut
@Name Varchar(250)='',
@Woker Varchar(250)='',
@Cnic Varchar(250)='',
@CardNumber Varchar(250)='',
@FlatNumber Varchar(250)='',
@Designation Varchar(250)='',
@CheckinID INT=0,
@CheckIn DateTime,
@CheckOutID INT=0,
@CheckOut DateTime,
@Action Varchar(7)=''
AS
BEGIN
IF(@Action='Checkin')
BEGIN
INSERT INTO Employee (Name,Woker,Cnic,CardNumber,FlatNumber,Designation)
VALUES (@Name,@Woker,@Cnic,@CardNumber,@FlatNumber,@Designation);
DECLARE @Wrkid int = 0;
SET @Wrkid=@Scope_IDENTITY(),
INSERT INTO CheckIn(WokerID,CheckinID,[Check-In])VALUES (@Wrkid,@CheckinID,@CheckIn)
INSERT INTO CheckOut(WokerID,CheckOutID,[Check-Out])VALUES(@Wrkid,0,NULL)
END
IF(@Action='CheckOut')
BEGIN
UPDATE CheckOut SET CheckOutID=@CheckOutID,[Check-Out]=@CheckOut;
END
END

@@Identity will not give you right Id, if multiple sessions are up at a time. so i removed that.

Guest User4y ago

Msg 102, Level 15, State 1, Procedure SpCheckInCheckOut, Line 24 [Batch Start Line 0]

Incorrect syntax near '1'.

Create procedure SpCheckInCheckOut
(
@Name Varchar(250)='',
@Woker Varchar(250)='',
@Cnic Varchar(250)='',
@CardNumber Varchar(250)='',
@FlatNumber Varchar(250)='',
@Designation Varchar(250)='',
@CheckinID INT=0,
@CheckIn DateTime,
@CheckOutID INT=0,
@CheckOut DateTime,
@Action Varchar(7)=''
)
AS
BEGIN
IF(@Action='Checkin')
BEGIN
INSERT INTO Employee (Name,Woker,Cnic,CardNumber,FlatNumber,Designation)
VALUES (@Name,@Woker,@Cnic,@CardNumber,@FlatNumber,@Designation);
DECLARE @Wrkid int = 0;
SET @Wrkid=@@IDENTITY(1,1)PRIMARY KEY,
INSERT INTO CheckIn(WokerID,CheckinID,[Check-In])VALUES (@Wrkid,@CheckinID,@CheckIn)
INSERT INTO CheckOut(WokerID,CheckOutID,[Check-Out])VALUES(@Wrkid,0,NULL)
END
IF(@Action='CheckOut')
BEGIN
UPDATE CheckOut SET CheckOutID=@CheckOutID,[Check-Out]=@CheckOut;
END
END

Sachin Singh4y ago

You need this

Create procedure SpCheckInCheckOut
@Name Varchar(250)='',
@worker Varchar(250)='',
@Cnic INT=0,
@CardNumber INT=0,
@RalNumbe INT=0,
@Designation Varchar(250)=''
@CheckOutId INT=0,
@checkInId INT=0,
@checkOut DateTime,
@CheckIn DateTime,
@Action Varchar(7)=''
as
begin
if(@Action='Checkin')
begin
insert into Employee (Name,worker,Cnic,CardNumber,RalNumber,Designation)
values(@Name,@worker,@Cnic,@CardNumber,@RalNumber,@Designation);
Declare @WkrId INT =0;
SET @WkrId=@ScopeIdentity();
Insert into CheckIn(WorkerId,checkInId,CheckIn) values(@WkrId,@checkInId,@CheckIn)
Insert into CheckOut(WorkerId,CheckOutId,checkOut) values(@WkrId,0,null)
end
if(@Action='CheckOut')
begin
Update CheckOut set CheckOutId=@CheckOutId, checkOut=@checkOut;
end
end

Note:- Make sure CheckOutId,checkOut in the table CheckOut are nullable columns.

Jaydeep Patil4y ago

Hi,

Check the following link, I think that will help you

https://stackoverflow.com/questions/15115759/update-multiple-tables-in-sql-server-using-inner-join

https://www.sqlshack.com/a-step-by-step-walkthrough-of-sql-inner-join/

Regards,

Jaydeep Patil

Guest User4y ago

Select * From Employee

only Data Insert

Select * from CheckIn

only Data Insert

Select * from CheckOut update

only Data Update

Employe Date And Checkin In Same id Save