[RESOLVED] Visual Studio 2005 - Modifying/Deleting Row in the table

[Siddharth Rout]

Hi

I am getting an error while updating a table after modifications...

Can anyone see what exactly is the problem. It gives an error message

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." at the place indicated below...

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

        'opening databse
        Dim Con As New OleDb.OleDbConnection

        Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Srout\Desktop\Reviews\Reviews\Reviews\table\Review.mdb;Persist Security Info=True;Jet OLEDB:Database Password=magicword"

        Con.Open()

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT EmpId, FullNm, RepsTo, MgcWrd, Levell, Statuss, Desg FROM Review"
        da = New OleDb.OleDbDataAdapter(sql, Con)

        da.Fill(ds, "Review")

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        'Checkrw is the row where the data has to be inserted. It give me a value betwenn 0 and 8
        'as required. i am okay with the value of checkrw.
        'MsgBox(checkrw)
        ds.Tables("Review").Rows(checkrw).Item(0) = Trim(TextBox1.Text) 'Emp ID
        ds.Tables("Review").Rows(checkrw).Item(1) = Trim(TextBox2.Text) 'FullNm
        ds.Tables("Review").Rows(checkrw).Item(2) = Trim(ComboBox1.Text) 'RepsTo
        ds.Tables("Review").Rows(checkrw).Item(3) = "CHANGEME" 'MgcWrd
        ds.Tables("Review").Rows(checkrw).Item(4) = Mid(Trim(ComboBox2.Text), 7, 1) 'Levell

        If RadioButton1.Checked = True Then
            ds.Tables("Review").Rows(checkrw).Item(5) = "Active" 'Status
        Else
            ds.Tables("Review").Rows(checkrw).Item(5) = "InActive" 'Status
        End If

        ds.Tables("Review").Rows(checkrw).Item(6) = Trim(ComboBox3.Text) 'Desc

        da.Update(ds, "Review") '<===== ERROR HERE....

        MsgBox("User Details have been modified Successfuly")
        Con.Close()
        Me.Close()
    End Sub

[Siddharth Rout]

Also while deleting i am getting the same error?

Here is the code...

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'opening databse
        Dim Con As New OleDb.OleDbConnection

        Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Srout\Desktop\Reviews\Reviews\Reviews\table\Review.mdb;Persist Security Info=True;Jet OLEDB:Database Password=magicword"

        Con.Open()

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT EmpId, FullNm, RepsTo, MgcWrd, Levell, Statuss, Desg FROM Review"
        da = New OleDb.OleDbDataAdapter(sql, Con)

        da.Fill(ds, "Review")

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("Review").Rows(checkrw).Delete()

        da.Update(ds, "Review") '<===== ERROR HERE
        MsgBox("User Details have been erased Successfuly")
        Con.Close()
        Me.Close()
    End Sub

[Asgorath]

Hi

I am getting an error while updating a table after modifications...

Can anyone see what exactly is the problem. It gives an error message

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." at the place indicated below...

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

        'opening databse
        Dim Con As New OleDb.OleDbConnection

        Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Srout\Desktop\Reviews\Reviews\Reviews\table\Review.mdb;Persist Security Info=True;Jet OLEDB:Database Password=magicword"

        Con.Open()

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT EmpId, FullNm, RepsTo, MgcWrd, Levell, Statuss, Desg FROM Review"
        da = New OleDb.OleDbDataAdapter(sql, Con)

        da.Fill(ds, "Review")

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        'Checkrw is the row where the data has to be inserted. It give me a value betwenn 0 and 8
        'as required. i am okay with the value of checkrw.
        'MsgBox(checkrw)
        ds.Tables("Review").Rows(checkrw).Item(0) = Trim(TextBox1.Text) 'Emp ID
        ds.Tables("Review").Rows(checkrw).Item(1) = Trim(TextBox2.Text) 'FullNm
        ds.Tables("Review").Rows(checkrw).Item(2) = Trim(ComboBox1.Text) 'RepsTo
        ds.Tables("Review").Rows(checkrw).Item(3) = "CHANGEME" 'MgcWrd
        ds.Tables("Review").Rows(checkrw).Item(4) = Mid(Trim(ComboBox2.Text), 7, 1) 'Levell

        If RadioButton1.Checked = True Then
            ds.Tables("Review").Rows(checkrw).Item(5) = "Active" 'Status
        Else
            ds.Tables("Review").Rows(checkrw).Item(5) = "InActive" 'Status
        End If

        ds.Tables("Review").Rows(checkrw).Item(6) = Trim(ComboBox3.Text) 'Desc

        da.Update(ds, "Review") '<===== ERROR HERE....

        MsgBox("User Details have been modified Successfuly")
        Con.Close()
        Me.Close()
    End Sub

