April 11, 2012 at 11:54 pm
Hello,
I have a datagrid which is binding in my MS SQL database. I've created a two button: Import Data (this button is used to import my table from access database then display it to my datagrid) and Update button (this button is used to save/update my sql server through datagridview). When i clicked the Import Data button, it successfully import my table in my datagrid but the problem is, when i clicked the Update Data button an error message occur. I found the error in this code (da.UpdateCommand = cmb.GetUpdateCommand) saying "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
Here is the part of the sourcecode:
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.IO
Imports System.Data.OleDb
Public Class form1
Inherits System.Windows.Forms.Form
'SQL Connection
Public cs As New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=myDatabase; Integrated Security=TRUE")
Public da As New SqlDataAdapter("SELECT * FROM tblProd", cs)
Public ds As New DataSet
Public cmb As New SqlCommandBuilder(da)
'Access Connection
Dim Conn As OleDbConnection
Dim Comm As OleDbCommand
Dim Olea As OleDbDataAdapter
Dim Sql As String
Private Sub MyConn()
Try
Conn = New OleDbConnection("Provider = Microsoft.jet.oledb.4.0; Data Source = C:\myDatabase.mdb ")
Conn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error In Connection", MessageBoxButtons.OK)
End Try
End Sub
Private Sub cmdImportfromAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdImportfromAccess.Click
Dim x As Integer
Try
MyConn()
Sql = "Select * from tblProd"
Comm = New OleDbCommand(Sql, Conn)
Olea = New OleDbDataAdapter(Comm)
ds.Clear()
Olea.Fill(ds, "ProdID")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "ProdID"
DataGridView1.Refresh()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error In Connection", MessageBoxButtons.OK)
End Try
End Sub
Private Sub cmdUpdateProfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdateProfile.Click
Dim x As Integer
Try
'Update SQL Table
da.UpdateCommand = cmb.GetUpdateCommand
x = da.Update(ds.Tables("Prod"))
MsgBox(x & "record(s) updated")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error In Connection", MessageBoxButtons.OK)
End Try
End Sub
End Class
Anyone can help me, please?
Thanks.
April 12, 2012 at 12:25 am
Hi,
You cann't update the sql data adapter from other source i.e. access filled dataset(data table), just do some research on replacing or updating the dataset filled from access(oledb) data adapter to sql data adapter then fire update command with the modified dataset on the sql data adapter..
Thanks
Ashok
April 12, 2012 at 9:33 am
If you look at the tables defined within the dataset, the table structure matching tblprod has no column marked as the primary key or the unique key. This doesn't necessarily need to reflect the actual primary key on the table in the DB, but the column chosen would have to be one of the candidate keys (i.e. it meets the criteria, and could have been picked as the primary key).
In short - any unique, non-nullable column would work. Once you do that, the datagridview will allow you to do updates (several of my CRUD applications have dozens of this kind of setup going on).
Recommendation would be to create the dataset as a permanent item in your project (it becomes a separate editable item in your project), with the tables registered in there. The wireup of the datagridview beomes so much easier after that. You define the tables, and their data access procs for each type of CRUD, once in a single place: the datagridview then automatically know which procedures to use and how to call them (which parameters to send and what populates the params).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply