CODEWITHSUNDEEP
c#sqlstring
Idan
Idan

Reputation: 31

An error with an sql string (c#)

I have written the following script (in c#):

string     sqlCommand = "SELECT   * " +
                 "FROM     tblMatches " +
                 "WHERE matchPlayerNick =" + comboBoxPlayer.Text + " " +
                 "ORDER BY matchName ";

When I run my program, I get this: "data type mismatch in the criteria experssion". the datatype of matchPlayer is, of course, "text".

what's wrong with the script then?

thanks!

Upvotes: 0

Views: 107

Answers (2)

John Woo
John Woo

Reputation: 263723

string     sqlCommand = "SELECT   * " +
                 "FROM     tblMatches " +
                 "WHERE matchPlayerNick ='" + comboBoxPlayer.Text + "' " +
                 "ORDER BY matchName ";

but the query above is vulnerable with sql injection. It can be prevented if you parameterized the values using Command Object and Parameters.

Try this code snippet:

string content = comboBoxPlayer.Text;
string connStr = "connection string here";
string sqlCommand = @"SELECT   *
                      FROM     tblMatches 
                      WHERE matchPlayerNick = @content
                      ORDER BY matchName";
using (SqlConnection conn = new SqlConnection(connStr))
{
    using(SqlCommand comm = new SqlCommand())
    {
        comm.Connection = conn;
        comm.CommandText = sqlStatement;
        comm.CommandType = CommandType.Text;

        comm.Parameters.AddWithValue("@content", content);

        try
        {
            conn.Open();
            // other codes here
        }
        catch(SqlException e)
        {
            // do something with the exception
            // do not hide it
            // e.Message.ToString()
        }
    }
}

For proper coding

  • use using statement for propr object disposal
  • use try-catch block to properly handle objects

Upvotes: 4

Serge
Serge

Reputation: 6692

You've forgotten the quotes. Using parameterized queries, it's a good practice.

Upvotes: 4

Related Questions