The way you are doing is not right.
Please use parameterized query.
Database
I have made use of the following table Hobbies with the schema as follows.
I have already inserted few records in the table
Note: You can download the database table SQL by clicking the download link below.
HTML
<b>Hobbies:</b>
<br />
<br />
<asp:ListBox ID="lstHobbies" runat="server" SelectionMode="Multiple">
</asp:ListBox>
<br />
<asp:Button ID="btnUpdate" runat="server" Text="Save" OnClick="Update" />
Namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Code
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT [HobbyId], [Description] FROM Hobbies"))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
lstHobbies.DataSource = cmd.ExecuteReader();
lstHobbies.DataTextField = "Description";
lstHobbies.DataValueField = "HobbyId";
lstHobbies.DataBind();
con.Close();
}
}
}
}
private void UpdateHobby(int hobbyId, bool isSelected)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "UPDATE Hobbies SET IsSelected = @IsSelected WHERE HobbyId = @HobbyId";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@HobbyId", hobbyId);
cmd.Parameters.AddWithValue("@IsSelected", isSelected);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
}
}
}
protected void Update(object sender, EventArgs e)
{
foreach (ListItem item in lstHobbies.Items)
{
this.UpdateHobby(int.Parse(item.Value), item.Selected);
}
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Records successfuly updated!');", true);
}
Screenshot
