SQL Command Question

[jasonp45]

What is the difference between the SqlClient.SqlCommand object and the SqlDataAdapter.SelectCommand object? For instance, if I want to retrieve some rows from a table, the following code works (after instancing connection, command, table, and data-adapter objects):

****************************************
sQueryText = "SELECT * FROM Table"
SQLCatalog = "MyDB"
Server_or_FileName = "MyServer"
With oCn
.ConnectionString = "Initial Catalog=" & SQLCatalog
& ";" & _
"Data Source=" & Server_or_FileName & ";" & _
"Integrated Security=SSPI"
.Open()
End With
oCmd.CommandText = sQueryText
With oDataAdapter
.SelectCommand = oCmd
.SelectCommand.Connection = oCn
.Fill(oTable)
End With
****************************************

But why doesn't this seem to work?:

****************************************
.
.
.
With oDataAdapter
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = sQueryText
.SelectCommand.Connection = oCn
.Fill(oTable)
End With
****************************************

Why can't I use the DataAdapter.SelectCommand without first assigning a separate instance of the SQLClient.Command object?

 

[CulpritCZ]

With SQLClient.SQLCommand you retrieve only data from a database.
With SQLDataAdapter you can fill DataSets and so

 

[RealQuiet]

Think of it like this:
SelectCommand is a property of the DataAdapter that exposes a command object inside the DataAdapter Object.
Your code doesn't work because that internal command object hasn't been instantiated. If you instantiate it, you can use the DataAdapter without creating a separate command object. Like this:

With oDataAdapter
.SelectCommand = New SqlClient.SqlCommand
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = sQueryText
.SelectCommand.Connection = oCn
.Fill(oTable)
End With

You could also do the same with the Connection object of the SelectCommand.

 

[Zarcom]

Check here for an explanation
thread796-389653 That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540