Stored procedure con doppio insert

lunedì 06 agosto 2007 - 22.22

_J_ Profilo | Senior Member

ciao ragazzi.
sono _J_ e vi scrivo perchè ho un problema con una stored che effettua due insert. Premetto che uso vs2005 con sqlserver2005 e c#
ho una tabella 'cittadino' che ha una primary key 'cod_persona' (di tipo int ma non identity), una tabella 'nucleo_familiare_e_convivenze' che ha pk 'cod_nucleo_familiare' (di tipo int e di tipo identity) e una tabella'ruolo_in_nf_o_conviv' che le collega entrambe mediante opportune foreignkey e che quindi genera la relazione molti a molti.

in precedenza ho creato una stored che mi crea un nuovo record per 'cittadino', adesso quello che voglio fare è creare una nuova famiglia aggiungendo 'a catena' dei record nelle tabelle 'nucleo_familiare' e 'ruolo_in_nf' (voglio gestire la situazione di gente che vive sola ma che a tutti gli effetti costituisce famiglia, formata da un solo elemento) in base al nuovo codice persona inserito.

la stored che ho scritto è questa:

ALTER PROCEDURE dbo.nuova_fam_da_nuovo_cittadino //creo nuova famiglia
(
//non ho messo il parametro per il cod_nucleo_fam perchè è identity
@data_creaz datetime, //uso questo param sia per nucleo_fam.data_creaz che per ruolo_in_nf.data_inserim_in_nf
@tipo int,
@data_sciss datetime,

@ultimo_ind int,

@cod_p int,
@cod_parent int,
@data_rimoz datetime,
@cond varchar(1)
)

AS
INSERT INTO nucleo_fam_e_convivenze //prima creo la famiglia
(
data_creaz, //non faccio insert sulla pk in quanto identity
tipo_nucleo,
data_scissione
)
VALUES (@data_creaz,@tipo,@data_sciss)


