1 Attachment(s)
Need help with manipulating relational database
Hello, I have a relational database in .mdb format and need to connect, insert, edit and delete its values. Here's the access diagram:
Attachment 130691
http://2.bp.blogspot.com/-OfZ3XrXBk0...screenshot.jpg
I'm using Visual Studio Express 2015 (Visual Basic). Could someone give me some example?
Re: Need help with manipulating relational database
Connecting to a database should not differ based on which style your databases is setup. It should be very basic stuff:
Code:
'Declare the connection object
Dim con As OleDbConnection
Try
'Set the connection object to a new instance
'TODO: Change "My Connection String Here" with a valid connection string
con = New OleDbConnection("My Connection String Here")
'Create a new instance of the command object
'TODO: Change [ID] to a valid column and [MyTable] to a valid table
Using cmd As OleDbCommand = New OleDbCommand("SELECT Count([ID]) FROM [MyTable]", con)
'Open the connection
con.Open()
'Commands go here
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
As far as commands, if you don't know basic commands then I highly suggest that you visit W3Schools' webpage on SQL. If you do know your basic commands, the only difference is that for all your sub-tables you'd pass the ID value into your products_tbl rather than the actual value.
By the way, relational databases are designed to help manage tables where a column may have different values but are essentially the same. I'll give you an example, say that you have a login database and you want to have a security column. Traditionally you'd setup the database with the following columns: ID(Primary Key), Username(Unique), Password, Security_Level(Integer), Security_Description. With relational databases you'd have two tables, the first would have the following columns: ID(Primary Key), Username(Unique), Password, SecurityID(Integer) and the second would have: ID, Description. When you'd insert values into the first table, the SecurityID column would link to the second table to get the description when needed.
Re: Need help with manipulating relational database
Thanks for the detailed help.
Can
Re: Need help with manipulating relational database
Also, you can either hand-code your dataset together or add as an external data source (the designer types it all for you, but you should probably do the former until you truly understand the classes/way it works). You will have to add the table relationships via code yourself in ADO.NET, even if you added them via Access.
Re: Need help with manipulating relational database
Bad to hear that. :p I thought it was simple.