[2005] Using the dataset row.find method???

[Jigabyte]

Can anyone help me with an example of how to find a row in my dataset. I dont want to do a for loop so I was wondering how I use the find method. I have a primery key in my table.

Thank you all for any help,

Jiggy!

[Jigabyte]

Hi,

I have tried this:-

sqlDSDepartments.Tables(0).Rows.Find("DP_DEPTCODE='" & sqlDSDeptOrder.Tables(0).Rows(0).Item("DO_DEPT_CODE") & "'")

but is says my table does not have a primary key yet it does. Do I need to do something different to how I populate the dataset? Here is my code:-

        sSQL = "SELECT * FROM DP_DEPARTMENTS"
        sqlDADepartments = New SqlCeDataAdapter
        sqlDADepartments.SelectCommand = New SqlCeCommand(sSQL, CeData)
        sqlDSDepartments = New DataSet
        sqlDADepartments.Fill(sqlDSDepartments, "DP_DEPARTMENTS")

Jiggy

[jmcilhinney]

Do I need to do something different to how I populate the dataset?

Yes, you do. The table in your database may have a primary key but that doesn't mean your DataTable does. The default value for the MissingSchemaAction property of a DataAdapter is Add. That means that when a DataAdapter retrieves data, any new column names, types and sizes will be propagated to the DataTable. That doesn't say anything about any primary key. If you want the primary key information to be propagated too you have to change the value to AddWithKey.

By the way, do you really need a DataSet? Are you using any more than one DataTable? If not then the DataSet is pointless and you should jsut use a lone DataTable. Would you create an array for just one String vlaue?

[Jigabyte]

Hi Mate,

I have found out how to do it but I don't know whether it is the correct practice:-

        sSQL = "SELECT * FROM DP_DEPARTMENTS"
        sqlDADepartments = New SqlCeDataAdapter
        sqlDADepartments.SelectCommand = New SqlCeCommand(sSQL, CeData)
        sqlDSDepartments = New DataSet
        sqlDADepartments.Fill(sqlDSDepartments, "DP_DEPARTMENTS")
        sqlDSDepartments.Tables(0).PrimaryKey = New DataColumn() {sqlDSDepartments.Tables(0).Columns("DP_DEPTCODE")}

and then to use the find command I do this :-

sqlDRDepartments = sqlDSDepartments.Tables(0).Rows.Find(sqlDSDeptOrder.Tables(0).Rows(0).Item("DO_DEPT_CODE"))

and then work off the datatable as I am only using it as a look up.

It seems bad practice to me to read all the data into a dataset which is detached from the live database. Can you give me an example for using a table and does this mean it is direct to the database all the time?

Thanks mate for your reply and any further advice.

Jiggy

[jmcilhinney]

Firstly, I already told you how to do it. You set the adapters MissingSchemaAction property to AddWithKey and then the primary key information will be read from the database. There's no need to set it yourself unless you want it to be different to the database.

As for using a DataTable, you already are. What do you think this does?

sqlDADepartments.Fill(sqlDSDepartments, "DP_DEPARTMENTS")

It creates a DataTable named "DP_DEPARTMENTS" in your DataSet and populates it. It's equivalent to this:

sqlDSDepartments.Tables.Add(New DataTable("DP_DEPARTMENTS")
sqlDADepartments.Fill(sqlDSDepartments.Tables("DP_DEPARTMENTS"))

DataSets don't contain data. They contain DataTables, which contain DataRows, which contain data. What I was saying is that if you aren't creating any more than one table in the DataSet then the DataSet is useless. Just create a DataTable on its own. Instead of this:

sqlDSDepartments = New DataSet
sqlDADepartments.Fill(sqlDSDepartments, "DP_DEPARTMENTS")

do this:

sqlDTDepartments = New DataTable("DP_DEPARTMENTS")
sqlDADepartments.Fill(sqlDTDepartments)

It is not bad practice to use a DataSet or DataTable that is detached from the database. ADO.NET is based on a disconnected model, so that's how EVERY .NET application works. If you want to know the details, MSDN has plenty of information just waiting to be read.

[Jigabyte]

Firstly I wasn't familiar with the code you mentioned in your first reply. I would however like to thank you for your explanation. I will stick to my routine for now as it works but I understand what you mean. Why keep doing dataset.tables(0).rows .... when you only have one table in your recordset.