Last post Aug 03, 2009 04:51 PM by mpoldfather
Aug 03, 2009 02:24 PM|mpoldfather|LINK
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.
Aug 03, 2009 03:44 PM|Isaac Garcia|LINK
Both are right to me. let me see our connection and comand code.
fore i have a better view off your problem.
Aug 03, 2009 04:30 PM|mpoldfather|LINK
Using conn2 As
"SELECT ItemID,SerialNumber FROM TblItems WHERE SerialNumber LIKE @SN"
New OleDbCommand(sqlstring2, conn2)
cmd2.CommandType = CommandType.Text
"*" & TextBox3.Text &
As OleDbDataReader = cmd2.ExecuteReader()
foundItem = True
Table1.Visible = True
current.NavigateUrl = "~\SageWork\ItemDetail.aspx?Id=" & reader2("ItemID")
'Response.Redirect("~\SageWork\ItemDetail.aspx?Id=" & reader2("ItemID"))
current.Text = reader2("SerialNumber")
cell =New TableCell()
row =New TableRow()
Aug 03, 2009 04:38 PM|hans_v|LINK
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!
Aug 03, 2009 04:51 PM|mpoldfather|LINK
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.