When learning how to work with databases in college \/ highschool they often teach people how to do it with the wizard from Visual Studio x<\/p>\n
I always found that this method doesn’t give me the desired amount of control i’d like to have over my application, so i’ve always done it code wise.
\nYes, it’s a bit of extra work, but it’s worth it.<\/p>\n
If you plan on doing this, there will always be a set of objects you’ll need in order to work with a database:<\/p>\n
I’m not going to handle working with datasets & gridviews in this post, you can view a small example of that here: Fill a DataGridView from a DataSet (code-wise)<\/a><\/p>\n We’ll start off by defining the objects we need:<\/p>\n <\/p>\n <\/p>\n There we go, we got our connection, command and datareader. <\/p>\n What this little snippet will do is select the row of where Name = ‘MyName’ and display the data for that row in 2 textfields (Ofcourse, if there are multiple people with the same name in your database you’ll prefer to be working with unique ID’s etc because you’ll get data from the last row read by the datareader)<\/p>\n There we go, that’s pretty much it.<\/p>\n","protected":false},"excerpt":{"rendered":" When learning how to work with databases in college \/ highschool they often teach people how to do it with the wizard from Visual Studio x I always found that this method doesn’t give me the desired amount of control i’d like to have over my application, so i’ve always done it code wise. Yes, … Continue reading Working with databases (OleDb, MS Access 2007) code-wise (SELECT COMMAND)<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[38],"tags":[17,18,21,23,24,27],"_links":{"self":[{"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=\/wp\/v2\/posts\/46"}],"collection":[{"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=46"}],"version-history":[{"count":2,"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions"}],"predecessor-version":[{"id":789,"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions\/789"}],"wp:attachment":[{"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dirk.schuermans.me\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nDim oCon As New OleDb.OleDbConnection(\"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabase.accdb;Persist Security Info=False;\")\r\nDim oCommand As New OleDb.OleDbCommand(\"\", con)\r\nDim oDR As OleDb.OleDbDataReader<\/pre>\n
\nWe’ll start off with selecting data from our database using the SELECT command:<\/p>\n'Code example to read data from a table and place it inside textboxes\r\n'Let's say we have a form with 2 textfields txtName and txtFirstName, and our table has 2 columns Name and FirstName\r\nTry\r\n\t'Set the query\r\n\toCommand.CommandText = \"SELECT * FROM MyTable WHERE Name = 'MyName'\"\r\n\r\n\t'Open connection\r\n\toCon.Open()\r\n\r\n\t'Set the datareader\r\n\toDR = oCommand.ExecuteReader\r\n\r\n\t'Get items while reading\r\n\tWhile oDR.Read\r\n\t\ttxtName.text = oDR(\"Name\").ToString\t\t\t'You can also work with column indexes -> oDR(0).ToString\r\n\t\ttxtFirstName.text = oDR(\"FirstName\").ToString\t\t'You can also work with column indexes -> oDR(1).ToString\r\n\tEnd While\r\n\r\n\t'Close connection\r\n\toCon.Close()\r\n\r\nCatch ex As OleDb.OleDbException\r\n\t'Show oledb exception\r\n\tMessageBox.Show(ex.ToString)\r\nEnd Try<\/pre>\n