Programming Forums > Application Development > Visual Basic

Abyss · Feb 18th, 2007, 9:55 AM

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!

Abyss · Feb 23rd, 2007, 3:24 PM

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?

(Toggle Plain Text)

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!

Arevos · Feb 23rd, 2007, 3:58 PM

Quote:

Originally Posted by Abyss

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"

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.

Abyss · Feb 23rd, 2007, 7:21 PM

Quote:

Originally Posted by Arevos

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.

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.

Arevos · Feb 24th, 2007, 7:16 AM

Quote:

Originally Posted by Abyss

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:

 vbnet Syntax (Toggle Plain Text) 
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()
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.

Feb 18th, 2007, 9:55 AM	#1
Abyss Newbie Join Date: Apr 2005 Posts: 18 Rep Power: 0	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!

Feb 23rd, 2007, 3:24 PM	#2
Abyss Newbie Join Date: Apr 2005 Posts: 18 Rep Power: 0	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? (Toggle Plain Text) 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 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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Compiling Maverik 6.2 (from C)	megamind5005	C	16	May 3rd, 2006 5:41 PM
libraries	matko	C	1	Jan 22nd, 2006 2:12 PM
Php Postgresql Class	Pizentios	Show Off Your Open Source Projects	15	Jun 28th, 2005 9:55 AM
Jackpot game	zorin	Visual Basic	3	Jun 10th, 2005 1:19 PM
airport Log program using 3D linked List : problem reading from file	gemini_shooter	C++	0	Mar 2nd, 2005 4:12 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic

Creating a Search function