I have a form by which i am saving the user (user has(id,name,email,password,District,tehsil,Uc,Villages) I am using for loops to save all those many to many relationship and i am using individual sql connection for each loop when i run the application locally it works but on hosting its slow down the process so i remove all individual connection and try to open connection once and close at the end but it gives that errer
"Arithmetic overflow error converting expression to data type tinyint.
The statement has been terminated."
Below is the whole code any one can tell me the solution or make this code good for me
Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
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
End Sub