Hi everyone.
I am trying to write 2 INSERT INTO statements into a Button click event. Both statements go to the same database but differnet tables. The first statement works fine but the 2nd causes an error with the Try, Catch, Finally statement. When I remove the ExecuteNonQuery from the 2nd statement, the 2nd INSERT INTO statement fails to work. Any help would be brilliant. Thanks!
Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
'Insert Guide changes into ChangesReport table in sql server
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1
Dim Name As String
Dim values As String
Dim sSQL As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText ContentManager"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, Label1"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager) VALUES (@.themeValue, @.guideValue, @.guidepageValue, @.pagetypeValue, @.changecategoryValue, @.changefromValue, @.changefromtextValue, @.changetoValue, @.changetotextValue, @.contentmanagerValue)"
SqlCommandThemeTest.Parameters.Add("@.themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@.changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@.contentmanagerValue", Label1.Text)
Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try
'Insert textbox to ChangeLogFrom ddl
Me.CmdDDLFromUpdate.Connection = Me.SqlConnection1
Name = "ChangeFromText"
values = "txtChangeFrom"
sSQL = "INSERT INTO dbo.Change Log From (ChangeLogFrom) VALUES (@.changelogfromValue)"
Me.CmdDDLFromUpdate.Parameters.Add("@.changelogfromValue", txtChangeFrom.Text)
Try
Me.SqlConnection1.Open()
Me.CmdDDLFromUpdate.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try
I have tried various different things with these 2 statements but can still only get the first to insert.
Any advice would be great. Thanks
|||It's hard to tell since you've only cut and pasted certain parts of the code. For example, I don't see where you've set the commandtext of the second sqlcommand. I see the sSQL string being set, but that isn't the same thing.
You might also want the keep the sqlconnection open for both updates, no use in closing it then reopening it.
|||Why dont you pass all the parameters to a stored proc and do both the inserts there. That will save you a round trip and your code will be at one place.