SqlDataAdapter Update Method Returns an Error when a where clause is added to the Select query

Hot Topic

Using System.Net Class
Can not add validation event handler for a Column.
Assembly loading problem in Winlogon, Windows 2000 ..
Calling the GC.Collect method
datasets and direct database connection
WSE 3.0 and ASP.NET 2.0 Membership
connection string + webservice
Easiest way to make a copy of a datatable
How-to establish an efficient cache strategy for DataTable(s)
When to call dispose when using SqlConnection and SqlCommand?

.NET Development

Bad design of ReaderWriterLock ?
FtpWebRequest
ADO: Copy Access Table into new database?
.net 2.0 instable?
Finding what an attribute is applied to
PerformanceCounter specifications ?
DataView question
TableAdapters.....
Is there a Parser class in C#?
Updating a DataSet - UPDATE all columns

SqlDataAdapter Update Method Returns an Error when a where clause is added to the Select query

I'm using .Net 2.0 and Visual Basic 2005 trying to update a SQL2005 Database.  I have created a SqlDataAdapter with a Command builder object in my code.  If I set the Select Command to a Select Statement that doesn't contain a where clause to filter the table I'm querying then after I've modified the table the DataAdapter.Update(myDataSet) method works correctly.  But if I filter the table I'm querying by adding a where clause, when I call the update method, I get the message "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information."  Why does a "where clause" cause that to occur

Answer this question

SqlDataAdapter Update Method Returns an Error when a where clause is added to the Select query

  • CGS

    Hi,

    Could you post the code on where you actuallu add the WHERE clause Coz normally this should work...

     

     

     

    cheers,

    Paul June A. Domag



  • davenitup

    Here is the code I used.

    Dim SQLQuery As String
    Dim Connection As New Data.SqlClient.SqlConnection
    Dim da As New Data.SqlClient.SqlDataAdapter
    Connection.ConnectionString =     "Persist Security Info=False;Integrated Security=SSPI;database=TransActionDatabase;server=Server1"

    'The following query works but when I put a where clause to filter the Table I get the following message.

    ' "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information."

    SQLQuery = "Select ID, Day, Month, Year, DateT from MonthlyTrans"
    da.SelectCommand = New Data.SqlClient.SqlCommand(SQLQuery, Connection)
    Dim TransCB As Data.SqlClient.SqlCommandBuilder = New     Data.SqlClient.SqlCommandBuilder(da)

    Dim Trans As Data.DataSet = New Data.DataSet
    da.FillSchema(Trans, Data.SchemaType.Source,     "MonthlyTrans")
    da.Fill(Trans,     "MonthlyTrans")
    Dim TransRow As Data.DataRow

    For Each TransRow In Trans.Tables("MonthlyTrans").Rows
       SQLQuery =     "#" & CStr(TransRow("Month")) & "/" & CStr(TransRow("Day")) & "/" & CStr(TransRow("Year")) & "#"

    TransRow(4) = CDate(SQLQuery)
    Next

    da.Update(Trans, "MonthlyTrans") 'I get the error message here

  • Fortey


    This was an issue with the July CTP build of SQL Server 2005, where SQL Server was not reporting the base table information when the query included a WHERE clause.  The issue has been resolved in more recent builds.

    David Sceppa
    ADO.NET Program Manager
    Microsoft

  • SqlDataAdapter Update Method Returns an Error when a where clause is added to the Select query
Answer this question