Click here to Skip to main content
15,917,926 members
Please Sign up or sign in to vote.
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
Updated 17-Mar-17 9:17am
Comments
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?

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
 
Share this answer
 
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.
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900