Numbers and wildcards - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Numbers and wildcards

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-09-20 : 11:00:43

I have a stored procedure (see below) but I get the error

Syntax error converting the varchar value '%' to a column of data type int.

I assume this is because I am trying to use a wildcard around a number. If the user types in 10, I want to list records say 101, 10, 100,910 etc. How can I do this ?

CREATE PROCEDURE spRMU_GetFilesforRequest

@strDepartment nvarchar(100),
@strDivision nvarchar(100),
@strSection nvarchar(100),
@strFileRef nvarchar(100),
@strSubject nvarchar(1000),
@strDescription nvarchar(1000),
@strBoxNo nvarchar(10),
@strConNo nvarchar(10)

AS
SELECT *
FROM tblFiles

WHERE
Dept like '%'+ @strDepartment +'%' and
Division like '%'+@strDivision +'%' and
Sect like '%'+@strSection+'%' and
FileRef like '%' + @strFileRef + '%' and
Subject like '%'+@strSubject+'%' and
FileDescription like '%'+@strDescription+'%' and
BoxNo like '%'+BoxNo+'%' and
ConNo like '%'+ConNo+'%'
GO

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 11:03:01

Try using CAST on the integer columns to convert the value to varchar. This won't be very efficient if all your columns are integer in your WHERE clause!

Raymond

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 11:04:14

change
ConNo like '%'+ConNo+'%'
to
ConNo like '%'+cast(ConNo as varchar(50))+'%'

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 11:04:35

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-09-20 : 11:22:28

Thanks. I don't get the error now but it seems to be ignoring the criteria if I enter a ConNo or BoxNo.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 11:29:06

You have a few parts to your WHERE clause, all with an AND condition. Are you sure your underlying data has rows which meet all the criteria? What happens if you one or more of the parameters is blank?

Raymond

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-09-20 : 11:32:40

That's why I put wildcards around them all - so if they can be left blank if necessary. Is my logic wrong ?

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 11:36:59

No the logic looks right, so long as you don't pass in any NULLs. Can you post an example call to the proc and confirm that there's underlying data you'd expect to be returned?

Raymond

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-09-20 : 11:42:19

Here's my call. I am getting back the correct Department and Division, but if I enter ConNo 301053 then 10101010 is displayed as well as 301053. All the other search fields I have left blank.

Dim SqlDataAdapter1 As New SqlDataAdapter
Dim MySQL As String = "spRMU_GetFilesForRequest"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strDepartment", Me.SearchtxtDepartment.Text))
Cmd.Parameters.Add(New SqlParameter("@strDivision", Me.SearchtxtDivision.Text))
Cmd.Parameters.Add(New SqlParameter("@strSection", Me.ddlSearchtxtSection.SelectedValue))
Cmd.Parameters.Add(New SqlParameter("@strFileRef", Me.SearchtxtFileRef.Text))
Cmd.Parameters.Add(New SqlParameter("@strSubject", Me.SearchtxtSubject.Text))
Cmd.Parameters.Add(New SqlParameter("@strDescription", Me.SearchtxtDescription.Text))
Cmd.Parameters.Add(New SqlParameter("@strBoxNo", Me.SearchtxtBoxNo.Text))
Cmd.Parameters.Add(New SqlParameter("@strConNo", Me.SearchtxtConNo.Text))
SqlDataAdapter1.SelectCommand = Cmd
SqlDataAdapter1.Fill(DsFileSearchRequests1, "tblFiles")
Me.DataGrid1.DataBind()

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 11:56:56

you forgot the @ in your query:

BoxNo like '%'+@BoxNo+'%' and
ConNo like '%'+@ConNo+'%'

is that it?

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-09-20 : 12:08:27

On dear me, yes.

IT's working fine now...how embarassing.....

Thank you everyone.

How much more work could I get through if I typed things correctly first time ??????

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 12:12:49

not much more....

these things have a way of compensating themselves in different areas...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources