VB.NET Tutorial - Update Records In An Access Database

[@devindwight9272]

As always, very professional and useful information for people working with databases. Thank you for the time you put into these great videos!

[@VBToolbox]

+Devin Dwight Thank you, Devin! I always appreciate your feedback. :-D

[@woodywickliffe]

Thanks for your tutorials. They've helped me immeasurably. I wrote a scheduling program in VB6 connecting to an Access database for a local company. The program grew over the years to about 45,000 lines of code. I'm moving it to Visual Studios 2015. I'm actually a civil engineer who enjoys dabbling in code so I had no idea how to use VB.Net to connect to an Access database. Your tutorials have given me all the info I need to figure it out. Once I get it moved to VB.Net, I'm going to change the type of database, which will be simple given your code. Then I want to move it from a form based program to a web-based program, and possibly sell it commercially. Your tutorials have opened the door for me so I can move on with this. Thanks a bunch.Woody

[@VBToolbox]

+Woody Wickliffe That's awesome, Woody! I wish you tremendous success! :-D I'm happy that the tutorials have been a help to you. I've always wished that I had the entrepreneurial gift. Having and maintaining a commercially viable app would be the ultimate job.

[@georgegallimore6690]

+VB Toolbox hey friend! i was wondering, DO you know how i could add custom theme to re-usable dll file for different? am trying to create dll files to store custom themes instead of creating a project ad class and paste custom themes, so can you help?

[@rowanbalana8578]

You're literally a hero ! Thank you and you have the best tutorials on KZbin :)

[@bradleywwiitala3229]

Very nicely done. I've been looking through all the usual website searches on this subject and found exactly what I needed here! I am converting a fairly large VB6 MDI EEG analysis application that uses both ado data controls and code generated database stuff. I have all the windows form stuff and threading figured out and my next step is getting database access down since they are a huge part of the application. IMO the 'wizards' and build-in data controls seem very confusing compared to the ones in VB6...although I have only worked with them a bit. I may stop using any kind of controls that you have to drop on a form and use code like this. I particularly like how you encapsulated the database stuff into your DBControl.vb. Thanks again for saving me a huge amount of searching and learning time! (Also typing time with your dropbox link.)

[@pw3111]

Once again a Great Tutorial. Thanks for showing

[@NathanWynn09]

Great tutorial. I was able to get mine to work. I got into issues where my col names are reserved words. I later rewatched the video again and picked up the [].

[@VBToolbox]

+Nathan Wynn Thanks, Nathan. :-) Yeah, I've run into those reserved words a lot as well. Microsoft did a pretty poor job at documenting their changes to OLEDB keywords with recent versions, in my opinion. Had to do a lot of searching to finally find some articles with up-to-date keyword listings.

[@pw3111]

Thanks you for a great series of videos. I’ve used your methods on a number of programs I use at home. Is it possible to set up a DBControl Class using Excel instead of Access as I’d like to replicate your programs for my daughter but using an Excel Spreadsheet as that’s what she receives from the head company. She doesn’t have the ability to change to Access Thanks for any advice

[@VBToolbox]

Hi there, PW! This is absolutely possible and I had actually created a DBControl (ExcelControl) class a long time ago, but I don't think I ever made a tutorial on it. DropBox Link: www.dropbox.com/s/n344lectco5uw2p/ExcelADO.zip?dl=0 It's slightly different than the others, but still very similar and usage on the application side should be the same. Please bear in mind that this is many years old now and the connection string might need a little tweaking for newer versions of Excel. Connectionstrings.com is a wonderful resource for these and should have everything you need for the ACE and JET drivers for excel (and every other database). I hope it helps!

[@VBToolbox]

Oops! Sorry about the last link. That actually was for the entire Excel project I had made, I think - which could still be useful as an example. I meant to send this text link, which is just the class code: www.dropbox.com/s/4xutj0fvz10c9wq/ExcelControl.txt?dl=0

[@pw3111]

Thanks. I'll certainly put it to good use😁@@VBToolbox

[@pw3111]

Is it possible to have a "wildcard" file entry in the Control so the user can search for, and load their own file from Form1? Cheers @@VBToolbox

[@jasonport6691]

Hi, Great Video, I have followed this an implemented into my application. I am however having an issue, by where my a combobox draws data from the PersonT table, I want to display the persons full name in the combobox, but store the personID in the table. if that makes sense

[@rezasurya6538]

hello, your tutorial was help me out with my project, thank you for tutorial, i hope you make another tutorial VB in an Access Database

[@bsworldsolution6626]

Great tutorial, Please am having a problem in an application am developing which your class has been helpful, the application works with a single database which has multiple tables in it, how can i put out a data from on table to the other and also print different data from different tables to a single page... Thank you

[@trevordaniels4443]

Is it possible to use multiple parameters in the AddParam query, so that the user can either enter the product name or the product code into the search textbox?

[@rowanbalana8578]

Please help asap How to do it like when i click on the record on the datagrid and click update on the menu strip, it will show the record/data that i chose instead of the first one showing on the datagrid.

[@lildemon3030]

Hello can you tell me the codes for data types of integer and date??? I really need this now for my project at school thanks for the tutorial but I cannot update my database it said "Data type mismatch in criteria expression."

[@zeosteven]

sir i facing some problem that is 'Object reference not set to an instance of an object.'

[@josemunozvargas]

Hi, are you still working on it? Your videos are great.

[@VBToolbox]

Unfortunately, I haven't been in a position to work on it for the past few years. Still working on building up my situation to where I can hopefully get back in the game. 🙂

[@trevordaniels4443]

Great series of videos but I keep getting "Syntax error in UPDATE statement and I simply can't find the error. Any ideas?

[@ksmemon1]

in your DbControl Class Where is Reader, How to Find the MaxID from Database

[@ksmemon1]

Sir Could you please Help me to Saving Data Into Database From Data Grid View Control, I am already using Your DbControl Class in my Project, Thanks and Regards

[@VBToolbox]

Here is a sample project that demonstrates how to update MS Access from a DGV: www.dropbox.com/s/i7xqu6sjantdpdx/AccessDGVUpdate.zip?dl=0 I hope that helps. :-)

[@dtCajun]

Never mind, I found the refresh code I had commented out :)

[@VBToolbox]

+David Torres Glad you got it sorted, David. :-)

[@thebotol]

Thanks great tutorial bro

[@eximious9102]

Hi, Quick question, how do I Update a Selected row in the DataGrid and show all the fields in a new window? instead of showing all records and flicking through all of them to find what I want to update, I would like to click on a record in the initial form and then click edit opening a new window with all of the relevant fields. Thank you! Great Videos, have really helped in my project!!

[@rowanbalana8578]

same question... please I need help,,

[@radmng]

hello, I want to ask you>>> i want to make school bell program with visual basic

[@VBToolbox]

+khaled ali Hello, Khaled ali. :-) Add a Timer control with a 1000ms interval and check the current time against your alarm time. It's pretty easy to capture the current time: DateTime.Now.ToString("HH:mm:ss") In your Timer1_Tick event you could do something simple like: If TextBox1.Text.Contains(DateTime.Now.ToString("HH:mm:ss")) Then MsgBox("ALARM!") ' Add your alarm sound or code here End If It the current time matches whatever you set your alarm to (TextBox1.Text) then it should fire. Once you have that you can simple Enable/Start the Timer with your Button_Click event, if you don't want it to start automatically when the app is opened. You could also add the current time to the Form's Text property and keep it active with the Timer_Tick event: Me.Text = String.Format("{0}", DateTime.Now.ToString("HH:mm:ss")) Hope that helps.

[@tralala449]

Sir.. Please Help me with my code here.. i cannot update my fields and i followed ur tutorial... please sir..

[@ksmemon1]

Connection Opens Very Slow When Db Is Password Protected How Can Apply Connection Pooling

[@vule562]

Thanks for your tutorials. I have question how to load the entry form blank so user don't have to click the Add New Record button. Thank you

[@VBToolbox]

Do you mean that you want to use the same form for updating and adding new records? If I were to do this I would probably create an ENUM list of modes so that SQL.Exec() would know which command to use [UPDATE or INSERT] and store it to a variable or property on the form.

[@littlekyb]

็Hello, When I click Gridview.How will I do data in Combo Box chance also.

[@robertvalentine7623]

Do you have a video on how to handle concurrency issues? Or can you make one? My situation is that my program has only one user, so i won't have a conflict between two users who are trying to make a change to the same record. However, if a user makes a change to a record and then advances to the next record., and then goes back to the original record and changes It , a concurrency violation..is incurred. How do I stop the concurrency violation?

[@VBToolbox]

+Robert Valentine If your project does not contain any sensitive data, perhaps you could zip it and share it via Dropbox or Google Drive. I might have a better chance of helping to troubleshoot the problem if I can see the source. :-)

[@nicholasjohnson4871]

Instead of opening to the first record, what if you wanted to open to the selected record on Form1?

[@rowanbalana8578]

Did you find an answer? This is also my problem

[@vanjomampusti2484]

hello. I realy love your toturials and I want to learn more because I forgot how to code vb.net. I have a question, when I try to update there is an error and it said "syntax error in UPDATE statement" what do you think went wrong? I follow all your code and videos. Hope you answer my query. Thanks and more tutorials!!

[@BigIslandLavaPioneers]

If you're using a multi-line query/command string, make certain that there is a space at the end of each line to prevent run ons.

[@ksmemon1]

Hi, Can You Please tell me how to View Access.DBDT Records In Debug mode? Thanks

[@rafrider11]

It doesn't appear all the data in update form, it appears only if the data in the access is single.. how to fix it?

[@VBToolbox]

I'm sorry, Rafael. I'm having trouble understanding. :-( Which part of the video are you having trouble with?

[@rowanbalana8578]

Hey sir please help I want to update the selected record on the datagrid like if i click on that record and click update, i want it to be the one to show on the new form instead of the first record

[@laser1915]

please im getting Data Type Mismatch in criteria expression

[@dtCajun]

Love your tutorials but I have 1 problem. My database is not updating. I've tried never copy which it says it can't find the db and copy if newer never updates. Any other suggestions why this may be happening? Thanks!

[@wiesiakrzyzek604]

CNN. b. )+:,h ) bvbjjhhjnlfghg

[@ksmemon1]

Sir Can You Help me To Create Crystal Report With Parameters Dialog box Thanks and Regards

[@zackduncan8833]

These videos have been excellent...THANK YOU! I have one question and it may be simple so sorry if I am missing something obvious. Each time I run the program the changes I made in the last run are not there. They do show up as long as I don't close the form. I tried a COMMIT TRANSACTIONS command but I either did not do it right or it is not the correct fix.

[@VBToolbox]

+Zack Duncan Hello, Zack. :-) Try this: 1.) In your Solution Explorer, locate your Access Database and select it. 2.) In the Properties window, set the "Copy To Output Directory" property to "Copy if newer". If this is set to Copy always, each time to debug it will overwrite the database file in the Debug folder with the template and erase your updated records. I hope that helps! :-)

[@zackduncan8833]

You are awesome. Thank you!

[@VBToolbox]

+Zack Duncan Sure thing! Happy to help. :-)

[@almarioaguirre9249]

Your tutorial is great however what to do to download source code thanks

[@eletheriakourteridou1487]

Can yoy help me plz? something was wrong and doesnt save the data , everything looks perfect , dont show me an error , but when i put the save button looks like works but dont save the new data.

[@VBToolbox]

+nick Chainis In your solution explorer, find your access database file and select it. Then find the "Copy to Output Directory" property and ensure that it's set to "Copy if Newer". If it's set to "Copy Always", each time you run the debugger it will overwrite the database with the template from the project directory.

[@eletheriakourteridou1487]

+VB Toolbox sorry but i tried many times and doesnt saved, can yoy help me plz? the db it's set to "Copy if Newer" everything looks perfect , no error, but dont save the updated record. it saves the current record Access.AddParams("@CustomerID", txtCustomerID.Text) Access.AddParams("@Company", txtCompany.Text) Access.AddParams("@Lname", txtLName.Text.Trim) ' run command Access.ExecQuery("UPDATE `Customers` SET `CustomerID` = ?, `Company` = ?, `LName` = ? WHERE (`@Αναγνωριστικό` = ?) ")

[@eletheriakourteridou1487]

+Eletheria Kourteridou Sorryyyyyyy i apologize becouse i dint paid attention in ORDER MATTERS!!! everything ok!!!

[@VBToolbox]

+Eletheria Kourteridou Yay! Glad you got it sorted. :-)

[@amberm2035]

THANK YOU SO MUCH !!! :D

[@mykeruiz2129]

hi sir, I get an error on the connection. it says "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." thank you!

[@VBToolbox]

Hello, Myke! :-) This is covered in the first video of the series: kzbin.info/www/bejne/p2KcgIuelpadetk If you don't have MS Access, you can download the free support library from Microsoft here: www.microsoft.com/en-us/download/details.aspx?id=13255 I hope that helps!

[@mykeruiz2129]

Thank you sir! I downloaded the component. Your tutorials are great! Looking forward for more.

[@FremaTvShow]

Please I love your tutorials. I have subscribed and shared them with my friends who have also subscribed. Can you help me with how to check duplicate records in the database. I created Access and Vb database management system following your tutorials. Thanks

[@VillagerLifeCraft]

Is it only VB DataGrid updated and nothing happens in access ?

[@alvinavila4238]

up

[@bkpickell]

I hate doing this, but I'm completely stuck. I'm getting a syntax error on the update string. And as far as I can tell it is correct. It's possible it's because I have a date field, and that field is causing me all kinds of headaches in this string of tutorials. I'll post my update string below, I'm not too confident that I will get any help since this video is over three years old, but a man can wish right? It's word wrapping the long strings, but I think you can still work out what's going on. Access.ExecQuery("UPDATE * VehicleInfo " & _ "SET [DateUsed]=@DateUsed,Unit=@Unit,Vehicle=@Vehicle,StartMileage=@StartMiles,EndMileage=@EndMiles " & _ "WHERE ID=@VID")

[@AardaerimusDAritonyss]

bkpickell It’s hard for me review these in my current situation, but one thing that jumped out at me was the asterisk next to your UPDATE. This should not be used for updates and inserts. Hope that helps! ☺️

[@bkpickell]

@@AardaerimusDAritonyss OMG Thank You! I stared at that stupid string for three hours this morning and couldn't figure it out. I am so used to putting the asterisk in the query string that I didn't even notice it wasn't in his. So yes it works now. Thanks again. I feel like such an idiot. I guess that's what lack of sleep does to a person.

[@BigIslandLavaPioneers]

bkpickell Very happy to be of help! (This is Aardaerimus & VBToolbox). I don’t get to help much anymore and it makes me sad. Glad I could grant one wish at least! Haha

[@bkpickell]

@@BigIslandLavaPioneers well, I really do appreciate it. I'm sad that this series isn't being updated anymore. This is one of the best VB channels on KZbin. I've been trying to figure out on my own how to interact with databases without the controls as the controls seem to be very limiting in what you can accomplish.

[@BigIslandLavaPioneers]

bkpickell I’m very happy that the videos have been so well received and helpful. I really miss doing these and wish I could continue them.

[@lildemon3030]

+VB Toolbox Hello can you tell me the codes for data types of integer and date??? I really need this now for my project at school thanks for the tutorial but I cannot update my database it said "Data type mismatch in criteria expression.".Need your help please!!!!!

[@VBToolbox]

+benjamin tennyson Are you using parameters in your query? Can I see the query that is throwing errors?

[@markmoretti6396]

How do I access the source code?

[@VBToolbox]

+Mark Moretti There is a download link in the video description. :-)

[@robertgagnon2947]

