Multiple Parameter Search Against Access Database

stublair · July 21st, 2008, 05:44 AM

Hi,

Apologises for this most likely being in the wrong place...but I am completely stuck! As you can see from the code below I have 4 text boxes user can enter text into (though they may not enter infor into all of them) and for results to be pulled back. I can get it working so that if input is entered into 1 text box but not if multiple parameters are entered. Any help would be greatly appreciated.

%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

    Sub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs)
        Dim objCommand As OleDbCommand
        Dim objAdapter As OleDbDataAdapter
        Dim objDataSet As DataSet
        Dim strSearch1 As String
        Dim strSearch2 As String
        Dim strSearch3 As String
        Dim strSearch4 As String

        Dim objConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Database Page\App_Data\WIRRAL_NLPG_FROM_PLANACCESS.MDB"
    Dim cn As New OleDbConnection(objConnection)
        Dim strSQLQuery As String

        strSearch1 = txtbx1.Text
        strSearch2 = txtbx2.Text
        strSearch3 = txtbx3.Text
        strSearch4 = txtbx4.Text

        If Len(Trim(strSearch4)) > 0 Then
            strSQLQuery = "SELECT * FROM GS_LLPG WHERE POSTCODE LIKE '%" & Replace(strSearch4, "'", "''") & "%' "

            cn.Open()
            Dim cmd As New OleDbCommand(strSQLQuery, cn)

            objCommand = New OleDbCommand(strSQLQuery, cn)

            objAdapter = New OleDbDataAdapter(objCommand)
            objDataSet = New DataSet()
            objAdapter.Fill(objDataSet)

            dgPaging.DataSource = objDataSet
            dgPaging.DataBind()

            cn.Close()
        Else

            txtbx4.Text = "CH41 5JU"

        End If
    End Sub

</script>
<html>
<head>
  <title>Database</title>
</head>
<body>

<form id="Form1" runat="server">

<asp:TextBox id="txtbx1" runat="server" Width="200px" /><br />
<asp:TextBox id="txtbx2" runat="server" Width="200px" /><br />
<asp:TextBox id="txtbx3" runat="server" Width="200px" /><br />
<asp:TextBox id="txtbx4" runat="server" Width="200px" /><br />

<asp:Button id="btnSearch" runat="server"
    Text ="Search"
    OnClick ="btnSearch_OnClick"
/>

<asp:DataGrid id="dgPaging" runat="server"
    HeaderStyle-Font-Bold="True"
/>

</form>

</body>
</html>

Old Pedant · July 21st, 2008, 03:56 PM

Yes, this *IS* the wrong forum. See the word "Classic" there?? That means "ASP as it was BEFORE ASP.NET came around." In other words, *NOT* ASP.NET.

However...

However, this question would be essentially the same in either ASP or ASP.NET, so let me take a stab at it.

Code:

    ... as is ...

    strSearch1 = txtbx1.Text
    strSearch2 = txtbx2.Text
    strSearch3 = txtbx3.Text
    strSearch4 = txtbx4.Text

    strSQLQuery = "SELECT * FROM GS_LLPG WHERE 1=0 "
    If Len(Trim(strSearch1)) > 0 Then
        strSQLQuery = strSQLQuery & " OR POSTCODE LIKE '%" & Replace(strSearch1, "'", "''") & "%'  "
    End If
    If Len(Trim(strSearch2)) > 0 Then
        strSQLQuery = strSQLQuery & " OR POSTCODE LIKE '%" & Replace(strSearch2, "'", "''") & "%'  "
    End If
    If Len(Trim(strSearch3)) > 0 Then
        strSQLQuery = strSQLQuery & " OR POSTCODE LIKE '%" & Replace(strSearch3, "'", "''") & "%'  "
    End If
    If Len(Trim(strSearch4)) > 0 Then
        strSQLQuery = strSQLQuery & " OR POSTCODE LIKE '%" & Replace(strSearch4, "'", "''") & "%'  "
    End If

    ... etc. ...

Old Pedant · July 21st, 2008, 05:26 PM

By the by...are you SURE you really want to use LIKE here????

If somebody enters (for example) just
0
into the txtbx1 field, then you'd end up finding every postcode that has a zero *ANYWHERE* in the code! Similarly for any single character value. Or 2-character value. Etc.

If you simply want to allw the user to enter up to 4 *REAL* postcodes (but all 4 completely spelled out, all characters) then you don't want to use LIKE, at all.

Instead, you'd use something like this:

strSearch1 = Replace(txtbx1.Text,"'","''")
strSearch2 = Replace(txtbx2.Text,"'","''")
strSearch3 = Replace(txtbx3.Text,"'","''")
strSearch4 = Replace(txtbx4.Text,"'","''")

Dim codes As String = ""
If strSearch1 <> "" Then codes = codes & ",'" & strSearch1 & "'"
If strSearch2 <> "" Then codes = codes & ",'" & strSearch2 & "'"
If strSearch3 <> "" Then codes = codes & ",'" & strSearch3 & "'"
If strSearch4 <> "" Then codes = codes & ",'" & strSearch4 & "'"
If codes <> "" Then
strSQL = strSQL & " WHERE POSTCODE IN (" & MID(codes,2) & ")"
End If

Or something to that effect.