Record Saving issue

Dear All , I have an issue i Have a page of user profile where i save the username ,eamil district ,tehsil ,uc and villages they area in that hiererchy (districts have tehsil) tehsils have UCs,UCS have villages and they are saving in loop DO WHILE
below is the code
" 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()

On local it works fine but when i connect to the database live it does not save and it does not show any error of timeout or else please check if any one know

5 Replies

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

Joginder Banger11y ago
Accepted answer
Hi once remove the try and catch block. Compiler throw the some error but catch exception can't handle proper way. I have no knowledge about VB code but your problem solve this type of .
0
Danish Habib11y ago
I did not make any table i want to ask the design and implementation i am new to .net and my programming skills are not very much good please share some example code for that District,tehsil,UC,Village saving.with hierarchy
0
Joginder Banger11y ago
HI Danish..

Make a simple table about your data. share it after that we can assume what is the best way for your performance issue.

Note:- Why are used the loop, i suggestion you make a function in database and join, it be boost your performance.
0
Danish Habib11y ago
Dear I want to store Districts,tehsils,UCs,Villages and the hierarchy is like that districts have tehsil and tehsils have ucs and ucs have villages I am using nested loops whcih makes my code very slow when hosted on server please any other method for that or any example please share.
0
Kunal Vaishya11y ago
Hi ,

Hi first use only one try catch in your code,

Second thing your code is too long can you please describe more that which type error coming in Exception block.

Please check Stored Procedures also, may are passing null values in SP instead of require values.

Regards,
Kunal.
0