incorrect syntax near the keyword 'select'

0
votes

I m getting an error when i try to use this code in visual studio
can some one please help me out 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace mywebsite
{
    public partial class myregistration : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(IsPostBack)
            {
                SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Registration1ConnectionString"].ConnectionString);
                Conn.Open();
                string checkuser = " Select count(*) from [userdata] where UserName='" + TextBoxuname.Text + "' ";
                SqlCommand com = new SqlCommand(checkuser, Conn);
                int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
                if (temp == 1)
                {
                    Response.Write(" User Already Exists ");
                }

                Conn.Close();



            }
        }



        protected void Button1_Click1(object sender, EventArgs e)
        {
            try
            {
                SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Registration1ConnectionString"].ConnectionString);
                Conn.Open();
                string insertQuery = "insert into userdata (UserName,Password,Email,select country) values (@Uname ,@Password ,@email ,@Country)";
                SqlCommand com = new SqlCommand(insertQuery, Conn);
                com.Parameters.AddWithValue("@Uname", TextBoxuname.Text);
                com.Parameters.AddWithValue("@Password", TextBoxpass.Text);
                com.Parameters.AddWithValue("@email", TextBoxemail.Text);
                com.Parameters.AddWithValue("@Country", DropDownListselectcountry.SelectedItem.ToString());

                com.ExecuteNonQuery();
                Response.Redirect("manager.aspx");
                Response.Write("Resgistration is Successfull");

                Conn.Close();
            }
            catch(Exception ex)
            {
                Response.Write("Error:" + ex.ToString());
            }

                    }
    }
}

The error:

Error:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'select'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at mywebsite.myregistration.Button1_Click1(Object sender, EventArgs e) in enter code hereC:\Users\Rahul\Documents\Visual Studio 2008\Projects\Project1\mywebsite\mywebsite\myregistration.aspx.cs:line 50

it showing error at line 50 i.e. com.ExecuteNonQuery(); if i m removing it from the code the records are not inserting into db so please suggest me what must be done to the code.

4
c#
You should be using parameterized queries throughout your application (as you do in the second query), not constructing your query by concatting strings. – Servy
WHy are you selecting country in the second statement? – eddie_cat
It's so easy to paramerterize your queries in c#, don't do this string concatenation crap (you're actually doing it in the second query) – Kevin DiTraglia

4 Answers

4
votes
string insertQuery = "insert into userdata (UserName,Password,Email,select country) values (@Uname ,@Password ,@email ,@Country)";

I think you need to remove the word "select"

2
votes

Try this:

string checkuser = "Select count(*) from [userdata] where UserName='" + TextBoxuname.Text + "' ";

ie, remove the space before select

Also try this:

string insertQuery = "insert into userdata (UserName,Password,Email,select country) values (@Uname ,@Password ,@email ,@Country)";

On a side note:

Dont use string concatenation instead try with paramerterize query

1
votes

It is because select is a keyword and you have a space in your column name. Write this query instead:

string insertQuery = "insert into userdata (UserName,Password,Email, [select country]) values (@Uname ,@Password ,@email ,@Country)";
0
votes

I wouldn't use inline SQL like this. Bad practice and open to SQL injection. See SQL Injection

You want to use Parameterized Query

Please use something like this

using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Registration1ConnectionString"].ConnectionString))
{
    Conn.Open();
    using (SqlCommand cmd = Conn.CreateCommand())
    {
        cmd.CommandText = "select count(*) from [userdata] where UserName=@username";
        cmd.Parameters.AddWithValue(TextBoxuname.Text);
        int temp = (int)cmd.ExecuteScalar();
        if (temp.Equals(1))
        {
            Response.Write(" User Already Exists ");
        }
    }
}