Inserimento dati con Stored Procedure in Asp.NET

E' stata una dura lotta tra me e chiccosimo (utente del forum aspitalia) contro un FormView e l'inserimento dati in un db Sql Server.

Il problema era che i campi FK di questa tabella finiscono con # e questo creava probemi con l'inserimento attraverso FormView.

Abbiamo provato con dei metodi per valorizzare quei text box ma senza risultato.

Alla fine ho risolto facendo tutto a mano.

Dentro la pagina ho creato questa tabella:

<asp:Table ID="insertForm" runat="server">
<asp:TableRow runat="server">
<asp:TableCell runat="server">Descrizione:
</asp:TableCell>
<asp:TableCell runat="server">
<asp:TextBox ID="descrizione" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow1" runat="server">
<asp:TableCell ID="TableCell1" runat="server">Gruppo#:
</asp:TableCell>
<asp:TableCell ID="TableCell2" runat="server">
<asp:TextBox ID="gruppo" runat="server">69</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow2" runat="server">
<asp:TableCell ID="TableCell3" runat="server">Tipo deploy#:
</asp:TableCell>
<asp:TableCell ID="TableCell4" runat="server">
<asp:TextBox ID="tipo_deploy" runat="server">3</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow3" runat="server">
<asp:TableCell ID="TableCell5" runat="server">Localita:
</asp:TableCell>
<asp:TableCell ID="TableCell6" runat="server">
<asp:TextBox ID="localita" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow4" runat="server">
<asp:TableCell ID="TableCell7" runat="server">IP:
</asp:TableCell>
<asp:TableCell ID="TableCell8" runat="server">
<asp:TextBox ID="ip" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow5" runat="server">
<asp:TableCell ID="TableCell9" runat="server">Riserva:
</asp:TableCell>
<asp:TableCell ID="TableCell10" runat="server">
<asp:TextBox ID="riserva" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow6" runat="server">
<asp:TableCell ID="TableCell11" runat="server">Tipo connessione#:
</asp:TableCell>
<asp:TableCell ID="TableCell12" runat="server">
<asp:TextBox ID="tipo_connessione" runat="server">3</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow7" runat="server">
<asp:TableCell ID="TableCell13" runat="server">TGU:
</asp:TableCell>
<asp:TableCell ID="TableCell14" runat="server">
<asp:TextBox ID="tgu" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow8" runat="server">
<asp:TableCell ID="TableCell15" runat="server">IP inizio range:
</asp:TableCell>
<asp:TableCell ID="TableCell16" runat="server">
<asp:TextBox ID="ip_inizio_range" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow9" runat="server">
<asp:TableCell ID="TableCell17" runat="server">IP fine range:
</asp:TableCell>
<asp:TableCell ID="TableCell18" runat="server">
<asp:TextBox ID="ip_fine_range" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow10" runat="server">
<asp:TableCell ID="TableCell19" runat="server">Subnet:
</asp:TableCell>
<asp:TableCell ID="TableCell20" runat="server">
<asp:TextBox ID="subnet" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow11" runat="server">
<asp:TableCell ID="TableCell21" runat="server">Gateway:
</asp:TableCell>
<asp:TableCell ID="TableCell22" runat="server">
<asp:TextBox ID="gateway" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow12" runat="server">
<asp:TableCell ID="TableCell23" runat="server">Traffico prepagato:
</asp:TableCell>
<asp:TableCell ID="TableCell24" runat="server">
<asp:TextBox ID="traffico_prepagato" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow13" runat="server">
<asp:TableCell ID="TableCell25" runat="server">Costo traffico eccesso:
</asp:TableCell>
<asp:TableCell ID="TableCell26" runat="server">
<asp:TextBox ID="costo_traffico_eccesso" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow14" runat="server">
<asp:TableCell ID="TableCell27" runat="server">Costo forfait:
</asp:TableCell>
<asp:TableCell ID="TableCell28" runat="server">
<asp:TextBox ID="costo_forfait" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow15" runat="server">
<asp:TableCell ID="TableCell29" runat="server">IP router:
</asp:TableCell>
<asp:TableCell ID="TableCell30" runat="server">
<asp:TextBox ID="ip_router" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow16" runat="server">
<asp:TableCell ID="TableCell31" runat="server">IP watchdog
</asp:TableCell>
<asp:TableCell ID="TableCell32" runat="server">
<asp:TextBox ID="ip_watchdog" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow17" runat="server">
<asp:TableCell ID="TableCell33" runat="server">IP ups
</asp:TableCell>
<asp:TableCell ID="TableCell34" runat="server">
<asp:TextBox ID="ip_ups" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow18" runat="server">
<asp:TableCell ID="TableCell35" runat="server">IP camera
</asp:TableCell>
<asp:TableCell ID="TableCell36" runat="server">
<asp:TextBox ID="ip_camera" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow19" runat="server">
<asp:TableCell ID="TableCell37" runat="server">Directory satellite:
</asp:TableCell>
<asp:TableCell ID="TableCell38" runat="server">
<asp:TextBox ID="directory_satellite" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow20" runat="server">
<asp:TableCell ID="TableCell39" runat="server">Directory monitor:
</asp:TableCell>
<asp:TableCell ID="TableCell40" runat="server">
<asp:TextBox ID="directory_monitor" runat="server">0</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow21" runat="server">
<asp:TableCell ID="TableCell41" runat="server">Tipologia monitor#:
</asp:TableCell>
<asp:TableCell ID="TableCell42" runat="server">
<asp:TextBox ID="tipologia_monitor" runat="server">2</asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow ID="TableRow22" runat="server">
<asp:TableCell ID="TableCell43" runat="server">Submit
</asp:TableCell>
<asp:TableCell ID="TableCell44" runat="server">
<asp:Button ID="Submit" runat="server" Text="Submit" OnClick="insertData" />
</asp:TableCell>
</asp:TableRow>
</asp:Table>

