Connecting to database

Question

I'm getting a run time error in my program when connecting to a SQL Server CE database.

Can anyone help me, and please don't write the whole code just a line of what needs to be changed to.

Here is my code:

string conString = Properties.Settings.Default.POSdatabaseConnectionString;

using (SqlCeConnection con = new SqlCeConnection(conString))
{
    con.Open();
    using (SqlCeCommand com = new SqlCeCommand("SELECT * FROM Customer where Customer ID ='" + this.useridtexbox.Text + "' and Name='" + this.nametexbox.Text + "'", con))
    {
        SqlCeDataReader reader = com.ExecuteReader();
        int count = 0;

        while (reader.Read())
        {
            count = count + 1;
        }

        if (count == 1)
        {
           MessageBox.Show("You have logged in succesfully");
           Homepage homepage = new Homepage();
           homepage.Show();
           homepage.LabelText = ("Welcome " + reader["name"].ToString());
        }
        else
        {
           MessageBox.Show("Username and password is Not correct ...Please try again");
           con.Close();
        }

Error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 39,Token in error = ID ]

When asking for help, don't tell us how to help you. – Sam Axe Mar 25 '14 at 12:10 — Sam Axe, Mar 25 '14 at 12:10
Also, you've left yourself wide open to sql injection. – Sam Axe Mar 25 '14 at 12:11 — Sam Axe, Mar 25 '14 at 12:11

score 2 · Answer 1 · answered Mar 25 '14 at 12:11

2

I think the problem with the space in Customer ID,Try this

SqlCeCommand com = new SqlCeCommand("SELECT * FROM Customer where CustomerID ='" +    this.useridtexbox.Text + "' and Name='" + this.nametexbox.Text + "'", con))

answered Mar 25 '14 at 12:11

NullReferenceException

1,641
15
22

or [Customer ID] if there truly is a space in that field. also- this code is wide open for sql injection attacks. you need to escape the user input: this.nametexbox.Text.Replace("'", "''") – Jeremy Danyow Mar 25 '14 at 12:13
@jdanyow Yes you are correct,but i answered for the exception what OP was getting – NullReferenceException Mar 25 '14 at 12:17
@NullReferenceException i get this error: The column name is not valid. [ Node name (if any) = ,Column name = CustomerID ] – user3439080 Mar 25 '14 at 12:17
@user3439080 follow the approach as answered by nvoigt,and check the Column name in the database. – NullReferenceException Mar 25 '14 at 12:21

score 1 · Accepted Answer · answered Mar 25 '14 at 12:14

In your command, do not use string concatenation. That will fail badly and leave you open to SQL injection attacks.

Image what happens if I enter the following text into this.nametexbox.Text:

Joe'; DROP DATABASE; --

You don't want have someone like little Bobby Tables as user.

Use sql parameters.

If you have tables or fields with spaces, you to have a word with your DBA. If you cannot change it, make sure you use the correct syntax:

WHERE [Customer ID] = '12345'

score 0 · Answer 3 · edited May 23 '17 at 12:06

Make sure you CustomerID column have space

Always use parameterized query to avoid SQL Injection

How does SQLParameter prevent SQL Injection

SqlCeCommand com = new SqlCeCommand = "SELECT * FROM Customer where CustomerID=@CustomerID and  
                                name=@name";
con.Parameters.AddWithValue("@CustomerID", valuesTextBox.Text);
con.Parameters.AddWithValue("@name", namwTextBox.Text);

Connecting to database

3 Answers3