You are not calling OleDbCommandBuilder.GetUpdateCommand() to generate the correct command.

[Asgorath]

Also while deleting i am getting the same error?

Here is the code...

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'opening databse
        Dim Con As New OleDb.OleDbConnection

        Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Srout\Desktop\Reviews\Reviews\Reviews\table\Review.mdb;Persist Security Info=True;Jet OLEDB:Database Password=magicword"

        Con.Open()

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT EmpId, FullNm, RepsTo, MgcWrd, Levell, Statuss, Desg FROM Review"
        da = New OleDb.OleDbDataAdapter(sql, Con)

        da.Fill(ds, "Review")

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("Review").Rows(checkrw).Delete()

        da.Update(ds, "Review") '<===== ERROR HERE
        MsgBox("User Details have been erased Successfuly")
        Con.Close()
        Me.Close()
    End Sub

You are NOT calling OleDBCommandBuilder.GetDeleteCommand()

[Siddharth Rout]

You are not calling OleDbCommandBuilder.GetUpdateCommand() to generate the correct command

Thanks Asgorath for the reply....

I am sorry, I am just trying my hand with VS 2005.

Can you explain or give an example of what you mean?

[Asgorath]

See if this works ok... Update Row

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
'opening databse 
Dim Con As New OleDb.OleDbConnection 
Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Srout\Desktop\Reviews\Reviews\Reviews\table\Review.mdb;Persist Security Info=True;Jet OLEDB:Database Password=magicword" 
Con.Open() 
Dim ds As New DataSet 
Dim da As OleDb.OleDbDataAdapter 
Dim sql As String 
sql = "SELECT EmpId, FullNm, RepsTo, MgcWrd, Levell, Statuss, Desg FROM Review" 
da = New OleDb.OleDbDataAdapter(sql, Con) 
da.Fill(ds, "Review") 
Dim cb As New OleDb.OleDbCommandBuilder(da) 
'Checkrw is the row where the data has to be inserted. It give me a value betwenn 0 and 8 'as required. i am okay with the value of checkrw. 
'MsgBox(checkrw) 
ds.Tables("Review").Rows(checkrw).Item(0) = Trim(TextBox1.Text) 'Emp ID 
ds.Tables("Review").Rows(checkrw).Item(1) = Trim(TextBox2.Text) 'FullNm 
ds.Tables("Review").Rows(checkrw).Item(2) = Trim(ComboBox1.Text) 'RepsTo 
ds.Tables("Review").Rows(checkrw).Item(3) = "CHANGEME" 'MgcWrd 
ds.Tables("Review").Rows(checkrw).Item(4) = Mid(Trim(ComboBox2.Text), 7, 1) 'Levell 
If RadioButton1.Checked = True Then 
    ds.Tables("Review").Rows(checkrw).Item(5) = "Active" 'Status 
Else 
    ds.Tables("Review").Rows(checkrw).Item(5) = "InActive" 'Status 
End If 
ds.Tables("Review").Rows(checkrw).Item(6) = Trim(ComboBox3.Text) 'Desc 
 
da.UpdateCommand = cb.GetUpdateCommand()
MessageBox.Show(da.UpdateCommand.CommandText)
da.Update(ds, "Review") '<===== ERROR HERE.... 
MsgBox("User Details have been modified Successfuly") 
Con.Close() 
Me.Close() 
End Sub

[Siddharth Rout]

Hi Asgorath

I get the same error but now at these lines respectively...

da.UpdateCommand = cb.GetUpdateCommand() 'in the update sub

'and

da.UpdateCommand = cb.GetDeleteCommand() 'in the delete sub

