AddNew e recupero id chiave primaria con SQL Server
Giuseppe Agoglia
Vorrei sottoporvi un mio problema, nella speranza che
qualcuno possa aiutarmi. Prima di leggere quanto segue
tenete presente che sono alle prime armi ... quindi non
infierite troppo!!! :-D
Tramite ADO voglio aggiungere un nuovo record ad una
tabella di database e contemporaneamente recuperare l'id
della chiave primaria (contatore) del nuovo record
aggiunto.
Il codice VBScript che io uso è:
...
Dim rsScrivi
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;" & _
"Persist Security Info=True;" &_
"User ID=sa;Password="""";Data Source=(local);" &_
"Initial Catalog=mio_database"
Set rsScrivi = server.CreateObject("ADODB.Recordset")
rsScrivi.open "mia_tabella", conn, 3, 3, 2
rsScrivi.AddNew
...
id_new = rsScrivi("id_mia_tabella")
...
rsScrivi.Update
...
Quando vado a leggere il contenuto di id_new questa
risulta vuota!
La cosa curiosa è che se uso lo stesso codice con un
database Access tutto fila liscio e in id_new memorizzo
effettivamente l'id dell'ultimo record aggiunto.
... Dov'è che sto sbagliando?
Grazie mille per l'aiuto.
Lorenzo Benaglia
> Tramite ADO voglio aggiungere un nuovo record ad una
> tabella di database e contemporaneamente recuperare l'id
> della chiave primaria (contatore) del nuovo record
> aggiunto.
> Il codice VBScript che io uso è:
>
> ...
> risulta vuota!
> La cosa curiosa è che se uso lo stesso codice con un
> database Access tutto fila liscio e in id_new memorizzo
> effettivamente l'id dell'ultimo record aggiunto.
>
> ... Dov'è che sto sbagliando?
Ciao Giuseppe,
il tuo esempio contiene un paio di "issues" che fanno in modo di non
recuperare con successo il contatore generato da una colonna di tipo
IDENTITY:
1) Il cursore che hai utilizzato è di tipo Static (3). I cursori Static
risiedono sul Client e rappresentano uno snapshot dei dati (una fotografia)
e sono utilizzati prevalentemente in modalità disconnessa per scorrere le
righe oppure per generare reportistica;
2) Leggi il valore del campo contatore subito dopo aver chiamato il metodo
AddNew. In SQL Server l'incremento avviene al momento dell'inserimento della
riga, ovvero dopo il metodo Update.
Nell'esempio che ti andrò a proporre, utilizzerò un cursore di tipo keyset
residente server side. Un keyset non è altro che una tabella in memoria
contenente le keys necessarie ad identificare univocamente una riga. In
questo modo quando un client apre un recorsert keyset, SQL Server caricherà
in memoria una tabellina con le chiavi e ogni volta che ti muoverai su un
determinato record il server andrà a leggere nel db le colonne della riga
referenziata dalla chiave corrente.
Vediamo un esempio.
Supponiamo di avere la tabella Students nel tempdb (apri una sessione di
Query Analyzer ed esegui questo piccolo script):
USE tempdb
GO
/* Creo la tabella dbo.Students */
CREATE TABLE dbo.Students(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
)
ed il seguente codice VBScript:
Dim rs
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adCmdTable = 2
Const CN_STRING = "Provider=SQLOLEDB;Initial Catalog=tempdb;Data
Source=localhost;Integrated Security=SSPI"
' Creo un oggetto Recordset
Set rs = CreateObject("ADODB.Recordset")
' Apro il recordset
rs.Open "dbo.Students", CN_STRING, adOpenKeyset, adLockOptimistic,
adCmdTable
' Aggiungo uno studente
rs.AddNew
rs.Fields("FirstName").Value = "Lorenzo"
rs.Fields("LastName").Value = "Benaglia"
rs.Update
' Leggo l'ID generato
MsgBox "StudentID = " & rs.Fields("StudentID").Value, vbInformation
' Pulizia
rs.Close
Set rs = Nothing
Se lo andrai ad eseguire, noterai che la message box visualizzerà
correttamente il valore della colonna IDENTITY.
OK, funziona, ma se il tuo scopo è semplicemente quello di popolare il
database, perché utilizzare un recordset?!
Un recordset serve soprattutto per leggere un insieme di righe da presentare
ad un utente ed eventualmente per aggiornare la riga corrente od inserirne
una nuova.
Se il tuo scopo è solo quello di inserire o aggiornare il database, allora
esistono sistemi molto più efficienti come le Stored Procedures.
Una stored procedure non è altro che una semplice procedura (la classica
Sub...End Sub in Visual Basic) scritta in linguaggio SQL e residente
all'interno del database.
Vediamo un esempio analogo al precedente che però sfrutta una stored
procedure per inserire lo studente nella tabella dbo.Students.
Come prima cosa occorre definire la procedura.
Sempre da Query analyzer esegui il seguente script:
/* Definisco la stored procedure dbo.upI_Student
** che effettua l'inserimento di uno studente
*/
CREATE PROCEDURE dbo.upI_Student(
@FirstName varchar(10),
@LastName varchar(10)
)
AS
INSERT dbo.Students
VALUES(@FirstName, @LastName)
/* Restituisco il valore del campo IDENTITY */
RETURN SCOPE_IDENTITY()
GO
La funzione SCOPE_IDENTITY() restituisce il valore della colonna IDENTITY
generato dal comando di INSERT.
Questo invece è il codice VBScript:
Dim cmd
Dim intStudetnID
Const CN_STRING = "Provider=SQLOLEDB;Initial Catalog=tempdb;Data
Source=localhost;Integrated Security=SSPI"
Const adInteger = 3
Const adVarChar = 200
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamReturnValue = 4
Const adExecuteNoRecords = 128
' Definisco un oggetto ADODB.Command
Set cmd = CreateObject("ADODB.Command")
With cmd
.CommandType = adCmdStoredProc
.CommandText = "dbo.upI_Student"
.Parameters.Append .CreateParameter("@ReturnValue", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@FirstName", adVarChar,
adParamInput, 10, "Luca")
.Parameters.Append .CreateParameter("@LastName", adVarChar,
adParamInput, 10, "Bianchi")
.ActiveConnection = CN_STRING
' Eseguo la stored procedure
.Execute, ,adExecuteNoRecords
' Leggo il valore del valore di ritorno dalla collection Parameters
intStudetnID = .Parameters("@ReturnValue").Value
End With
MsgBox "StudentID = " & intStudetnID, vbInformation
' Pulizia
Set cmd = Nothing
L'oggetto Command è stato progettato appositamente per richiamare le Stored
Procedures.
Puoi approfondire gli argomenti trattati sui Books Online di SQL Server e su
MSDN a partire dal seguente link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaobj01_1.asp
> Grazie mille per l'aiuto.
Prego.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
UGIdotNET - http://www.ugidotnet.org
UGISS - http://www.ugiss.org
Federico Saravalle
come piccola aggiunta alla notevole risposta di Lorenzo, ti invito a
sperimentare anche la proprietà UPDATE RESYNC dell'oggeto recordset (sempre
dopo l'update e mai prima, come dice giustamente Lorenzo...).
Se dai un'occhiata ai possibili valori per questa proprietà, trovi:
adResyncNone
adResyncAutoIncrement (default)
adResyncConflicts
adResyncUpdates
adResyncInserts
adResyncAll
ad esempio:
rs.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
Trovi una descrizione dettagliata del suo uso per esempio su "Programmare
ADO" di David Sceppa; a mio parere una vera bibbia per questo ambito.
Ciao
Federico
"Giuseppe Agoglia" <ago...@mediaus.it> ha scritto nel messaggio
news:0ee801c32c44$fdb17690$a501...@phx.gbl...