|
Please Help.. Update MS Access using DataGridView in VB2005
I've seen so many post and tutorial about this subject.. and they are all most the same code logic and many of them appreciate it because it works for them?.. on form load the records load smoothly and it works but when everytime datagridview/datasets committ changes and i tyr to update the record and i always got an ERROR like this..
System.InvalidOperationException: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Employee_System.Form1.btnUpdate_Click(Object sender, EventArgs e) in C:\Employee_System\Form1.vb:line 31
****************
Here's my Code just like most of the forum i've visited
Please anyone who had a good heart to help me with this difficulties..
Please...
********************
Imports System.Data.OleDb
Public Class Form1
Dim theOleDbCommand As New OleDbCommand()
Dim theOleDbDataAdapter As New OleDbDataAdapter(theOleDbCommand)
Dim theDataSet As New DataSet()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Me.theOleDbCommand = New OleDbCommand("SELECT * FROM SampleTable", New OleDbConnection(dbConnectionString))
Me.theOleDbDataAdapter = New OleDbDataAdapter(Me.theOleDbCommand)
Me.theDataSet = New DataSet()
Me.theOleDbCommand.Connection.Open()
Me.theOleDbDataAdapter.Fill(Me.theDataSet)
Me.theOleDbCommand.Connection.Close()
Me.DataGridView1.DataSource = Me.theDataSet.Tables(0).DefaultView
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
Me.theOleDbCommand.Connection.Open()
Me.theOleDbDataAdapter.Update(Me.theDataSet)
Me.theOleDbCommand.Connection.Close()
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub
End Class
|
|
|
|
|
You gave the DataAdapter the SQL SELECT to get the records, but you never gave it how to update the database with SQL UPDATE, INSERT, and DELETE statements.
You can have these created automatically by using the OleDbCommandBuilder class.
Dim myDA As New OleDbDataAdpater(myCommand)
Dim cb As new OleDbCommandBuilder(myDA)
|
|
|
|
|
It works! Thank You So Much.... I do appreciate...
Your such a great help! Thanks again...
|
|
|
|
|
Do? OleDbDataAdapter.Update() supports (multiple or INNER JOIN) MS Access Table to be update?? or any other way to update two tables at the same time without making manual hardtype code or such a very long codes?? because in some instance of my application.. I have a DataGridView which its records came from an sql query which are two join table.. The Loading of record works fine.. but when i try to make changes into the DataGridView which also means dataset has also committ changes?? and i try to update the record using:
<b>OleDbDataAdapter.Update()</b>
then an ERROR occur just like this:
<b>System.InvalidOperationException: Dynamic SQL generation is not supported against multiple base tables.
at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Employee.frmDTR.UpdateRecord() in C:\Employee\frmDTR.vb:line 136</b>
******************
Heres my Code...
**************************
Private Sub LoadRecord(ByVal strSQL As String)
Try
Me.Cursor = Cursors.WaitCursor
oleConnection.ConnectionString = dbConnectionString
oleConnection.Open()
oleDataAdapter = New OleDbDataAdapter(strSQL, oleConnection)
oleCommandBuilder = New OleDbCommandBuilder(oleDataAdapter)
dbDataSet = New DataSet()
oleDataAdapter.Fill(dbDataSet)
DataGridView1.DataSource = dbDataSet.Tables(0).DefaultView
Me.Cursor = Cursors.Default
Catch ex As Exception
MsgBox(ex.ToString())
Finally
oleConnection.Close()
End Try
End Sub
Public Sub UpdateRecord()
Try
oleConnection.Open()
oleDataAdapter.Update(dbDataSet)
Catch ex As Exception
MsgBox(ex.ToString())
Finally
oleConnection.Close()
End Try
End Sub
***************************
heres my SQL Query
*******************************
LoadRecord("SELECT p.IDno as [ID No],p.Lastname,p.Firstname,t.[Time In],t.[Time Out],t.[Date In],t.[Date Out],t.[Remarks] FROM tblPersonal as p inner join tblTimeEntry as t on t.IDNo = p.IDNo Order by p.Lastname")
************
Please anybody there..
|
|
|
|
|
DataAdapters only work with single-table queries. Anything more complicated and you have to supply ALL the SQL statements and objects to update the database yourself.
|
|
|
|
|
Thank your very much...
I've already created a manual/hardtype code to do that... i'm just wondering if VB2005 has that kind of function so i can make the code much easier... ehehe.. anyway... thank so much again.. such a great help!
|
|
|
|
|
Plz can Any one tell me how to make a s/w as Trial Version....For 15 Days....
Thanks
-- modified at 23:01 Monday 8th October, 2007
|
|
|
|
|
|
hello....
I need article to report in vb.net 2005,, do you have this article?
please...
Iam very...very... need it.
|
|
|
|
|
|
Hi can anybody help me? I need to retrieve the song play length for mp3 files for my program? I'm not concerned about the other ID3 tag information. Just the mp3 song length(aka individual mp3 file play lengths). please help. thanks
Mr Oizo
|
|
|
|
|
Hello Mr Ozio,
I hope the code below helps to retrieve information about MP3 file:
Option Explicit
Public Type MP3Info
Tag As String * 3
Songname As String * 30
Artist As String * 30
Album As String * 30
Year As String * 4
Comment As String * 30
Genre As String * 1
End Type
Function MP3GetInfo(strFileName As String) As MP3Info
Dim sTemp As String, iFreeFile As Integer
On Error GoTo ErrFailed
iFreeFile = FreeFile
Open strFileName For Binary As iFreeFile
With MP3GetInfo
Get #1, FileLen(strFileName) - 127, .Tag
If Not .Tag = "TAG" Then
Debug.Print "No tag for " & strFileName
Else
Get #1, , .Songname
Get #1, , .Artist
Get #1, , .Album
Get #1, , .Year
Get #1, , .Comment
Get #1, , .Genre
End If
End With
Close iFreeFile
Exit Function
ErrFailed:
Debug.Print "Error in MP3GetInfo: " & Err.Description
Close iFreeFile
End Function
Thanks,
James Smith
James Smith
www.componentone.com
|
|
|
|
|
Half that code doen't run.I think thats like vb6 or something. Dunno. I'll try change it a bit but I'm really looking to just get the mp3file play time length(song length)? What line or 2 do I use
thanks for the reply
Mr Oizo
|
|
|
|
|
Assuming this works at all, it looks to me like it reads id3 tags, which don't include length.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Both managed DirectX and the Windows Media Player can give you this info. MDX might be better, I think it will do it without a control being required on a form.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hello all. I am in college, my major is CNS. However, we are taking programming I for VB.Net, and either my teacher is awful OR I am just dense when it comes to this stuff. Is there any websites that I can go to get Help via tutorials etc. for this? Any help is appreciated.
Thank you
Kristi
|
|
|
|
|
Search for ‘vb.net’ on Google and you will get enough links for tutorials.
However one which I may suggest you is www.startvbdotnet.com, check other sites also.
-Dave.
Dave Traister,
ComponentOne LLC.
www.componentone.com
|
|
|
|
|
Thank you Dave
|
|
|
|
|
Hello. Like Dave said in his reply, and I know it sounds crazy, a lot of times when programmers get stuck the best place to start is google. Here is a tutorial I found via google:
http://visualbasic.about.com/od/learnvbnet/a/LVBE_L1.htm[^]
If you run into any specific problems you can always post it here.
Good Luck.
|
|
|
|
|
Thank you very much
|
|
|
|
|
Hi,
Here is a good VB .NET tutorial Click Here
The tutorial is a programming tutorial so it misses quite a bit of the background to .NET - however if you want to get stuck in to programming immediately it is well worth a look at.
In fact I used this to teach myself VB .NET so it comes highly recommended.
Good luck on your journey through .NET and enjoy
Guy
You always pass failure on the way to success.
|
|
|
|
|
Thank you so much for your help
|
|
|
|
|
Hi
Anybody out there know how to trace who was the last user to logon to a PC using a LAN account as opposed to a local account. I have tried various options searching the AD using the DSQuery and DSGET commands but rather think this may need some kind of vbs or VB script.
Thanks
|
|
|
|
|
This isn't tracked by AD or the machine, unless auditing is turned on. Only then will it show up in the machines event log.
|
|
|
|
|
Thank you for your quick response
|
|
|
|