How To Bind 3 Table

Question

tbl_Employee Any Data Enter than any id set apply in tbl_CheckIn > tbl_CheckOut

Sachin Singh · Answer

Then remove with create procedure SpGetEmployee @searchText Varchar(500)= '' as begin select * from tbl_Employee e JOIN tbl_CheckIn ci ON e.Id=ci.EmpId JOIN tbl_CheckOut co ON co.EmpId=e.Id where AND 1 = ( CASE WHEN ( @searchText IS NULL OR LEN(@searchText) ) THEN 1 ELSE ( CASE WHEN ( (CAST(e.Id AS VARCHAR(50)) LIKE '%' + @searchText + '%' ) OR (EmpName LIKE '%' + @searchText + '%' ) ) THEN 1 ELSE 0 END ) END ) end

Guest User · Answer

private void BtnTimteIn_Click( object sender EventArgs e) { try { SqlConnection connection = new SqlConnection(myconn); connection.Open(); SqlCommand cmd = new SqlCommand( spCheckIn  connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue( @ActionType  SaveData ); cmd.Parameters.AddWithValue( @CheckIn  DateTime.Parse(dtpTimeIn.Text)); int NumRes = cmd.ExecuteNonQuery(); if (NumRes > 0) { MessageBox.Show( Record Saved Successfully !!!  Register  MessageBoxButtons.OK MessageBoxIcon.Information); ClearAllData(); } else { MessageBox.Show( Please Try Again !!! ); } connection.Close(); } catch (Exception ex) { MessageBox.Show( Error:-  + ex.Message); } }

Sachin Singh · Answer

Yes AND is not needed it is typo create procedure SpGetEmployee @searchText Varchar(500)= '' as begin select * from tbl_Employee e JOIN tbl_CheckIn ci ON e.Id=ci.EmpId JOIN tbl_CheckOut co ON co.EmpId=e.Id where 1 = ( CASE WHEN ( @searchText IS NULL OR LEN(@searchText) ) THEN 1 ELSE ( CASE WHEN ( (CAST(e.Id AS VARCHAR(50)) LIKE '%' + @searchText + '%' ) OR (EmpName LIKE '%' + @searchText + '%' ) ) THEN 1 ELSE 0 END ) END ) end

Guest User · Answer

Msg 156 Level 15 State 1 Procedure SpGetEmployee Line 15 [Batch Start Line 0] Incorrect syntax near the keyword 'AND'.

Guest User · Answer

Msg 156 Level 15 State 1 Procedure SpGetEmployee Line 6 [Batch Start Line 0] Incorrect syntax near the keyword 'With' . Msg 319 Level 15 State 1 Procedure SpGetEmployee Line 6 [Batch Start Line 0] Incorrect syntax near the keyword 'with' . If this statement is a common table expression an xmlnamespaces clause or a change tracking context clause the previous statement must be terminated with a semicolon. Msg 319 Level 15 State 1 Procedure SpGetEmployee Line 6 [Batch Start Line 0] Incorrect syntax near the keyword 'with' . If this statement is a common table expression an xmlnamespaces clause or a change tracking context clause the previous statement must be terminated with a semicolon. Msg 319 Level 15 State 1 Procedure SpGetEmployee Line 6 [Batch Start Line 0] Incorrect syntax near the keyword 'with' . If this statement is a common table expression an xmlnamespaces clause or a change tracking context clause the previous statement must be terminated with a semicolon. Msg 319 Level 15 State 1 Procedure SpGetEmployee Line 6 [Batch Start Line 0] Incorrect syntax near the keyword 'with' . If this statement is a common table expression an xmlnamespaces clause or a change tracking context clause the previous statement must be terminated with a semicolon.

Sachin Singh · Answer

Suppose you have a textbox where you can enter employee name or Id and it should return employee's all information including CheckIn time and CheckOut time then your Stored Procedure should be create procedure SpGetEmployee @searchText Varchar(500)= '' as begin select * from tbl_Employee e JOIN With (NoLock) tbl_CheckIn ci With (NoLock) ON e.Id=ci.EmpId JOIN With (NoLock) tbl_CheckOut co With (NoLock) ON co.EmpId=e.Id where AND 1 = ( CASE WHEN ( @searchText IS NULL OR LEN(@searchText) ) THEN 1 ELSE ( CASE WHEN ( (CAST(e.Id AS VARCHAR(50)) LIKE '%' + @searchText + '%' ) OR (EmpName LIKE '%' + @searchText + '%' ) ) THEN 1 ELSE 0 END ) END ) end