MS access query between two columns

Question

0.00/5 (No votes)

See more:

Hi!
I have an access database with forms. I created a query that would filter records between range. The criteria will be coming from a textbox.

What I have tried:

below is my code but it is giving me a no value record. how would i make this query read the input from the textbox?

VB

"SELECT TrainingType, Condensed FROM tblOTrainings WHERE LowerBound = " & .txtCJobLevel.Text & " BETWEEN LowerBound AND UpperBound"

Posted 17-Mar-17 6:52am

Surviving Panda

Updated 17-Mar-17 9:17am

Add a Solution

Comments

Richard Deeming 17-Mar-17 13:08pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Richard Deeming 17-Mar-17 13:10pm

Also, you have a syntax error in your query:

WHERE LowerBound = ? BETWEEN LowerBound AND UpperBound

Are you looking to find records where the textbox value is between two columns?

WHERE ? BETWEEN LowerBound AND UpperBound

Surviving Panda 17-Mar-17 13:18pm

Yes, it should look like this "WHERE ? BETWEEN LowerBound AND UpperBound" where the ? is the input from the textbox. How will i parameterized the criteria using a BETWEEN query?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-03-17T07:23:00

Solution 1

Use a parameterized query, and fix the syntax error:

VB.NET

Using connection As New OleDbConnection("...")
    Using command As New OleDbCommand("SELECT TrainingType, Condensed FROM tblOTrainings WHERE @CJobLevel BETWEEN LowerBound AND UpperBound", connection)
        command.Parameters.AddWithValue("@CJobLevel", txtCJobLevel.Text)
        
        Dim table As New DataTable()
        Dim da As New OleDbDataAdapter(command)
        da.Fill(table)
        ...
    End Using
End Using

Posted 17-Mar-17 7:23am

Richard Deeming

Comments

Surviving Panda 17-Mar-17 13:41pm

Hi, below is my code using the parameters

cn.Open()
cmd = cn.CreateCommand
cmd.CommandText = "SELECT TrainingType, Condensed FROM tblOTrainings WHERE @CJobLevel BETWEEN LowerBound AND UpperBound"
cmd.Parameters.AddWithValue("@CJobLevel", .txtCJobLevel.Text)
cmd.ExecuteNonQuery()

Dim table As New DataTable
Dim da As New OleDbDataAdapter(cmd)
da.Fill(table)

For Each dr As DataRow In dt.Rows
.dgCTraining.Rows.Add()
With frmPersonalTraining.dgCTraining.Rows(frmPersonalTraining.dgCTraining.Rows.Count - 1)
.Cells("cTraining").Value = dr("TrainingType")
.Cells("cCourse").Value = dr("Condensed")
End With
Next
cn.Close()

Am I right that after the da.fill(table) line i inserted the code that would display the result in a datagridview? It still has a no value error.

Sorry if I ask too many questions.

Richard Deeming 17-Mar-17 13:47pm

The simplest way is probably to use a BindingSource to bind the data to the grid:
How to: Bind Data to the Windows Forms DataGridView Control[^]

You'll also want to remove that cms.ExecuteNonQuery() line, which doesn't do anything.

Dave Kreskowiak 17-Mar-17 14:05pm

This may seem stupid but did you replace "LowerBound" and "UpperBound" with ACTUAL VALUES or did you leave that text in that query with nothing to define what "LowerBound" and "UpperBound" represent?

Richard Deeming 17-Mar-17 14:07pm

I was assuming they were column names in the table. :)

Dave Kreskowiak 17-Mar-17 14:11pm

Originally, so did I, but then this started reeking of "noob". I started thinking this is a copy'n'paste job of some example somewhere.

Surviving Panda 17-Mar-17 14:13pm

Yes, LowerBound and UpperBound are column names on my table.

Surviving Panda 17-Mar-17 14:12pm

Hi, How would I define the values for that? When I created the query in Access it gives the expected result maybe that's why I didn't realized that I should also define their values.

Dave Kreskowiak 17-Mar-17 14:24pm

If LowerBound and UpperBound are actual columns names in the same table the query will use the values in the same row as the value being evaluated.

Surviving Panda 17-Mar-17 14:00pm

Thanks Richard for answering my questions. I don't still have a lot of background in using database. I hope I can figure out this. Thank you so much!

Surviving Panda · Accepted Answer · 2017-03-17T09:17:00

Hi Richard and Dave, just want to update you that my code is working now. :) Thanks for your replies!

VB

cn.Open()
 cmd = cn.CreateCommand
 cmd.CommandText = "SELECT Course, Condensed FROM tblOTrainings WHERE @CJobLevel BETWEEN LowerBound AND UpperBound"
 cmd.Parameters.AddWithValue("@CJobLevel", .txtOJobLevel.Text)

 Dim table As New DataTable("tblOTrainings")
 Dim da As New OleDbDataAdapter(cmd)
 da.Fill(table)
 .dgOTraining.DataSource = table