VB.NET Tutorial - Connect & Query a Microsoft SQL Server Database - Part 1

[@k1ng50]

Thank you very much for a well-organized, clear tutorial. I did a lot of VB4 programming in the 1990s but none since. I retired a few years ago and wanted to resurrect and continue a project I did back then. This video served as a great refresher and I am ready to continue your series.

[@roadie3124]

Thank you very much for this. It's very useful and I like your laconic style :-) I'm using VS2019. I'm 72 years old, retired, and I'm having fun exercising my mind learning VB.net and Visual Studio. I've been programming off and on since the late 1960s. The off periods were when I was managing mainframe software projects, doing strategic planning and being an IT Director for an insurance company. Programming is more fun. I started with FORTRAN at uni, then went onto Assembler, machine code, COBOL, ALGOL with Burroughs, used Burroughs DMSII, IBM's IMS, Digital's Rdb until I retired from the corporate rat race, bought a copy of Access when it was first released and had to learn VBA. I learned enough VB6 to modify existing systems. I got into SQL Server in 1997 and became an accidental DBA. I had such a lucrative business developing systems with Access front ends to SQL Server that I never got around to learning .net. I became fluent in T-SQL. I've flirted with learning VB.net a few times, but commercial pressured got in the way. I subscribed to an online learning company and didn't get much satisfaction. This tutorial of yours is exactly what I've been looking for to learn VB.net and how to interface with SQL Server. You explain things at the level I need. Thank you very much.

[@mariusadrianstefan9844]

Hi, many thanks for this VB tutorial, by far is the best from what i found on KZbin so far and i was looking for a while (few months).

[@grantharrismusic]

I've found this video equal parts challenging and rewarding. Rewarding because it has expanded my knowledge about databases and how sql works. Challenging because there are aspects of the coding that are above my self-taught level. To that extent if there is anyone who would be kind enough to show me how to make use of the Over-ride code in very very simple terms I would be extremely grateful. Thank you. UPDATE: I have done some thinking and reading and I re-watched this video and realise that the answer to my question was there all along - I just didn't understand enough to fully recognise it. Thanks for this video - it's been a great catalyst for learning so much new material for someone who started learning Vb by hacking excel macros. Many thanks.

[@zmareyshahi]

Same Here

[@chloekimball536]

My good sir, you are a lifesaver. I should browse through your channel. i have a feeling you will become one of my best teachers!

[@poweroverwhelming1847]

Your tutorials are too good to exist. 10000/10

[@pw3111]

Just started this SQL series of videos after your Access ones. Excellent and a lot to learn😊

[@VBToolbox]

Awesome! Very happy you're enjoying the series! SQL server is still my favorite DB to work with. :-)

[@fletcherevans830]

Thank you so much! These DB tutorial videos are super - I've learned a lot. Your content is well laid out and your presentation is excellent.

[@randysandberg3755]

Hi VBToolbox. Glad to see you active again. I hate moving too--I think most people do. At 66 years old I have done it 20 times so far in my adult life. I followed many of your DB tutorials in the past, and am waiting to see what you will present next. When you fill the combo box you can use a row index like r(0) instead of the column name. No big deal. It's just shorter.

[@aungyethu9743]

never seen like the way you teach before, thanks a million.... wish you could post more of tutorial videos.....

[@unicomp5705]

Great detailed intro to using VB.NET with SQL Server... Thanks... Mike in LA, Calif, USA. Believe it or not I have made tons of money using MS Access/VBA as a front-end to SQL Server on a LAN because it's much quicker to build with and has a great report writer built-in. But at some point you will outgrow MS Access as a front-end(you can still use it for other smaller apps), and need more speed and more options as a front-end and that means going to C# or VB .Net Winforms(or WebForms - ASP.NET) to re-create that front-end. And that's me now... LOL... Thanks again for the info.

[@VBToolbox]

UNICOMP Awesome! Now, if I could just get you to make me a tutorial on how to make tons of money (or even a little money) with Access and/or VB, that would be ultra rad! 😋 Thank you for the great comment!

[@leansixsigmabusinessoperat4833]

Thank you so much .................................!! You're doing a great help to the generations of learners

[@anthonypulsejr3521]

