Odd query failure

  • Thread starter Thread starter B. Chernick
  • Start date Start date
B

B. Chernick

I'm writing a Winforms program in VB (Dot Net 2.0, VS2005) using a SQL Server
2000 database. I'm not sure whether I have a programming bug or a sql
problem. I can't get a 'Like' query to work.

I have a dll with an xsd. I dragged 2 fields from the Server Explorer onto
the xsd. I then created a second query for a Like search. (I've probably
used this basic technique hundreds of times on this job but I rarely use the
'Like' command.) This runs perfectly in the Query Builder. The query is as
follows:

SELECT serial, cnst
FROM dbo.micro
WHERE (serial LIKE @serial + '%')

where 'serial' is the primary key, Char 11. 'cnst' is char 10 - null
permitted.

I have a data management layer which inherits the xsd. In that I created a
pair of functions to fill the default 'micro' table:
Public Sub FillMicro()
Dim da As New
DataManager.dsMicroScheduleTableAdapters.microTableAdapter
Try
da.Fill(micro)
Catch ex As Exception
MsgBox("Error Message")
End Try
End Sub

Public Sub FillMicro(ByVal vsJobno As String)
Dim da As New
DataManager.dsMicroScheduleTableAdapters.microTableAdapter
Try
da.FillByLikeQuery(micro, vsJobno)
Catch ex As Exception
MsgBox("Error Message")
End Try
End Sub

So, the first function FillMicro() works. The second, FillMicro(jobno)
returns nothing, even though there should be about 1200 rows. I'm not
getting any error messages or crashes. It just returns 0 rows.

I'll probably find the bug 5 minutes after I post this but for now I'm
stumped.

Anyone see anything wrong with this?
 
Ok. Cancel this. I found the problem.

The parameter automatically created by Visual Studio has the same data type
as the table field it's being compared against. In this case the data type
was char 11 (fixed length).

The solution to the bug was to change the data type of the input parameter
to varchar.
 
Back
Top