473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

dataset and where clause

I am playing around with making a dataset with the designer in vb.net
2005. I realize you can add parameters to select statement and then
filter off of those parameters. What I would like to do is to take the
dataset that the designer made and add the whole where clause to it at
run time. Is this possible?

Jun 26 '06 #1
6 10882
Altman,

I don't think so, the designer builds beside the Select as well the Update,
the Insert and the Deletecommands.

Those are more complex than the standards, because for concurrencychecking
are the old rows first checked against the last in the database.

If you want to do things like you ask, than you should in my opinion forget
the designer and do everything in code.

Just my idea.

Cor

"Altman" <ba*****@easy-automation.com> schreef in bericht
news:11**********************@c74g2000cwc.googlegr oups.com...
I am playing around with making a dataset with the designer in vb.net
2005. I realize you can add parameters to select statement and then
filter off of those parameters. What I would like to do is to take the
dataset that the designer made and add the whole where clause to it at
run time. Is this possible?

Jun 26 '06 #2
Thanks for the reply, the reason I would like to do this with the
designer is I plan to use the rdlc. In order to make the rdlc I need a
dataset to design it off of. But as I said before I would like to set
the where clause programatically. I know I can filter the dataset
table but I would like to filter it on the select statement to speed
things up.

Jun 26 '06 #3
I think you are confusing dataset with dataAdapter. The dataAdapter is the
component that contains/uses the sql statements. You have the 4 basic sql
commands "Select", "Insert", "Update", "Delete" that are part of the
dataAdapter. Note: it is way better/easier to write out the
dataAdapters/datasets than to use the designer. Here is a sample:

Imports System.Data.SqlClient