You little marvel! You've gone and made functions into programs!!

[@VBToolbox]

🤣 You're killin' me, Anthony!

[@TheJawtech]

GREAT tutorial, dude you have saved my application. I haven't done much newer VB but was pretty awesome with VB6. I've got my application together but for the life of me cannot get the "built in" stuff to work with the logic I need. I had realized that the default controls were not going to be much help but it's not like you see their code so there is nothing to change. Tableadapters seem to be a nightmare. This sqlcontroller class is EXACTLY what I was missing. JAW

[@boundhell3998]

dude i love you so much right now, you just saved me from the fucking assignment. thank godness i find you here

[@8T8Keez]

22:24 - THIS IS the BEST THING I'VE HEARD!!! EXACTLY what I'm trying to do... THANK YOU!!!

[@rowanbalana8578]

I just discovered your channel and Holy adsadjaskdsajd you are saving my life now. I wonder why you don't get million of views! Thank you so much bro

[@VBToolbox]

Awesome! Always happy to save a life! ;-D

[@VaderFaderVader]

Awsome!! I've litteraly just finished Part5, and was a little sad to miss the "User Manager" -part. And then the Updated version you mentioned 10 months ago, suddenly pops up. Thanks - your tutorials are really useful & informative. I'm learning a lot from you!.

[@VBToolbox]

Hah! Suhweet! :-) I really hope I can deliver this time around. Been trying hard to get caught up after my move.

[@alvinilmo8577]

can you help me with that error? and by the way thanks for the reply i really appreciate it. also thanks for uploading this video tutorial it really helps a lot

[@dougiemui4519]

Welcome back bro. Thumbs up for your efforts!!

[@misperry]

So how do you use the conneciton override you talked about? I would like to open another form and get user name and password info from user and then when they click the Search button it uses that for the connection. However, I run into lots of problems with not initializing the conenciton string on startup.

[@BigIslandLavaPioneers]

You can add a new connection string at any point. The important thing is to not immediately run commands against it until it has had enough time to fully initialize. Opening the connection is fast, but even at milliseconds, commands in the same thread may execute faster than the connection opens. Just a thought. Are you testing the connection each time?

[@misperry]

Yep I figured it out. What I did was create a global string called strConnect and stored the connect string in that from a form. Then All I had to do is in the LoadGrid() sub add the line SQL = New SQLControl(strConnect). This uses the override constructor that the vid had seen and puts whatever string you have into the DBcon.

[@prinschoco]

Thanks I could simplify my code at work a lot using this SQL control class.

[@wim2561]

This is great. Have been searching for time to find the right stuff where it is explained well. This did. Thanks just did part one have to go for the other parts. It feels as you sitting beside us and showing. Tanks a lot.

[@VBToolbox]

I'm very happy that the tutorials have been helpful! :-)

[@ranie4877]

THank you I'm newbie and I really appreciated your work here

[@giorgosnasenas3290]

Thanks for the tutorials. Great Examples for real work and great for teaching

[@gyustice665]

Thanks for these videos!! I've been using them for my job and they've helped me tons!! you're the best!

[@VBToolbox]

Awesome! I'm very happy that they're helping you out. I'm envious that you get to do this for work. :-D

[@bernardlogador5364]

Thanks man, your videos made transitioning from VB6 to .net easy!

[@Cylon39]

Your BACK!!! Wonderful videos!!! Thanks for posting them... So helpful.

[@VBToolbox]

I hope so! Figured I'd best get to it whilst there's a lull in the chaos. :-D

[@dorothebertens8490]

