locked
selectcommand that does not return any base table information RRS feed

  • Question

  • sir,

     I have problem to update the database to server from form then i get the exception that is

    """selectcommand that does not return any base table information """

    my code is below 

    Imports System.Data.SqlClient

    Public Class Form1

        Dim connetionString As String

        Dim connection As SqlConnection

        Dim adapter As SqlDataAdapter

        Dim cmdBuilder As SqlCommandBuilder

        Dim ds As New DataSet

        Dim changes As DataSet

        Dim sql As String

        Dim i As Int32



        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

            connection = New SqlConnection(connetionString)

            Sql = "nested query"

            Try

                connection.Open()

                adapter = New SqlDataAdapter(Sql, connection)

                adapter.Fill(ds)

                connection.Close()

                DataGridView1.Data Source= ds.Tables(0)

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub



        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Try

                cmdBuilder = New SqlCommandBuilder(adapter)

                changes = ds.GetChanges()

                If changes IsNot Nothing Then

                    adapter.Update(changes)

                End If

                MsgBox("Changes Done")

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub

    End Class

      please help me

    Saturday, July 21, 2012 9:30 AM

All replies

  • You didn't specify which line porcudes the error.  Some here are a few observations that I can make looking at your code

    1) You have to run the code from button 1 before button 2.  button one connects to the database while button 2 doesn't.  Every time you restart the program you must run the button 1 code before the button 2 code.

    2) You are connecting to a table in the data base called "DatabaseName" from the line following line of code Catalog=DatabaseName.

    3) You are running a macro in the data base called "nested query".  the nested query should be running on the table DatabaseName and have priveledge to run under tha account User ID=UserName;Password=Password"


    jdweng

    Saturday, July 21, 2012 10:53 AM
  • Dear Sir/Mam ,

    I used the update method of Adapter , then from this line i get the Exception "SelectCommand that does not return any base table information"

    my code is below, this code get the data successfully but at the time of update we have the problem

                   

    Imports System.Data.SqlClient

    Public Class Form1

        Dim connetionString As String

        Dim connection As SqlConnection

        Dim adapter As SqlDataAdapter

        Dim cmdBuilder As SqlCommandBuilder

        Dim ds As New DataSet

        Dim changes As DataSet

        Dim sql As String

        Dim i As Int32



        Private Sub BtnGetData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnGetData.Click

            connetionString = "Data Source=i7;Initial Catalog=baheti"

            connection = New SqlConnection(connetionString)

            Sql = "xxxxxxx"   // i used here nested select Query of two tables , both table have the                                  same primary key

            Try

                connection.Open()

                adapter = New SqlDataAdapter(Sql, connection)

                adapter.Fill(ds)

                connection.Close()

                DataGridView1.Data Source= ds.Tables(0)

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub



        Private Sub Btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnupdate.Click

            Try

                cmdBuilder = New SqlCommandBuilder(adapter)

                changes = ds.GetChanges()

                If changes IsNot Nothing Then
                    adapter.Update(change)       //this line throw the exception



                End If

                MsgBox("Changes Done")

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub

    End Class

    • Moved by Ego Jiang Monday, July 23, 2012 6:41 AM it is a vb issue (From:Visual Studio Extensibility)
    • Merged by Mike FengModerator Monday, July 23, 2012 10:06 AM duiplicate
    Saturday, July 21, 2012 1:14 PM
  • Dear sir.,

    Connection is ok,and I get the Data successfully and also update the data successfully when in the select query have the only one table withn primary key and when i used the select query of combination of two table then the exception  , i get 

    So, Please sir, According to my project i wana used the selct query of combination of two table and update both table

    Imports System.Data.SqlClient

    Public Class Form1

        Dim connetionString As String

        Dim connection As SqlConnection

        Dim adapter As SqlDataAdapter

        Dim cmdBuilder As SqlCommandBuilder

        Dim ds As New DataSet

        Dim changes As DataSet

        Dim sql As String

        Dim i As Int32



        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

            connection = New SqlConnection(connetionString)

            Sql = "select * from Product"

            Try

                connection.Open()

    adapter = New SqlDataAdapter(Sql, connection)    this line throw the exception

                adapter.Fill(ds)

                connection.Close()

                DataGridView1.Data Source= ds.Tables(0)

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub



        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Try

                cmdBuilder = New SqlCommandBuilder(adapter)

                changes = ds.GetChanges()

                If changes IsNot Nothing Then

                    adapter.Update(changes)

                End If

                MsgBox("Changes Done")

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub

    End Class

                         Thanx for your help

    Monday, July 23, 2012 6:05 AM
  • You didn't specify which line porcudes the error.  Some here are a few observations that I can make looking at your code

    1) You have to run the code from button 1 before button 2.  button one connects to the database while button 2 doesn't.  Every time you restart the program you must run the button 1 code before the button 2 code.

    2) You are connecting to a table in the data base called "DatabaseName" from the line following line of code Catalog=DatabaseName.

    3) You are running a macro in the data base called "nested query".  the nested query should be running on the table DatabaseName and have priveledge to run under tha account User ID=UserName;Password=Password"


    jdweng

    Dear sir.,

    Connection is ok,and I get the Data successfully and also update the data successfully when in the select query have the only one table withn primary key and when i used the select query of combination of two table then the exception  , i get 

    So, Please sir, According to my project i wana used the selct query of combination of two table and update both table

    Imports System.Data.SqlClient

    Public Class Form1

        Dim connetionString As String

        Dim connection As SqlConnection

        Dim adapter As SqlDataAdapter

        Dim cmdBuilder As SqlCommandBuilder

        Dim ds As New DataSet

        Dim changes As DataSet

        Dim sql As String

        Dim i As Int32



        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

            connection = New SqlConnection(connetionString)

            Sql = "select * from Product"

            Try

                connection.Open()

    adapter = New SqlDataAdapter(Sql, connection)    this line throw the exception

                adapter.Fill(ds)

                connection.Close()

                DataGridView1.Data Source= ds.Tables(0)

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub



        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Try

                cmdBuilder = New SqlCommandBuilder(adapter)

                changes = ds.GetChanges()

                If changes IsNot Nothing Then

                    adapter.Update(changes)

                End If

                MsgBox("Changes Done")

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

        End Sub

    End Class

                         Thanx for your help


    Monday, July 23, 2012 6:08 AM
  • Hi Parful,

    Thank you for your post.
     
    Based on this case relate to VB, I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
     
    Thank you for your understanding and support.

    Best regards,


    Ego [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 23, 2012 6:40 AM
  • Here is a few things to try

    1) Make sure the following line is returing a valid connection

     connection = New SqlConnection(connetionString)

    Add connection to the watch window (or hover over the variable) and make sure it is not returning a null

    2) Try changing the connection string below.  From your SQL statement youi are trying to use the Product table (not DatabaseName).  After changing the connection string go back and verify that the connection string isn't returning a null. 

    From : connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

    To: connetionString = "Data Source=ServerName;Initial Catalog=Product;User ID=UserName;Password=Password"


    jdweng

    Monday, July 23, 2012 8:39 AM
  • Can you show us the SQL Query or at least a skeleton?

    The update is controlled by the Update Command that is on the Data Adapter. Since you have multiple tables involved in the Select Command, Ado.net cannot automatically generate the Update Command because the Dataset itself doesn't automatically know how to match your columns and the parameters in your Update Command.

    But you can do it by supplying a custom UpdateCommand Command object to Adopter before you call Update. A sample code snippet is as below.

    Dim command As SqlCommand = New SqlCommand
     ("UPDATE Customers SET 
    	CustomerID = @CustomerID,
    	CompanyName = @CompanyName " & _
           	"WHERE 
    	CustomerID = @oldCustomerID", connection))
    adapter.UpdateCommand = command
     adapter.Update

    Your issue is bit similar to an Updatable SQL View. (You can ask your self can I Delete/update a view which joins two tables.) .  The Answer is,

    You cannot delete a row if the view references more than one base table. You can only update columns that belong to a single base table. Refer: http://msdn.microsoft.com/en-us/library/ms180800.aspx or http://stackoverflow.com/questions/10531785/update-sql-view-using-tableadapter-in-vbnet-2010

    The correct way of implementing is populate all tables independently to a Dataset and apply a Relationship through ado.net and update the rows.

    We have a specialized forum for Ado.net @ ADO.NET DataSet or ADO.NET Managed Providers where some experts can give you prompt answer.



    Lingaraj Mishra

    Monday, July 23, 2012 2:27 PM