• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1997
  • Last Modified:

There is no row at position 1

Hello Experts,
I am trying to loop through a datatable and run a stored proc for each record to insert the values into a database.
The first row writes fine then I get the "There is no row at position 1" error.  I know there is more then one record in the datatable becase I can respone.write the row.count property.  
Here is my code:

        Dim i As Integer

        For i = 0 To Cart.Rows.Count - 1
            order_key = insSL.Parameters.Add(New SqlParameter("@order_key", SqlDbType.Int))
            order_key.Direction = ParameterDirection.Input
            order_key.Value = OrderKey

            document_no = insSL.Parameters.Add(New SqlParameter("@document_no", SqlDbType.VarChar, 20))
            document_no.Direction = ParameterDirection.Input
            document_no.Value = OrderKey

            line_no = insSL.Parameters.Add(New SqlParameter("@line_no", SqlDbType.Int))
            line_no.Direction = ParameterDirection.Input
            line_no.Value = Cart.Rows.Count 'Cart.Rows(i).Item("AID")

            line_type = insSL.Parameters.Add(New SqlParameter("@line_type", SqlDbType.VarChar, 50))
            line_type.Direction = ParameterDirection.Input
            line_type.Value = "Item"

            sell_to_cust_no = insSL.Parameters.Add(New SqlParameter("@sell_to_cust_no", SqlDbType.VarChar, 20))
            sell_to_cust_no.Direction = ParameterDirection.Input
            sell_to_cust_no.Value = Company.Rows(i).Item("co_key")

Thanks for any comments
  • 2
1 Solution
well propably there is row in cart.rows.count

but not company.rows     sell_to_cust_no.Value = Company.Rows(i).Item("co_key")
there is absolutely no need to do it the way you have done. 100% not done.
ChadMarshAuthor Commented:
You are exactly right.  Can't believe I missed that.
Dim connection As New SqlConnection(..)
Dim adapter As New SqlDataAdapter()
Dim insertCommand As SqlCommand = connection.CreateCommand()
insertCommand.CommandType = CommandType.StoredProcedure
'all stored procedure types have a return parameter
With insertCommand.Parameters
      insertCommand.Parameters(0).Direction = ParameterDirection.Return
      'the rest
      'the last parameter is the name of the column in the datatable
      .Add("@line_type",SqlDbType.VarChar,50).Value = "Item"
End With
adapter.InsertCommand = insertCommand

you create a hybrid table that holds the added rows with the columns "document_no","line_no" and sell_to_cust_no"
and use the Update method. That will do call the InsertCommand for each row.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now