database insert problems..

[CGreenMTU]

can anyone tell me what is wrong with this code for trying to insert records into a database?

I get the following error:

Exception Details: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Source Error:


Line 309:
Line 310: cnNewClaim.Open()
Line 311: cmdInsert.ExecuteNonQuery()
Line 312: cnNewClaim.Close()
Line 313:


Source File: C:\Inetpub\

http://wwwroot\MTU2\NewClaim.aspx.vb

Line: 311

MY CODE:
Private Sub lnkSaveClaim_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkSaveClaim.Click
Dim cmdInsert As New OleDb.OleDbCommand

Dim OleDbString As String = "INSERT INTO TblMasterClaimsRecord ([Account Name], Division, TrailorType, INSCompany, Policy_ID, [Policy Number], [Claim Number], [Type of Loss], [Insurance Claim Number], [Claim Status], [Date Closed], Reopen, ReClosed, [Report Date], [Date of Loss], [Time of Loss], [City of Loss], [State of Loss], [Location Code], Tractor, Trailer1, VIN, Unladen?, TPA, Subrogation, [Last Name], [First Name], MI, [License Number], [License State], [Driver Hire Date]) VALUES (@AccountName, @Division, @TrailorType, @INSCompany, @Policy_ID, @PolicyNumber, @ClaimNumber, @TypeofLoss, @InsuranceClaimNumber, @ClaimStatus, @DateClosed, @Reopen, @ReClosed, @ReportDate, @DateofLoss, @TimeofLoss, @CityofLoss, @StateofLoss, @LocationCode, @Tractor, @Trailer1, @VIN, @Unladen?, @TPA, @Subrogation, @LastName, @FirstName, @MI, @LicenseNumber, @LicenseState, @DriverHireDate)"

cmdInsert = New OleDb.OleDbCommand(OleDbString, cnNewClaim)

cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@AccountName", ddlInsured.SelectedItem))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Division", ddlDivisions.SelectedItem))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@TrailorType", ddlTrailorType.SelectedItem))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@INSCompany", txtInsCo.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Policy_ID", txtPolicyID.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@PolicyNumber", txtPolicyNumber.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@ClaimNumber", txtClaimNUmber.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@TypeofLoss", ddlLossType.SelectedItem))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@InsuranceClaimNumber", txtInsClaimNumber.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@ClaimStatus", ddlStatus.SelectedItem))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@DateClosed", txtDateClosed.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Reopen", txtReopened.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@ReClosed", txtReClosed.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@ReportDate", txtReportDate.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@DateofLoss", txtDateofLoss.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@TimeofLoss", txtTimeofLoss.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@CityofLoss", txtCityofLoss.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@StateofLoss", txtStofLoss.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@LocationCode", ddlLocationType.SelectedItem))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Tractor", txtTractor.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Trailer1", txtTrailor.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@VIN", txtVIN.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Unladen?", ckbUnladen.Checked))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@TPA", ckbTPA.Checked))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@Subrogation", ckbSubro.Checked))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@LastName", txtLast.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@FirstName", txtFirst.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@MI", txtMiddle.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@LicenseNumber", txtLicNum.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@LicenseState", txtLicSt.Text))
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@DriverHireDate", txtStartDAte.Text))

cnNewClaim.Open()
cmdInsert.ExecuteNonQuery()
cnNewClaim.Close()

End Sub

 

[jmeckley]

It is most likely erroring out on one of the SelectedItem values.

When you load your drop down lists do you have a selected default value? If not then this may be the issue. If no value is selected by default and the user never selects an item, then there is no selected value.

@ ways to approach this. Set a selected default value right
after you bind the Combo Box, or Check to see if the SelectedIndex is > -1. -1 means no items are selected

You may also want to try SelectedItem.Value, SelectedItem.Text, or if using .Net 1.1 SelectedValue.

Jason Meckley
Database Analyst
WITF

 

[CGreenMTU]

i've tried those suggestions, but i'm still getting the same error message as above. any more suggestions???

thanks

 

[jmeckley]

you could also try this:

With cmdInsert.Parameters.Add
("@AccountName").Value = ddlInsured.SelectedValue
...
End With

Jason Meckley
Database Analyst
WITF

 

[CGreenMTU]

that also did not work for me. this is becoming a headache and it doesn't seem like it should be that big of a problem....haha

any more suggestions?

I took out the '?' in the variable name Unladen? in case it tried recognizing '?' as another variable, but no luck there either...

 

[jmeckley]

for debuggin purposes try this.

comment out open connection, execute nonquery, close connection.

place a lable some where on your form named lblParam

then just before the commented code and after you load the parameters

Dim P as SqlParameter ' OleDBParameter if not Sql Server
Dim Txt as New System.Text.StringBuilder
Try
  For Each P in cmdInsert.Parameters
    Txt.Append(p.ParameterName)
    Txt.Append(" = ")
    Txt.Append(p.Value.ToString)
    Txt.Append(Chr(10))
  Next
Catch ex as Exception
  Txt.Append(Chr(10))
  Txt.Append(ex.ToString)
Finally
  lblParam.Text = Txt.ToString
End Try

they run the code and see what the values are. If an error occurs during the cycling the message will be appended to the end of the string.
Check the error message and the last key/value pair printed. The on below that will be the field that in code should be the one that is erroring.

Jason Meckley
Database Analyst
WITF

 

[CGreenMTU]

I did what you suggested, however no errors occur during the cycling of the message....

 

[jmeckley]

If there were no errors, and the values listed were correct, then the issue maybe with the query string, table structure, or relationships amoung tables.

Jason Meckley
Database Analyst
WITF

 

[cappmgr]

maybe a better description might help catch the FormatException

Catch ex As FormatException
Response.Write("Format Error: " & ex.Message & "<br>" & ex.StackTrace)

Marty