How To Bind 3 Table

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

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) <= 0
)
THEN 1
ELSE (
CASE
WHEN (
(CAST(e.Id AS VARCHAR(50)) LIKE '%' + @searchText + '%')
OR (EmpName LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
END
)
END
)
end

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);
}
}

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) <= 0
)
THEN 1
ELSE (
CASE
WHEN (
(CAST(e.Id AS VARCHAR(50)) LIKE '%' + @searchText + '%')
OR (EmpName LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
END
)
END
)
end

Msg 156, Level 15, State 1, Procedure SpGetEmployee, Line 15 [Batch Start Line 0]

Incorrect syntax near the keyword 'AND'.

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.

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) <= 0
)
THEN 1
ELSE (
CASE
WHEN (
(CAST(e.Id AS VARCHAR(50)) LIKE '%' + @searchText + '%')
OR (EmpName LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
END
)
END
)
end

6 Replies

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

Sachin Singh4y ago
Accepted 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) <= 0
)
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 User4y ago
1. private void BtnTimteIn_Click(object sender, EventArgs e)
2. {
3. try
4. {
6. SqlConnection connection = new SqlConnection(myconn);
7. connection.Open();
8. SqlCommand cmd = new SqlCommand("spCheckIn", connection);
9. cmd.CommandType = CommandType.StoredProcedure;
10. cmd.Parameters.AddWithValue("@ActionType", "SaveData");
11. cmd.Parameters.AddWithValue("@CheckIn", DateTime.Parse(dtpTimeIn.Text));
13. int NumRes = cmd.ExecuteNonQuery();
14. if (NumRes > 0)
15. {
16. MessageBox.Show("Record Saved Successfully !!!", "Register", MessageBoxButtons.OK, MessageBoxIcon.Information);
17. ClearAllData();
18. }
19. else
20. {
21. MessageBox.Show("Please Try Again !!!");
22. }
23. connection.Close();
24. }
25. catch (Exception ex)
26. {
27. MessageBox.Show("Error:- " + ex.Message);
28. }
30. }