How To Insert Data With INNER JOIN With 3 TABLE

Question

I Will Attach DataBase File Check And Send ME Code Table CheckOut Not Inster Only UPDATE

Sachin Singh · 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 Singh · Answer

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

Sachin Singh · Answer

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.

Sachin Singh · Answer

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.

Sachin Singh · Answer

Use the SP from my recent answer (2 vote up)

Guest User · Answer

private void BtnSubmit_Click( object sender, EventArgs e) { SqlConnection connection = new SqlConnection(myconn); if ( string .IsNullOrEmpty(txtName.Text)) { MessageBox.Show( "Please Enter Worker Name !!!" , "Error" ,MessageBoxButtons.OK,MessageBoxIcon.Error); } else if ( string .IsNullOrEmpty(txtCnic.Text)) { MessageBox.Show( "Please Enter A CNIC-NO !!!" , "Error" , MessageBoxButtons.OK, MessageBoxIcon.Error); } else { try { SqlCommand cmd = new SqlCommand( "SpCheckInCheckOut" , connection); connection.Open(); cmd.Parameters.AddWithValue( "@Name" , ddlName.Text); cmd.Parameters.AddWithValue( "@Action" , "Checkin" ); cmd.Parameters.AddWithValue( "@Worker" , txtWorker.Text); cmd.Parameters.AddWithValue( "@Cnic" , txtCnic.Text); cmd.Parameters.AddWithValue( "@CardNumber" , txtCardNumber.Text); cmd.Parameters.AddWithValue( "@FlatNumber" , txtFlatNumber.Text); cmd.Parameters.AddWithValue( "@Designation" , txtDesignation.Text); cmd.Parameters.AddWithValue( "@CheckIn" , dtpTimeIn.Text); cmd.Parameters.AddWithValue( "@CheckOut" , dtpTimeOut.Text); int NumRes = cmd.ExecuteNonQuery(); if (NumRes > 0) { MessageBox.Show( "Record Saved Successfully !!!" ); } else MessageBox.Show( "Please Try Again !!!" ); } catch (Exception ex) { MessageBox.Show( "Error:- " + ex.Message); } connection.Close(); } } USE [StaffDB] GO /****** Object: StoredProcedure [dbo].[SpCheckInCheckOut] Script Date : 6/22/2022 11:39:33 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[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 Worker(FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation) VALUES (@ Name ,@Woker,@Cnic,@CardNumber,@FlatNumber,@Designation); DECLARE @Wrkid int = 0; SET @Wrkid=SCOPE_IDENTITY(); INSERT INTO tblCheckIn(WorkerID,CheckinID,CheckIn) VALUES (@Wrkid,@CheckinID,@CheckIn) INSERT INTO tblCheckOut(WorkerID,CheckOutID,CheckOut) VALUES (@Wrkid,0, NULL ) END IF(@ Action = 'CheckOut' ) BEGIN UPDATE tblCheckOut SET CheckOutID=@CheckOutID,CheckOut=@CheckOut; END END

Guest User · Answer

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 User · Answer

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 Singh · Answer

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 User · Answer

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

Guest User · Answer

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

Jaydeep Patil · Answer

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 User · Answer

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

How To Insert Data With INNER JOIN With 3 TABLE

Answers (13)