How to save associated records

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

' 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

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Danish Habib11y ago
Thanks I am waiting for more useful guidelines..
Wim Sturkenboom11y ago
The only thing I currently notice is that you keep on opening and closing connections; that is a very expensive exercise. I suggest that you open the connection once in the beginning (before the the most outer loop) and close at the end (after the most outer loop).

I'm not a VB person (C#), so still trying to figure a few things out. It might take a while though.
Danish Habib11y ago
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
Wim Sturkenboom11y ago
No, that's not what I'm saying.

Maybe you can explain why you're looping? Maybe show the relevant code. So we can try to understand what you're doing and why you're looping.
Danish Habib11y ago
Dear please Tell me how to store the values 1- You said that i store the user (username,Email,Password) in one table 2- then i store the UserId and district id in another table 3-then i save the userid and tehsil id in another table 4- then i store the userid and uc id in another table 5- then i store the userid and village id in another table u mean to say i have to create a seperate table for user basic information with districts ,another table for tehsil,another table for ucs,another table for villages Please reply??
Wim Sturkenboom11y ago
Is this what it is supposed to be? A user lives in a village that is in an uc that is in a tehsil that is in a district?

If so

Assuming you have tables for district, tehsil, uc and villages, you basically only have to store the village with the user information; the other information (uc, tehsil, district) can be retrieved when you retrieve the user's record form the database.