I see the problem. I think the decision to build query dynamically is a wrong one. I see that fn_split function is mentioned, so your query should be somehting like
select * from myTable inner join dbo.fn_split(@Roles) RoleList on myTable.Role = RoleList.Col1 (if you're using the function showed here by the previous poster). If you use the function from my link, then it would be Item instead of Col1 (I think Item is
better name for the column, BTW).
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
Yes...those statements are there...To avoid confusion, i did not copied here
Only cmd.CommandText = "bporesults"; is not there but without it also it was working fine
Dim objConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\test\App_Data\Database.mdf;Integrated Security=True;User Instance=True")
Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("dbo.bporesults")
cmd.CommandType = Data.CommandType.StoredProcedure
SQL Server Query Window what I mean is the place where you are creating / altering your stored procedure. It looks like there should be some problem with @SQL value only. Can you post your SET @SQL = 'Select..... ' statement here.
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
I see the problem. I think the decision to build query dynamically is a wrong one. I see that fn_split function is mentioned, so your query should be somehting like
select * from myTable inner join dbo.fn_split(@Roles) RoleList on myTable.Role = RoleList.Col1 (if you're using the function showed here by the previous poster). If you use the function from my link, then it would be Item instead of Col1 (I think Item is
better name for the column, BTW).
Hi,
I am not using fn_split right now..
can you give any clues why I am getting this error?
ERROR
Conversion failed when converting the varchar value 'SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like '%mumbai%' and DATEDIFF(Day, postdate, getdate(
) ) <='' to data type int.
SQL Server Query Window what I mean is the place where you are creating / altering your stored procedure. It looks like there should be some problem with @SQL value only. Can you post your SET @SQL = 'Select..... ' statement here.
Ok..Here I am copying my stored procedure as it is
ALTER PROCEDURE dbo.bporesults
(
@datediff int ,
@city varchar(20),
@role varchar(100)
)
AS
DECLARE @SQL NVARCHAR(1000)
SET @role = '''' + REPLACE(@role,',',''',''') + ''''
SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <=''' + @datediff + ''' and Role
in (' + @role + ')'
EXEC sp_executesql @SQL
@SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like
''%' + @city
+ '%'' and DATEDIFF(Day, postdate, getdate( ) ) <='
+ CAST(@datediff
AS VARCHAR(3))
+ ' and Role in ('
+ @role +
')'
Or chage the follwoing
ALTER PROCEDURE dbo.bporesults
(
@datediff int ,
to
ALTER PROCEDURE dbo.bporesults
(
@datediff varchar(3) ,
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
How is the data stored in the Role field? Is it a comma separated value ?
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
Naom
All-Star
36004 Points
7901 Posts
Re: problem in pattern matching..
Sep 18, 2008 03:27 PM|LINK
I see the problem. I think the decision to build query dynamically is a wrong one. I see that fn_split function is mentioned, so your query should be somehting like
select * from myTable inner join dbo.fn_split(@Roles) RoleList on myTable.Role = RoleList.Col1 (if you're using the function showed here by the previous poster). If you use the function from my link, then it would be Item instead of Col1 (I think Item is better name for the column, BTW).
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 18, 2008 04:38 PM|LINK
I do not see the statements
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "bporesults";
Are they there?
In the SQL Server Query Window run your stored procedure and see what is the result you are getting. For eg.
exec bporesults 3,'Mumbai','VP-Operations,Fresher'
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 06:00 PM|LINK
Yes...those statements are there...To avoid confusion, i did not copied here
Only cmd.CommandText = "bporesults"; is not there but without it also it was working fine
Dim objConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\test\App_Data\Database.mdf;Integrated Security=True;User Instance=True")
Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("dbo.bporesults")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@datediff", beforedays)
cmd.Parameters.AddWithValue("@city", city1)
cmd.Parameters.AddWithValue("@role", moreabout)
cmd.Connection = objConn
Dim ds As New System.Data.DataSet
Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)
da.Fill(ds, "callcenter")
DataList1.DataSource = ds
DataList1.DataBind()
But why the need to do so? Earlier I used to get results . If the logic is correct I should get results.
Also, Even if Query runs in SQL server query window, I will still have to try to remove that error
Also I don't know where is Sql server query window.
Do you know why the error I mentioned above is coming? Any clues?
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 18, 2008 06:10 PM|LINK
SQL Server Query Window what I mean is the place where you are creating / altering your stored procedure. It looks like there should be some problem with @SQL value only. Can you post your SET @SQL = 'Select..... ' statement here.
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 06:15 PM|LINK
Hi,
I am not using fn_split right now..
can you give any clues why I am getting this error?
ERROR
Conversion failed when converting the varchar value 'SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like '%mumbai%' and DATEDIFF(Day, postdate, getdate( ) ) <='' to data type int.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 06:29 PM|LINK
Ok..Here I am copying my stored procedure as it is
ALTER PROCEDURE dbo.bporesults
(
@datediff int ,
@city varchar(20),
@role varchar(100)
)
AS
DECLARE @SQL NVARCHAR(1000)
SET @role = '''' + REPLACE(@role,',',''',''') + ''''
SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <=''' + @datediff + ''' and Role in (' + @role + ')'
EXEC sp_executesql @SQL
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 06:35 PM|LINK
Please check my post above. I have copid my SP
I got some clue..Error comes due to
DECLARE @SQL NVARCHAR(1000)
SQL IS DECLARED AS VARCHAR BUT QUERY PRODUCING INT RESULTS ??
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 18, 2008 06:41 PM|LINK
Try this
SET
@SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <=' + CAST(@datediff AS VARCHAR(3)) + ' and Role in (' + @role + ')'Or chage the follwoing
ALTER PROCEDURE dbo.bporesults
(
@datediff int ,
to
ALTER PROCEDURE dbo.bporesults
(
@datediff varchar(3) ,
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 07:03 PM|LINK
Your both the option do not give even single error but not producing even results..
( I checked the tables to see if rows matching to criterias I am selecting do exists in table or not,...and they do )
I am not in hurry...If you want to analyze the problem please take your time..
Since you have got expertise I am sure you would be able to solve it..thanks..
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 18, 2008 07:19 PM|LINK
How is the data stored in the Role field? Is it a comma separated value ?
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.