Need "refresh connection" or something ?

HI,

I have a problem with the data that I want to delete and insert new one on my SQL.
It will work if I just delete a row. And work well if I just insert a row.
But it will not work if I delete and insert at once on one procedure.

Here's the code :

Protected Sub RolesRadioButtonList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RolesRadioButtonList.SelectedIndexChanged
Session("CurrentRoleId") = Me.RolesRadioButtonList.SelectedValue
' Call Sub RemoveUsersInRoles
RemoveUsersInRoles()
' Call Sub AddNewUsersInRoles
AddNewUsersInRoles()
End Sub

Sub RemoveUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
' Delete that row with correct UserId
cmd.CommandText = "DELETE aspnet_UsersInRoles WHERE (UserId = '" & Session("CurrentUserId") & "')"
cmd.Connection = sqlConn
sqlConn.Open()
cmd.ExecuteNonQuery()
' Close SQL connecton
sqlConn.Close()
End Sub

Sub AddNewUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
sqlConn.Open()
' Create new row with new UserId
Dim sqlString As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES( '" & Session("CurrentUserId") & "','" & Session("CurrentRoleId") & "')"
Dim sqlComm As New SqlCommand(sqlString, sqlConn)
Dim sqlExec As Integer = sqlComm.ExecuteNonQuery
' Close SQL connection
sqlConn.Close()
End Sub

--------------

I can delete the row if I call 'RemoveUsersInRoles'
And I can insert new one if I call 'AddNewUsersInRoles'

But if 'RolesRadioButtonList_SelectedIndexChanged' is called, it will not work well.
If the row not exist, it will insert new row. And that what I want.
But If the row is exist, It wont delete that row and wont insert the new one. That's the problem.

Do I need some 'pause' or 'refresh connection' here ?

Thank You.

Why don't you create one procedure that checks if the row exists, then delete and insert if it does (or just amends/updates it), otherwise it just performs an insert?

|||

uh, Mike, thanks for your respon.

My mistake. I put this at my page load. It always reset the variable when the page is reload.

If Len(Session("CurrentRoleId")) Then
Me.RolesRadioButtonList.SelectedValue = Session("CurrentRoleId")
End If

And Mike, thanks for the advise : use update. I already use it.

And thank you all at that 2nd class, keep programming ...