Error: Index was outside the bounds of the array.

[mike55]

Hi all

 

I am getting the error: "Index was outside the bounds of the array" when updating database records using a dataset and the sqldataadapter.update command.

 

Here is my database code:

'Provides a means of updating basic required data for all members.
       Public Function QuickUpdate(ByVal orgData As DataSet, ByVal organisation As String) As Boolean
           Dim sqlReturn As Boolean = True

           Try

               quickAddAdapter.InsertCommand = New SqlCommand
               quickAddAdapter.InsertCommand.Connection = cnConn
               quickAddAdapter.InsertCommand.CommandText = "QuickInsertMembers"
               quickAddAdapter.InsertCommand.CommandType = CommandType.StoredProcedure

               quickAddAdapter.DeleteCommand = New SqlCommand
               quickAddAdapter.DeleteCommand.Connection = cnConn
               quickAddAdapter.DeleteCommand.CommandText = "QuickDeleteMembers"
               quickAddAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure

               quickAddAdapter.UpdateCommand = New SqlCommand
               quickAddAdapter.UpdateCommand.Connection = cnConn
               quickAddAdapter.UpdateCommand.CommandText = "QuickUpdateMember"
               quickAddAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure

               quickAddAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Forename", SqlDbType.NVarChar, 24))
               quickAddAdapter.InsertCommand.Parameters("@Forename").Direction = ParameterDirection.Input
               quickAddAdapter.InsertCommand.Parameters("@Forename").SourceColumn = "Forename"
               quickAddAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Surname", SqlDbType.NVarChar, 24))
               quickAddAdapter.InsertCommand.Parameters("@Surname").Direction = ParameterDirection.Input
               quickAddAdapter.InsertCommand.Parameters("@Surname").SourceColumn = "Surname"
               quickAddAdapter.InsertCommand.Parameters.Add(New SqlParameter("@MobileNumb", SqlDbType.Char, 20))
               quickAddAdapter.InsertCommand.Parameters("@MobileNumb").Direction = ParameterDirection.Input
               quickAddAdapter.InsertCommand.Parameters("@MobileNumb").SourceColumn = "MobileNumb"
               quickAddAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar, 50))
               quickAddAdapter.InsertCommand.Parameters("@Email").Direction = ParameterDirection.Input
               quickAddAdapter.InsertCommand.Parameters("@Email").SourceColumn = "Email"
               quickAddAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Organisation", SqlDbType.NVarChar, 10))
               quickAddAdapter.InsertCommand.Parameters("@Organisation").Direction = ParameterDirection.Input
               quickAddAdapter.InsertCommand.Parameters("@Organisation").Value = organisation

               quickAddAdapter.DeleteCommand.Parameters.Add(New SqlParameter("@Organisation", SqlDbType.NVarChar, 10))
               quickAddAdapter.DeleteCommand.Parameters("@Organisation").Direction = ParameterDirection.Input
               quickAddAdapter.DeleteCommand.Parameters("@Organisation").Value = organisation
               quickAddAdapter.DeleteCommand.Parameters.Add(New SqlParameter("@MID", SqlDbType.BigInt))
               quickAddAdapter.DeleteCommand.Parameters("@MID").Direction = ParameterDirection.Input
               quickAddAdapter.DeleteCommand.Parameters("@MID").SourceColumn = "Member_ID"


               quickAddAdapter.UpdateCommand.Parameters.Add(New SqlParameter("@MemberID", SqlDbType.BigInt))
               quickAddAdapter.UpdateCommand.Parameters("@MemberID").Direction = ParameterDirection.Input
               quickAddAdapter.UpdateCommand.Parameters("@MemberID").SourceColumn = "Member_ID"
               quickAddAdapter.UpdateCommand.Parameters.Add(New SqlParameter("@Forename", SqlDbType.NVarChar, 24))
               quickAddAdapter.UpdateCommand.Parameters("@Forename").Direction = ParameterDirection.Input
               quickAddAdapter.UpdateCommand.Parameters("@Forename").SourceColumn = "Forename"
               quickAddAdapter.UpdateCommand.Parameters.Add(New SqlParameter("@Surname", SqlDbType.NVarChar, 24))
               quickAddAdapter.UpdateCommand.Parameters("@Surname").Direction = ParameterDirection.Input
               quickAddAdapter.UpdateCommand.Parameters("@Surname").SourceColumn = "Surname"
               quickAddAdapter.UpdateCommand.Parameters.Add(New SqlParameter("@MobileNumb", SqlDbType.Char, 20))
               quickAddAdapter.UpdateCommand.Parameters("@MobileNumb").Direction = ParameterDirection.Input
               quickAddAdapter.UpdateCommand.Parameters("@MobileNumb").SourceColumn = "MobileNumb"
               quickAddAdapter.UpdateCommand.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar, 50))
               quickAddAdapter.UpdateCommand.Parameters("@Email").Direction = ParameterDirection.Input
               quickAddAdapter.UpdateCommand.Parameters("@Email").SourceColumn = "Email"
               quickAddAdapter.UpdateCommand.Parameters.Add(New SqlParameter("@Organisation", SqlDbType.NVarChar, 10))
               quickAddAdapter.UpdateCommand.Parameters("@Organisation").Direction = ParameterDirection.Input
               quickAddAdapter.UpdateCommand.Parameters("@Organisation").Value = organisation

               quickAddAdapter.Update(orgData, "Everybody")

               Return sqlReturn
           Catch ex As Exception
               sqlReturn = False
           Finally
               clsConnection.CloseConnection(cnConn)
           End Try
       End Function

 

One posting that I saw suggested that the problem was due to the user using the "Select *" rather than selecting only the columns needed. I am not using the "Select *" so that is one thing ruled out.

 

Mike55.

[mike55]

Solved the problem, caused by incorrect data being in the database.

 

Mike55.