Question Adding a row to a datagrid

[tcdangers333]

I am developing an application that uses a datagrid to display data from a sql table. I need to allow the user to add a new row to the datagrid.

When I attempt to add a new row during testing I get an "IndexOutOfRangeException - Cannot find column" error.

Has anyone run into this problem, and if so, could you direct me to sample code. I am ready to claw out my eyes.

(By the way, I am brand new to .net with limited resources. I am the only developer here at this company and that explains my misery. I was a cobol programmer prior to this so it is a completely different world like night and day.)

 

[MattP]

Mind posting some code so we can determine where the error is?

I would recommend looking here in the meantime: Forms Over Data

 

[tcdangers333]

Thanks Matt, I will take a look at the link.

Here is some sample code. Again, thank you for replying.

Tom

             Dim mds As DataSet = New DataSet()
        Dim mda As SqlDataAdapter = New SqlDataAdapter()
        Dim cmdSelect As SqlCommand = mcnn.CreateCommand()
        cmdSelect.CommandType = CommandType.Text

        cmdSelect.CommandText = _
        "SELECT EINECS, CAS FROM REACHSubstance"

        Dim cmdInsert As SqlCommand = mcnn.CreateCommand()

        cmdInsert.CommandType = CommandType.Text
        cmdInsert.CommandText = _
        "INSERT INTO REACHSubstance " & _
        "(EINECS, CAS) " & _
        "VALUES(@EINECS, @CAS)"
        cmdInsert.Parameters.Add( _
        "@EINECS", SqlDbType.VarChar, _
            30, "EINECS")
        cmdInsert.Parameters.Add( _
        "@CAS", SqlDbType.VarChar, _
            30, "CAS")
        cmdInsert.Parameters("@EINECS"). _
        SourceVersion = DataRowVersion.Original
        mda.SelectCommand = cmdSelect
        mda.InsertCommand = cmdInsert

        mda.Fill(mds, "REACHSubstance")

        Dim dr As DataRow = _
            mds.Tables("REACHSubstance").NewRow()


        dr(3) = TextBox2.Text
        dr(4) = TextBox3.Text
        mds.Tables("REACHSubstance").Rows.Add(dr)
        mda.Update(mds, "REACHSubstance")

 

[jmcilhinney]

How many columns are you retrieving from the database?

SELECT EINECS, CAS FROM REACHSubstance

I see two. Which columns are you trying to add data to?

        dr(3) = TextBox2.Text
        dr(4) = TextBox3.Text

Those at indexes 3 and 4, i.e. the fourth and fifth columns. If you only retrieved two columns though, how can you put data in columns four and five? You only have columns at indexes 0 and 1 if you only have two columns. Any other index will be out of that range, hence the IndexOutOfRangeException.

I'd be interested to know where you got those numbers from because they seem somewhat arbitrary.

 

[jmcilhinney]

One further point. I always tend to refer to columns by name rather than by numeric index. I find that it makes code more readable.

 

[tcdangers333]

I was only retrieving the columns I needed to update. So, you're saying should select all of the columns, correct? That makes sense. Thank you!
Problem solved!
Sometimes it just takes another set of eyes to point out the painfully obvious
If only all problems in life were so simple...

Thanks again!

 

[jmcilhinney]

I was only retrieving the columns I needed to update. So, you're saying should select all of the columns, correct? That makes sense. Thank you!
Problem solved!
Sometimes it just takes another set of eyes to point out the painfully obvious
If only all problems in life were so simple...

Thanks again!

Actually, no, I wasn't saying that you should retrieve all the columns. You certainly should only retrieve the data you need and no more. What I was saying was that the column indexes you specify to access fields in a DataRow relate to the columns in your DataTable, not the number of columns in your database table. The DataTable contains the result set of a query. That query might involve a single table or multiple tables and it might return every column or only some columns. The database schema is irrelevant when accessing the DataTable. Only the query schema matters. The column at index 0 of the DataTable is the first column listed in your SELECT statement, which may or may not be the first column in the database table. Etc.

 

[tcdangers333]

I know. I understood what you meant afterwards about not selecting all fields. I should have stated in the follow-up email that I did not do that but followed the advice in regards to the indeces instead.

 

[cjard]

Better:

        dr("EINECS") = TextBox2.Text
        dr("CAS") = TextBox3.Text

Easier:
Use a typed datatable, and bind it