CODEWITHSUNDEEP
c#asp.netsqlvisual-studio
gymcode
gymcode

Reputation: 4623

SQL Select statement from a webpage control and check relevant check boxes according to result

I have a sql select statement in my VS2005 C# server-side coding for a web application and I am meeting some errors.

Below is a screenshot of the controls in the webpage: enter image description here

Data Source SqlDataSource1 : Query:SELECT [Name] FROM [Users].

Dropdownlist UserNameList : Lists all userName retrieved from SqlDataSource1.

Checkboxes AdminCb and UserCb : Automatically checks if the userType of the userName is as.

Button loadUser : Gets the user type and checks the check boxes accordingly.

Below is my code for my loadUser button

    SqlConnection conn = new SqlConnection("Data Source=DATASOURCE");
    string sql = string.Format("SELECT [User Type] FROM [Users] where Name like " + UserNameList.Text);
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

    if(sql== "Administrator"){
        AdminCb.Checked=true;
    }
    if(sql== "User"){
        UserCb.Checked=true;
    }

Currently I am stuck with the error (Wong is the 2nd word of the user's name):

enter image description here

Questions:

1) How can change my Sql query so that it can take in more than 1word?

2) And will I be able to check boxes once I am able to run my sql query?

Thank You.

Upvotes: 1

Views: 989

Answers (3)

KV Prajapati
KV Prajapati

Reputation: 94625

You must have to use Parameter and call the ExecuteScalar() method instead of ExecuteNonQuery().

 string sql = "SELECT [User Type] FROM [Users] where [Name]=@Name";
 SqlCommand cmd = new SqlCommand(sql, conn);
 cmd.Parameters.Add("@Name",SqlDbType.VarChar,50).Value=UserNameList.Text;
 conn.Open();
 Object result=cmd.ExecuteScalar();
 conn.Close();

 if(result!=null)
  {
     string usertype=result.ToString();
     if(usertype=="Administrator")
       {}
     else
       {}
  }

In case, if result returned from the database contains more then one rows then use ExecuteReader() method.

 string sql = "SELECT [User Type] FROM [Users] where [Name] like @Name";
 SqlCommand cmd = new SqlCommand(sql, conn);
 cmd.Parameters.Add("@Name",SqlDbType.VarChar,50).Value="%" + UserNameList.Text + "%";
 conn.Open();
 SqlDataReader result=cmd.ExecuteReader();
 while(result.Read())
 {
    ///
   }
 result.Close();
 conn.Close();

Upvotes: 1

thekaveman
thekaveman

Reputation: 4399

Since the error is indicating there is something wrong with the Name, I would take a closer look at this line:

string sql = string.Format("SELECT [User Type] FROM [Users] where Name like " + UserNameList.Text);

If you are using string.Format, you might as well use it

string sql = string.Format("SELECT [User Type] FROM [USERS] where Name like {0}", UserNameList.Text);

Upvotes: 0

Brian Mains
Brian Mains

Reputation: 50728

Since you are concatenating the SQL string, if the input itself has a single quote in it, it thinks this is the end of the input, and the continuing input is SQL statements, which is why you may be getting that error.

Switch to using a parameter, or make sure any single quotes are escaped as a pair of single quotes, like:

string sql = string.Format("SELECT [User Type] FROM [Users] where Name like " + UserNameList.Text.Replace("'", "''"));

Upvotes: 0

Related Questions