sub something()
Dim da As sqlDataAdapter, ds As Dataset, conn As Sql Connection
conn = New SqlConnection
conn.ConnectionString = "Data
Source=yourserver;UID=yourID;PWD=yourpassword;DATA BASE=someDB"
da = New sqlDataAdapter
ds = New Dataset
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * from tbl1 Where RecordID = @ID"
da.SelectCommand.Parameters.Add(New SqlParameter("@ID", SqlDBType.Int, 4,
"RecordID")

da.SelectCommand.Parameter("@ID").Value = txtRecordID.Text

da.Fill(ds, "tbl1")
Datagridview1.Datasource = ds.Tables("tbl1")
End Sub

Now your dataset contains a table called "tbl1" which is a copy of the table
from the database -- except in memory and gets displayed in the datagridview.

HTH,
Rich

"Altman" wrote:
I am playing around with making a dataset with the designer in vb.net
2005. I realize you can add parameters to select statement and then
filter off of those parameters. What I would like to do is to take the
dataset that the designer made and add the whole where clause to it at
run time. Is this possible?

Jun 26 '06 #4
I do understand the difference between a dataadatper and a dataset. I
am trying to make a rdlc report and as far as I can tell, I cannot use
a dataset that is created programatically. Therefore I was using the
designer to make a dataset. Obviously the dataset that is created with
the designer must create it's own dataadapter to pull the data in. I
basically just need to access this adapter, and either change the sql
select or at the very least obtain what the sql select is. Unless
someone knows of an easier way that I can design a rdlc and be able to
set the where clause programatically.

Jun 26 '06 #5
You can use the CommandBuilder object to build the update, insert and delete
commands as necessary based on a select command. Naturally, you will suffer
some performance implications in doing this. See http://msdn.microsoft.com/library/de...andbuilder.asp.
The CommandBuilder is used by the VS designer to create the commands. I would
recommend NOT using it in a production application, but rather have more
fine-grained control of your environment.

Per the original question, you should be able to check the DataAdapter.SelectCommand.CommandText
to manipulate the SQL being sent. One thing to remember: use parameterized
queries not string concatenation to avoid SQL Injection.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx
Altman,

I don't think so, the designer builds beside the Select as well the
Update, the Insert and the Deletecommands.

Those are more complex than the standards, because for
concurrencychecking are the old rows first checked against the last in
the database.

If you want to do things like you ask, than you should in my opinion
forget the designer and do everything in code.

Just my idea.

Cor

"Altman" <ba*****@easy-automation.com> schreef in bericht
news:11**********************@c74g2000cwc.googlegr oups.com...
I am playing around with making a dataset with the designer in vb.net
2005. I realize you can add parameters to select statement and then
filter off of those parameters. What I would like to do is to take
the dataset that the designer made and add the whole where clause to
it at run time. Is this possible?

Jun 26 '06 #6
Bob
When you create a bound datagridview on a form you will see that doing that
creates three objects a dataset a binding source and a table adatapter. In
the binding source you will find a property called filter. This is basically
where you can enter a value that is everything you would find in a where
clause except the Where keyword
In your code you can write a sequence that does the following
1- clears the dataset or maybe just the datatable you want to use
2- Sets the filter on the bindingsource
3- Fill the tabledapter(s)
Lets say you have dataset1, that would give you Bindingsource1 and
datatable1 and your sql table itself is named Table1

You would do something like
dataset1.clear 'This clears all the data from all the rows in the dataset
If you want to be able to refill just a single table one at a time, you can
set the tableadapter's ClearBeforeFill property to true.
When you do that each time you call for a fill on a tableadapter it will
automatically clear that table adapter's content.

BindingSource1.filter = "Myfield1 = 'a string' and Myfield2 = 2"
TableAdapter1.fill(dataset1.table1)

You can put this in the form load event (in which case you don't need the
dataset1.clear statement) or you can do this in any event like a button
click. I spoke about the datagridview but basically any bound control on
your form will need to use the same three objects, dataset bindingsource and
table adapter.

HTH

Bob


"Altman" <ba*****@easy-automation.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...
I am playing around with making a dataset with the designer in vb.net
2005. I realize you can add parameters to select statement and then
filter off of those parameters. What I would like to do is to take the
dataset that the designer made and add the whole where clause to it at
run time. Is this possible?

Jun 26 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: John Holmes | last post by:
I have a web interface where the user types in ID's one at a time. After an ID is typed in, a button is clicked and the button click event has code that does a query and returns a data reader and...
4
by: Simon | last post by:
Hi all, I have a process, where I take a dataset from an SQL call, and need to write an XML file from that dataset. The data set can contain 10's of tables, each with 100's of rows, and I have...
1
by: msnews.microsoft.com | last post by:
Hi All, When I use a SQL Query to Fill the Dataset if I use the following Where Clause ( > '100' ) Then I am getting a 'Data Type Mismatch Error' because the Field Type is double.
1
by: brian | last post by:
If I create a dataset called "Test" can I run SQL code against that dataset? If so How? Can a dataset be cached? If so how would you run a query on that? The reason I am asking is because...
3
by: jason | last post by:
I'll try to explain in detail what it is I'm struggling with. I created a SQL database with one table called CallSheet which contains the following columns: Caller ID: <--Primary Key, auto...
2
by: Peter Van Wilrijk | last post by:
Hi, In VB6 I used a method requery to repopulate a recordset. I don't find an equivalent for the .NET dataset. I don't update my datasets at all, but I use them to navigate through subsets...
4
by: Joris De Groote | last post by:
Hi, (my code is @ the bottom of this message) I have a piece of code that runs the rows of a dataset until it finds the one needed. When he finds it, I delete that row and start all over...
11
by: Sid Price | last post by:
I have Dataset that I need to filter into another Dataset for display and I can not find out how to do this. I have found methods for copying a Dataset but I can not see how to filter the rows...
2
by: Shum | last post by:
Hi! i have a question.. I'm filling a dataset from a table in which some rows are empty, i dont want those empty records to be filled in the dataset.. Does any one know how to restrict it to only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.