dhanesh1405241511 0 Report post Posted May 14, 2006 Jeez .. i can see people already starting to take up sticks and stones to beat me up .. well .. i know m annoying .. and this always happens when my questions are related to programming Newayz .. to the point .. I have an MS Access DB .. I need a VB Code to retrieve the value from a field in the table. Let me just put it in a better way. I have a table that has a column: Sno. .. under sno. i have numbers like 1,2,3,4,5,6,7,8 .... now i created a form where i could view the records and edit them as i please .. works fine till here ! .. When i DELETE records .. say : 4 , 6 .. my table in Access as well as the datagrid in one of the forms shows my records as : 1 , 2 , 3 , 5 , 7 , 8 .. etc now .. i asked a few guys around .. and i was told to write a loop or say procedure that checks for serial wise numbers .. and if not .. then updating them in the right serial and then updating them to the database in Access and lastly show them in the datagrid .. i know the explaination .. and understand it .. but i dont know the code to use .. could someone be kind enuf to provide me with this check & update code ? .... err .. Help please .. Regards Dhanesh. Share this post Link to post Share on other sites
miCRoSCoPiC^eaRthLinG 0 Report post Posted May 14, 2006 Tomorrow man - i have the whole thing for u ready.. but my mind's too fuzzy for alcohol tonight later sometime tomorrow you'll have it all.. or simply check the Code Librarian software I was working on at Antilost.. As far as I'd done, it had the code to access MS-Access DB and Add/Edit/Delete stuff from it. Check under the Ongoing Projects section at Antilost. Share this post Link to post Share on other sites
dhanesh1405241511 0 Report post Posted May 14, 2006 (edited) Aaaaa ... ... this is not happening .. m gona start a count down to the day i would kill myself .. Monday .. Tuesday .. 2 DAYS .. omg ... no no .. ok sorry m freeking out .. but i cant help to think of the amount of time i have to submit the project .. gosh !Newayz .. bro .. i d'loaded the project u mentioned right away .. and ..1) When i run the .exe in the /bin folder .. i get errors. (screenshot)2) When i open the .sln file in VB and run the Prog .. i get a break error (screenshot)3) I dont see an Access file .. maybe i over looked it in hype but please do teme how the database is taken .. I guess the first error was cause of a Databse only .. newayz .. u guys are more experienced so u will be able to guide me better. Umm .. I guess i'll just send the code to u .. Help .. Guide .. Shower Ideas .. anything u can do .. just get me tru this .. Projects , exams .. and assignments are killing me together .. up down sideways .. Sorry for being so hyped up ... RegardsDhanesh. Edited May 14, 2006 by dhanesh (see edit history) Share this post Link to post Share on other sites
miCRoSCoPiC^eaRthLinG 0 Report post Posted May 15, 2006 Forget about that project - it's got a bunch of bugs, as in, hardcoded file-path and hence it cannot find the database at the given location. I've to fix those. Anyway, I started a series of tutorials on interaction between VB.NET and MS-Access. Here's the first part I'll whip up the second and third parts tomorrow and day after. Hopefully these will cover everything you might want to know about this topic. If you have any specific requests, post back and I'll try to include that in the tutorials. Share this post Link to post Share on other sites
dhanesh1405241511 0 Report post Posted May 15, 2006 One and only request .. keep my asta account running for me after tommorow .. as i will not be in a state to sit or stand .. eat or drink .. do 1 or 2 .. and best of all ... i will be shi**ing vb codes in the loo for the next 1 year after i flunk .. :PI guess i got a way to add sql queries from access to vb .. but bloody thing shows errors or dont work at all .. i just saw ur tutorial .. so will try to catch up to it .. will let u know once i am done :PThankx alot for the support and help bro ... RegardsDhanesh. Share this post Link to post Share on other sites
marsden 0 Report post Posted May 30, 2006 (edited) Hi Dhanesh,Here is what I use to delete a record from my dataset in my VB.Net program that accesses a MS Access DB.Here is the creation of my dataset. 'Create a new dataset to hold the records returned from the call to FillDataSet. 'A temporary dataset is used because filling the existing dataset would 'require the databindings to be rebound. Dim objDataSetTemp As SecureComp.DataSet1 objDataSetTemp = New SecureComp.DataSet1() Try 'Attempt to fill the temporary dataset. Me.FillDataSet(objDataSetTemp) Catch eFillDataSet As System.Exception Try Dim writeFile As New StreamWriter("\\MAIN\hbh\Error.txt", True) If writeFile Is Nothing Then 'if file didn't open MessageBox.Show("File failed to open") Else 'write error to file writeFile.WriteLine(System.DateTime.Now & " Error loading database") writeFile.Close() End If 'log back out of windows ' ExitWindowsEx(0, 0) Catch 'log back out of windows ' ExitWindowsEx(0, 0) End Try End Try Try 'Empty the old records from the dataset. objdsClient1.Clear() 'Merge the records into the main dataset. objdsClient1.Merge(objDataSetTemp) 'get dataset length totalRec = objdsClient1.Tables("password").Rows.Count 'set password text box to focus txtPass.Focus() 'lock common keyboard functions e.x. alt+tab, alt+F4 ... HookKeyboard() 'start focus timer StartTimer1.Start() Catch eLoadMerge As System.Exception Try 'write system glitch to file Dim writeFile As New StreamWriter("\\MAIN\hbh\Error.txt", True) If writeFile Is Nothing Then 'if it didn't open MessageBox.Show("File failed to open") Else writeFile.WriteLine(System.DateTime.Now & "Error loading database") writeFile.Close() End If 'log back out of windows ' ExitWindowsEx(0, 0) Catch 'log back out of windows ' ExitWindowsEx(0, 0) End Try End Try Here is the call to the delete function (Me.btnDelete_Click(sender,e)), notice that I commit the changes right after the delete occurs. ElseIf (number = 1) Then MessageBox.Show("1 Minute Left on password", "Password Time Notice", _ MessageBoxButtons.OK, MessageBoxIcon.Warning) ElseIf (number < 1) Then 'time ended 'delete record from dataset If (editpass_type.Text <> 4) Then Me.btnDelete_Click(sender, e) End If Try 'commit changes Me.UpdateDataSet() Catch 'write error to file Dim writeFile As New StreamWriter("\\MAIN\hbh\Error.txt", True) If writeFile Is Nothing Then 'if it didn't open MessageBox.Show("File failed to open") Else writeFile.WriteLine(System.DateTime.Now & " Error updating database") writeFile.Close() End If End Try Here is the actual delete function that I call in the program. Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) If (Me.BindingContext(objdsClient1, "password").Count > 0) Then Me.BindingContext(objdsClient1, "password").RemoveAt(Me.BindingContext(objdsClient1, "password").Position) End If End Sub Lastly here is the update function I used. Public Sub UpdateDataSet() 'Create a new dataset to hold the changes that have been made to the main dataset. Dim objDataSetChanges As SecureComp.DataSet1 = New SecureComp.DataSet1() 'Stop any current edits. Me.BindingContext(objdsClient1, "password").EndCurrentEdit() 'Get the changes that have been made to the main dataset. objDataSetChanges = CType(objdsClient1.GetChanges, SecureComp.DataSet1) 'Check to see if any changes have been made. If (Not (objDataSetChanges) Is Nothing) Then Try 'There are changes that need to be made, so attempt to update the datasource by 'calling the update method and passing the dataset and any parameters. Me.UpdateDataSource(objDataSetChanges) objdsClient1.Merge(objDataSetChanges) objdsClient1.AcceptChanges() Catch eUpdate As System.Exception Dim writeFile As New StreamWriter("C:\HBH\cmd\Error1.dat", True) If writeFile Is Nothing Then 'if it didn't open MessageBox.Show("File failed to open") Else writeFile.WriteLine(System.DateTime.Now & " Error updating database") writeFile.Close() End If Throw eUpdate End Try 'Add your code to check the returned dataset for any errors that may have been 'pushed into the row object's error. End If End Sub Hope this helps. Edited November 21, 2006 by miCRoSCoPiC^eaRthLinG (see edit history) Share this post Link to post Share on other sites
iGuest 3 Report post Posted July 25, 2008 vb.net code for add column to exsiting access table VB.NET / MS Access Question I have an MS Access DB .I want make Programme to add column according to neccesity of individul clients.Tell me the vb.Net code to add column(Integer Data Type) to exsiting access Dat table. I have tried with "ALTER TABLE table_name Add Column Column_name Datatype" using OleDb.OleDbCommand,ExecuteNonQuery().But failed.Error"End of statment expected. -reply by amalrose Share this post Link to post Share on other sites