Below is the code of my Save user information button.
User table(userid,Username,Email,Password,Security Question,SecurityAnswer,District(from which he belongs to)
The Location hierarchy is like that (districts populates Tehsils ->mean one district can have many tehsils, and tehsil populate Ucs ->mean one tehsil may have many ucs in it , and Ucs populate villages (means->one uc may have 100 of villages ) so the story is like that one user may belong to just one district and 2 tehsil and 4 ucs and 20 villages in these ucs .so below is the code whcih i am using
"If Not Page.IsValid Then Return
Dim blnIsUpdate As Boolean = False
Dim hasedBytes As Byte()
Dim strPassword As String = String.Empty
Dim md5Hasher As New MD5CryptoServiceProvider()
If Not litUserId.Text.Trim().Length.Equals(0) Then blnIsUpdate = True
Dim cnnContact As New SqlConnection(AppSettings("DbSqlPortal"))
Dim cmdContact As New SqlCommand("spAdminSaveUsers", cnnContact)
Dim IDs As Integer = Nothing
Dim trnContact As SqlTransaction = Nothing
cmdContact.CommandType = CommandType.StoredProcedure
' hasedBytes = md5Hasher.ComputeHash(New UTF8Encoding().GetBytes((txtPassword.Text.Trim() & txtEmail.Text.Trim())))
GetDistrictId()
'If GetDistrictId() > 0 Then
' DisplayMessage(Me.Master.MessageBox, "A User with same District and Same user role already exists.", "Record Exists", MessageBoxTypes.Warning)
'Else
Try
With cmdContact.Parameters
If blnIsUpdate Then
.Add("@UserId", SqlDbType.BigInt).Value = CInt(litUserId.Text.Trim())
Else
.Add("@UserId", SqlDbType.BigInt).Value = DBNull.Value
End If
.Add("@FullName", SqlDbType.NVarChar).Value = txtFullName.Text.Trim()
.Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text.Trim()
.Add("@Password", SqlDbType.VarChar).Value = txtPassword.Text.Trim()
'Convert.ToBase64String(hasedBytes)
.Add("@SecurityQuestion", SqlDbType.VarChar).Value = ddlSecurityQuestions.SelectedItem.Text
.Add("@SecurityAnswer", SqlDbType.VarChar).Value = txtAnswer.Text.Trim()
.Add("@UserTypes", SqlDbType.TinyInt).Value = ddlUserTypes.SelectedValue
.Add("@IsActive", SqlDbType.Bit).Value = chkActive.Checked
'For Each item As ListItem In Me.CheckBoxList1.Items
' If item.Selected Then
' i += 1
' CheckBoxList1.SelectedValue = item.Value
' If i = 1 Then
' listitems += item.Text
' Else
' listitems += "'" + item.Text
' End If
' listitems = item.Value
' CheckBoxList1.SelectedValue = item.Value
' If i >= 1 Then
' If item.Selected Then
' Dim ccmd As New SqlCommand("insert into UserAndDistricts values ('" & CheckBoxList1.SelectedValue & "','" & "1" & "')", cnnContact)
' ccmd.Parameters.AddWithValue("@DistrictID", CheckBoxList1.SelectedValue)
' ccmd.Parameters.AddWithValue("@UserId", litUserId.Text)
' ' status = (status(String.Format("{0},", item)))
' cnnContact.Open()
' ccmd.ExecuteNonQuery()
' cnnContact.Close()
' End If
' End If
' Else
' End If
'Next
.Add("@DistrictId", SqlDbType.TinyInt).Value = CheckBoxList1.SelectedValue
'.Add("@DistrictId", SqlDbType.TinyInt).Value = ddlDistricts.SelectedValue
.Add("@IsAdmin", SqlDbType.Bit).Value = chkAdmin.Checked
'.Add("@ReportingPerson", SqlDbType.TinyInt).Value = CInt(ddlReporintUser.SelectedValue)
.Add("@IPAddress", SqlDbType.VarChar).Value = Request.UserHostAddress
.Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output
.Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output
' .Add("@CreateDate", SqlDbType.SmallDateTime).Value = DateTime.Now
If ChkIsReportAble.Checked = False Then
.Add("@IsReportable", SqlDbType.Bit).Value = False
Else
.Add("@IsReportable", SqlDbType.Bit).Value = ChkIsReportAble.Checked
End If
If ChkIsReportAble.Checked Then
.Add("@ReporintPerson", SqlDbType.TinyInt).Value = CInt(ddlReporintUser.SelectedValue)
Else
.Add("@ReporintPerson", SqlDbType.TinyInt).Value = DBNull.Value
End If
End With
cnnContact.Open()
trnContact = cnnContact.BeginTransaction()
'GetDistrictId()
'If GetDistrictId() > 0 Then
' DisplayMessage(Me.Master.MessageBox, "A User with same District and Same user role already exists.", "Record Exists", MessageBoxTypes.Warning)
'Else
cmdContact.Transaction = trnContact
cmdContact.ExecuteNonQuery()
If ddlUserTypes.SelectedValue = "6" Then
If CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Or (CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Or GetDistrictId()) > 0 Then
' Msg.Text = "The Record has been Successfully Submitted !"
DisplayMessage(Me.Master.MessageBox, "A User with same District and role already exists.", "Record Exists", MessageBoxTypes.Warning)
'MessageBoxShow(Page, "This Record Exists Try with some other record!")
litUserId.Text = String.Empty
Return
End If
End If
If CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Then
' Msg.Text = "The Record has been Successfully Submitted !"
DisplayMessage(Me.Master.MessageBox, "A User with same Email already exists.", "Record Exists", MessageBoxTypes.Warning)
'MessageBoxShow(Page, "This Record Exists Try with some other record!")
litUserId.Text = String.Empty
Return
End If
trnContact.Commit()
litUserId.Text = (cmdContact.Parameters("@ReturnId").Value)
cnnContact.Close()
SearchUsers()
'ScriptManager.RegisterStartupScript(Page, Page.GetType(), "Success", "Data has been saved", True)
'Dim status As String = String.Empty
'Dim cmdsContact As New SqlCommand("GETID", cnnContact)
'cmdsContact.CommandType = CommandType.StoredProcedure
'Dim rdr As SqlDataReader = Nothing
'rdr = cmdsContact.ExecuteReader()
' rdr.Close()
'listitems = ControlChars.CrLf
'Open this section when multiple districts came in
If blnIsUpdate Then
Dim cmd3 As New SqlCommand("Delete from UserAndDistricts Where UserId=@Userid", cnnContact)
cmd3.CommandType = CommandType.Text
cmd3.Parameters.AddWithValue("@UserId", litUserId.Text)
cnnContact.Open()
cmd3.ExecuteNonQuery()
cnnContact.Close()
Dim cmd2 As New SqlCommand("shiftDistrictsAndSUsers", cnnContact)
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Parameters.AddWithValue("@UserId", litUserId.Text)
cnnContact.Open()
cmd2.ExecuteNonQuery()
cnnContact.Close()
End If
'open this when multiple districts came in
'This isthe Code whcih check all the selected districts checkboxes cheked and then save '''''''
'mjhy ab in districts main sy tehsil check krni hain k wo b jo jo selected hain wo b save houn
'This is comment open this when multiple districts came in
Dim i As Integer = 0
Do While (i < CheckBoxList1.Items.Count)
If CheckBoxList1.Items(i).Selected Then
Dim cmdDistrict As New SqlCommand("AssociateDistricts", cnnContact)
cmdDistrict.CommandType = CommandType.StoredProcedure
Try
With cmdDistrict.Parameters
If blnIsUpdate Then
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
' .Add("@UserDistrictId", SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim())
Else
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
End If
.Add("@DistrictId", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
'If CheckBoxList2.Items(i).Selected = False Then
' .Add("@TehsilId", SqlDbType.BigInt).Value = 0
'End If
' .Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(i).Value)
.Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text
.Add("@IsSelcted", SqlDbType.Bit).Value = True
.Add("@DistrictName", SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).Text
.Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output
.Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output
End With
cnnContact.Open()
trnContact = cnnContact.BeginTransaction()
cmdDistrict.Transaction = trnContact
cmdDistrict.ExecuteNonQuery()
trnContact.Commit()
cnnContact.Close()
Dim j As Integer = 0
Dim k As Integer = 0
Dim L As Integer = 0
Do While (j < CheckBoxList2.Items.Count)
If CheckBoxList2.Items(j).Selected Then
Dim cmdsDistrict As New SqlCommand("AddTehsil", cnnContact)
cmdsDistrict.CommandType = CommandType.StoredProcedure
Try
With cmdsDistrict.Parameters
If blnIsUpdate Then
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
' .Add("@UserDistrictId", SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim())
Else
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
End If
.Add("@DistrictId", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
.Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value)
'GetListBoxSelStringInCommaTehsil(CheckBoxList2)
'CInt(CheckBoxList2.Items(j).Value)
.Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text
.Add("@IsSelcted", SqlDbType.Bit).Value = True
.Add("@DistrictName", SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).Text
.Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output
.Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output
'.Add("@DistrictID", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
End With
cnnContact.Open()
trnContact = cnnContact.BeginTransaction()
cmdsDistrict.Transaction = trnContact
'GetDistrictId()
cmdsDistrict.ExecuteNonQuery()
trnContact.Commit()
cnnContact.Close()
Catch ex As Exception
End Try
End If
Do While (k < CheckBoxList3.Items.Count)
If CheckBoxList3.Items(k).Selected Then
Dim cmdsDistrictd As New SqlCommand("AddUcs", cnnContact)
cmdsDistrictd.CommandType = CommandType.StoredProcedure
Try
With cmdsDistrictd.Parameters
If blnIsUpdate Then
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
' .Add("@UserDistrictId", SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim())
Else
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
End If
.Add("@DistrictId", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
If CheckBoxList2.Items(j).Selected = True Then
.Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value)
Else
.Add("@TehsilId", SqlDbType.BigInt).Value = DBNull.Value
End If
' .Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value)
'GetListBoxSelStringInCommaTehsil(CheckBoxList2)
'CInt(CheckBoxList2.Items(j).Value)
.Add("@UcId", SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value)
.Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text
.Add("@IsSelcted", SqlDbType.Bit).Value = True
.Add("@DistrictName", SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).Text
.Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output
.Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output
'.Add("@DistrictID", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
End With
cnnContact.Open()
trnContact = cnnContact.BeginTransaction()
cmdsDistrictd.Transaction = trnContact
cmdsDistrictd.ExecuteNonQuery()
trnContact.Commit()
cnnContact.Close()
Catch ex As Exception
End Try
End If
Do While (L < CheckBoxList4.Items.Count)
If CheckBoxList4.Items(L).Selected Then
Dim cmdsDistrict As New SqlCommand("AddVillages", cnnContact)
cmdsDistrict.CommandType = CommandType.StoredProcedure
Try
With cmdsDistrict.Parameters
If blnIsUpdate Then
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
' .Add("@UserDistrictId", SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim())
Else
.Add("@UserDistrictId", SqlDbType.BigInt).Value = DBNull.Value
End If
.Add("@DistrictId", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
If CheckBoxList2.Items(j).Selected = True Then
.Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value)
Else
.Add("@TehsilId", SqlDbType.BigInt).Value = DBNull.Value
End If
'.Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value)
' GetListBoxSelStringInCommaTehsil(CheckBoxList2)
'CInt(CheckBoxList2.Items(j).Value)
If CheckBoxList3.Items(k).Selected = True Then
.Add("@UcId", SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value)
Else
.Add("@UcId", SqlDbType.BigInt).Value = DBNull.Value
End If
'.Add("@UcId", SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value)
.Add("@villageId", SqlDbType.BigInt).Value = CInt(CheckBoxList4.Items(L).Value)
.Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text
.Add("@IsSelcted", SqlDbType.Bit).Value = True
.Add("@DistrictName", SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).Text
.Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output
.Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output
'.Add("@DistrictID", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)
End With
cnnContact.Open()
trnContact = cnnContact.BeginTransaction()
cmdsDistrict.Transaction = trnContact
cmdsDistrict.ExecuteNonQuery()
trnContact.Commit()
cnnContact.Close()
Catch ex As Exception
End Try
End If
L = (L + 1)
Loop
k = (k + 1)
Loop
j = (j + 1)
Loop
Catch ex As Exception
End Try
End If
i = (i + 1)
Loop
cnnContact.Close()
Dim strMessage As String = "A new User has successfully been created and saved."
If blnIsUpdate Then strMessage = "The selected User has successfully been updated and saved."
'lblMesssages.InnerText = "Welcome"
DisplayMessage(Me.Master.MessageBox, strMessage, "User Saved Successfully", MessageBoxTypes.Success)
'SendMAilMessage()
MVSearch.ActiveViewIndex = 0
SearchUsers()
'DisplayInitialPage()
Catch ex As Exception
If trnContact IsNot Nothing Then trnContact.Rollback()
DisplayMessage(Me.Master.MessageBox, "An unexpected error occurred while saving the Record. Please retry.", "Record Not Saved", MessageBoxTypes.Error)
Finally
DisposeDataObjects(, , cmdContact, cnnContact)
End Try
' End If
"
locally it works fast but when hosted its too slow , I have made four tables for that one table contains user basic information Name,email,District , another table contains userid,with districts and tehsil id, another table contains userid districtid and tehsilid and uc id, another table contains userid,districtid,tehsilid,ucid,villagesid ,so what is the other way this way is not a good way whcih i am using below is the attached form of user:
please respond
Kind Regards
Danish HAbib