Ottengo un errore di chiave duplicata inserendo il ciclo! In pratica intercetta l'eccezione SqlException! Ecco il codice, modificato seguendo il tuo consiglio:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Specialized;
namespace MyProject.Registration
{
public partial class NewActivityAccount : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//Start Check Profile Status
string connString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Locale WHERE UserId=@ActiveUserId AND ActivityProfileComplete=@ProfileStatus", conn))
{
string Status = "1";
string UserLogged = Session["ActiveUserId"].ToString();
cmd.Parameters.AddWithValue("@ActiveUserId", UserLogged);
cmd.Parameters.AddWithValue("@ProfileStatus", Status);
conn.Open();
SqlDataReader risultato = cmd.ExecuteReader();
while(risultato.Read())
{
Response.Write("---Risulta già un profilo, desideri modificarlo?---");
}
}
}
}
catch
{
}
// End Profile Check Status
if (Session["ActiveUserID"] != null)
{
lblUSerID.Text = Session["ActiveUserID"].ToString();
}
else
{
Response.Redirect("../Login.aspx");
}
}
protected void a_Submit_Click(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
string a_UserIP = Request.ServerVariables["REMOTE_ADDR"];
string userLoggedID = Session["ActiveUserID"].ToString();
string a_InsertDate = DateTime.Now.ToShortDateString();
string ClosingTime = a_Orario_Apertura.Text.ToString() + "-" + a_Orario_Chiusura.Text.ToString();
string a_ProfileComplete = "1";
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Locale (UserId, RefererName, RefererSurname, RefererMail, RefererPhone, RefererMobilePhone, ActivityName, TipoLocale, Regione, Provincia, Paese, Cap, Orario, Chiusura, Website, Phone, SmallDescription, Description, UserIP, InsertDate, FacebookPage, ActivityProfileComplete) VALUES(@UserID, @RefererName, @RefererSurname, @RefererMail, @RefererPhone, @RefererMobilePhone, @ActivityName, @ActivityType, @Regione, @Provincia, @Paese, @Cap, @Orario, @Chiusura, @Website, @Phone, @SmallDescription, @Description, @UserIP, @InsertDate, @FacebookPage, @ActivityProfileComplete)", conn))
{
foreach (ListItem item in a_ActivityType.Items)
{
string Columns = string.Empty;
string Value = string.Empty;
if(item.Selected)
{
Columns += "," + item.Text;
Value += "," + item.Text;
cmd.Parameters.AddWithValue("@TipoLocale" + item.Text, item.Value);
}
}
cmd.Parameters.AddWithValue("@UserID", userLoggedID.ToString());
cmd.Parameters.AddWithValue("@RefererName", r_Name.Text.ToString());
cmd.Parameters.AddWithValue("@RefererSurname", r_Surname.Text.ToString());
cmd.Parameters.AddWithValue("@RefererMail", r_Mail.Text.ToString());
cmd.Parameters.AddWithValue("@RefererPhone", r_Phone.Text.ToString());
cmd.Parameters.AddWithValue("@RefererMobilePhone", r_MobilePhone.Text.ToString());
cmd.Parameters.AddWithValue("@ActivityName", a_ActivityName.Text.ToString());
cmd.Parameters.AddWithValue("@ActivityType", Session["TipoLocale"]);
cmd.Parameters.AddWithValue("@Regione", a_Regione.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@Provincia", a_Provincia.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Paese", a_Paese.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Cap", a_Cap.Text.ToString());
cmd.Parameters.AddWithValue("@Orario", ClosingTime.ToString());
cmd.Parameters.AddWithValue("@Chiusura", a_Chiusura.Text.ToString());
cmd.Parameters.AddWithValue("@Website", a_Website.Text.ToString());
cmd.Parameters.AddWithValue("@Phone", a_Telefono.Text.ToString());
cmd.Parameters.AddWithValue("@SmallDescription", a_SmallDescription.Text.ToString());
cmd.Parameters.AddWithValue("@Description", a_Description.Text.ToString());
cmd.Parameters.AddWithValue("@UserIP", a_UserIP.ToString());
cmd.Parameters.AddWithValue("@InsertDate", a_InsertDate);
cmd.Parameters.AddWithValue("@FacebookPage", a_FbPage.Text.ToString());
cmd.Parameters.AddWithValue("@ActivityProfileComplete", a_ProfileComplete);
conn.Open();
int queryStatus = cmd.ExecuteNonQuery();
if (queryStatus != 0)
{
Response.Write("Registrazione correttamente effettuata!");
}
else
Response.Write("Problemi nella fase di registrazione.");
}
}
}
catch (SqlException KeyAlreadyExist)
{
Response.Write("Chiave duplicata!");
}
}
}
protected void a_Regione_SelectedIndexChanged(object sender, EventArgs e)
{
a_Provincia.Enabled = true;
string connStringRegione = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
using (SqlConnection connRegione = new SqlConnection(connStringRegione))
{
using (SqlCommand cmdRegione = new SqlCommand("SELECT * FROM province WHERE idRegione=@IdRegione", connRegione))
{
cmdRegione.Parameters.AddWithValue("@IdRegione", a_Regione.SelectedValue.ToString());
connRegione.Open();
a_Provincia.DataSource = cmdRegione.ExecuteReader();
a_Provincia.DataTextField = "nomeProvincia";
a_Provincia.DataValueField = "siglaProvincia";
a_Provincia.DataBind();
a_Provincia.Items.Insert(0, new ListItem("--SELEZIONA--", "0"));
}
}
}
protected void a_Provincia_SelectedIndexChanged(object sender, EventArgs e)
{
a_Paese.Enabled = true;
string connStringProvincia = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
using (SqlConnection connProvincia = new SqlConnection(connStringProvincia))
{
using (SqlCommand cmdProvincia = new SqlCommand("SELECT * FROM comuni WHERE Provincia=@idProvincia", connProvincia))
{
cmdProvincia.Parameters.AddWithValue("@idProvincia", a_Provincia.SelectedValue.ToString());
connProvincia.Open();
Response.Write(a_Provincia.SelectedValue.ToString());
Response.Write(a_Paese.SelectedValue.ToString());
a_Paese.DataSource = cmdProvincia.ExecuteReader();
a_Paese.DataTextField = "Comune";
a_Paese.DataValueField = "Comune";
a_Paese.DataBind();
a_Paese.Items.Insert(0, new ListItem("--SELEZIONA--", "0"));
}
}
}
protected void a_Paese_SelectedIndexChanged(object sender, EventArgs e)
{
string connStringPaese = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
using (SqlConnection connPaese = new SqlConnection(connStringPaese))
{
using (SqlCommand cmdPaese = new SqlCommand("SELECT * FROM comuni WHERE comune=@PaeseName", connPaese))
{
cmdPaese.Parameters.AddWithValue("@PaeseName", a_Paese.SelectedValue.ToString());
connPaese.Open();
}
}
}
}
}