The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this... I tried several alternatives that I found on the internet but none seem to work...
Thanks!
Dim objConn3As SqlConnection
Dim mySettings3AsNew NameValueCollection
mySettings3 = AppSettings
Dim strConn3AsString
strConn3 = mySettings3("connString")
objConn3 =New SqlConnection(strConn3)
Dim strInsertPatientAsString
Dim cmdInsertAs SqlCommand
Dim strddlSexAsString
Dim strddlPatientStateAsString
Dim rowsAffectedAsInteger
strddlSex = ddlSex.SelectedItem.Text
strddlPatientState = ddlPatientState.SelectedItem.Text
strInsertPatient ="Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _
"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _
"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _
"Today_Date_Year) Values (@.UserID, @.Accession, @.FirstName, @.MI, @.LastName, @.MedRecord, " & _
"'" & strddlSex &"', @.DOB, @.Address1, @.Address2, @.City, @.Suite , '" & strddlPatientState &"', " & _
"@.ZIP, @.HomeTelephone, @.OutsideNYC, @.ClinicalImpression, @.Today_Date_Month, @.Today_Date_Day, " & _
"@.Today_Date_Year)SELECT @.@.IDENTITY AS NewID SET NOCOUNT OFF"
cmdInsert =New SqlCommand(strInsertPatient, objConn3)
cmdInsert.Parameters.Add("@.UserID","Joe For Now")
cmdInsert.Parameters.Add("@.Accession", Accession.Text)
cmdInsert.Parameters.Add("@.LastName", LastName.Text)
cmdInsert.Parameters.Add("@.MI", MI.Text)
cmdInsert.Parameters.Add("@.FirstName", FirstName.Text)
cmdInsert.Parameters.Add("@.MedRecord", MedRecord.Text)
cmdInsert.Parameters.Add("@.ddlSex", strddlSex)
cmdInsert.Parameters.Add("@.DOB", DOB.Text)
cmdInsert.Parameters.Add("@.Address1", Address1.Text)
cmdInsert.Parameters.Add("@.Address2", Address2.Text)
cmdInsert.Parameters.Add("@.City", City.Text)
cmdInsert.Parameters.Add("@.Suite", Suite.Text)
cmdInsert.Parameters.Add("@.strddlPatientState", strddlPatientState)
cmdInsert.Parameters.Add("@.ZIP", zip.Text)
cmdInsert.Parameters.Add("@.HomeTelephone", Phone.Text)
cmdInsert.Parameters.Add("@.OutsideNYC", OutsideNYC.Text)
cmdInsert.Parameters.Add("@.ClinicalImpression", ClinicalImpression.Text)
cmdInsert.Parameters.Add("@.Today_Date_Month", Today_Date_Month.Text)
cmdInsert.Parameters.Add("@.Today_Date_Day", Today_Date_Day.Text)
cmdInsert.Parameters.Add("@.Today_Date_Year", Today_Date_Year.Text)
objConn3.Open()
cmdInsert.ExecuteNonQuery()
objConn3.Close()
Try this - a zillion ways to get Scope_Identity back:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54
No comments:
Post a Comment