Very clear, simple and easy to undestand for us beginners. Used your override sub from the DBcontrol class, ( new(connectionString) and getting a type exeception. How to fix ?

[@yekyawthu219]

Excellent Teaching

[@derrickwalker3018]

Great work! How do you call the new connection string?

[@GautamBhatia713284]

do you take private online clasess plesae let us know "Please do it"........ Good teachers are real valuable asset and very hard and rare to find

[@VBToolbox]

I kinda wish I could, but it's extremely difficult where I live now. Also, I'm terribly out of practice since I haven't coded much in several years.

[@skfunnext]

Thanks for such a sweet explanation - really loved this tutorial and started following your series

[@ikramsyahpindolubis5747]

Never give up

[@CarlFritz24]

OK. That's good. Still we have noticed Microsoft said will nomore update version VS Express 10. I liked it a lot but... shouldn't we expect future errors when running a program ? Better, you could trach us using newer versions of VS, right ?

[@justinshears879]

Brilliant Videos for us newbies!! Building my first Program following as much of your work as I can, but have a real problem!!! I have copied Private Sub LoadCBX and renamed it, now I want to call up DataBase Column names only, so I can try and work out how to search with certain columns. Hope you can help BIG thank you !!!

[@VBToolbox]

Are you thinking of something like a dynamic query - or populating a ComboBox with table or column names? I did a video on this for MS Access, but SQL Server is slightly different - a little easier actually. In a nutshell, your query will look something like this: [In your ExecQuery] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable' After that, you populate your CBX from your DataTable [SQL.DBDT] just like you normally would with any other query. I hope that helps! :-)

[@justinshears879]

Really sorry for late reply as you replied straightway to me!, but I was like a child with new tool, got all excited and went off and watched your Access tutorial, and that was exactly what i was after. Thank you so so much!!!! Your brilliant

[@jasonjason8317]

OOOH BUSTED, you gave it away! I saw that WoW folder, lol. Tell me you're not a night elf hunter named legolas :P

[@VBToolbox]

🤣 That was a looong time ago - but I was a gnome DK, back in the day.

[@ataculsabiei]

wow that was an amazing tutorial, really clear code and well explained

[@lbb5754]

LOVE it - You are a life saver.

[@vinnypassmore5657]

Hi, thanks for sharing, great stuff, how about calling stored procedures ?

[@VBToolbox]

Hi, Vinny! Not sure if you already saw it, but I just recently released a video on this topic. 🙂

[@moseslauyihieng8436]

Thanks for the tutorial! I really like the clean codes using classes, still learning for this skill :)

[@InaGalaxyFarFarAway]

Excellent job with the videos. I hope all is well. I've used your instructions to get me going in the right direction for my project and I thank you for that. I do have one question though. Would you know of any resources that I could check out that would help me with being able to search my database using multiple keywords that are typed into a single textbox? I assume I would have to cycle through the words and split them apart which would allow me to search by each word; however, I don't really know the process for that and how to return the search results and display them into a listbox instead of a datagrid.

[@Ruud9010]

wow, awsome, thanks for this, tomorrow I will start with part 2

[@ronaldwaanders4690]

Hi, I realy love your tutorials... I learnd many new, now I have a problem. I'm sure you can help me... I want to print my data, using the DBControl, can you help me with it ??

[@VBToolbox]

