How to filter data from a single table using 3 comboboxes as filters?

Question

0.00/5 (No votes)

See more:

Quote:
The conversion of the string "SELECT * FROM dv_principal where" to type 'Double' is not valid. '

What I have tried:

SELECT * FROM dv_principal where MOTIVE='" + cmbMotive.SelectedValue + "' AND CIV_ID='" + cmbCliente.SelectedValue + " AND DUE_DATE= '"+ dtpDueDate.SelectedText+"'"

Posted 9-Jun-17 5:25am

Scribling Doodle

Updated 9-Jun-17 5:49am

Add a Solution

2 solutions

Solution 2

Based on @F-ES Sitecore 's answer I found the problem regarding the double values.

Firstly, when using number's it's recommended to use "&" symbol instead of "+" symbols. Because we are talking about numbers and for the querys itself We need to define what type of date it is. Fixed the problem by replacing all the "+" for "&", making the query like this.

SELECT * FROM dv_principal where MOTIVE='" & cmbMotive.SelectedValue & "' AND CIV_ID=" & cmbCliente.SelectedValue & " AND DUE_DATE= '"+ dtpDueDate.SelectedText +"'"

Posted 9-Jun-17 5:49am

Scribling Doodle

Comments

Richard Deeming 9-Jun-17 12:11pm

None of which would have been a problem if you'd started with a properly parameterized query.

Scribling Doodle 9-Jun-17 12:15pm

That's the next step as I said in the comments. I start always this way so I can imagine my database while reading the code. Instead of reading tonnes of AddwitValues beneath it... It's simpler to read and more vulnerable yes. I will create the parameterized query now since It's all going great now.

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)

F-ES Sitecore · Accepted Answer · 2017-06-09T05:35:00

You are starting a single quote but not closing it

SELECT * FROM dv_principal where MOTIVE='" + cmbMotive.SelectedValue + "' AND CIV_ID='" + cmbCliente.SelectedValue + " AND DUE_DATE= '"+ dtpDueDate.SelectedText+"'"

Implement some basic debugging and look at the string you are creating

SELECT * FROM dv_principal where MOTIVE='Speeding' AND CIV_ID='15441151 AND DUE_DATE= '4th May 2018'

See the mistake? If a value is numeric you don't need the quotes at all

SELECT * FROM dv_principal where MOTIVE='" + cmbMotive.SelectedValue + "' AND CIV_ID=" + cmbCliente.SelectedValue + " AND DUE_DATE= '"+ dtpDueDate.SelectedText+"'"

You should look to use parameterised queries also as your code is open to SQL Injection attacks, and given the obviously sensitive nature of the data it's something you need to ensure is protected.

PS Note that if your response to this is "still doesn't work" that does not give anyone enough information to help you, your handling of dates looks suspect so that would be the next thing I'd look at.