[jmcilhinney]

You're not supposed to call GetDeleteCommand, GetInsertCommand or GetUpdateCommand. The CommandBuilder generates the required commands implicitluy when and as needed. The only reason to calls those methods explicitly is if you want to make come changes to the Command objects they create, like enlisting them in a transaction. If you don't need to make any changes then DO NOT call those methods.

The problem is that your SelectCommand doesn't return any primary key information. The CommandBuilder needs a way to uniquely identify each record in order to generate the appropriate SQL statements. Without a primary key it can't do that. Change the MissingSchemaAction property of your DataAdapter from the default Add to AddWithKey.

[Siddharth Rout]

The problem is that your SelectCommand doesn't return any primary key information. The CommandBuilder needs a way to uniquely identify each record in order to generate the appropriate SQL statements. Without a primary key it can't do that. Change the MissingSchemaAction property of your DataAdapter from the default Add to AddWithKey.

What would I do without you????????

What does this mean? "Change the MissingSchemaAction property of your DataAdapter from the default Add to AddWithKey."

[jmcilhinney]

If I said "Change the Text property of your TextBox from the default empty string to 'Hello World'" what would you do?

The MissingSchemaAction property defines the action taken by the DataAdapter when it encounters missing schema information in the target DataTable. Add means that when you call Fill it will add any missing schema information, i.e. columns, to the target DataTable. If you change it to AddWithKey then it will, as the name suggests, add primary key information too, thus allowing your CommandBuilder to generate SQL code that uniquely identifies each record as required.

[Siddharth Rout]

If I said "Change the Text property of your TextBox from the default empty string to 'Hello World'" what would you do?

The MissingSchemaAction property defines the action taken by the DataAdapter when it encounters missing schema information in the target DataTable. Add means that when you call Fill it will add any missing schema information, i.e. columns, to the target DataTable. If you change it to AddWithKey then it will, as the name suggests, add primary key information too, thus allowing your CommandBuilder to generate SQL code that uniquely identifies each record as required.

Hi I am very new to the concept of DataAdapters [4 days old ]. Had it been Vb6, I wouldn't have had a problem in doing what I wanted....

The link(home & learn) which you had given me did a wonderful job of explaining everything to me but it didn't talk about 'DataAdapters' as an object which i could Right Click and change the property like i can do in textboxes.... could you please in layman terms explain what you said above above....

Has it got something to do with "da.Fill(ds, "Review")"?

Hope you will be patient with me and treat me like an absolute noob in VS 2005.

Looking forward for your full support

[jmcilhinney]

Forget the fact that it's a DataAdapter. Setting a property is a universal concept. An object is an object and a property is a property. If you want to set the value of any property for any object you just type:

VariableName.PropertyName = Value

You know the name of your variable and you know the name of your property. Intellisense will give you the value because its an enumeration. Just start typing.

[Siddharth Rout]

Oh you mean
as in code. I was looking for an object. ...sorry... but i am learning fast...

so i need to add

da.MissingSchemaAction = MissingSchemaAction.AddWithKey

before

da.Update(ds, "Review")

Is that correct? Er, I don't have a primary key in my database. Is that necessary? if yes where do i mention that in the above code?

Thnx

[jmcilhinney]

If you don't have a primary key then how can you distinguish one record from another? There are times when it is appropriate to have a table without a primary key but they are very, very much in the minority. If this is one of those times then you can't use a CommandBuilder, plain and simple. The fact that you are trying to use a CommandBuilder at all suggests to me that this isn't one of those times though. How can you update or delete a record if you cannot uniquely identify it?

[Siddharth Rout]

If you don't have a primary key then how can you distinguish one record from another? There are times when it is appropriate to have a table without a primary key but they are very, very much in the minority. If this is one of those times then you can't use a CommandBuilder, plain and simple. The fact that you are trying to use a CommandBuilder at all suggests to me that this isn't one of those times though. How can you update or delete a record if you cannot uniquely identify it?

Hmmm. I created a primary key even though I didn't need it and now it works fine

jmcilhinney, I appreciate your patience....

[edit]: It says that I need to spread some reputations but I will remember this and I will come back and rate your post. It's a promise. I owe you two reps now