CODEWITHSUNDEEP
c#sqlsql-server
Aravind Bharathy
Aravind Bharathy

Reputation: 1610

exception with sql and c# code

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

Upvotes: 1

Views: 619

Answers (5)

sgeddes
sgeddes

Reputation: 62831

At minimum you need spaces in your sql statement:

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

Upvotes: 3

newfurniturey
newfurniturey

Reputation: 38436

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.

Upvotes: 1

Praveen
Praveen

Reputation: 1449

Try this:

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

Upvotes: 1

Jens H
Jens H

Reputation: 4632

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?

Upvotes: 2

Nick Ryan
Nick Ryan

Reputation: 2670

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

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

Upvotes: 1

Related Questions