How do I get a value from a textbox into a sql query?

Question

0.00/5 (No votes)

See more:

Hi.

I have a question about SQL statement.
I have a MonthCalendar where I select a date. The date then shows up in a textbox.
On the form I also have a datagridview.

I have tried alot of diffrent converters.

C#

DateTime condition = Convert.ToDateTime(textBox1.Text);
            sda = new SqlDataAdapter("SELECT * FROM tbl_Tidrapport WHERE Datum LIKE "+ condition +"", cn);
            dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;

Always get error.

Please help....
How can I get the date in the SQL query?

Posted 27-Apr-15 23:36pm

thaagaard elofsson

Updated 27-Apr-15 23:44pm

phil.o

v2

Add a Solution

3 solutions

Solution 3

First of all - Do not insert into your query directly. Instead use SqlParameters. This helps avoid SQL Injection dangers.

Your query has a second issue: the "condition" would be used as a field name as it has no single quotes within the query. Parameters take care of that too.

Try this:

C#

DateTime condition = Convert.ToDateTime(textBox1.Text);
dt = new DataTable();
using (SqlCommand cmd = cn.CreateCommand())
{
    cmd.CommandText = "SELECT * FROM tbl_Tidrapport WHERE Datum LIKE @condition";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(new SqlParameter("@condition", SqlDbType.DateTime) {Value = condition});
    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
    {
        cn.Open();
        sda.Fill(dt);
        cn.Close();
    }
}

Also, using "Like" with a date type doesn't really make any sense. I have no idea what you are trying to achieve with that, but I have left it in my solution.

If you want help with the "Like" issue then let me know in a comment

Good luck :)

Posted 28-Apr-15 0:00am

Andy Lanng

Solution 1

Syntax error for Like search
refer this SQL Like query [^]

do something like this

C#

string condition = "";
        var   sda = new SqlDataAdapter("SELECT * FROM tbl_Tidrapport WHERE Datum LIKE '%" + condition + "%'", cn);

Note: Be aware of SQL Injection [^]
SQL Injection Attacks[^]

Posted 27-Apr-15 23:53pm

Karthik_Mahalingam

Updated 28-Apr-15 1:37am

v2

Comments

Andy Lanng 28-Apr-15 6:05am

Try to avoid answers that do not address SQL Injection. The Date issue appears to be a whole problem in itself.
Technically correct though. You did answer his question as asked ;)

Karthik_Mahalingam 28-Apr-15 6:17am

I agree,
do you think, if i bring the topic SQL Injection, he will be aware of that ??

Andy Lanng 28-Apr-15 6:26am

The point is that he is most likely unaware of it, so we should make him aware, IMHO anyway ^_^

Karthik_Mahalingam 28-Apr-15 7:38am

updated the solution :)

Andy Lanng 28-Apr-15 7:39am

upvoted :D

Karthik_Mahalingam 28-Apr-15 7:44am

Thank u :)

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)

OriginalGriff · Accepted Answer · 2015-04-27T23:55:00

Solution 2

LIKE won't work, it's for strings: and the chances are that it doesn't match because the Datum value doesn't exactly match to the microsecond - which is what an equality check will expect.
So try this:

C#

DateTime condition = Convert.ToDateTime(textBox1.Text).Date;
sda = new SqlDataAdapter("SELECT * FROM tbl_Tidrapport WHERE CONVERT(date, Datum) = @DT", cn);
sda.SelectCommand.Properties.AddWithValue("@DT", condition);
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;

Posted 27-Apr-15 23:55pm

OriginalGriff

Comments

Andy Lanng 28-Apr-15 6:03am

I like this solution because it uses parameters (i forgot how to use parameters without creating a command ^_^) AND it answers the 'Like' issue I mentioned - 5*

thaagaard elofsson 28-Apr-15 6:25am

Thank you so much. I got it to work.
sda.SelectCommand.Parameters.AddWithValue("@DT", condition);

OriginalGriff 28-Apr-15 6:32am

You're welcome!