Replying to VB.NET / MS Access Question

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

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.

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 ..

I 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

Thankx alot for the support and help bro ...

Regards
Dhanesh.

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.

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 ...

Regards
Dhanesh.

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.

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.