Unfortunately, printing is not a trivial thing. If memory serves, there are many device settings to set via variables as well as some tricky RichText formatting concerns. I think I was going to attempt a video once, but got lost in the weeds. I will have to defer to the old Google search for this one. :-(

[@ronaldwaanders4690]

@@VBToolbox Thank you, I was making a list but I got problems with more pages... when I use the DBContol, every next page starts with the first record again, Maybe something with record index can help... start the next page with the following index... I still have a hard time with that...

[@rashedkarim1944]

Your tutorial was very helpful for me. Thanks A lot.

[@ghassansalem1711]

Great presentation, Thank you very much, I am having an error when filling DBDT, message(DBDT is not a dataset (I am using VS 2019), Can you please help

[@devwilliams3036]

Excellent material and presentation. Helping me quite a bit. Quick question: Do you have a video showing how to convert the ExecQuery sub into an ExecSP (stored procedure) sub? I have toyed with it a bit, seems that the trick is to change the command object and to handle the parameters. i seem to be deficient with regard to the latter.

[@devwilliams3036]

Figured it out. Changed the DBCmd object using DBCmd.CommandType = CommandType.StoredProcedure in the next line, then called the AddParam sub before calling the ExecSP sub DB.AddParam("@AccountID", 1) DB.AddParam("@UserID", 1) Worked great!

[@jameschen401]

Thanks for sharing such a great tutorial on youtube, you help me so much. After the class, I got some question need your help. Sometimes the username could be repeated more than 1 time, how to let the CBX only add each username only single time? Thanks for your time and reply.

[@Xyr00]

In your SQL query add "DISTINCT" like this: "SELECT DISTINCT username FROM members"

[@fredward5550]

Great tutorial. I am using VBS 2022 and have 6 errors. The following 4 are the same: ___________ is not a member of SQLConnection; state, Parameters, Open, and Close. #5 is add is not a member of CreateParamerters #6 is

[@VBToolbox]

Oh my! I haven't even seen 2022 yet. It's hard to say how much of this old code is deprecated now.

[@abhayp9]

@@VBToolbox I am using your class even in VB2022 and still it works like a charm,

[@ahmadhussainwebshare1001]

Thx lot Bro . Very helpful tutorial . God bless you

[@josepachecou]

Very nice tutorial !!! waiting for the next video, hope comes soon. Thanks a lot.

[@jRQLME]

YOU ARE THE VB GOD

[@jasontodt836]

I enjoyed your video! great work. Would you mind showing how to allow the end user to map and connect to a SQL database please? Example would be to place the SQL database onto a shared drive and multiple end users would be able to locate and connect through a mapped drive. Thank you for all your videos!!

[@SanthoshKumar-mx1rh]

ur video is very useful and very informative.. I m using vs2008. Which version i havte to use to use ur source code.

[@VBToolbox]

Hello, Santhosh Kumar! My source code is from VB.NET 2010; However, if you copy from the videos everything should work in 2008 except for lambda expressions. In 2008, these should be replaced with standard For Each loops. I hope that helps. :-)

[@hassansamara7381]

Thanks for this Simple and reach tutorial.

[@FERRISHVAC]

Great Resource, Thank You Sir you're the best

[@rafaelreis2632]

Thanks man!! That's a wonderful video! I'll see every single one now! I'm initiating on Vb.NET now, after various developments in Excel VBA, and that helped me a LOT! Hope there's much more yet! Congratulations!

[@jamesoconnor1056]

Great video. I tried adapting your solution to an asp.net web enabled solution but the GridView displays as empty on my first web page when it's displayed. Have you made a similar video illustrating how to do this as a web solution using asp.net rather than forms?

[@Knych-nr4ot]

Awesome video!, Im using Vb.net 2017 express windows form and connecting to sql server 2017 express, i would like the users when they run the program to be able to select what database to load. I tried setting up a textbox where the users will have to paste the location of the dbase then later on connect with the use of a button. However, it says value of Strong cannot be converted to connection (which is dim connection as new sqlconnection)

[@w00blyn]

Hi, thanks very much for sharing your work! It's incredibly helpful. Do you if you could use your SQLControl class for an ODBC connection? Thanks!

[@macrobody]

Very nice stuff, just what I was looking for. I need to convert a semi large project in Visual Fox Pro to something new(er). This combination of Visual Studio and SQL server should be a way to make everything look and feel the same for the users.

[@VBToolbox]

Awesome! I've had to do a bit of work in a SQL/VFP hybrid environment. Not gonna lie, I was tremendously happy to see VFP go the way of the dodo. :-P I had a VFP connector class [like my SQLControl] that would make it pretty easy to migrate data from VFP to SQL, but I recently moved and I'm not sure where that class went. :-(

[@harryjames7654]

Hi VBT Ill start by saying that this tutorial is absolutley fantastic!! - i was looking forever for a good video that explains this well so i'm really pleased you've taken the time to upload your knowledge to KZbin for us all :) My Question is, willt his SQL Control Class work ok with a multi-user application accessing a single Database? or will conflicts still happen? Many thanks!

[@pramodnegi7131]

Thanks for sharing knowledgeable video. You are an Erudite Person. Can you please help in to use the program to get multiple return parameters.

[@dorothebertens8490]

Love your tutorials. Do you have any with transaccions, love to learni more on this matter.

[@Chrissyhof]

Great tutorial!! Best I have seen!

[@arjunkhatiwada5148]

Really great!, we expect such more other videos, Thanks a lot.

[@chazbush2934]

Amazing Tutorial Man!

[@Kicajz7]

You just saved my life :) Thank you!

[@lorenzol707]

Great Video/ Learned a bunch

[@jackthehammer2245]

