Creating Query

dserg

New member
Joined
Oct 13, 2005
Messages
3
Programming Experience
Beginner
Hi,
I am having problems with two queries that I am trying to create.

I am using VB.NET and have made a connection to ADO.NET.

First, I have a form that has one label that must display the total number of records in a database once the search button is pressed.

Second, I have a form with a combo box to select an item and then a search button takes you to a results form that must display in datagrid.

Does anyone have any examples of code. I am not sure as to the structure of the query and how to establish connection . I know the first is COUNT(*) VCode FROM VIDEO- I think- The other a select statement. My biggest problem is not knowing where to place the query and link the dataadapter in code to retrive the query.

Thanks

Kristy
 
Can you please port some code how did you do the connection... By the way, the query should be something like:
VB.NET:
SELECT COUNT(*) FROM VIDEO;
or
VB.NET:
SELECT COUNT(VCode) AS COUNT_RESULT FROM VIDEO;
 
Title

Hi Kristy,
You have two choices when you are creating the Connection object in .NET. You can do so declaratively by using the visual tools provided with Visual Studio .NET or SQL Server 2000. These visual tools enable you use point-and-click operations to specify the database you want to connect to, and they prepare the appropriate Connection object for you. Alternately, you can opt to create the Connection object programmatically.
Anyway, a client application performs the following when retrieving data:
  • Establishes a connection to the database
  • Executes commands against the database
  • Maps database properties to data results
  • Stores data results
ADO.NET's basic objects correspond to these operations, and they are appropriately named Connection, Command, and DataAdapter. Both DataSets and DataReaders store or relay data results, respectively.
It's up to you and your decision which one to use under which circumstances. However it might be more economical for you to use the DataReader object instead of a DataSet. Using the DataReader can be faster than populating the DataSet, and would be appropriate in situations when you need read-only data.
The DataReader object is the heart of ADO.NET. I would mention one of the features and that's it that DataReaders stream data, rather than retrieving it from an inmemory cache as do DataSets.

Well, let’s make something concrete here (I’m assuming you have SQL Server installed on the local machine):

The ConnectionString property is a long string with several attributes separated by
semicolons. Add the following line to your button's click event to set the connection (you can add the this code to any other event as load event is):
VB.NET:
conn.ConnectionString="data source=(local);"& _
"initial catalog=MyDatabase;integrated security=SSPI;"

Replace the data source value with the name of your SQL Server, or keep the local
setting if you are running SQL Server on the same machine. If you aren't using Windows
NT integrated security, then set your user ID and password like so:
VB.NET:
conn.ConnectionString="data source=(local);"& _
"initial catalog= MyDatabase; user ID=sa;password=xxx"


note:Some of the names in the connection string also go by aliases. You can use Server instead of data source to specify your SQL Server. Instead of initial catalog, you can specify database.


One of the nice things about the .NET Framework is that it supports constructor arguments by using overloaded constructors. You might find this useful for creating your ADO.NET objects, such as your database Connection. As a shortcut, instead of using the ConnectionString property, you can pass the string right into the constructor, as such:
VB.NET:
Dim conn as New SqlConnection _
("data source=localhost; initial catalog=Northwind; user ID=sa;password=xxx")
Or you could overload the constructor of the connection string by using the following:
VB.NET:
Dim myConnectString As String = "data source=localhost; initial
catalog=Northwind; user ID=sa;password=xxx"

After you have specified the ConnectionString property of the Connection object, you must call the Open() method to establish a connection to the database. You must first specify the ConnectionString property and then call the Open() method without any arguments, as shown here ( conn is the name of a Connection object):
conn.Open()


note: Always use the Connection object's Close() method to close an open connection. It is good practice if you put Close() method in finally part of try statement. In this way you are sure that connection will be closed no matter what as code after finally statement is always executed.

After you instantiate your connection, you can use the Command object to execute commands that retrieve data from your data source. The Command object carries information about the command to be executed. This command is specified with the control's CommandText property. The CommandText property can specify a table name, an SQL statement, or the name of an SQL Server stored procedure. To specify how ADO will interpret the command specified with the CommandText property, you must assign the proper constant to the CommandType property. You have three available enumerated values for CommandType: Text, StoredProcedure and TableDirect.
After you have connected to the database, you must specify one or more commands to execute against the database. A command could be as simple as a table's name, an SQL statement, or the name of a stored procedure.

An example about Commandtext:
VB.NET:
strCmdText = "SELECT ALL FROM myTable"
strCmdText = strCmdText & "WHERE myField = 'someValue'"
cmdCustomers.CommandText = strCmdText
cmdCustomers.CommandType = CommandType.Text
Finally is time to execute the query:
Means you specify the command to execute against the database with the Command object.
Btw, the Command objects have several methods for execution: the ExecuteReader() method returns a forward-only, read-only DataReader, the ExecuteScalar() method retrieves a single result value, and the ExecuteNonQuery() method doesn't return any results.

In your case it should be ExecuteNonQuery() method because you want to calculate aggregates …


note: SQL can calculate aggregates on selected fields. An aggregate is a function that counts rows, calculates sums and averages, and performs a few more common math operations used to summarize data. Aggregate functions accept field names (or calculated fields) as arguments, and they return a single value, which is the sum (or average) of all values.
More precisely COUNT() returns the number (count) of values in a specified column or table.
The COUNT() statement ignores the Null values, unless you specify the * argument. The asterisk indicates that you want to include the Null values. Such statement will return the count of all rows in the target table, even if some of them have a Null value. If you want to count unique values, you must use the DISTINCT keyword along with the name of the field to count i.e. SELECT COUNT(DISTINCT myField) AS totalNumber FROM myTable.



HTH
Regards ;)
 
top post kulrom :)
 
Back
Top