Programming Forums - Creating a Search function

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Visual Basic (http://www.programmingforums.org/forum18.html)

- - Creating a Search function (http://www.programmingforums.org/showthread.php?t=12609)

Creating a Search function

Hey folks,

I'm new to VB but my job requires it from time to time so I'm picking it up in bits and pieces. At the minute, I'm working on a database in MSAccess that is storing a large number of records in no particular order. I am trying to add a search function in a seperate form, which will retrieve certain records based on 9 fields which are entered by the user. The primary key is pretty complex hence the 9 fields.

Can you recommend what is the best way to go about creating something like this?

We have a few books on VB and MSAccess in the office but unfortunately no Internet access for security reasons. If I even knew where to start looking in the books that would be some progress...

Thanks for the help!

Got to work on this a bit more during the week. I can create the search successfully using input boxes. When I try to do it using text fields I get the error: "You entered an expression that has an invalid reference to the property bookmark"

Can anyone spot an error with this code?



Private Sub cmdSearch_Click()





Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sSQL As String



Set cn = CurrentProject.Connection

  

sSQL = "select * from CorrespRec where "

    sSQL = sSQL & " Month = " & Month.Value

    sSQL = sSQL & " and Year = " & Year.Value

    sSQL = sSQL & " and Centre = " & Centre.Value

    sSQL = sSQL & " and [Session] = " & Session.Value

    sSQL = sSQL & " and DS = " & DS.Value

    sSQL = sSQL & " and Bar = " & Bar.Value

    sSQL = sSQL & " and DOB = #" & Mid(DOB, 3, 2) & "/" & Left(DOB, 2) & "/" &

Right(DOB, 4) & "#"

    sSQL = sSQL & " and CHI = " & CHI.Value

    sSQL = sSQL & " and DateComm = #" & Mid(DateComm, 3, 2) & "/" &

Left(DateComm, 2) & "/" & Right(DateComm, 4) & "#"

    Set rs = cn.Execute(sSQL)

          

      

    DoCmd.OpenForm "CorrespRecTabbed"

    [Forms]![CorrespRecTabbed].RecordSource = strSQL

    

    DoCmd.Close acForm, "CorrespRecSearch1"



    

End Sub

I'd appreciate the help!

Quote:

Originally Posted by Abyss (Post 124323)

The TextBox class has the property Text, rather than Value, to denote the input.

As an aside, the way you've built up your SQL string would give a malicious user direct access to the database via an SQL injection attack. If this is just a local database that the user has access to anyway, then there probably isn't any problem. However, it's something of a bad habit to get into.

Quote:

Originally Posted by Arevos (Post 124326)

Just out of interest, what would be the correct way to build the SQL string? This database is under minimal risk to be targetted by a malicious user though it would be good to know for the future.

Thanks for the textbox information also, I'll change it on Monday.

Quote:

Originally Posted by Abyss (Post 124332)

Just out of interest, what would be the correct way to build the SQL string? This database is under minimal risk to be targetted by a malicious user though it would be good to know for the future.

I haven't worked in VB.NET much, or really at all, but I suspect it would be something like this:

Dim command As ADODB.Command
Dim usernameParam As ADODB.Parameter
 
Set command = New ADODB.Command
 
command.CommandText = "SELECT * FROM users WHERE username = @username"
command.ActiveConnection = CurrentProject.Connection
 
Set usernameParam = New ADODB.Parameter
 
usernameParam.ParameterName = "username"
usernameParam.Type = adVarChar
usernameParam.Value = "Bob"
 
command.Parameters.Append usernameParam
 
Set rs = command.Execute()

Long winded, I know. Maybe there's a more concise way.