Nice and smooth tutorial, thanks a lot.

[@ga7853]

Thank you so much for this great tutorial. its as always very helpful and easy to degust thanks to your easy way of explaining things.

[@sriragkt]

Awesome 👍

[@yurishizu9]

Thank you this series will help me with my project

[@TheBrianLedbetter]

When loading the combobox, I’m having a heck of a time pulling in an “ID” field as the value behind what’s displayed in the drop-down box (LastName, FirstName). I need this value, UserID, once an item is selected from the combobox to re-query the database for user details, etc. Any chance you could point me in the right direction? Love the videos - keep up the great work!!!

[@VBToolbox]

Hello, Brian! This can definitely be a little tricky since the ComboBox Item has no additional storage properties apart from their text. You might be able to do this using Extension Methods, or you can create a very simple companion class to store additional item data [e.g., UserID] and maintain that list via your form. This worked for me: 1.) Add a class [ComboItem] to store the extra info 2.) Add a List(Of ComboItem) in the Form 3.) Create a Sub() to fill the List and ComboBox 4.) Create a Function() to Return the ID from the ComboItem 5.) Perform your Re-Query via the ComboBox_SelectedIndexChanged event and output the result to a TextBox Rather than post my code here, I made you a small sample project: www.dropbox.com/s/93f5hob7jgg4atz/ExtendedComboBox.zip?dl=0 I hope that helps! :-)

[@TheBrianLedbetter]

Ok, thanks very much. I was trying to re-create something I had done in VBv.6 way back when. I had the following inside of an ADO RecordSet loop that loaded the combobox. You can see that I was able to include the UserID field from the database in addition to what was displayed to the user. I just haven’t figured out the VB.Net syntax to achieve the same: cboUserList.AddItem objRS("UserLastName") & ", " & objRS("UserFirstName") & "" cboUserList.ItemData(cboUserList.NewIndex) = objRS("UserID")

[@VBToolbox]

Hmmm... Unfortunately, I wasn't able to get your method to work, but I was able to shorten my previous example quite a bit by using a dictionary instead of a companion class. Private UserDic As New Dictionary(Of Integer, String) Public Sub LoadCBX2() UserDic.Clear() cbx2.Items.Clear() SQL.ExecQuery("SELECT id, username + ', ' + password As Name FROM members;") For Each r As DataRow In SQL.DBDT.Rows Dim kvp As New KeyValuePair(Of Integer, String)(r("ID"), r("Name")) UserDic.Add(kvp.Key, kvp.Value) Next cbx2.DataSource = UserDic.Values.ToList End Sub Public Function GetID() As Integer Return UserDic.ToList.Find(Function(x) x.Value = cbx2.Text).Key End Function GetID() can then be used as a SQL Parameter for the re-query.

[@TheBrianLedbetter]

I'll give this a go. Thanks very much!!

[@adriaancoetzee7238]

Great Video, Thank you so much

[@aliali-zr3jk]

why not use ExecuteNonQuery() in sub ExecQuery() when you use insert or delete in query

[@yanpolettoangulski5148]

Ok man, its looks good its loooks ok but you forgot the main reason im here, how do i update the dataset and datatable with this query, counting we have 2 sql queries?

[@michaelnewman9645]

Another great tutorial. Are you a programmer by profession?

[@mirkomarkovic1492]

So clean and nice code! This was very helpful, thanks a lot.

[@vshah2703]

Hi, I am making a Web Application and have followed your excellent video to connect to SQL. Everything works fine but the gridview is not displaying any records. Can you help

[@norteclaportugal8829]

Great work. Thanks for this great job.

[@w00blyn]

awesome tutorial! thanks so much

[@fredward5550]

#6 is Value of Type SQLConnection cannot be converted to SQLConnection; code is DBcmd = New SQLCommand(Query, DBcon)

[@VBToolbox]

Hmmm... Might want to double check your declaration of DBCmd and make sure it's a SQLCommand and not a SQLConnection.

[@jordanlesesne5901]

Great tutorial! Thank you!

[@jimohajibolaakinyemi8252]

The tutorial is excellent

[@clashofclans6089]

awesome and really helpful code for learners.

[@erikvandervlugt2139]

Perfectly explained!!!

