Vs 2003 to 2005 SelectCommand Difference

[jdavis2]

Hello all!

First let me get this on the table, I am pretty new to visual studio 2005 and programming in general so please excuse my.... ignorance at times

Anyway I have a pretty general question: I am working from a school book that was based in visual studio 2003, it is asking us to pass a sql statement to a data adapter when a button is clicked. Ok simple enough...

***Little info, basically on the form design screen at the bottom I have the data adapter and dataset components added.***

However in 2003 the specific line of code would be something like

DaDBName.SelectCommand.CommandText = "Select * From .... ect..

However in 2005 there is no such property anymore. Now I did do a ton of looking around and came up with a way to completely setup a adapter and dataset through code. This works fine, but my question is : In 2003 you just drag the database table onto the form and it sets up all the connection strings ect for you, then in code to manipulate these components with a few lines of code. Is this just not how it works in 2005? or am I missing something pretty basic here.

Thanks in advance to any help!

-Jason

 

[cjard]

You are indeed missing something basic: DataAdapter is still in 2005 but it is supplanted by a component called a TableAdapter

In short, in 2005 you will create a dataset, then drag tables into it, from the server explorer. The IDE creates a DataTable and a TableAdapter, and you can tune the SQL that is used for the basic fill - i.e. whatever is the Select Command of the table adapter, is used to derive the schema of the data table. The tableAdapter houses ALL queries that relate to that data table, and it can hold multiple Select, update, insert and deletes for you to call etc

One set of I/U/D statements is special - they are used to send changes back to the database that are made to the datatable by the user. You are familiar with this, probably. I recommend you make a new connection in the Server Explorer, browse it, and drag some table out of it, into a DataSet designer surface. Click on the data table and the Table Adapter that are generated and see tehir properties. Open a windows form and drag items out of the Data Sources window onto the form. Databound controls will be made.

For more information, ask me some specific questions, or have a read of the Data Walkthroughs 2.0 in my signature

Also in my sig is a link to a picture of the dataset designer with some annotations.. i forget what I called it now, something like DataSet Designer Surface .

 

[jdavis2]

Awesome, I dont know why but that just filled in a lot of gaps. Okay so now I can get my TableAdapter to work correctly and apply different queries to it "And actually get this to update on my form ya!!" So my next question is : how do I pass a dynamic query to the table adapter? I now see where the selectcommand/commandtext ect.. is located in the dataset design however I am still in the dark on how to actually pass it a query in code.

commandText = "Select * From tblVehicles Where fldMake = '" & strMake & "';"

right.. but how do I acheive that from the click of a button.

On a side note and do now have a ton of tutorials and examples to read through and may find my answer somewhere in there. Thanks!

 

[cjard]

How dynamic do you want it to be? We dont really write queries like that any mnore, we parameterize them to proitect against SQL injection hacking and things like o'clock or names like o'brien breaking our SQLS

Try this:

Right click TableAdapter
Choose Add Query
Choose Select That Returns Rows
In the command text write one of:

If you are Using MS Access
Select * From tblVehicles Where fldMake = ?

If you are Using SQ Server
Select * From tblVehicles Where fldMake = @make

If you are Using Oracle
Select * From tblVehicles Where fldMake = :make

Call the methods FillByMake/GetDataByMake


now in your code, do:

vehiclesTableAdapter.FillByMake(vehiclesDataSet.VehiclesDataTable, "Ford")

 

[jdavis2]

Now I dont say this to often but I think I love you. I have spent countless hours trying to figure this out. Thanks much!

Edit : But I do have one last little question. Why can I find some documentation on this part of your respone :

If you are Using MS Access
Select * From tblVehicles Where fldMake = ?

If you are Using SQ Server
Select * From tblVehicles Where fldMake = @make

If you are Using Oracle
Select * From tblVehicles Where fldMake = :make

The = ? , = @make ect.... that is the specific part that I was never able to figure out. Thanks again!

 

[cjard]

Why can you find ..

if you meant:
Where can I find...
-> I cant remember now. MSDN did have a page talking about setting up the various queries, I just remembered that OLE requires ? marks, SQLS requires @ and Oracle requires : for parameter demarkers

Why cant i find...
-> er.. its one of those har dto find bits of info, like how to write a Loop in VB - so common that everyone knows so noone writes about it

 

[jmcilhinney]

OLEDB doesn't necessarily "require" the "?". That's a function of the individual provider. The OLEDB provider for SQL Server does require "?" as the parameter place-holder. The Jet OLEDB provider, as used for Access, will accept either unnamed parameters, i.e. "?", or named parameters, e.g. "@ParamName".

There are plenty of examples of using paramaters in ADO.NET all over the Web. Once you know that you have to use the Parameter class that corresponds to your data source then you should read the documentation for that class. The help topic for the OleDbParameter has code examples for both.

 

[cjard]

Funny.. I never succeeded in using named parameters for Access; I always got some error related to bound variables not being set/present/count msimatch. I forget the exact error now, but the only success I had was through using ?. I'll bear it in mind for future use. Thanks J!

 

[jdavis2]

Scratches head... I will dig into the parameter thing later tonight. However a quick question: Im trying to pass

