To convert to C#.NET, try using an online converter like:
(you need an account to see links)
I made these functions for my work to help automate Microsoft Access to the point where everything is on auto-pilot and the end-user doesn't have to even open Access. Your basic functions for manipulating a database. Posting these for anyone who might find them useful.
Note: Basically, create an Access file and save it as an MDB type, which is 2002-2003 I believe.
At the top of your project, either in your class or form add the reference to:
Code:
Imports System.Data.OleDb
In your variable declarations, add:
Code:
'--- MS Access Connection Variables ---
Public aString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Z:\AppData\Databases\test.mdb" '<- LOCATION OF MDB FILE
Public aConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
Dim aCommand As New OleDbCommand
Dim aDR As OleDbDataReader
'-------------------------
I made aConnection public so you can set the connection string not just where you declare it, in case you need to change between database connections, working with multiple instances, etc.
This function allows you to insert to your database table created in Access. As you can see, I am formatting my string as it makes sending the query a little more managed and easier to read.
Code:
Public Sub InsertDB(ByVal dbTable As String, ByVal BodyID As Integer, ByVal ItemName As String, ByVal XP As String, ByVal XM As String, ByVal YP As String, ByVal YM As String, ByVal ZP As String, ByVal ZM As String)
'Write to DB
aCommand.CommandType = CommandType.Text
aCommand.CommandText = String.Format("INSERT INTO {0} VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')", dbTable, BodyID, ItemName, XP, XM, YP, YM, ZP, ZM)
aCommand.Connection = aConnection
aConnection.Open()
Try
aCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
aConnection.Close()
End Sub
This function returns a string of what you are looking for, based on your input. As you can see, this can probably be managed a little more efficiently but it works for what I need. If someone has a better way of doing this, feel free to share.
Code:
''' <summary>
''' Grabs an item from the specified column based on our DB key identifier
''' </summary>
''' <param name="dbTable">Specified DB table to scan</param>
''' <param name="keyColumn">Key column identifier</param>
''' <param name="keyItem">Key identifier</param>
''' <param name="FindColumn">Column to grab data from</param>
Public Function FindDB(ByVal dbTable As String, ByVal keyColumn As String, ByVal keyItem As String, ByVal FindColumn As String) As String
'Read from DB
FindDB = vbNullString
aCommand.CommandType = CommandType.Text
aCommand.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = '{2}'", dbTable, keyColumn, keyItem)
aCommand.Connection = aConnection
aConnection.Open()
Try
aDR = aCommand.ExecuteReader
If aDR.HasRows <> True Then
MessageBox.Show("Found no results for '" & keyItem & "'.")
aConnection.Close()
Return vbNullString
End If
While aDR.Read()
FindDB = aDR(FindColumn).ToString()
End While
aConnection.Close()
Return FindDB
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
aConnection.Close()
Return vbNullString
End Function
Delete a row from our table.
Code:
Public Sub DeleteDB(ByVal dbTable As String, ByVal ItemType As String, ByVal Item As String)
'Delete from DB
aCommand.CommandType = CommandType.Text
aCommand.CommandText = String.Format("DELETE FROM {0} WHERE {1} = '{2}'", dbTable, ItemType, Item)
aCommand.Connection = aConnection
aConnection.Open()
Try
aCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
aConnection.Close()
End Sub
Update a cell in our table.
Code:
Public Sub UpdateDB(ByVal dbTable As String, ByVal ItemType As String, ByVal Item As String, ByVal BodyID As String)
'Update items in DB
aCommand.CommandType = CommandType.Text
aCommand.CommandText = String.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = {4}", dbTable, ItemType, Item, "BodyID", BodyID)
aCommand.Connection = aConnection
aConnection.Open()
Try
aCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
aConnection.Close()
End Sub
This one scans our table and returns the specified column into a list. Can be useful, here I have the column name hardcoded so you might want to make that dynamic.
Code:
''' <summary>
''' Scans our DB table and pulls all item names into our listbox
''' </summary>
''' <param name="lstItems">Listbox to store body names</param>
Public Sub ScanDB(ByRef lstItems As ListBox)
'Read from DB
aCommand.CommandType = CommandType.Text
aCommand.CommandText = String.Format("SELECT * FROM {0} WHERE {1} LIKE '%{2}%'", _
"Block", "ItemName", "")
aCommand.Connection = aConnection
aConnection.Open()
Try
aDR = aCommand.ExecuteReader
While aDR.Read()
lstItems.Items.Add(aDR("ItemName").ToLower())
End While
aConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
aConnection.Close()
End Sub
If anyone has suggestions on what they might need, feel free to ask. I posted the basic functions I thought users might find useful. And I'm definitely open for suggestions to change/add to this code.