exception with sql and c# code

Question

I am new to C# and sql.I have a function in c# which searches a database table for a particular record my function is

 public string returnstudentdata(string primarykey, string table, string regno, string column)
    {
        string temp = "";
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        SqlCommand newCmd = conn.CreateCommand();
        newCmd.Connection = conn;
        newCmd.CommandType = CommandType.Text;
        newCmd.CommandText = "SELECT" + column + "FROM" + table + "WHERE" + primarykey + "=" + regno + "";
        SqlDataReader dr = newCmd.ExecuteReader();
        while (dr.Read())
        {
            temp = dr[column].ToString();
        }
        dr.Close();
        conn.Close();
        return temp;
    }

this code executes properly but when it comes to SqlDataReader dr = newCmd.ExecuteReader(); it throws an exception stating:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near '='.

the calling statement is

 string regno = txtRegNo.Text;
 txtFName.Text = update.returnstudentdata("Regno","student",regno,"Fname");

What is the problem with my code.please help

There's tons of problems... (1) you should be using parameterized queries, as you're vulnerable to SQL injections, (2) if you choose to ignore this advice, you still have no spaces aroud any of your words, so your SQL query is one big run-on query like `SELECTcolumnFROMtableWHEREpk=regno`, and (3) you didn't wrap your `regno` string with apostrophes. So, in short, use parameterized queries. I should totally register **useparameterizedqueries.com**. — LittleBobbyTables - Au Revoir, Jan 09 '13 at 15:35
I tried `SELECTFnameFROMstudentWHERERegno=whatever` and it didn't work for me either! — Devin Burke, Jan 09 '13 at 15:36

score 3 · Accepted Answer · edited Feb 01 '13 at 15:19

3

At minimum you need spaces in your sql statement:

newCmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE " + primarykey + "=" + regno + "";

edited Feb 01 '13 at 15:19

wickedone

542
1
6
18

answered Jan 09 '13 at 15:34

sgeddes

62,311
6
61
83

thanks for the answer.I am new to sql.Wont commit this mistake again – Aravind Bharathy Jan 09 '13 at 15:38

score 2 · Answer 2 · edited May 23 '17 at 12:18

Put some Spaces around the SQL keywords within your quotation marks, your string does not result into valid SQL code:

newCmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE " + primarykey + "=" + regno + "";

Also, for security reasons, I recommend using parameterized queries instead of self-assembled strings. Your code is vulnerable to, for example, SQL injection attacks.

See this SO thread for more explanation: How do parameterized queries help against SQL injection?

score 1 · Answer 3 · answered Jan 09 '13 at 15:35

1

This line won't give valid SQL. No spaces.-

newCmd.CommandText = "SELECT" + column + "FROM" + table + "WHERE" + primarykey + "=" + regno + "";

answered Jan 09 '13 at 15:35

Nick Ryan

2,662
1
17
24

score 1 · Answer 4 · answered Jan 09 '13 at 15:36

1

Try this:

newCmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE " + primarykey + " = '" + regno + "'";

answered Jan 09 '13 at 15:36

Praveen

1,449
16
25

score 1 · Answer 5 · answered Jan 09 '13 at 15:36

Your CommandText, or Sql query, has the potential for a lot of errors.

The first is that you do not have spaces between the SQL-keywords, such as SELECT, and your variables (assuming you variables aren't padded with spaces):

newCmd.CommandText = "SELECT" + column + "FROM" + table + "WHERE" + primarykey + "=" + regno + "";

You should first add the spaces like this:

newCmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE " + primarykey + "=" + regno + "";

The second is that you're not wrapping your regno value with quotes. Now, this could validy be because the value is numeric; if that's the case, you can ignore the quotes and your query should execute fine (after adding spaces noted above). However, if it's a string, you'll want to wrap the value in quotes like this:

newCmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE " + primarykey + "=\"" + regno + "\"";

However, regardless whether or not the value is numeric or a string, I would actually recommend you use a prepared statement instead. You can accomplish this with:

newCmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE " + primarykey + "=@regNo";
newCmd.Parameters.AddWithValue("@regNo", regno);

The prepared statement will help alleviate the need for remembering to wrap the value in quotes (it will handle that for you), and will also add security to your query by helping to prevent SQL injection.

exception with sql and c# code

5 Answers5