dataAdapter.SelectCommand property needs to be initialized error msg??

[SJG0526]

I get error 'The dataadapter,selectcommand property needs to be initialized' when this code hits the line that sets the dataadapter.deletecommand. Any ideas what is wrong? The dataset going into this function is not a custom dataset.

Public Overrides Function SaveDataSet(ByVal ds As DataSet) As Integer
Dim DataAdapter As SqlDataAdapter

If TypeOf ds Is CDataSet Then
'/ If this is a custom DataSet, get a reference to the
'/ DataSet's data adapter and store it in the DataAdapter variable
Dim dsSql As CDataSet = CType(ds, CDataSet)
DataAdapter = CType(dsSql.DataAdapter, SqlDataAdapter)
Else
'/ If this is NOT a Custom DataSet, create a new Data Adapter
DataAdapter = New SqlDataAdapter()
End If

Dim CommandBuilder As New SqlCommandBuilder(DataAdapter)
DataAdapter.DeleteCommand = CommandBuilder.GetDeleteCommand()
DataAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand()
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand()

' Update the data in the DataSet
Dim RowsUpdated As Integer = DataAdapter.Update(ds, ds.Tables(0).ToString())

Return RowsUpdated
End Function 'SaveDataSet

 

[RiverGuy]

You need to have a .SelectCommand for the CommandBuilder to work.

 

[SJG0526]

This is puzzling since the routine never had a .SelectCommand line in it and it used to work. The only difference is that I now use the function below to populate the dataset. The dataset is then saved by calling the SaveDataSet function. Could the SelectCommand or something in GetDataSP be interfering somehow? Thanks for your help!

Public Overrides Function GetDataSP(ByVal sSP As String, ByVal o As Object, ByVal CurrentPage As Int16, ByVal PageSize As Int16, ByRef lblTotalPages As Object) As DataSet
'return total # of pages
'Open the connection
Dim Connection As SqlConnection = New SqlConnection(ConnectionString)

Dim myCommand As SqlCommand = New SqlCommand(sSP, Connection)

' Specify that the command type is a stored procedure
With myCommand
.CommandType = CommandType.StoredProcedure
'pass in an array of parameters with their types and values !
.Parameters.Add(New SqlParameter("@CurrentPage", SqlDbType.Int))
.Parameters("@CurrentPage").Value = CurrentPage
.Parameters.Add(New SqlParameter("@PageSize", SqlDbType.Int))
.Parameters("@PageSize").Value = PageSize
.Parameters.Add(New SqlParameter("@TotalRecs", SqlDbType.Int))
.Parameters("@TotalRecs").Direction = ParameterDirection.Output
End With

Connection.Open()

Dim da As New SqlClient.SqlDataAdapter
da.SelectCommand = myCommand
Dim ds As New DataSet
da.Fill(ds)

' Execute the command
'Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()
'o.datasource = myDataReader
o.datasource = ds
o.DataBind()

Connection.Close()
'must close the connection before getting value of parameter or it
'returns nothing
Dim Total_Records As Integer = CInt(myCommand.Parameters("@TotalRecs").Value)
Dim Total_Pages As Decimal = Decimal.Parse(Total_Records.ToString()) / PageSize
lblTotalPages.Text = (System.Math.Ceiling(Double.Parse(Total_Pages.ToString()))).ToString()
Return ds
End Function

 

[RiverGuy]

In your function, you are creating everything as local variables. Therefore, other parts in your program will not reference them.

You could declare your DataAdapter and Commands so they are available everywhere in your form. In your function, set them to new if they have not been instantiated.

 

[SJG0526]

GetDataSP returns the dataset ok. Then the user can modify the data in the dataset and that same dataset is passed as a variable to SaveDataSet.

In SaveDataSet, the command DataAdapter = New SqlDataAdapter() creates the new sqldataadapter. At this point, the dataadapter has nothing associated with it. I believe that's the problem since the CommandBuilder evidently needs a select statement to derive the deletecommand, etc from. If not, what data would it know to use? The dataset isn't doing anything - only if it was a custom dataset. Then it would use the code that derives the dataadapter from the dataset.

Does my logic hold true? It appears that if its not a custom data set, this routine will never work...

 

[RiverGuy]

This is how your program is working:

1. You have a DataSet
2. GetData--You fill the DataSet in #1 with a new DataAdapter and new Command. The DataAdapter and Command Objects are out of scope after the routine ends.
3. SaveData--You have your DataSet from #1. You create a new DataAdapter (non custom). Your program does not know you have filled the DataSet already. It doesn't know you used a Command object to fill it. So you create a new DataAdapter. It is not initialized with a generic Select Command. It throws an exception because it cannot derive Update Commands without a Select Command.

 

[SJG0526]

I'm confused. If I create the dataset with the code below (GetDataSet) instead of GetDataSP, then the same SaveDataSet works great. I don't understand why GetDataSP is different than GetDataSet since they both return a dataset. Is it the type of dataset they are returning that is the problem?

Sorry if I'm being dense! and Thanks for the help!

Public Overrides Function GetDataSet(ByVal command As String, ByVal tableName As String) As DataSet
Dim Connection As IDbConnection = OpenConnection()
Dim oCommand As IDbCommand = CreateCommand(command, Connection)
Dim ds As New CDataSet()
ds.DataAdapter = New SqlDataAdapter(CType(oCommand, SqlCommand))
ds.DataAdapter.Fill(ds, tableName)
Return ds
End Function 'GetDataSet