set @ultimo_indice=scope_identity() /*per avere l'ultimo indice dalla tabella di prima, per immetterlo nella chiave primaria della tabella 'ruolo_in_nf_o_conviv' qui sotto

insert ruolo_in_nf_o_conviv //e poi ci inserisco il cittadino appena creato
(
cod_persona, //lo prenderò da una textbox
cod_nucleo_familiare , //uso @ultimo_ind per abbinare al cittadino appena creato la famiglia nuova che si sta creando
cod_parentela_con_is_o_cc,
data_inserim_in_nf_conv,
data_rimoz_da_nf_conv,
condizione_in_nf_conv
)
values (@cod_p,@ultimo_indice, @cod_parent, @data_creaz,@data_rimoz,@cond)
return


ma a runtime mi dice 'formato della stringa di input non corretto', e penso che la colpa sia di @ultimo_indice (quello dello scope_identity) che tra i parametri NON dichiaro (sinceramente non saprei da dove prenderlo visto che con scope_identity dovrebbe farlo lui), infatti nel file c# i parametri sono:


SqlDataAdapter adatt = new SqlDataAdapter(tutti, oConn);
adatt.SelectCommand.CommandType = CommandType.StoredProcedure;
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cod_nf", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@data_creaz", SqlDbType.DateTime)); //usato per 2 campi
adatt.SelectCommand.Parameters.Add(new SqlParameter("@tipo", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@data_sciss", SqlDbType.DateTime));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cod_p", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cod_parent", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@data_rimoz", SqlDbType.DateTime));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cond", SqlDbType.VarChar, 1));

adatt.SelectCommand.Parameters["@cod_nf"].Value = Server.HtmlEncode(TextBox35.Text);
adatt.SelectCommand.Parameters["@data_creaz"].Value = DateTime.Now;
adatt.SelectCommand.Parameters["@tipo"].Value = Server.HtmlEncode(Label5.Text);
adatt.SelectCommand.Parameters["@data_sciss"].Value = DBNull.Value;
adatt.SelectCommand.Parameters["@cod_p"].Value = Server.HtmlEncode(TextBox1.Text);
adatt.SelectCommand.Parameters["@cod_parent"].Value = "IS";
adatt.SelectCommand.Parameters["@data_rimoz"].Value = DBNull.Value;
adatt.SelectCommand.Parameters["@cond"].Value = Server.HtmlEncode(Label4.Text);

adatt.SelectCommand.ExecuteNonQuery();
oConn.Close();

come posso ottenere il parametro @ultimo_indice per poi poterlo immettere nella tabella delle relazioni così da ottenere quello che voglio?
accetto suggerimenti e grazie in anticipo a chi mi risponderà!
_J_

"Chi fa domande rischia di sembrare stupido,
chi non ne fa rischia di restare stupido..."

"Conosco me stesso? No, non parlo con gli estranei..."

lbenaglia Profilo | Guru

>ma a runtime mi dice 'formato della stringa di input non corretto',

Ciao _J_,

puoi postare i comandi DDL delle 3 tabelle (CREATE TABLE), alcune righe di prova (INSERT INTO) ed un esempio di chiamata della stored procedure che genera questo errore (EXEC dbo.nuova_fam_da_nuovo_cittadino...)?

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

alx_81 Profilo | Guru

>ciao ragazzi.
Ciao, scusate se intervengo, ma abbiamo già parlato di questo problema con _J_

>ho una tabella 'cittadino' che ha una primary key 'cod_persona'
>(di tipo int ma non identity), una tabella 'nucleo_familiare_e_convivenze'
>che ha pk 'cod_nucleo_familiare' (di tipo int e di tipo identity)
>e una tabella'ruolo_in_nf_o_conviv' che le collega entrambe mediante
>opportune foreignkey e che quindi genera la relazione molti a
>molti.
>in precedenza ho creato una stored che mi crea un nuovo record
>per 'cittadino', adesso quello che voglio fare è creare una nuova
>famiglia aggiungendo 'a catena' dei record nelle tabelle 'nucleo_familiare'
>e 'ruolo_in_nf' (voglio gestire la situazione di gente che vive
>sola ma che a tutti gli effetti costituisce famiglia, formata
>da un solo elemento) in base al nuovo codice persona inserito.
>come posso ottenere il parametro @ultimo_indice per poi poterlo
>immettere nella tabella delle relazioni così da ottenere quello
>che voglio?
Innanzitutto, il problema dell'errore "formato della stringa di input non corretto" è riferito alla gestione dei parametri lato C#. Come nell'altro post in cui abbiamo discusso proprio su questo tipo di messaggio, l'errore è probabilmente dovuto al mancato controllo dei valori null o alla chiamata di una funzione (come potrebbe essere la HtmlEncode) con parametri non corretti. Basta passare un valore non stringa (o non riconducibile a stringa) ad una funzione che accetta solo quel tipo di parametro per ottenere sempre quell'errore.
Inoltre, noterai che sotto il messaggio ti viene indicata proprio la riga in cui si verifica l'eccezione. DEBUG!
In secondo luogo, a prescindere dalle strutture (che conosco già dai precedenti post ), dichiari un parametro in più rispetto a quelli che si aspetta la stored procedure. Come mai crei il parametro @cod_nf se poi nella sp nemmeno lo dichiari? Se nella tabella il CAMPO è identity, non significa che lo devi dichiarare ma non passare. Non lo devi passare e basta . Quindi eliminalo dal tuo C#.
Idem per @ultimo_ind, non ti serve fra i parametri. Eliminalo dalla sp (a meno che non ti serva come parametro di output).
Puoi comunque creare una variabile che contenga lo SCOPE_IDENTITY() nella stored con la seguente sintassi:

DECLARE @ultimo_ind int -- .... -- e poi fai la select o la set SET @ultimo_ind = SCOPE_IDENTITY()

Però, torno a ripetere, per l'errore, leggiti i post su cui abbiamo già ampiamente discusso. Fatti un debug e controlla dove gli errori bloccano l'esecuzione, ok?
ciao!
Alx81 =)

http://blogs.dotnethell.it/suxstellino

nullatore Profilo | Junior Member

Ciao _J_ non conosco la funzione scope_identity(). Forse già questa è la tua soluzione.
Io uso (sicuramente chi è intervenuto al thread lo conosce) la clausola OUTPUT all'interno della INSERT in questo modo:
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales) OUTPUT INSERTED.LastName VALUES (....,....).

Questa restituisce il valore che ti serve (come se fosse una SELECT).
~nullatore~
mai dire corto circuito virtuale

_J_ Profilo | Senior Member

ciao alx,
la causa dell'errore la conosco, è quel parametro @ultimo_ind che non so proprio come passargli visto che lavora internamente alla sp, nel senso che se lo prende lui dall'ultimo indice inserito nella tabella 'nucleo_fam'
gli altri parametri lavorano bene (il passato mi è servito), il problema lo dà solo quel parametro che non so come gestire...

'cod_nf' è la chiave primaria (che è pure identity) per 'nucleo_fam', e inoltre lo uso nella tabella 'ruolo_in_nf' come foreign per collegare le due tabelle.. il relativo parametro sta lì perchè semplicemente ho dimenticato di toglierlo dal mio listato...^^'
lo so che non serve visto che voglio usare @ultimo_ind per il 'cod_nf' della tabella 'ruolo_in_nf' (foreign)

il problema sta come fargli capire che il cod_nf della tabella 'ruolo_in_nf' deve essere quello appena inserito nella tabella 'nucleo_fam'
il problema per me è proprio come gestire il parametro dello scope_identity...
cmq proverò il declare che mi hai detto tu (graaassssie )


per nullatore:
lo scope_identity restituisce l'ultimo valore di una colonna identity, ma non ne sono molto pratico visto che non l'ho mai usato... ma come si suol dire... c'è sempre una prima volta
tu hai suggerito OUTPUT Inserted (grazie anche a te... )....scusa, ma che ci metti nel 'values (....,....)'?
_J_

"Chi fa domande rischia di sembrare stupido,
chi non ne fa rischia di restare stupido..."

"Conosco me stesso? No, non parlo con gli estranei..."

_J_ Profilo | Senior Member

per LBENAGLIA:

ciao, eccoti gli script che mi hai chiesto, li ho allegati,
per quel che riguarda la chiamata, uso la stored che ho postato prima, cioè:

ALTER PROCEDURE dbo.nuova_fam_da_nuovo_cittadino //creo nuova famiglia
(
//non ho messo il parametro per il cod_nucleo_fam perchè è identity
@data_creaz datetime, //uso questo param sia per nucleo_fam.data_creaz che per ruolo_in_nf.data_inserim_in_nf
@tipo int,
@data_sciss datetime,

@ultimo_ind int,

@cod_p int,
@cod_parent int,
@data_rimoz datetime,
@cond varchar(1)
)

AS
INSERT INTO nucleo_fam_e_convivenze //prima creo la famiglia
(
data_creaz, //non faccio insert sulla pk in quanto identity
tipo_nucleo,
data_scissione
)
VALUES (@data_creaz,@tipo,@data_sciss)


set @ultimo_indice=scope_identity() /*per avere l'ultimo indice dalla tabella di prima, per immetterlo nella chiave primaria della tabella 'ruolo_in_nf_o_conviv' qui sotto

insert ruolo_in_nf_o_conviv //e poi ci inserisco il cittadino appena creato
(
cod_persona, //lo prenderò da una textbox
cod_nucleo_familiare , //uso @ultimo_ind per abbinare al cittadino appena creato la famiglia nuova che si sta creando
cod_parentela_con_is_o_cc,
data_inserim_in_nf_conv,
data_rimoz_da_nf_conv,
condizione_in_nf_conv
)
values (@cod_p,@ultimo_indice, @cod_parent, @data_creaz,@data_rimoz,@cond)
return

i cui parametri sono presi da :

SqlDataAdapter adatt = new SqlDataAdapter(tutti, oConn);
adatt.SelectCommand.CommandType = CommandType.StoredProcedure;
adatt.SelectCommand.Parameters.Add(new SqlParameter("@data_creaz", SqlDbType.DateTime)); //usato per 2 campi
adatt.SelectCommand.Parameters.Add(new SqlParameter("@tipo", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@data_sciss", SqlDbType.DateTime));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cod_p", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cod_parent", SqlDbType.Int));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@data_rimoz", SqlDbType.DateTime));
adatt.SelectCommand.Parameters.Add(new SqlParameter("@cond", SqlDbType.VarChar, 1));

adatt.SelectCommand.Parameters["@data_creaz"].Value = DateTime.Now;
adatt.SelectCommand.Parameters["@tipo"].Value = Server.HtmlEncode(Label5.Text);
adatt.SelectCommand.Parameters["@data_sciss"].Value = DBNull.Value;
adatt.SelectCommand.Parameters["@cod_p"].Value = Server.HtmlEncode(TextBox1.Text);
adatt.SelectCommand.Parameters["@cod_parent"].Value = "IS";
adatt.SelectCommand.Parameters["@data_rimoz"].Value = DBNull.Value;
adatt.SelectCommand.Parameters["@cond"].Value = Server.HtmlEncode(Label4.Text);

adatt.SelectCommand.ExecuteNonQuery();
oConn.Close();

grazie in anticipo per la disponibilità :)

_J_

"Chi fa domande rischia di sembrare stupido,
chi non ne fa rischia di restare stupido..."

"Conosco me stesso? No, non parlo con gli estranei..."

lbenaglia Profilo | Guru

>la causa dell'errore la conosco, è quel parametro @ultimo_ind
>che non so proprio come passargli visto che lavora internamente
>alla sp, nel senso che se lo prende lui dall'ultimo indice inserito
>nella tabella 'nucleo_fam'

Quel parametro semplicemente devi rimuoverlo sia dall'interfaccia della stored procedure che dal codice client.
Se sei interessato a memorizzarlo in una variabile all'interno della sp, dichiarane una mediante il comando DECLARE, altrimenti utilizza direttamente SCOPE_IDENTITY() nel comando di INSERT.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

_J_ Profilo | Senior Member

in poche parole uso la stored che ho fatto, però senza quel parametro.. ok, proverò subito
intanto grazie 1.000.000
_J_

"Chi fa domande rischia di sembrare stupido,
chi non ne fa rischia di restare stupido..."

"Conosco me stesso? No, non parlo con gli estranei..."

_J_ Profilo | Senior Member

perfetto, funziona tutto! grazie a tutti voi per l'aiuto che mi avete dato e che continuate a darmi


ps: toglietemi una curiosità: nel caso in cui non avessi avuto l'identity settato sulla prima tabella del primo insert, come avrei potuto procedere?
_J_

"Chi fa domande rischia di sembrare stupido,
chi non ne fa rischia di restare stupido..."

"Conosco me stesso? No, non parlo con gli estranei..."

lbenaglia Profilo | Guru

>ps: toglietemi una curiosità: nel caso in cui non avessi avuto
>l'identity settato sulla prima tabella del primo insert, come
>avrei potuto procedere?
Avresti dovuto generare una valore univoco (as esempio prevedendolo come parametro di input oppure generandolo con una banale SELECT MAX(id) + 1 o qualsiasi altra tecnica) ed utilizzandolo per valorizzare la colonna corrispondente nella tabella figlia.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

nullatore Profilo | Junior Member

>per nullatore:
>lo scope_identity restituisce l'ultimo valore di una colonna
>identity, ma non ne sono molto pratico visto che non l'ho mai
>usato... ma come si suol dire... c'è sempre una prima volta
>tu hai suggerito OUTPUT Inserted (grazie anche a te... )....scusa,
>ma che ci metti nel 'values (....,....)'?

nel values ci metti i valori che vuoi inserire nella tua tabella in ordine come hai dichiarato i nomi delle colonne (dopo il nome della tabella stessa).

Ciao

~nullatore~
mai dire corto circuito virtuale
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5