This message was discovered on microsoft.public.dotnet.framework.odbcnet.
| Murali |
HI
I have a SqlDataAdapter sda and i fills the Dataset DS1.
I pass this DS1 to another DS2. I fill the datagrid Using DS2.
Then i made some changes in datagrid records.
I want to update this changes in database.
How to do this?
Explanation of problem.
Code In the dll.
---------
Function getRecords() as Dataset
Dim sda as new SqlDataAdapter(byval tblname as string)
Dim ds as new DataSet()
sda.Selectcommand.Commadtext="Select * from Employee"
sda.SelectCommand.Connection=Con
sda.SelectCommand..ExecuteNonQuery()
sda.Fill(ds, tblname)
return ds
End Function
Function SaveRecords(byval ds as dataset)
dim sda as new sqldataadapter()
sda.update()------------------How to update here?
End function
Code in the form
---------
Dim ds1 as new dataset()
Private cmdShow_click .........
Dim dg as new datagrid()
ds1=getRecords("Emp)
dg.datasource=ds1
dg.dataMember=ds1.tables("Emp")"
end sub
Private cmdUpdate_click ..........
saveRecords(ds1)
end sub
Finally i do changes to datagrid.
So i want the changes reflects in dataset and have to update the table
Employee.
How to do this?
Can anyone help in this?
Thanks in advance.
-Murali
|
|
|
| |
|
| |
| |
| Elton |
I believe that you have to apply your Dataset and associated
SqlDataAdapter to perform your update:
Sub SaveRecord(byref dap as SqlDataAdapter, byref ds as DataSet)
Dim commBuild As New SqlCommandBuilder(dap)
dap.UpdateCommand = commBuild.GetUpdateCommand()
dap.Update(ds)
ds.AcceptChanges()
End Sub
"Murali" <Click here to reveal e-mail address> wrote in message news:<uBCai2#YCHA.2184@tkmsftngp11>...
[Original message clipped]
|
|
|
| |
|
| |
|
| |
| VBDotNet Team [MS] |
Murali,
If I understand your question correctly this should be no different than
updating through the DataAdapter using the same Dataset.
The Update method on the adapter will take any DataSet assuming that the
DataSet's schema matches that of the adapter's DB and Table.
Here is some simple code that should demostrate what you like. I have tested
this code out and it works fine.
Private ds1 As New DataSet()
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
SqlDataAdapter1.Fill(DataSet11)
ds1 = DataSet11
DataGrid1.DataSource = ds1
DataGrid1.DataMember = "Table1"
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
SqlDataAdapter1.Update(ds1)
DataGrid1.Refresh()
End Sub
Cameron McColl
MS
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message
news:uBCai2#YCHA.2184@tkmsftngp11...
[Original message clipped]
|
|
|
| |
|
|
| |
| |
| Murali |
HI Thanks for ur help.
But the thing is
1.I dont use the same SqlDataAdopter for both the operation like fill and
update.
How to do in this situation?
2.I try to use the same SqlDataAdopter and tried , it says error as
"at System.Data.Common.DbDataAdapter.Update(DataSet dataSet), Update unable
to find TableMapping['Table'] or DataTable 'Table'."
My code is
Dim sda as new SqlDataAdapter()
Function ShowRecords()
sda = New SqlDataAdapter(strSql, oCON)
sda.SelectCommand.ExcecuteNonQuery()
sda.Fill(ds)
return ds
End Function
Funtion Update(byval ds as dataset)
sda.Update(ds) --- Error here.
End Function
How to handle both ?
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message
news:uo9E2NAZCHA.1384@tkmsftngp12...
[Original message clipped]
|
|
|
| |
|
| |
| |
| VBDotNet Team [MS] |
Murali,
You will receive an error like this if the SQL statement in your
DataAdapter's UpdateCommand object does not match the schema for the
supplied datasource. Sounds like you providing the wrong dataset or using
the wrong dataadapter.
Perhaps you could explain why you need different datasets AND different data
adapters each time you retrive or update data?
Cameron McColl
MS
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message
news:uigh9hAZCHA.1724@tkmsftngp10...
[Original message clipped]
|
|
|
| |
|
| |
| |
| Murali |
HI Cameron McColl
Actually I have created all the code in the dll.
So one function will generete code for to show the records and another one
is used to save those records.
For each function i created one dataAdapter and some.
thanks
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message
news:#mJ0zeMZCHA.2440@tkmsftngp08...
[Original message clipped]
|
|
|
| |
|
|
| |
|
|
|
|
| |
| VBDotNet Team [MS] |
Murali -
You need to setup the adapter with commands and a connection. Try something
like this:
Function SaveRecords(byval ds as dataset)
Dim sda As New SqlClient.SqlDataAdapter("SELECT * FROM EMPLOYEES",
Con)
Dim cb As New SqlClient.SqlCommandBuilder(sda) ' This creates the
Insert, Delete commands etc...
sda.Update(ds)
End Function
Hope this helps
Steve
VB Team
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message
news:uBCai2#YCHA.2184@tkmsftngp11...
[Original message clipped]
|
|
|
| |
|
| |
| |
| Murali |
HI
I again changed my code for testing purpose.But it says the same error as
"Update unable to find TableMapping['Table'] or DataTable 'Table'."
I dont know why this?
my sample code is,
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmd As New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds)- here is the error.
Thanks
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message news:uOOGcCNZCHA.2264@tkmsftngp10...
[Original message clipped]
|
|
|
| |
|
| |
| |
| Murali |
HI
I just modified my code as follows,it works without error but no updation.
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmdB as new SqlCommandBuilder= New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds,"Employee")
It works without error, But the thing is it never update the changes made to database.
"Murali" <Click here to reveal e-mail address> wrote in message news:eTxJaPWZCHA.2556@tkmsftngp08...
HI
I again changed my code for testing purpose.But it says the same error as
"Update unable to find TableMapping['Table'] or DataTable 'Table'."
I dont know why this?
my sample code is,
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmd As New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds)- here is the error.
Thanks
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message news:uOOGcCNZCHA.2264@tkmsftngp10...
[Original message clipped]
|
|
|
| |
|
|
| |
| |
| VBDotNet Team [MS] |
So thi smay seem wierd but...
Don't call dataset.acceptchanges before doing a dataadapter update.
If you do call acceptchanges the dataadapter will not see any changes in the dataset and hence will not do any updates.
I agree this is not very intuitive but that's the way it works.
Let me know if that solves you're issue.
Cameron McColl
MS
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message news:u9$YQjYZCHA.1728@tkmsftngp08...
HI
I just modified my code as follows,it works without error but no updation.
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmdB as new SqlCommandBuilder= New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds,"Employee")
It works without error, But the thing is it never update the changes made to database.
"Murali" <Click here to reveal e-mail address> wrote in message news:eTxJaPWZCHA.2556@tkmsftngp08...
HI
I again changed my code for testing purpose.But it says the same error as
"Update unable to find TableMapping['Table'] or DataTable 'Table'."
I dont know why this?
my sample code is,
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmd As New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds)- here is the error.
Thanks
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message news:uOOGcCNZCHA.2264@tkmsftngp10...
[Original message clipped]
|
|
|
| |
|
| |
| |
| Murali |
I just removed the acceptchanges from the code and it says error as,
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
Sample Code
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmdB as new SqlCommandBuilder= New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
'ds.AcceptChanges() --------Commented
sda.Update(ds,"Employee")
I dont get anything.I'm working on this for the past one week.
Help me.
-Murali
|
|
|
| |
|
| |
|
| |
| Murali |
HI
Atlast i found why it is not updating my records.
Reason is the uploaded table has no primary key.So i changed and it worked fine.
But my question is how can we update the table which has no primary key?
Thx
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message news:#RHtGJZZCHA.2440@tkmsftngp08...
So thi smay seem wierd but...
Don't call dataset.acceptchanges before doing a dataadapter update.
If you do call acceptchanges the dataadapter will not see any changes in the dataset and hence will not do any updates.
I agree this is not very intuitive but that's the way it works.
Let me know if that solves you're issue.
Cameron McColl
MS
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message news:u9$YQjYZCHA.1728@tkmsftngp08...
HI
I just modified my code as follows,it works without error but no updation.
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmdB as new SqlCommandBuilder= New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds,"Employee")
It works without error, But the thing is it never update the changes made to database.
"Murali" <Click here to reveal e-mail address> wrote in message news:eTxJaPWZCHA.2556@tkmsftngp08...
HI
I again changed my code for testing purpose.But it says the same error as
"Update unable to find TableMapping['Table'] or DataTable 'Table'."
I dont know why this?
my sample code is,
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmd As New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to new value 10.
ds.AcceptChanges()
sda.Update(ds)- here is the error.
Thanks
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message news:uOOGcCNZCHA.2264@tkmsftngp10...
[Original message clipped]
|
|
|
| |
|
|
| |
| |
| VBDotNet Team [MS] |
Murali,
The CommandBuilder needs a primary key to generate the update statement.
This is an excerpt from the CommandBuilder reference topic:
(ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdatasqlclientsqlcommandbu
ilderclasstopic.htm)
The SelectCommand must also return at least one primary key or unique
column. If none are present, an InvalidOperation exception is generated, and
the commands are not generated.
To update a table that has no primary key you can manually create the Update
statement and assign it to the UpdateCommand of your dataadapter.
Hope that helps
Steve Stein
VB Team
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message
news:O41TPviZCHA.3660@tkmsftngp08...
HI
Atlast i found why it is not updating my records.
Reason is the uploaded table has no primary key.So i changed and it worked
fine.
But my question is how can we update the table which has no primary key?
Thx
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message
news:#RHtGJZZCHA.2440@tkmsftngp08...
So thi smay seem wierd but...
Don't call dataset.acceptchanges before doing a dataadapter update.
If you do call acceptchanges the dataadapter will not see any changes in the
dataset and hence will not do any updates.
I agree this is not very intuitive but that's the way it works.
Let me know if that solves you're issue.
Cameron McColl
MS
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Murali" <Click here to reveal e-mail address> wrote in message
news:u9$YQjYZCHA.1728@tkmsftngp08...
HI
I just modified my code as follows,it works without error but no updation.
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmdB as new SqlCommandBuilder= New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to
new value 10.
ds.AcceptChanges()
sda.Update(ds,"Employee")
It works without error, But the thing is it never update the changes made to
database.
"Murali" <Click here to reveal e-mail address> wrote in message
news:eTxJaPWZCHA.2556@tkmsftngp08...
HI
I again changed my code for testing purpose.But it says the same error as
"Update unable to find TableMapping['Table'] or DataTable 'Table'."
I dont know why this?
my sample code is,
Dim sda As SqlDataAdapter
Dim ds As New DataSet()
Dim strsql As String
strsql = "Select * from EmployeeDetail where EmpId=10"
sda = New SqlDataAdapter(strsql, obj.oCON)
Dim cmd As New SqlCommandBuilder(sda)
sda.Fill(ds, "Employee")
ds.Tables(0).Rows(0).Item("Age") = 10 'Here i changed the value for Age to
new value 10.
ds.AcceptChanges()
sda.Update(ds)- here is the error.
Thanks
Murali
"VBDotNet Team [MS]" <Click here to reveal e-mail address> wrote in message
news:uOOGcCNZCHA.2264@tkmsftngp10...
[Original message clipped]
|
|
|
| |
|
| |
|
|
|
|
|
|
|
|
|
|
