changing a datasets contents

[Guest]

Hi,

I created a dataset via adding a new item (dataset) to my project.
Following through the wizard I have now complete a data set that contains
data. (select the tables, create the joins and enter the first where clause
(abc=1). This works fine.

My question is that the query that I build in the dataset wizard in my
project, needs to now change its where clause. (select ...... where abc= 1
now needs to be abc=2)

Is there a way to change the datasets select statement ?

How do I do this in my vb.net code ? I can't have a dataset for every
possible where clause, as there are several thoughs values for abc ?

 

[Miro]

If you used the wizard, take a look at the parameter class.

It will create a variable with an @symbol...
If you are using an access table - it will be a question mark.

example on access: (written in notepad)
SELECT Name
FROM Names
Where CustID = ?

or
In SQL ( written in notepad )
SELECT Name
FROM Names
Where CustID = @Name

If you click on your tableadapter and click on Query Builder, you should see
a "Filter" column. That is the column you want to put in the @/? parameter.

So just go to your table, either edit the table adapter you have, or create
a new one and call that one.

Hope that makes sense.

Miro

 

[Heath P. Dillon]

I don't want to do this via the wizard, as I need this to change as the user
uses the program.

I need to be able to change the query via vb.net at run time

Any thoughts ?

 

[Miro]

You will be able to.

Once you setup a query / table adapter with a "@Filter"
then when you call your tableadapter, it takes in parameters....

Create a new project, a dummyhelloworld,
attach a dummy dataset,
right click in the dataset table, and add a new table adapter,
take the table adapter and add a filter,
Nameit something like "helloworldtest"

on the form, when you "drag and drop" the fields on the form, go to the
code, and look for the tableadapter.
Change the tableadapter that was added to the one you just created
"helloworldtest"...
you should see what I mean with the parameters being passed into it.

If you need some screenshot examples - I can create some for you - let me
know.

Miro

 

[Heath P. Dillon]

Hi Miro

Firstly, thanks for your assistance on this.

I have taken your suggestions below, and have managed to get a little
further than before.

I have added a dataset to my project. When I open the dataset, I then add a
table adapter, and define my query etc. I end up with datatable1 above a
datatable1tableadapter. On the datatable1tableadapter I have added a
parameter which now appear along side the fill,getdata in the
datatable1tableadapter

So I think I have the first part correct.

The problem I have is that I want to be able to change the value in my
select statements where clause in my code.

I can't seem to find any way to access the select statement that I created
in the table adapter, nor understand how the parameter I created links to
this query.

Be greatful for any more pointers that help me sort this.

Thanks





I have tried to work through your suggested solutions

 

[Miro]

Ok,

Lets see if this helps you...
I created a HelloWorldDB file with 1 table "Customers"
it has 2 fields
CustID -unique - auto increment
CustName - character 30

I added the dataset to my project,
then opened up the dataset and added another table adapter "query" to the
customers datatable.

It looks like this:
SELECT CustID, CustName
FROM Customers
WHERE (CustID = @CustID)

take note as well what it looks like when you click on the "query builder".
I saved this query as "FillByCustID" "GetDataByCustID".

Ok...so now step 1 is done.

My Customers table in my dataset designer now has TWO table adapters.
Fill,GetData()
and FillByCustID, GetDataByCustID (@CustID)

Do you see the FILL,GetData() "original one"...
right click on it and click on "configure" to view the select statement...
it will look like this:
SELECT CustID, CustName FROM dbo.Customers

click cancel...

Now lets go back to our hello world form.
I drag and drop the datagrid over to the form,
By default this will use the [FILL,GetData] table adapter.
Lets take a look.
View the code on form1, you should see something like this in the
FORM1_LOAD:

Me.CustomersTableAdapter.Fill(Me.HelloWorldDBDataSet.Customers)

Ok, so what is happening here...
the CustomersTableAdapter is calling the "FILL" table adapter portion and
the FILL table adapter grabs all the data.

So...lets change the above line to this:
Me.CustomersTableAdapter.FillByCustID(Me.HelloWorldDBDataSet.Customers, 1)
----OR----
DIM myCustID_ToSearchFor as Integer = 1 'Or whatever value you want.
Me.CustomersTableAdapter.FillByCustID(Me.HelloWorldDBDataSet.Customers,
myCustID)

Do you see now how we are calling the .FILLBYCUSTID table adapter, and not
only that, as soon as you added the comma after
Me.HelloWorldDBDataSet.Customers it wanted the parameter specified for
your table.

So basically - this above code can be added on a command button or whever
you need to - to just "SELECT" the records you want based on a "FILTER"
where a parameter ( @Parm ) is specified in the table adapter.
You can have as many parameters as you like.

That should get you started using the 'wizard' to create a table adapter.

Let me know if the above clarified your issue for you.

Once we get this working, there is one more thing to read up on which is
"SQL Injections", and why we use the @Parameter within the query.
But lets get the top working first.

Let me know,

Miro