Hello, love the tutorials. I have a problem that you may be able to help me with. I was able to use your tutorial to successfully update a database but the problem I am encountering is that I am using a "for loop" to update numerous entries at a time and it takes for ever since we are opening and closing the connection every time. I assume that I need to create a new CLASS to do the UPDATING but unfortunately I am not very skilled in that area. Any ideas? MY CODE Private Sub CheckOutParts(dbase As String, stat As Boolean, first As Integer, last As Integer) For i = first To last Access.AddParam("@assigned", stat) Access.AddParam("@uid", i) Access.execQuery("UPDATE " & dbase & " SET assigned = @assigned WHERE ID = @uid") Next MsgBox("PARTS CHECKED OUT SUCCESSFULLY") End Sub Thanks

[@VBToolbox]

A better way to do batch updates is to create a "command chain" that will be processed during a single ExecQuery so that it only opens/closes one time during processing. I have done this, but haven't made a tutorial on it yet. Can't remember if I tested it with OleDB, but I *think* it should work. Essentially, you'll create a String for your update command, and then append each UPDATE command to that string, separating them with semi-colons. The trick will be dynamically generating parameters for each link in the chain. I usually increment an integer with each loop and append it to the parameter name so that they're in order and each parameter is unique. Example: Dim MyIndex As Integer Dim MyString As String = "" Loop: MyIndex += 1 Access.AddParam("@value" & MyIndex, somevalue) Access.AddParam("@filter" & MyIndex, somefilter) MyString += "UPDATE mytable SET mycolumn=@value" & MyIndex & " WHERE something=@filter" & MyIndex & ";" After Loop: Access.ExecQuery(myString) This will auto generate incremental parameters for update value and filter.

[@ksmemon1]

how to Display Max ID from Query Statement into Msgbox

[@VBToolbox]

A safer way to return your last ID is to modify The ExecQuery in the DBControl class to optionally run a "SELECT @@IDENTITY;" after your INSERT. EXAMPLE: [In DBControl Class - ExecQuery] Public Sub ExecQuery(Query As String, Optional ReturnIdentity As Boolean = False) ... ... Try ... ... Normal DB connection and query stuff ... ... ... ' RETURN IDENTITY If ReturnIdentity = True Then Dim ReturnQuery As String = "SELECT @@IDENTITY;" DBCmd = New OleDbCommand(ReturnQuery, DBCon) DBDT = New DataTable DBDA = New OleDbDataAdapter(DBCmd) RecordCount = DBDA.Fill(DBDT) End If Catch ... End Try ... Closing stuff ... EXAMPLE USAGE: ' EXECUTE INSERT COMMAND Access.ExecQuery("INSERT INTO members (username,[password],email,active) " & _ "VALUES (@user,@pass,@email,@active);", True) ' TEST If Access.DBDT.Rows.Count > 0 Then MsgBox(Access.DBDT.Rows(0)(0).ToString) End If

[@ksmemon1]

+VB Toolbox Glad to See Your Prompt Reply I Want to Show Max Id In Message Box Using Query "Select max(Id) From Members"

[@VBToolbox]

You have it correct. :-) Access.ExecQuery("SELECT MAX(ID) FROM members;") If Access.DBDT.Rows.Count > 0 Then MsgBox(Access.DBDT.Rows(0)(0).ToString) Please keep in mind that if multiple users are using the database to insert records at the same time, MAX(ID) may return the last identity created by User2 to User1 if User2 completes their INSERT first.

[@ksmemon1]

+VB Toolbox Thank you so much for your Reply, Y I am new for Learning Programming and Just Started from VB.Net And Your Videos Are Very Very Helpful for Me Specially DbControl Class, Could you Suggest me any Complete VB.net Learning Tutorial?

[@VBToolbox]

While I can't recommend any tutorials specifically, there are many tutorials on KZbin as well as other sources [e.g., Google Search]. I just found these on Google: www.tutorialspoint.com/vb.net/ www.visual-basic-tutorials.com/ I hope this helps some. :-)

[@lildemon3030]

Hello can you tell me the codes for data types of integer and date??? I really need this now for my project at school thanks for the tutorial but I cannot update my database it said "Data type mismatch in criteria expression.".Need your help please!!!!!

[@tralala449]

Sir.. Please Help me with my code here.. i cannot update my fields and i followed ur tutorial... please sir..

[@tralala449]

Sir.. Please Help me with my code here.. i cannot update my fields and i followed ur tutorial... please sir..