locked
Query returns no results???? RRS feed

  • 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.

    Monday, August 3, 2009 2:24 PM

Answers

  • User-1199946673 posted

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

    No you don't Wink

    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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 3, 2009 4:38 PM

All replies

  • 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.

     

    Monday, August 3, 2009 3:44 PM
  • 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

    Monday, August 3, 2009 4:30 PM
  • User-1199946673 posted

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

    No you don't Wink

    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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 3, 2009 4:38 PM
  • 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 Smile

    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.

    Monday, August 3, 2009 4:51 PM