Poi nel code behind ho creato questo evento per l'inserimento:

public void insertData(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MYTELENEWS"].ConnectionString);
SqlCommand cmd = new SqlCommand("insert_sito", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@descrizione", System.Data.SqlDbType.VarChar).Value = descrizione.Text;
cmd.Parameters.Add("@gruppo#", System.Data.SqlDbType.Int).Value = gruppo.Text;
cmd.Parameters.Add("@tipo_deploy#", System.Data.SqlDbType.Int).Value = tipo_deploy.Text;
cmd.Parameters.Add("@localita", System.Data.SqlDbType.VarChar).Value = localita.Text;
cmd.Parameters.Add("@ip", System.Data.SqlDbType.VarChar).Value = ip.Text;
cmd.Parameters.Add("@riserva", System.Data.SqlDbType.VarChar).Value = riserva.Text;
cmd.Parameters.Add("@tipo_connessione#", System.Data.SqlDbType.Int).Value = tipo_connessione.Text;
cmd.Parameters.Add("@TGU", System.Data.SqlDbType.VarChar).Value = tgu.Text;
cmd.Parameters.Add("@IP_inizio_range", System.Data.SqlDbType.VarChar).Value = ip_inizio_range.Text;
cmd.Parameters.Add("@IP_fine_range", System.Data.SqlDbType.VarChar).Value = ip_fine_range.Text;
cmd.Parameters.Add("@subnet", System.Data.SqlDbType.VarChar).Value = subnet.Text;
cmd.Parameters.Add("@gateway", System.Data.SqlDbType.VarChar).Value = gateway.Text;
cmd.Parameters.Add("@traffico_prepagato", System.Data.SqlDbType.Float).Value = traffico_prepagato.Text;
cmd.Parameters.Add("@costo_traffico_eccesso", System.Data.SqlDbType.Money).Value = costo_traffico_eccesso.Text;
cmd.Parameters.Add("@costo_forfait", System.Data.SqlDbType.Money).Value = costo_forfait.Text;
cmd.Parameters.Add("@IP_router", System.Data.SqlDbType.VarChar).Value = ip_router.Text;
cmd.Parameters.Add("@IP_watchdog", System.Data.SqlDbType.VarChar).Value = ip_watchdog.Text;
cmd.Parameters.Add("@IP_UPS", System.Data.SqlDbType.VarChar).Value = ip_ups.Text;
cmd.Parameters.Add("@IP_camera", System.Data.SqlDbType.VarChar).Value = ip_camera.Text;
cmd.Parameters.Add("@directory_satellite", System.Data.SqlDbType.VarChar).Value = directory_satellite.Text;
cmd.Parameters.Add("@directory_monitor", System.Data.SqlDbType.VarChar).Value = directory_monitor.Text;
cmd.Parameters.Add("@tipologia_monitor", System.Data.SqlDbType.Int).Value = tipologia_monitor.Text;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("Execption adding account. " + ex.Message);
}
finally
{
conn.Close();
}
}

Creo la connessione.

Come comando gli do il nome della Stored Proedure.

Come CommandType gli dico di usare appunto una Stored Procedure.

Aggiungo tutte le voci ch mi servono (che corrispondo alle textbox), ricordandosi anche di impostare il tipo di dato.

Classico try/catch/finally per eseguire la query.

Provato più volte e ha funzionato.

Spero possa essere d'aiuto a qualcuno e ringrazio moltissimo chiccosimo!!