Question Querying a dataset

[kfirba]

Hello!

Is it possible querying a dataset instead of the Database? When I create a query in the designer, I think that when I activate the query, I actually querying the Database and not the Dataset.

In addition, lets say I want to find some record in the dataset from Table called "books" how do I search for it? The way I do it now is using a query with "WHERE" and retrieving/deleting/updating the record.

and again, if I query the Dataset, for example get all the books that their name contains the letter 'a' how do I store the result? In a dataTable? How do I actually query a dataset and only once the user presses on the "save" button, the Adapter will update the Database?

Thanks!

 

[Lotok]

If you want to query a DataTable you can do so using the Select method. Say for example you want to query a table of names to pull out all records with your username. Using select returns an array of DataRows

Dim result = DataTable.Select("username=kfirba")

DataTable would be replaced with an actual instance of a DataTable

Details Here: http://msdn.microsoft.com/en-us/library/det4aw50.aspx

 

[kfirba]

Not sure i have fully understood that.

When you wrote "DataTable.Select" the DataTable is the name of the DtaTable that I'm querying? Is it like: "Names.Select()"?

and what exactly the variable result holds? Could you provide me an example and show me how do I then use the variable result?

When I done editing the result variable, How do I update the Dataset wit the changes that I have just made?

In addition to that, how do I DELETE a record from the Dataset according to some specific parameter or UPDATE a record in the Dataset according to some parameter?

Thanks!

 

[Lotok]

When you wrote "DataTable.Select" the DataTable is the name of the DtaTable that I'm querying? Is it like: "Names.Select()"?

Yes.

and what exactly the variable result holds?

An Array of DataRow

Could you provide me an example and show me how do I then use the variable result?

That depends what you are trying to achieve. You could loop through the result looking at each returned DataRow and do what you want. What is it you want to do with the results?

When I done editing the result variable, How do I update the Dataset wit the changes that I have just made?

Microsoft already have very clear examples of manipulating DataTables, so rather than reinventing the wheel...

Delete Rows in a DataTable: How to: Delete Rows in a DataTable
Edit Rows in a DataTable: How to: Edit Rows in a DataTable
Add Rows to a DataTable: How to: Add Rows to a DataTable

 

[kfirba]

thanks I have just found those tutorials by Microsoft and il going through them.

There is only 1 thing which I can't understand, what they do is:

result(rowIndex)(columnName) = "value" or result(rowIndex).Delete()
why they do: result(rowIndex)(columnName) and not result(rowIndex).Item(columnName)? Or maybe it doesn't matter?

oh and, one more thing, the result array, is holding a POINTER to the actual record to the dataset and not a clone of the row right? Therefore if I commit any changes to the result array, it actually commit the changes to the dataset automatically, right?

When do I have to use the AcceptChanges? Before updating the Database?

 

[Lotok]

result(rowIndex)(columnName) = "value" or result(rowIndex).Delete()
why they do: result(rowIndex)(columnName) and not result(rowIndex).Item(columnName)? Or maybe it doesn't matter?

I would use the Result(row)(col) but I suppose it's the same thing

oh and, one more thing, the result array, is holding a POINTER to the actual record to the dataset and not a clone of the row right? Therefore if I commit any changes to the result array, it actually commit the changes to the dataset automatically, right?

You are returned a reference type, so yes, the changes will affect the table.

When do I have to use the AcceptChanges? Before updating the Database?

Accept changes applies all of your changes to the DataTable. So that is when deleted rows become deleted etc. So yeah, call that before updating your DB.

 

[kfirba]

how do I query the dataset with more than 1 table, I mean, using the JOIN statement.
if I want to select data only from 1 table, it's easy, I can do it with the select method and give the parameters in the function. How do I query more than 1 table to get result from more than 1 table, making connection between foreign key and primary key?

Thanks

 

[Lotok]

Usually If I were to have a need for that level of manipulation I would be using LINQ or doing it on the SQL end. You could look at the DataRelation class,that will allow you to do what you need for multiple tables in the DataSet but it's not something I use, so don't have much to input on it.

 

[kfirba]

Usually If I were to have a need for that level of manipulation I would be using LINQ or doing it on the SQL end. You could look at the DataRelation class,that will allow you to do what you need for multiple tables in the DataSet but it's not something I use, so don't have much to input on it.

LINQ? What's that?

 

[Lotok]

Language Integrated Query and a whole other thread.

LINQ (Language-Integrated Query)
101 LINQ Samples | Visual Basic

 

[eno01]

how can i filter data between two dates and to add other filter how name or something else in one button and date and name from textbox

 

[jmcilhinney]

how can i filter data between two dates and to add other filter how name or something else in one button and date and name from textbox

If you have already executed a query and populated a DataTable then you can filter the data in that DataTable via a DataView. Every DataTable is inherently associated with a DataView via its DefaultView property. You can set the RowFilter property of the DataView and then access the filtered data via the DataView. It's important to note that the data in the DataTable is unaffected. Setting the Sort and RowFilter properties of the DataView only affect the data exposed by the DataView. E.g.

myDataTable.DefaultView.RowFilter = String.Format("[Name] LIKE '{0}%' AND [Date] > #{1:M/dd/yyyy}#",
                                                  nameTextBox.Text,
                                                  datePicker.Value)

When you bind a DataTable to one or more controls, it is actually the contents of the DefaultView that gets displayed, so filtering the DefaultView will automatically update what you see in the UI. That said, if you're binding then you should usually use a BindingSource in between. In that case, you should set the Filter property of the BindingSource. You usee exactly the same syntax as for the RowFilter of a DataView.

It's worth noting that the Sort properties of a DataView or BindingSource basically accept a SQL ORDER BY clause while the RowFilter and Filter properties accept a WHERE clause. To learn more about the limited SQL syntax accepted by ADO.NET objects like this, see the MSDN documentation for the DataColumn.Expression property.

 

[eno01]

thankyou jmcilhinney for quick time replay
now i want to show i want to do

I am making a software in vb.net 2010 to control hdmi switch for ps3 from rs232, to get work time of ps, to use this software in ps3 game point
I want to find total value made by operator for this day, time is 24 hour format if time is >00:00 i want to filter between two time 07:00 one day before and 07:00 date for this day, this is one report other is to find total value between two dates and operator name, and all to display in textbox. sorry for my english. Excuse me if i do lots of questions, I do because I am a beginner programmer and with your help will serve more in solving this with problem which forbade my work.

 

[eno01]

how to format date before save in database i want to save in on column date+time in format dd/MM/yyyy HH:mm, how to format datepicker ? please if you can help me