SELECT fldID, fldType, fldMake, fldModel, fldYear, fldPrice, fldDoors, fldHp, fldEngineType
FROM tblVehicles
WHERE (? = ?)

At the Where (? = ?)
I can not pass two parameters? When using query builder it asked for value so I enter fldMake and Ford and it returns nothing. However if I change the first ? to fldMake and then run it, it asks for a value. I put Ford and it returns what I want. I am assuming this has to do with using ?'s and not parameters as jmc suggested. Just would like to know if that is the true reason or if there is just some like character Im missing

 

[TechGnome]

You cant' substitude a parameter for the Field name.... you have to specify WHAT field you want to look in.

That's why when you replace the first ? with fldMake, it works.... but when you try to parameterize it, it doesn't work.

When using parameterized queries, it doesnot do a simple insert & replace..... it's more complicated than that.

-tg

 

[jdavis2]

Okay that makes sense, so then how you I accomplish a dynamic search? Or is the answer to simply just have a different query for each search? My original thought was to have a combo box which the user can select which "field" they would like to search in and then add the criteria to check. I know I could just throw a select case of if then statement in to select a different query for each situation and these queries would then be pretty much re-usable. Just seems more complicated than it needs to be.

Time to hit the books on ADO.Net and sql queries!!

Thanks again guys!

 

[jmcilhinney]

It is usually considered poor practice to use string concatenation to build an SQL statement but if you want to choose a table name or column names dynamically then it may be your best bet. Alternatively you could create a DataTable that contains only the columns you want, then perfrom a query that returns all columns. If you set the DataAdapter's MissingSchemaAction property to Ignore the extra columns will be ignored and your DataTable will wind up with just the data you want. This would not be possible using a typed DataSet and a TableAdapter. You'd have to use an ad hoc DataTable and a DataAdapter.

 

[cjard]

Okay that makes sense, so then how you I accomplish a dynamic search? Or is the answer to simply just have a different query for each search? My original thought was to have a combo box which the user can select which "field" they would like to search in and then add the criteria to check. I know I could just throw a select case of if then statement in to select a different query for each situation and these queries would then be pretty much re-usable. Just seems more complicated than it needs to be.

Time to hit the books on ADO.Net and sql queries!!

Thanks again guys!

You cant use parameters for field names any more than you can compile a VB.NET program with a textbox whose name is a variable:

Dim str As String = "TextBox1"
Dim (str?) As TextBox = New TextBox <-- how would you do this??



To perform a dynamic search you have a few options:

Make the sql dynamically
Make the sql cater for ever eventuality


When asked to make a any-field search facility I chose the first option.
I made a form, where you typed into the field that you wanted to search in.
Then i made an sql fragment and added it to a sql command:
Dim cmd = New OracleCommand("SELECT * FROM cars WHERE 1=1 ")

Then, for every column in the datatable (remember my form controls are bound, and some values have been edited, so my 10 fields in the datatable, maybe 2 have values)

For Each col as DataColumn in myDataTable.Columns
  If Not mySearchDataRow(col.Name).IsNull
    cmd.CommandText += string.Format("AND {0} LIKE :{0}", col.Name)
    cmd.Parameters.Add(...)
  End If
Next col

So for each column in the datatable, i looked up the value in that column of the populated row. if it was filled in, i supplemented the sql command text to include the parameter placeholder, and then i added a parameter of the value (you can work that out

thus for 2 fields filled in o MAKE and MODEL my sql text became:

SELECT * FROM cars WHERE 1=1 AND MAKE LIKE :MAKE and MODEL LIKE :MODEL


and the parameters colelction held values for those 2


So why didnt i just do it with string concat? Because parameters are SAFE from sql injection hacking - heck, if a user even typed an apostrophe like O'CLOCK ina field,, then a basic string concatted sql would fail. Note that even though parameters allow safe addition of SQL commands, they still allow wildcards! If the user types 50% into my SQL up there, then it will find all makes starting with 50 (% is the wildcard on oracle)

SELECT * FROM table WHERE field LIKE arameter
parameter = 'A%'
--> returns all fields starting with A. If you want to search literally for % signs, use the = operator, NOT the LIKE operator




--

The other option you have is to create one sql for all possibilities. this is easier than it sounds, its just UGLY:

SELECT * FROM cars WHERE
(make LIKE :MAKE OR :MAKE IS NULL) AND
(color LIKE :COLOR OR :COLOR IS NULL) AND
(model LIKE :MODEL OR :MODEL IS NULL)

keep going til you have done everything
Then, when you search simply make sure that only the filds to be searched have text and the otehrs are null

How does it work? Suppose you have rows in your database:

ford, blue, explorer
ferrari, red, f350
chevy, red, grand voyager

searching for red in the colour box gives:

SELECT * FROM cars WHERE
(make LIKE NULL OR NULL IS NULL) AND
(color LIKE 'red' OR 'red' IS NULL) AND
(model LIKE NULL OR NULL IS NULL)

think about the where clause.. only 2 rows match that.

I forgot to mention before, anything that is true doesnt affect a where clause when AND is used:

SELECT * FROM people WHERE true = true AND 1 = 1 AND name = fred



its only the last part of the clause that has any effect. Can you see how this can be used to search for anything?