Changing Data Adapter commands

  • Thread starter Thread starter mklapp
  • Start date Start date
M

mklapp

Hello,

The following code 'swaps out' one Selectcommand for
another. Both commands are stored procedures in a SQL
Server 2000 DB. Both commands work in the Query Builder.
The Fill Command does not populate the dataset.

Is there a little error or a profound limitation?

Thanks

Michael Klapp


<WebMethod()> Public Function GetNotThisProvSchool(ByVal
ProvId As Integer) As ProvSchoolDataset
Dim provNotSchool As New ProvSchoolDataset

'First, we use the adapter for the other command, to
retrieve schools not serviced by this provider
SqlProvSchoolAdapter.SelectCommand.CommandText
= "[sp_GetNotThisProviderSchool]"
SqlProvSchoolAdapter.SelectCommand.Parameters.Clear()
SqlProvSchoolAdapter.SelectCommand.Parameters.Add
("@ProviderCode", SqlDbType.Int)
SqlProvSchoolAdapter.SelectCommand.Parameters
("@ProviderCode").Value = ProvId
SqlProvSchoolAdapter.Fill(provNotSchool)

'Now we set the adapter back to the default command, to
retrieve schools serviced by this provider

SqlProvSchoolAdapter.SelectCommand.CommandText
= "[sp_GetThisProviderSchools]"

SqlProvSchoolAdapter.SelectCommand.Parameters.Clear()
SqlProvSchoolAdapter.SelectCommand.Parameters.Add
("@Prov", SqlDbType.Int)
SqlProvSchoolAdapter.SelectCommand.Parameters
("@Prov").Value = ProvId
Return provNotSchool

End Function
 
Hello,
Yes I only call Fill once. In this case, the adapter
has already been used to fill one dataset. Now I need to
populate the other dataset.

Both datasets are based upon the same table. They
have different Selects which together form the set of all
rows in the table.(basically : 'SELECT WHERE' and 'WHERE
NOT EXISTS')

Oh yes, tables(0).rows.count = 0
And I have tried setting the commands with both square
brackets and not square brackets.

Michael Klapp
-----Original Message-----
Hi,

You call Fill only once.
You sure that there is no data returned?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

mklapp said:
Hello,

The following code 'swaps out' one Selectcommand for
another. Both commands are stored procedures in a SQL
Server 2000 DB. Both commands work in the Query Builder.
The Fill Command does not populate the dataset.

Is there a little error or a profound limitation?

Thanks

Michael Klapp


<WebMethod()> Public Function GetNotThisProvSchool (ByVal
ProvId As Integer) As ProvSchoolDataset
Dim provNotSchool As New ProvSchoolDataset

'First, we use the adapter for the other command, to
retrieve schools not serviced by this provider
SqlProvSchoolAdapter.SelectCommand.CommandText
= "[sp_GetNotThisProviderSchool]"
SqlProvSchoolAdapter.SelectCommand.Parameters.Clear ()
SqlProvSchoolAdapter.SelectCommand.Parameters.Add
("@ProviderCode", SqlDbType.Int)
SqlProvSchoolAdapter.SelectCommand.Parameters
("@ProviderCode").Value = ProvId
SqlProvSchoolAdapter.Fill(provNotSchool)

'Now we set the adapter back to the default command, to
retrieve schools serviced by this provider

SqlProvSchoolAdapter.SelectCommand.CommandText
= "[sp_GetThisProviderSchools]"

SqlProvSchoolAdapter.SelectCommand.Parameters.Clear ()
SqlProvSchoolAdapter.SelectCommand.Parameters.Add
("@Prov", SqlDbType.Int)
SqlProvSchoolAdapter.SelectCommand.Parameters
("@Prov").Value = ProvId
Return provNotSchool

End Function
Click to expand...


.
Click to expand...
 
mklapp said:
Hello,
Yes I only call Fill once. In this case, the adapter
has already been used to fill one dataset. Now I need to
populate the other dataset.

Both datasets are based upon the same table. They
have different Selects which together form the set of all
rows in the table.(basically : 'SELECT WHERE' and 'WHERE
NOT EXISTS')

Oh yes, tables(0).rows.count = 0
And I have tried setting the commands with both square
brackets and not square brackets.
Click to expand...

Is there only one table in dataset?
Try passing the table instance to fill method (instead the dataset).
 
Sadly, no, that did not work. I have just given up, and
created a separate data adapter.

Now of course, a new problem has presented itself but I
do not quite know what to make of it.

It seems the Data Adapter Configuration Wizard will not
build a parameter for any column not retrieved in a SQL
statement (Stored Procedure).

I have used non-returned columns as parameter before so I
know SQL can support it. I am not quite as clear about
SQL Server.

Must I retrieve a column to use it in a where clause?
Ughh.

Michael Klapp
 
That other problem is another issue that makes perfect
sense. Thanks for your help.

Mklapp
 
Hi Mklapp,

Please also try to set the CommandType property of the SelectCommand to
StoredProcedure like the following:

SqlProvSchoolAdapter.SelectCommand.CommandType =
CommandType.StoredProcedure;

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top