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.
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.
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.
Thank you, The Best tutorials for dealing with SQL Server dynamics. And it really works! I appreciate all the hard work and time you have put into these series!
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.
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.
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?
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.
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.
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!
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
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!.
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
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.
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
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. 🙂
At 42:00 you give us the option to change the connection string, but I don't see any examples of how to actually do it. I am using other parts of your code and it's been flawless so far, but I'm at a point where I need to connect to a different SQL server. Can you provide any direction on how to do this?
I'd be happy to, Justin. :-) With the connection string override used in this tutorial, you simply add a new connection string when you define your SQLControl class in your main form. Example: [In your main Form] Private SQL As New SQLControl("Server=YOURSERVER\YOURSQLINSTANCE;Database=YOURDATABASE;Trusted_Connection=True;") Alternatively, you could enhance the New() method in the SQLControl Class to provide a cleaner way to provide your Connection String parameters: Example: [In your SQLControl Class] ' ALLOW CONNECTION STRING OVERRIDE Public Sub New(Server As String, Database As String, Username As String, Password As String, Optional UseTrusted As Boolean = False) If UseTrusted = False Then DBCon = New SqlConnection(String.Format("Server={0};Database={1};User={2};Pwd={3};", Server, Database, Username, Password)) Else DBCon = New SqlConnection(String.Format("Server={0};Database={1};Trusted_Connection=True;", Server, Database)) End If End Sub You may then create your SQLControl instance like this: Private SQL As New SQLControl("MyServer", "MyDatabase", "MyUsername", "MyPassword")
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.
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 ?
@@tommcmeekin1 So glad I could be of help! I feel that using stored procedures is a much better approach to DB driven apps than embedded SQL commands, because you can maintenance them without recompiling, and they keep your code tidy when you need complex commands. 😀
hi am using vb 2008. so when adding parameter to db params.foreach(sub(p) dbcmd.parameters.add(p)) is not working, you said to use if foreach loop, cau help?
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 !!!
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! :-)
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
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
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
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 TH-cam 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!
Concurrent Simultaneous Action Hi i am having 2 laptops with different user names but same OS & settings, files. If i open control panel and change some settings say mouse sensitivity in laptop 1, the same should happen live/real time in laptop 2 and vice versa. Is is possible?
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.
@@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
@@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! 😀
I can not seem to get the sample tutorial to work. It keeps complaining about the server connection. DO I need to do something with the bak file? Can this tutorial work with SQLite?
Hi Sir! Can you Help me, I cant restore your database "SampleDB.BAK, I'm using MS SQL Server 2008 R2 and MS SQL Server 2012, in SQL 2008 R2 says that The Media family on device is incorrectly formed. SQL Server cannot process this media Family. Then in SQL 2012 it returns no backupset selected to be restored.
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 ?
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
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)
Hi VBToolbox. i have a problem with the login. I connect the sql db with windows authentication, everything looks good , but when the code run and try to DBCon.Open() takes a fault "Login Failed foe user...." any suggestion?
Hi .. I.m facing problem in executing query in my DataGrid.. Error Mesage "Object reference not set to an instance of an object" .... I tried to replace the table name from my own SQL table against your sample but it seems not identified....Can you provide any help how I can connect to my data table. Thanks
Hmmm... It's difficult to say, but I recommend double checking your object names, especially where you're calling them as a Sub() parameter. It sounds like you may be trying to use an object that doesn't exist.
I'm trying to restore the SampleDB.bak that you provided. I'm using SSMS version 18.6. I get this error when I try to restore: "The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SampleDB.BAK' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)." It is ERROR Number 3241. I do have SA rights. I realize that your .bak file is from 2008, but from what I understand versioin 18.6 should be backwards compatible. Any suggestions?
I have a database with 4 fields, I work on the first field on Form1 and the second field on form 2. The querys performed on form1 work great but the same code basically wont work from form2. Is it not possible to run from 2 seperate forms, it is an insert query and on form 2 I always get a insert error.
Hey! I am facing a problem when I try to connect to my server using an IP address. Error pops up saying- "The server was not found or was not accessible". Is there anything wrong in the connection string?
Hi VB Toolbox, I'm having a conversion problem and the error message said. Conversion Failed when converting the varchar value RCSP123 to data type Int. I don't know how to fixed this and I was wondering if you can help me. Thanks :)
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!
'LOAD PARAMS INTO DB COMMAND Params.ForEach(sub(p) DBCmd.parameters.add(p)) this code has an error it says expression expected can you help me with this? in SQLControl code.
Hi, i need to store an integer value from one of the cells to a variable. I need to use Execute Scalar but i cant understand how to call that using the SQLControl class. Can someone help please?
First I want to say Thank You. I have a question that I got a search form with datagridview and I tried IF SQL.RecordCount > 0 do something ELSE do something but it was not working. Thanks
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
I was able to restore it fine using 2014. There was an issue with the code though. the query that looks for username is incorrectly spelled as usernme without an "a"
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!!!
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! :-)
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")
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.
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?
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.
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!
I am working on the Grading system of the student. My sql database column named Av_lg has the record A+,A-,A,B,C,D and F. When I am using the logic LoadGrid("Select Roll, Av_lg, Av_gpa From Marks Where Av_lg Like @item;") it nicely produces all the Av_lg record. But when I am going to search for "A+" it produces all the result for "A" e.g. A+, A- A insted of only The record of "A+". Pls provide me a solution. Thanking you in advance.
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. :-)
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?
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.
![VB.NET Tutorial - Connect & Query a Microsoft Access Database [FULL]](http://i.ytimg.com/vi/q19OXha1jDw/mqdefault.jpg)
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.
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.
Hi, many thanks for this VB tutorial, by far is the best from what i found on TH-cam so far and i was looking for a while (few months).
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.
Same Here
Thank you, The Best tutorials for dealing with SQL Server dynamics. And it really works! I appreciate all the hard work and time you have put into these series!
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!
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.
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.
Your tutorials are too good to exist. 10000/10
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.
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?
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.
Just started this SQL series of videos after your Access ones. Excellent and a lot to learn😊
Awesome! Very happy you're enjoying the series! SQL server is still my favorite DB to work with. :-)
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.
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!
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
Awesome! Always happy to save a life! ;-D
22:24 - THIS IS the BEST THING I'VE HEARD!!! EXACTLY what I'm trying to do... THANK YOU!!!
never seen like the way you teach before, thanks a million....
wish you could post more of tutorial videos.....
You little marvel! You've gone and made functions into programs!!
🤣 You're killin' me, Anthony!
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
Your BACK!!! Wonderful videos!!!
Thanks for posting them... So helpful.
I hope so! Figured I'd best get to it whilst there's a lull in the chaos. :-D
dude i love you so much right now, you just saved me from the fucking assignment. thank godness i find you here
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!.
Hah! Suhweet! :-) I really hope I can deliver this time around. Been trying hard to get caught up after my move.
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
Thanks for these videos!! I've been using them for my job and they've helped me tons!! you're the best!
Awesome! I'm very happy that they're helping you out. I'm envious that you get to do this for work. :-D
THank you I'm newbie and I really appreciated your work here
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.
I'm very happy that the tutorials have been helpful! :-)
Welcome back bro. Thumbs up for your efforts!!
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
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. 🙂
Thank you so much .................................!!
You're doing a great help to the generations of learners
At 42:00 you give us the option to change the connection string, but I don't see any examples of how to actually do it. I am using other parts of your code and it's been flawless so far, but I'm at a point where I need to connect to a different SQL server. Can you provide any direction on how to do this?
I'd be happy to, Justin. :-)
With the connection string override used in this tutorial, you simply add a new connection string when you define your SQLControl class in your main form.
Example:
[In your main Form]
Private SQL As New SQLControl("Server=YOURSERVER\YOURSQLINSTANCE;Database=YOURDATABASE;Trusted_Connection=True;")
Alternatively, you could enhance the New() method in the SQLControl Class to provide a cleaner way to provide your Connection String parameters:
Example:
[In your SQLControl Class]
' ALLOW CONNECTION STRING OVERRIDE
Public Sub New(Server As String, Database As String, Username As String, Password As String, Optional UseTrusted As Boolean = False)
If UseTrusted = False Then
DBCon = New SqlConnection(String.Format("Server={0};Database={1};User={2};Pwd={3};", Server, Database, Username, Password))
Else
DBCon = New SqlConnection(String.Format("Server={0};Database={1};Trusted_Connection=True;", Server, Database))
End If
End Sub
You may then create your SQLControl instance like this:
Private SQL As New SQLControl("MyServer", "MyDatabase", "MyUsername", "MyPassword")
Thanks man, your videos made transitioning from VB6 to .net easy!
Thanks for such a sweet explanation - really loved this tutorial and started following your series
Thanks for the tutorials. Great Examples for real work and great for teaching
How would you load a combo box with data that has a primary key, an example would be customer with state on the address.
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.
Thanks for the tutorial! I really like the clean codes using classes, still learning for this skill :)
Thanks I could simplify my code at work a lot using this SQL control class.
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 ?
OOOH BUSTED, you gave it away! I saw that WoW folder, lol. Tell me you're not a night elf hunter named legolas :P
🤣 That was a looong time ago - but I was a gnome DK, back in the day.
wow that was an amazing tutorial, really clear code and well explained
Very nice tutorial !!! waiting for the next video, hope comes soon. Thanks a lot.
LOVE it - You are a life saver.
Hi. How can I make the lambda expression here to foreach loop? I am using vb 2008. I need help. please
Hi there, Cedielyn! I'm not certain which part of the code you're referencing, but should be a simple "For Each" loop using the object types shown. 🙂
Hi VB, Do you know how the Class SQLControl could be used to run a stored procedure in a ms server?
Hey there! SQLControl should run an SP no problem. Just run your EXEC sp_your_proc right from your query execution string with normal syntax. 🙂
@@VBToolbox Thanks man that worked great. Your class cuts out so much code it's awesome. 😊
@@tommcmeekin1 So glad I could be of help! I feel that using stored procedures is a much better approach to DB driven apps than embedded SQL commands, because you can maintenance them without recompiling, and they keep your code tidy when you need complex commands. 😀
hi am using vb 2008. so when adding parameter to db params.foreach(sub(p) dbcmd.parameters.add(p)) is not working, you said to use if foreach loop, cau help?
For i as integer = 0 to params.count -1
Dbcmd.parameters.add(params(i))
Next
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 !!!
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! :-)
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
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
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
Oh my! I haven't even seen 2022 yet. It's hard to say how much of this old code is deprecated now.
@@VBToolbox I am using your class even in VB2022 and still it works like a charm,
You just saved my life :) Thank you!
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 TH-cam 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!
wow, awsome, thanks for this, tomorrow I will start with part 2
Your tutorial was very helpful for me. Thanks A lot.
Concurrent Simultaneous Action
Hi i am having 2 laptops with different user names but same OS & settings, files.
If i open control panel and change some settings say mouse sensitivity in laptop 1, the same should happen live/real time in laptop 2 and vice versa.
Is is possible?
Really great!, we expect such more other videos, Thanks a lot.
Can I use the same steps using a service based database. i would like my project to be portable once finished. Thanks in advance
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.
@@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
@@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! 😀
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
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.
I can not seem to get the sample tutorial to work. It keeps complaining about the server connection. DO I need to do something with the bak file? Can this tutorial work with SQLite?
why not use ExecuteNonQuery() in sub ExecQuery() when you use insert or delete in query
Thx lot Bro . Very helpful tutorial . God bless you
Hi Sir! Can you Help me, I cant restore your database "SampleDB.BAK, I'm using MS SQL Server 2008 R2 and MS SQL Server 2012, in SQL 2008 R2 says that The Media family on device is incorrectly formed. SQL Server cannot process this media Family. Then in SQL 2012 it returns no backupset selected to be restored.
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 ?
So clean and nice code! This was very helpful, thanks a lot.
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
Thanks for this Simple and reach tutorial.
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)
Hi VBToolbox. i have a problem with the login. I connect the sql db with windows authentication, everything looks good , but when the code run and try to DBCon.Open() takes a fault "Login Failed foe user...." any suggestion?
and the connection string is this Private DBCon As New SqlConnection("Server=(LocalDB)\MSSQLLocalDB;Database=ECdb.mdf;Trusted_Connection=True; ")
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!
Great tutorial!! Best I have seen!
Hi .. I.m facing problem in executing query in my DataGrid.. Error Mesage "Object reference not set to an instance of an object" .... I tried to replace the table name from my own SQL table against your sample but it seems not identified....Can you provide any help how I can connect to my data table. Thanks
Hmmm... It's difficult to say, but I recommend double checking your object names, especially where you're calling them as a Sub() parameter. It sounds like you may be trying to use an object that doesn't exist.
hi Julius... double check your Sub() parameter
Amazing Tutorial Man!
I'm trying to restore the SampleDB.bak that you provided. I'm using SSMS version 18.6. I get this error when I try to restore: "The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SampleDB.BAK' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)." It is ERROR Number 3241. I do have SA rights. I realize that your .bak file is from 2008, but from what I understand versioin 18.6 should be backwards compatible. Any suggestions?
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.
I have a database with 4 fields, I work on the first field on Form1 and the second field on form 2. The querys performed on form1 work great but the same code basically wont work from form2. Is it not possible to run from 2 seperate forms, it is an insert query and on form 2 I always get a insert error.
Great Video/ Learned a bunch
Hey! I am facing a problem when I try to connect to my server using an IP address. Error pops up saying- "The server was not found or was not accessible". Is there anything wrong in the connection string?
#6 is Value of Type SQLConnection cannot be converted to SQLConnection;
code is DBcmd = New SQLCommand(Query, DBcon)
Hmmm... Might want to double check your declaration of DBCmd and make sure it's a SQLCommand and not a SQLConnection.
Hi VB Toolbox, I'm having a conversion problem and the error message said. Conversion Failed when converting the varchar value RCSP123 to data type Int.
I don't know how to fixed this and I was wondering if you can help me. Thanks :)
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!
Great work! How do you call the new connection string?
what is the alternate of microsoft SQL server management studio, if i dont have that?
'LOAD PARAMS INTO DB COMMAND
Params.ForEach(sub(p) DBCmd.parameters.add(p)) this code has an error it says expression expected can you help me with this? in SQLControl code.
Great Resource, Thank You Sir you're the best
I am getting a too many arguments error on Public Sub LoadGrid under the FindItem sub. I am using Visual Studio 2015.
Hi, i need to store an integer value from one of the cells to a variable. I need to use Execute Scalar but i cant understand how to call that using the SQLControl class. Can someone help please?
Nice and smooth tutorial, thanks a lot.
What can I do here to use this code as a transaction? I am facing problems in implementing rollback and commit.
Thanks a lot, It's really awesome :) so clean and simple.
Excellent Teaching
Hi, thanks for sharing, great stuff, how about calling stored procedures ?
Hi, Vinny! Not sure if you already saw it, but I just recently released a video on this topic. 🙂
First I want to say Thank You. I have a question that I got a search form with datagridview and I tried IF SQL.RecordCount > 0 do something ELSE do something but it was not working.
Thanks
Great Video, Thank you so much
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
Hi mate your SampleDB.Bak is faulty it will not restore I have tired it on 2008 and 2017 sqlserver so what can I do?
I was able to restore it fine using 2014. There was an issue with the code though. the query that looks for username is incorrectly spelled as usernme without an "a"
Great work. Thanks for this great job.
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!!!
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! :-)
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")
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.
I'll give this a go. Thanks very much!!
Great tutorial! Thank you!
Never give up
Thank you this series will help me with my project
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?
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.
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!
I am working on the Grading system of the student. My sql database column named Av_lg has the record A+,A-,A,B,C,D and F. When I am using the logic LoadGrid("Select Roll, Av_lg, Av_gpa From Marks Where Av_lg Like @item;") it nicely produces all the Av_lg record. But when I am going to search for "A+" it produces all the result for "A" e.g. A+, A- A insted of only The record of "A+". Pls provide me a solution. Thanking you in advance.
awesome tutorial! thanks so much
Hello I haven't visit your videos in a while. I been away But again very nice video keep up the great work.
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. :-)
I didn't know you moved. I'm soon to move my self.
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?
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.
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.