Query returns no results????

Question

User1368882009 posted

Hi all,

A strange issue here, I am using an ASP.net site with VB back end and an ACCESS 2003 database.  I am trying to find all items whose serial number contains a string. I already figured out that access uses LIKE instead of CONTAINS, but now I have run into another strange issue.....

Here is my Query: "SELECT ItemID,SerialNumber FROM TblItems WHERE SerialNumber LIKE @SN"

then I add the parameter later:  cmd2.Parameters.AddWithValue("@SN", "*" & TextBox3.Text & "*")

When I copy and paste the Query into Access and run it (have to manually enter parameter, but i simply take what i put in the textbox and put a * on either end), I get the results I expect, however when the program runs, it returns no results.

This is very confusing to me, why would the SAME query run in VB code return zero results while running SAME (copy and paste)  Query in access do same thing?

I have tried using non parametized query like this: "SELECT ItemID,SerialNumber FROM tblItems WHERE SerialNumber LIKE '*" & Textbox3.text & "*'"   and same issue.

Answer 1

User650467224 posted

 mpoldfather,

Both are right to me. let me see our connection and comand code.

fore i have a better view off your problem.

 

Answer 2

User1368882009 posted

Using conn2 As New OleDbConnection(Utils.GetConnString)

Dim sqlstring2 As String = "SELECT ItemID,SerialNumber FROM TblItems WHERE SerialNumber LIKE @SN"

Using cmd2 As New OleDbCommand(sqlstring2, conn2)

conn2.Open() 

cmd2.CommandType = CommandType.Text

cmd2.Parameters.AddWithValue("@SN", "*" & TextBox3.Text & "*")

Dim reader2 As OleDbDataReader = cmd2.ExecuteReader()

While reader2.Read() 

foundItem = True 

Table1.Visible = True

Dim current As New HyperLink 

current.NavigateUrl = "~\SageWork\ItemDetail.aspx?Id=" & reader2("ItemID") 

'Response.Redirect("~\SageWork\ItemDetail.aspx?Id=" & reader2("ItemID"))

current.Text = reader2("SerialNumber")

cell.Controls.Add(current)

row.Cells.Add(cell)

cell =New TableCell()

Table1.Rows.Add(row)

row =New TableRow() 

End While 

End Using

conn2.Close()

End Using

Answer 3

User-1199946673 posted

I am using an ASP.net site with VB back end and an ACCESS 2003 database

 

No you don't

When you're using a MDB file in an ASP.NET environment, you're NOT using An Access Database, but you're using A Jet Database Engine! And in Jet, the WildCard Character is %, not *

So when you run the query in Access, you should use *. Howeve, when running the query from ASP.NET, you should use % instead!

Answer 4

User1368882009 posted

 Well that worked perfect.  Thanks a Million!

I will cahnge how I refer to the database from know on as JET instead of ACCESS

Is it just me or is it stupid that it is different characters in both? I guess Microsoft has their reason, beyond just confusing us.