[@psychoLeow]

You are absolutely amazing. :)

[@DiyintheGhetto]

Hello I haven't visit your videos in a while. I been away But again very nice video keep up the great work.

[@VBToolbox]

Always great to see you, Joseph! I apologize for my slow responses. I'm still getting settled from my move and it's been difficult to keep up with all of the comments and questions and even more difficult to make new content. I'm trying to get caught up, though. :-)

[@DiyintheGhetto]

I didn't know you moved. I'm soon to move my self.

[@VBToolbox]

Yep, and I'll be moving again in less than a year, so it's going to be hard to keep up but I'll do what I can. I hate moving, but we gotta chase those goals and dreams, right?

[@DiyintheGhetto]

Just a update how did the move go? I know it has been a while so i was wondering. I also moved to Georgia my self.

[@edbohnemann5932]

Trying to replace the hard coded query in Inventory.vb with a stored procedure. Trying to create a new SQLCommand but finding it's not available to me. Any help Thanks

[@VBToolbox]

The Stored Procedure will work with standard syntax replacing your hard coded query SQLControl.ExecQuery("EXEC my_stored_proc @param1, @param2") - and you're right - this is the best approach, for sure. If you can't access the SQLCommand from outside the SQLControl class, just make sure to set the access level to PUBLIC instead of PRIVATE or Dim where it's declared in the class. 🙂

[@michaelnewman9645]

I am trying to write a tutorial on how an accounting package works using Visual Basic. I found this series of videos so good that I would like to incorporate the SQLControl Class code into my work with references to you and your KZbin channel.Would you have any objection to me doing this as I am not doing it for profit.. I have learned so much from people like you and would just like to give something back to the community.

[@VBToolbox]

Please feel free to use it in any way you like. :-) My only request is that credit is given where it's due. Thanks!

[@michaelnewman9645]

Thank you and I will say where it came from and insert a link to your YourTube channel. I will give you the credit and praise that you deserve. I believe that your video says far more than I can write in a brief description.

[@KhalidAfridi1]

Thanks a lot, It's really awesome :) so clean and simple.

[@mscoco1431]

you are the best thank u

[@fernandob9040]

Can I use the same steps using a service based database. i would like my project to be portable once finished. Thanks in advance

[@VBToolbox]

Perhaps I'm not fully understanding your question, but these steps work with most all common darabases, both service and file based. The only real difference I've seen is in the namespace Imports at the top of your DBControl class, and to make sure you have installed the .NET connector libraries for that, since only SQL is native in VB.NET.

[@fernandob9040]

@@VBToolbox ok thank you. So what will the connection string look like if my db is . An mdf? I know it might sound like a dumb question but I'm lost. Thank you in advance

[@VBToolbox]

@@fernandob9040 Not a dumb question at all. I know I have source for connecting an .mdf, somewhere, but I'm not certain where it is and I'm on the road; however, this site is an invaluable resource for pretty much every type of connection string you could ever need: www.connectionstrings.com/ I hope that helps! 😀

[@ahmedabdelwahd9520]

thanks for the class can you send converter of this class to work with Xamarin Android class

[@thiery572]

Interesting. I'll check out sometimes. Thanks.

[@JimBob-tn1hk]

How would you load a combo box with data that has a primary key, an example would be customer with state on the address.

[@VBToolbox]

You should be able to set the Data Source of the ComboBox directly from your SQL Data Adapter, else cycle through the records and add them to the combobox with a loop. I believe we did this in one of these old connect & query tutorials. I'm sorry I can't make a sample at this time, as my situation makes it difficult.

[@voice_of_habib]

Thank you for nice tutorial.

[@lifeonearth7040]

Most of tutorials on KZbin have videos where written items on screen are not clear that makes difficult for beginners

[@anthonypulsejr3521]

I could hug you! GET OUT OF THAT CHAIR AND LET ME!

[@VBToolbox]

😆

[@lionelcambe3805]

I have a question regarding in SQL Transaction commit and rollback, it is applicable to use it if the code happens just like this ex. sub button1 add.param() sql.execquery() for I=0 to rows add.param() sql.execquery() next end sub how can I put commit if this scenario happen, I hope I get answer to my question thank you