Question Initialising DataAdapter.SelectCommand

[mesiry_4]

Hi It is the first time for me to post a thread because I am a real beginner in VB, I use Visual Basic 2005 Express so I would really appreciate any help in this problem.
The problem is that this message shows when I click the save icon in strip tool in gridview
"The DataAdapter.SelectCommand property needs to be initialized."
and here is my code:

Public Class Form1   
  
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load   
        'TODO: This line of code loads data into the 'HesabatDataSet.Hesabat' table. You can move, or remove it, as needed.   
        Me.HesabatTableAdapter.Fill(Me.HesabatDataSet.Hesabat)   
  
    End Sub   
  
    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click   
        Dim conn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Documents and Settings\Youssef El-Messiry\Local Settings\Application Data\Temporary Projects\Hesabat\Hesabat.mdf""Integrated Security=True;Connect Timeout=30;User Instance=True"  
        Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter   
        Dim sqlcb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(da)   
        da.UpdateCommand = sqlcb.GetUpdateCommand   
        da.InsertCommand = sqlcb.GetInsertCommand   
        da.DeleteCommand = sqlcb.GetDeleteCommand   
        sqlcb.ConflictOption = ConflictOption.CompareRowVersion   
        da.Update(HesabatDataSet.Hesabat)   
        sqlcb.RefreshSchema()   
        da.Update(HesabatDataSet.Hesabat)   
  
    End Sub   
End Class

 

[jmcilhinney]

I don't think that you are the topic of your own thread so please, in future, provide a title that describes the topic of your thread. I've edited it for you this time.

Now, you're getting that error because you haven't created a SelectCommand for your DataAdapter. You normally do that by passing an SQL SELECT statement to the constructor. The CommandBuilder can't build any commands if it doesn't know what data it's supposed to be building them for. It uses the SELECT statement that you specify to determine what table columns to save changes to.

Having said that, you shouldn't be using a DataAdapter at all. You've already got a TableAdapter and you're using that to get the data in the first place. You should be using the same TableAdapter to save the data too. Get rid of all that code in that Click event handler and replace it with this:

Me.HesabatTableAdapter.Fill(Me.HesabatDataSet.Hesabat)

I suggest that you do some reading on TableAdapters at MSDN.

Finally, this is a data access question and we have a Data Access forum. Please post in the most appropriate forum for your topic. Moved.

 

[cjard]

When jmc says to read up on TableAdapters, you can do so in the DW2 link in my signature. FOllow the steps in the section Creating a Simple Data App for starters

 

[mesiry_4]

Tried to fix the problem, but...........

Thanks for Replying. I tried to fix the problem, but a msgbox appears "Update Failed", and I read what concerns this matter in msdn(How to: Save Dataset Changes to a Database)
but still didn't work
Here is the new code

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'HesabatDataSet.Hesabat' table. You can move, or remove it, as needed.
        Me.HesabatTableAdapter.Fill(Me.HesabatDataSet.Hesabat)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Me.Validate()
            Me.HesabatBindingSource.EndEdit()
            Me.HesabatTableAdapter.Update(Me.HesabatDataSet.Hesabat)
            MsgBox("Update Successful")
        Catch ex As Exception
            MsgBox("Update Failed")
        End Try
    End Sub
End Class

 

[jmcilhinney]

If you're getting the Update Failed message then you know that an exception was thrown. The exception is thrown for a reason: to tell you what went wrong. You need to examine the exception object, starting with it's Message property. If that doesn't tell you enough to fix the problem then you need to look deeper.

 

[mesiry_4]

Here is the error message

I removed the try & catch, and this error message showed "Update requires a valid UpdateCommand when passed DataRow collection with modified rows".
Here is the code

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'HesabatDataSet.Hesabat' table. You can move, or remove it, as needed.
        Me.HesabatTableAdapter.Fill(Me.HesabatDataSet.Hesabat)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Validate()
        Me.HesabatBindingSource.EndEdit()
        Me.HesabatTableAdapter.Update(Me.HesabatDataSet.Hesabat)
        MsgBox("Update Successful")

    End Sub
End Class

 

[mesiry_4]

what to wrie in query

if query solves the problem what to write in this query

 

[jmcilhinney]

The most likely problem is that your database table doesn't have a primary key. When the Data Source generates the typed DataSet it cannot generate an UPDATE or DELETE statement if it cannot uniquely identify each row, which is what the primary key is for. Fix your database by ensuring that all your tables have a primary key, then go to Data Sources window and run the configuration wizard again. This should fix the problem.

Hopefully this demonstrates why you need to provide all the relevant information. Whenever an error occurs the IDE provides you with information about it to help you diagnose and fix the problem. If you want us to do that for you then we need that information. In this case, as soon as it was provided a solution was forthcoming. We need a full and clear description of the problem to provide the most appropriate solution. That ALWAYS includes:

1. What you're trying to do.
2. How you're trying to do it, including relevant code.
3. What happens, including error messages.

 

[cjard]

The most likely problem is that your database table doesn't have a primary key

Or it does, but:

1) you did not include it in the selection list or
2) you unticked the box "Generate Insert Update and Delete statements" in the wizard

error messages.

Amen

 

[mesiry_4]

I don't know what to do

I found this box "Generate Insert Update and Delete statements" ticked so I debugged again, updated the rows in the table, then clicked the save icon in the toolstrip menu and, no problem then I close the application and debug again and I found that the update hasn't been applied to the data in the dataset.
Here is the code

Public Class Form1

    Private Sub HesabatBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HesabatBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.HesabatBindingSource.EndEdit()
        Me.HesabatTableAdapter.Update(Me.HesabatDataSet.Hesabat)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'HesabatDataSet.Hesabat' table. You can move, or remove it, as needed.
        Me.HesabatTableAdapter.Fill(Me.HesabatDataSet.Hesabat)

    End Sub
End Class

 

[cjard]

then I close the application and debug again and I found that the update hasn't been applied

Read the DNU link in my signature

 

[mesiry_4]

Thanks for your help

thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!:d:d:d

 

[jmcilhinney]

Change this:

Me.HesabatTableAdapter.Update(Me.HesabatDataSet.Hesabat)

to this:

MessageBox.Show(Me.HesabatTableAdapter.Update(Me.HesabatDataSet.Hesabat)
 & " records saved.")

If you get any value other than zero then the changes were saved. If you're not seeing those changes when you restart your app then it's probably because, by default, your database gets overwritten each time you run your project. Read this:

How to: Manage Local Data Files in Your Project

 

[mesiry_4]

It's ok now but there is a still a problem

I am entering the data in the dataset in arabic language, however it is not shown, it is shown as "?????????" , there is the same number of characters of the entered arabic word but the arabic characters are not shown. So I would appreciate if you would help me.(the prtscreen would explain)

 

[cjard]

Strings in VB are Unicode so they can hold arabic, but probably the database is not set to have unicode on thsi column, so it is losing the information. Make sure your database is using unicode