Your vb videos have been more helpful than many other videos, I can understand you, yo explain things very thoroughly, and your topics are spot on. I know you have updated many of your videos and look foreword to watching your other videos!
I'm currently a intermediate programmer. I skipped SQL lessons from my book because its not understandable easily. But you helped me learn SQL very easily.
Thank you so much for this video series!! I spent a day and a half over a weekend trying to figure out how to do this via separate tutorials, and couldn't make a dent. You spelled it out for me perfectly, and I got it to connect to my local SQL Database!
I'm glad that it has been helpful! :-) I highly recommend checking out the latest iteration of the SQLControl class as it has been greatly improved and simplifies usage. Source: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0 I've been planning a reboot of the SQL series using the updated class once things get settled a bit for me.
Great job. You move slow enough for me to get it without repeating the video a million times. Great teaching skills. I just got connected to my sql db via my vb.net. Thanks a bunch.
Posting my codes used might also help out other people .. What I really like your tutorials you sound so experts, know what you are doing and can debug codes without any hassle you know what are the inside out no matter how lengthy it is...Thumbs upp!!!
thank you so much for sharing your brain wealth, Sir!!! It has been a month sifting through countless tutorials knowing there's a better way than what they're showing...
+Julius Espinosa Hey! I'm happy to be of help! :-) It gets better, too. I have a new and vastly improved SQLControl class if you're interested. You're welcome to check it out if you like. www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
I see there is a lot of thanking you going on, still I want to add my appreciation for this crystal clear tutorial. This is something I can build and expand on!
Great video, someone in a previous comment said to use: Trusted_Connection=true works fine in place of sa/pswd. Thanks for the comments that told me to use trusted_connection=true, this works better if you are windows authenticated already logged in to your windows, they help too
That would be awesome. Thank you so much. Your videos are the only thing i have found so far to help me with VSTO. I'm a huge fan! I would love a copy of the project. :)
Here ya go, Sunni: www.dropbox.com/s/ugy6uz8zycy1vvb/DGVtoExcel.zip?dl=0 I just threw it together so it's likely got some imperfections, but I tried to comment the code to make it easy to see how it works. What it does: 1.) Connects to and Queries a SQL Database 2.) Returns SQL records to DataGridView 3.) Export Options: (With or Without Column Headers) 4.) Export button click launches SaveFileDialog 5.) Select a file name to save to *[.xlsx - Excel 2010]* 6.) Generates a new Excel spreadsheet and populates from DataGridView Bear in mind that is just one of several ways to work with Excel, and it may not be the most efficient, but it seems to be working well for me. Hope it helps! :-)
I really appreciate your work, you're good talking and explaining, I'm sorry about my grammar but I'm spanish speaker, anyway thanks for your good work sir!
That's a good idea. I'd really like to start doing more beginner videos so I appreciate the suggestion. :-) Hopefully, I can get some time in the near future to make more videos. For now, if you have any specific questions, I'll be happy to help when I'm able.
Hello.. I'm new here with SQL SERVER. What should I do with the 'user' and 'pwd', just because I set them with 'Windows Authentication'. When I installing the SQL Server, I set them to default settings. Your coding is work very well, but seems like I can't access my database. PLEASE HELP!
Pretty sure it's too late for Eddie, but I found this on stackoverflow and it worked for me, so i'll just leave it here for anyone who needs it. If you selected windows authentication during SQL Server installation you can add Integrated Security=SSPI instead of the username and password.
"Trusted_Connection=True;" should also be a viable replacement for the username and password segments of the connection string. :-) Improved SQLControl Class: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Thank you so much for this! Maybe you can also make a tutorial for MS Access connection, insert, update, view, delete .. and also populating database on Datagrid and how to use crystal report for vs2010 .. keep sharing!
This is a very clear video; though my attempts to apply its contents to my education seem to be failing. I set up the function in the SQLConnection class but that code doesn't seem to run when I use the IF SQL.HasConnection line
Chris Read Hello, Chris. I'm sorry that it's giving you trouble. :-( Is the Catch reporting any errors from the connection? When you declare an instance of your SQLControl class in your main form are you using "New"? If you continue to have issues with it, feel free to zip it up and share your project with me and I'll be happy to check it over when I get some time.
VB Toolbox Mr VB Toolbox - You got me to relook at the bit I needed to look at! I've got 20+ years experience of programming in COBOL and SQL(professionally) and Access Basic(for my own use) and I'm now trying adapt in my own time to OO coding! Its taking some adapting but I'm sure I'll get there! You were right, I didn't have the keyword New in the declaration of the SQL Control... So let me get this right, I needed the 'New' for the declare of the SQL Connection and the SQLControl (which is a self created object).... but no 'new' is required for the SQLCommand. Is that a different sort of Object? Thanks for your help
Chris Read Excellent! Glad that got you steered in the right direction. :-) Generally, when you declare an object variable (without new) you're setting aside an empty (null) memory space as a placeholder for that object. When you use the *New* keyword, you're actually creating an instance of that object. In the case of the SQLCommand [In the SQLControl Class], we've prepared the object, but it hasn't actually been used in this tutorial because we haven't created any queries yet. That was my fault. I should have omitted it from this tutorial, but it *is* used in the next. The SQLCmd [SqlCommand] will be instantiated with *New* each time that we run a new query.
VB Toolbox Ha! Its hardly your fault that I hadn't watched your next video yet! I'm sure there will be many things I will learn later, but I usually have to try things out while I'm learning. I do have one question though, - and I hope I'm not going to over-assume your assistance here - But when I created the database I'm trying to connect to in SQL Server management studio I used the Windows Authentication option, so it doesn't have a password as such. I've tried using a null string as a password but that doesn't work. But at least its telling me its not working now! So the code you showed in the first video is doing its job!
Chris Read No worries, Chris. I'm happy to assist as I'm able. :-) The Windows Authentication (Trusted Connection) is an easy fix. Simply remove the user and password parts [User/Pwd] of the connection string and replace them with: Trusted_Connection=True;
Love your videos. I appreciate all of your time and hard work. Keep up the good work! Question - How could I change that connectionstring to connect to a sql database which is located on a shared drive using file browser so that the end user may located and pick their own mdf? Thank you.
DV8 Computers Unfortunately, I’m not in a position to demonstrate this, but you should be able to pass the selected file path from your OpenFileDialog directly into your connection string. You can store the path in a text file, .ini, or application variable if you want the application to remember it. 🙂
if we are planning to supply values to the sql connection separately than doing it in the declaration part. do we have to type SQLCon.ConnectionString = { " " }
This was the most elegant and helpful coding I have yet to find on VSTO. However, I am completely stuck on trying to get data from a database into a worksheet. I'm working with an add-in, have your basic form as an action task pane, and I've been able to get my query onto a data grid, but I can't figure out how to export that onto my excel spreadsheet afterwards. I don't suppose you have a tutorial on this? Thanks in advance for your help.
Thank you for the feedback, Sunni! :-) Unfortunately, I don't have any VB to Excel videos, yet. I wish that I had a simple and straight forward solution for your app, but there are so many different options and controls for working in Excel with VB. On a positive note, Excel has native database access built in, so pulling database records directly into Excel is surprisingly simple using Microsoft Query. You can write SQL queries directly in, else run Stored Procedures right from Excel/Microsoft Query. In Excel (2010) --> Select the Data tab --> The click the "From Other Sources" button --> Choose or Create a new Data Source. Once it connects to the Data Source, the Microsoft Query window will open. You can then select your columns and tables via the wizard, else cancel and continue editing in MS Query. (I usually just cancel and write my SQL code directly by clicking the "SQL" button in MS Query. When you're done and see your query output, just click File --> Return Data to Microsoft Excel and select the worksheet to place the query output in. This will generate a refreshable query within your worksheet. If you must be able to edit Excel data via VB.NET, then we'll have to take a different approach and probably add a .NET reference to the "Microsoft.Office.Interop" namespace. This will give us access to our Excel controls in VB.NET.
I went ahead and made a small sample project that populates a DataGridView from SQL and then exports the contents of the DataGridView to Excel. If you want a copy of the project, let me know and I'll be happy to share. :-)
6 років тому
Hi! How can i change the conn. string variables (for ex. the location, database name...)? How can i create PUBLIC variables to change this informations, and access there from another forms?
Hello, a subtle point, in the lines where you Dim the classes SQLConnection and SQLCommand. The key word 'New' appears in one line but not the other. I was expecting it to be in both lines. I tried running with it in both lines, seems not to make a difference ..............................or does it ????????
+Les Collier Hello, Les. :-) Sorry for the confusion. I got a little ahead of myself in this tutorial. The SQLCommand isn't even used until the next tutorial. The reason I use *New* for the SQLConnection and not the SQLCommand is because I'm only defining the connection one time (giving it default values that won't be changed). The SQLCommand, on the other hand, will be re-initialized every time a new query or command is run against the database. When we don't use *New* it only sets aside a _bubble_ of memory, kind of like an empty box. When we use New we fill that box with items like Properties and other useful things. It's the difference between 'Dog' and 'Yellow Labrador with short hair, brown eyes, and loves to fetch sticks'. One is just an empty idea that could represent any kind of dog, and the _New_ dog is fully defined. I hope that makes sense. :-)
Hello, and thank you for sharing the most useful information about Data base programming that I have ever found .I have question and a very big problem . I want to create a setup file for install the program I have built on another system . I have tried very videos and tutorials but any of them wasn't useful and complete . I beg you help me and upload a video
I have a question. Can the information that you store in this SQL server be accessed by other computers that has installed my app? For example: Computer 1 put Orange in the SQL database. ..then the other computers or users accessing the database can see the "Orange." Is this possible with SQL server?
Absolutely. This is what SQL Server is designed for. :-) There are many considerations that must be taken for a large scale and multi-user environment, but in the end, SQL Server is designed for this and many high end systems use SQL Server as their back end.
Hello, Jona. :-) You need to make sure that the username and password that you're using in your connection string have been set up in your database. Additionally, you must keep in mind that the password is case sensitive and that you don't have any unexpected characters (i.g., spaces) in your User and Pwd.
This would be a good tutorial, but I've been super tied up lately. :-( There are different ways to handle validation. One way is enforce data types via SQL parameters (I have videos on Parameterized queries, but they don't show DataType enforcement). If you learn these, you can Add the params and then set their DBType property to be the type of your choosing. Another way that doesn't require parameters, is to simple build functions to look over the input data and return a value - either an updated/cleaned value, or a Boolean indicating that the data was proper.
This is great, but one question, when I build my project, the other user cant access it. I have added my PC name (host of the database) to their hosts file.a network related or instance specific error occurred while establishing a connection to SQL Sever. The server was not found or was not accessible. I have added the ports to my firewall as well as the service (even though my firewall is OFF)Thanks,
+Shootingnewb Make certain that the "SQL Server Browser" service is turned on and running on your server machine. You can do this via "services.msc" or via the SQL Server Configuration Manager.
i subscribe because you make me laught in the good way im not joking or trollin when you show your sql server name xD hahaha you sound so serious and that.. nice dude :)
Hi, this is very helpful series that I plan to watch them all. Quick question? I'm working on capstone project & it requires that we use the datafile (.MDF) SQLserver datafile. That way, you can attach to the project to send with it. Is there a ways to make this connection happen?
Hello! :-) I'm honestly not certain if it's possible to directly interface with the master data file of a SQL server. Are you trying to ship an empty database with your application, or does it need to contain data? How large must it be? If you are shipping an empty MDF template, it would be best to script generate a new database on the destination server. This would be done with SQL and can be fairly complex but is likely the cleanest way. If you must ship the MDF, you'll want to ship the LDF (Transaction Log) and then attach them to SQL server on the client side. I'm not sure if that process can be done in VB or if it must be done in the SQL server environment. Finally, if the database doesn't need to be very large (less than 4GB), the easiest way to ship a database with your app is to use SQL Compact Edition (native to VB). I haven't made a SQL Ce tutorial yet, but I'm planning on doing this. I hope this points you in the right direction.
VB Toolbox Small database around 5 tables, with almost less than 100 records. Probably database is not more than 10mb. I'm trying to ship the database with the application and it is not going to be in production, just for academic. Your tutorials were really awesome. I'm going to finish them all.
Abdillahi Jibril Thank you, Abdillahi. :-D In your case, I think that SQL Ce would be perfect for your application. SQL Ce is easy to use and very similar to SQL Server except that it requires no server install and is contained completely in a single database file that can ship with your app. :-) I have a sample app that you're welcome to download and review until I'm able to get working on a tutorial. Let me know if you're interested and I'll link it.
(local) (SQL Server 10.0.1600 - DELL-PC\DELL) is the name of my instance as displayed in SQL Server , when I named the server name in the sqlcontrol code sqlCon As New SqlConnection With {.ConnectionString = "local;Database=video;Trusted_Connection=True"} , it showed an error message !!
+Malani Gola All you should have to do is change the "Data Source=" portion of your connection string to point to the network share path where the database file is stored. Example: "Data Source=\\servername\sharename\Sample.accdb;"
hi! Please help me because im having problem with connecting to sql server. I am using vb .net 2012 and i have installed sql server management studio 2012 with database engine. First, I followed your tutorial and it worked fine and i actually finished part 1 and 2 of your tutorial but after I restarted my computer, it didn't work anymore. So I tried to do it again from the start but it didn't worked well. The try catch command isn't working also even though i checked every command many times. I don't see any problem with the codes and i think the sql server is running properly. I might have disabled something very important. Thank You in advance, I'll really appreciate your help.
I Apologies for this comment, I finally found my error on code. It took me a while because it didn't show any error message while running the program. What i did was this "Private SQLUSER As SQLControl" instead of this "Private SQLUSER As New SQLControl" i didn't notice New. That was a big mistake because my class became useless. The only problem is myself :D, by the way THANK YOU SO MUCH FOR VERY HELPFUL TUTORIALS.
Thanks for this tutorial .. anyway I am having a bit of a problem when I am inserting multiple data only four of it are created ! Why is that ?! Thanks in advance!!
Hello, Melina. :-) I'm using SQL Server 2008 Express for these videos. However, my next series will be on SQL Ce (Compact Edition), which is awesome because the SQLCe database is portable and requires no installation. It's a great alternative to SQL Server if your needs do not require a full server.
Great tutorial, thanks a lot.It is impressive to see that you have helped so many people with their questions also! your steps worked well for me but I have one question; is there an issue using "Integrated Security=SSPI" rather than supplying a username and password? My app will be used by multiple clients connecting to a central server and I have assumed this method will use windows login credentials so will be secure. thanks
Bernie Milne Greetings, Bernie! Trusted connections (Integrated Security) are more secure, but the reason that many apps rely upon a SQL username/password is due to the fact that managing application access on the database end - especially in larger environments - could be a nightmare. Instead of using a single SQL db account to gain access to the application database, you'd have to grant access and permissions to each Windows client within the SQL server. In a smaller environment this may not be a big problem. For an app that would require very few installs, a Trusted Connection may actually be a better solution. Most large scale applications use a single SQL login to access the database, and then apply more granular security within the application itself.
+John Roish Beduya It is not necessary to use SQL Server Authentication. As an alternative you may use a Trusted Connection [Windows Authentication] by simply replacing the "User=username; and Pwd=password;" sections of the connection string with "Trusted_Connection=True;". You will want to ensure that your credentials have been added to the proper SQL security roles for your database.
+John Roish Beduya The main difference is that Windows authentication is passed from your operating system or domain, whereas SQL credentials are held within SQL server's security system. As far as efficiency goes, it really depends upon the nature of your system and how you intend to use it. If you're creating a database application that will be distributed to others [clients], then it might not make sense to grant security roles to _your_ windows account which will be inaccessible to the client. For a quick "out of the package" deployment, using a default 'sa' or master security account will allow the end user to forego worrying about individual SQL user accounts. In most cases, all clients using the db app will be using the same [SQL] account to access the system, but they will not see this. The administrator can change the credentials on the back end as desired, but the end user will not need to know how to connect to SQL server. The client will access the database with an administrative account, typically, but will be restricted within the application by separate application account (stored within your application database tables). From an administrative standpoint it doesn't really matter, since it all comes down to the SQL security roles. If the account has the necessary roles applied then it's all the same to the server whether SQL or Windows authentication is used.
thanks for the video just want ask: what are the benefits to contact to the database with a connection string vs connecting with sql server data tools thanks again
Functionally, I don't think that one way is really better than another. I mainly do a coded connection class because it helps us understand what's actually going on under the hood of our application, unlike components that are derived from wizards. One place where the wizards and bindings do win, however, is when working with Reporting. I've found that it's absurdly difficult (which is ridiculous) to work with the Report Designer and custom Datasets.
Hi, I currently have VB 2013 downloaded and want to link it to SQL Server Databases. What version of SQL should I download? If possible could you reply with a link to a suitable download? Thanks
Hello, Ryan. The version of SQL server that you'll want to download is really dependent upon your needs. You should be able to connect to most any SQL version with VB, though I'm not sure what version the native ADO.NET library is in VB 2013. Older databases may require an additional ADO.NET download and reference. If you do not wish to download and run a complete SQL installation you can use the Local Database option in VB. This is a great, lightweight database, though is not as robust as a fun SQL install. Beyond that, you can download SQL Express (free, 4GB size limit) from Microsoft: SQL Express 2008 R2: www.microsoft.com/en-us/download/details.aspx?id=30438 SQL Express 2012: www.microsoft.com/en-us/download/details.aspx?id=29062 SQL Express 2014: www.microsoft.com/en-us/download/details.aspx?id=42299 Also, you're not limited to SQL Server with VB. Most all databases (MS Access, MySQL, etc.) have a downloadable ADO.NET library which can be referenced within your project. The syntax is nearly identical with each connector. I hope that helps. :-)
PLEASE HELP!!! Hey, im having a bit of trouble, i cant connect to my database, i keep getting this error "sql network interfaces error 26 - error locating server/instance specified"... but i look at my server and my program and they both have the same server/instance. PLEASE HELP!!!
Michael Chaney Hello, Michael. Because this error could indicate a number of different problems, I can't give a definite answer. Here are some things to consider: Is there a typo in your connection string? Do you have a firewall or other security software enabled that could be blocking you? Are you trying to connect to a server that is not on your local network? Can you successfully ping and communicate with the server computer by hostname and IP address? Make certain that your SQL server is configured [In *SQL Server Configuration Manager*] to allow TCP/IP and Named Pipes connections *ALSO* ensure that the SQL Server Browser service is set to start automatically. Here are some search results regarding this error: blogs.msdn.com/b/sql_protocols/archive/2013/07/11/2609615.aspx?PageIndex=13 social.msdn.microsoft.com/Forums/sqlserver/en-US/ed7b78c0-7dcb-4be7-96fa-f85b74f0e413/sql-network-interfaces-error-26-error-locating-serverinstance-specified?forum=sqldataaccess
VB Toolbox no typo in my string. i set the firewall to allow the sql port of 1433 and 1434, so nope. im trying to connect to a database on a different computer on my LAN. yes i can ping to the server computer. so idk whats going on...
Hi there! This is really a very helpful video. I just notice an issue when connecting to my DB. When I used a connection string using servername\instancename and username "sa" with the correct password, I am getting a network-related or instance specific error....But when I removed the instance name and only use the server name I am connecting to the DB successfully. Now if I try to connect using ODBC to test, I cannot connect without the instance name. Any ideas why is this happening?
I'm not 100% certain, but I believe that if you're connecting to the default instance of SQL server with ADO.NET it doesn't need or want the instance name.I think that you will only need the instance if you're connecting to a non-default instance. ODBC likely handles the connection slightly differently.
+daizaree dablo I'm always happy to help if I am able. :-) I can't promise that I'll be able to solve every problem, but we can always try. Feel free to ask and I will respond if I am able.
Hello, i'm a beginner and i'm having trouble setting up a decent database. Do you happen to have a tutorial on this aswel on your profile or a link to a good tutorial that could help me? thx
Hi there, Oliver. Unfortunately, I don't have any Database setup tutorials. A big problem with this is that there are so many setup variables based upon the specific needs of applications and environments. My recommendation would be to check out "Best Practices" for your preferred database platform. Beyond that, data normalization and good indexing practices are always a good thing to check out. :-)
This is most commonly caused by a missing "New" command when initializing a new object. If you try to use the object for it is created it is NULL and will crash. Common problem: Private SQL As SQLControl Fix: Private SQL As New SQLControl Check the error message details to see which object is throwing the error and it should be easy to fix. :-)
Can I assume that XXXX is the name of your server? You might wish to verify that TCP/IP and NamedPipes are both enabled in your SQL Configuration Manager. Beyond that, you should be able to just specify the XXXX (server hostname) in your connection string.
I tried this on a Visual Studio 2019 Community and SQL Server v18.5 and it worked perfectly. Thumbs up. Great tutorial.
I want to say a lot of thanks to you for this great tutorial. Very very helpful.
I never thought I'd nerd out enough to enjoy doing this, but you made it so simple!
Your vb videos have been more helpful than many other videos, I can understand you, yo explain things very thoroughly, and your topics are spot on. I know you have updated many of your videos and look foreword to watching your other videos!
I'm currently a intermediate programmer. I skipped SQL lessons from my book because its not understandable easily. But you helped me learn SQL very easily.
I'm very happy that it's been helpful. :-)
Thank you so much for this video series!! I spent a day and a half over a weekend trying to figure out how to do this via separate tutorials, and couldn't make a dent. You spelled it out for me perfectly, and I got it to connect to my local SQL Database!
I'm glad that it has been helpful! :-) I highly recommend checking out the latest iteration of the SQLControl class as it has been greatly improved and simplifies usage.
Source: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
I've been planning a reboot of the SQL series using the updated class once things get settled a bit for me.
+VB Toolbox Thanks! I will look into that when I get to set down and work on it again :)
Great job. You move slow enough for me to get it without repeating the video a million times. Great teaching skills. I just got connected to my sql db via my vb.net. Thanks a bunch.
Thank you for the encouragement, Sean! :-) I'm happy that it was helpful.
Posting my codes used might also help out other people .. What I really like your tutorials you sound so experts, know what you are doing and can debug codes without any hassle you know what are the inside out no matter how lengthy it is...Thumbs upp!!!
Love your tutorials, great job and Thanks alot from a guy who is a bit lost in his VB MIS class right now.
I'm very happy that they've been a help, Todd. I appreciate the feedback. :-) Let me know if you have any suggestions for future tutorials.
this video might be old but still saved my life. thanks a lot, man!
Awesome, Jamie. I'm glad that it was helpful! :-)
thank you so much for sharing your brain wealth, Sir!!! It has been a month sifting through countless tutorials knowing there's a better way than what they're showing...
+Julius Espinosa Hey! I'm happy to be of help! :-) It gets better, too. I have a new and vastly improved SQLControl class if you're interested. You're welcome to check it out if you like.
www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
I see there is a lot of thanking you going on, still I want to add my appreciation for this crystal clear tutorial. This is something I can build and expand on!
Awesome, Sven! I really appreciate the encouraging feedback. :-D
the best VB Teacher i glad to see VB Pro Programmer and your good series
Excellent set of video tutorials. Many, many thanks.
Great video, someone in a previous comment said to use: Trusted_Connection=true works fine in place of sa/pswd. Thanks for the comments that told me to use trusted_connection=true, this works better if you are windows authenticated already logged in to your windows, they help too
Merci :) you are a calm clear and freaky easy to understand....
Many thanks to take the time to share your knowledge.
That would be awesome. Thank you so much. Your videos are the only thing i have found so far to help me with VSTO. I'm a huge fan! I would love a copy of the project. :)
Here ya go, Sunni: www.dropbox.com/s/ugy6uz8zycy1vvb/DGVtoExcel.zip?dl=0
I just threw it together so it's likely got some imperfections, but I tried to comment the code to make it easy to see how it works.
What it does:
1.) Connects to and Queries a SQL Database
2.) Returns SQL records to DataGridView
3.) Export Options: (With or Without Column Headers)
4.) Export button click launches SaveFileDialog
5.) Select a file name to save to *[.xlsx - Excel 2010]*
6.) Generates a new Excel spreadsheet and populates from DataGridView
Bear in mind that is just one of several ways to work with Excel, and it may not be the most efficient, but it seems to be working well for me. Hope it helps! :-)
This is a very nice tutorial vdo. I am an absolute beginner. Just following your steps now i can build my own app!
I really appreciate your work, you're good talking and explaining, I'm sorry about my grammar but I'm spanish speaker, anyway thanks for your good work sir!
+Peter García Thank you, Peter! I appreciate the encouraging feedback. :-)
Thank you for this tutorial. It is very helpful for beginners looking for simple explanations how it should be. We appreciate your work.
Fantastic tutorial. You've no idea how much this has helped me. Thanks!
I'm very happy that it's been helpful to you! :-)
you help me recall my programming knowledge in vb.net and ms sql..thanks a lot!
At last, classes tutorial!!!!,it is very helpful video,you are really the best!!,thank you sooo much
I really love your tutorials, I wish you could make one on declaring variables in VB
That's a good idea. I'd really like to start doing more beginner videos so I appreciate the suggestion. :-) Hopefully, I can get some time in the near future to make more videos. For now, if you have any specific questions, I'll be happy to help when I'm able.
I appreciate it, thanks a lot. I will contact you
Thanks a lot!! (Following your tutorials from Argentina)
This is grate, I really appreciate your time and effort to giving us a good lesson.
thank you dude please keep going that so amazing tutorial i'am from Egypt i learn so much from you :D !
MrSilk Gaming Hello, MrSilk! That's awesome! I've always wanted to visit Egypt and see the pyramids. Thanks for stopping in and commenting. :-)
ME TOO egypt
Fantastic tutorial! Very helpful and doesn't drag on, thank you!
Worked and easy to follow! Thank you!
thanks this video tutorial really help me out😀😀😀😀
Very helpful and excellently explained! Thanks!
I'm very happy that it has been helpful! Many thanks for the encouraging feedback. :-D
i love it, you explain it real good
Thank you very for your help. All its working fine. I'll see all your videos
Thank you so much, your tutorials are awesome.
Thank you for watching, Paul! :-)
Hello.. I'm new here with SQL SERVER.
What should I do with the 'user' and 'pwd', just because I set them with 'Windows Authentication'.
When I installing the SQL Server, I set them to default settings.
Your coding is work very well, but seems like I can't access my database.
PLEASE HELP!
Pretty sure it's too late for Eddie, but I found this on stackoverflow and it worked for me, so i'll just leave it here for anyone who needs it. If you selected windows authentication during SQL Server installation you can add Integrated Security=SSPI instead of the username and password.
"Trusted_Connection=True;" should also be a viable replacement for the username and password segments of the connection string. :-)
Improved SQLControl Class:
www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
+VB Toolbox thank you sir
+Florin Militaru yaa.. but it's okay.. thank you sir
thanks to your tutorial. it helps me a lot
Thanks for this. Informative and to the point!
This is the most helpful tutorial and channel. Thumbs up :like: :)
thanks bro! great tutorial keep it up...i'm still learning .net
Thank you so much for this! Maybe you can also make a tutorial for MS Access connection, insert, update, view, delete .. and also populating database on Datagrid and how to use crystal report for vs2010 .. keep sharing!
Thank you for this tutorial , I really appreciate your effort to giving and teaching us thru this video
Thanks a lot! You're a life saver. God bless you! Nice tutorial, I just listened to your instructions and It worked flawlessly. Thanks again~ :)
And thank *YOU* for the feedback, Nell. :-) I'm very happy that it was helpful.
Ya bro. I like your friendly talking and nice tutorial. It helped me. Thanks bro.
Perfect! It simply helps me out a lot!
Thanks a lot. :-)
I'm very happy that it was helpful! :-)
Thanks. It is very helpful.
*Hey if you get errors like me* try writing this to the ConnectionString:
Server=YOUR_SERVER;Database=YOUR_DATABASE;Trusted_Connection=True;
I was fighting with the error for about hour hope this help if you get errors
Hey! i would like to say thank you becuase we have some problem, all we just need todo is remove the instance name
Awesome vid! thank you for making a very helpful video
you are the best... thanks a lot :) I am from chile
Thank You. Very useful to me.
Excellent!
Thank you so much!
Great tutorial! Thumbs up!
this was awesome, super easy
This is a very clear video; though my attempts to apply its contents to my education seem to be failing. I set up the function in the SQLConnection class but that code doesn't seem to run when I use the IF SQL.HasConnection line
Chris Read Hello, Chris. I'm sorry that it's giving you trouble. :-( Is the Catch reporting any errors from the connection? When you declare an instance of your SQLControl class in your main form are you using "New"?
If you continue to have issues with it, feel free to zip it up and share your project with me and I'll be happy to check it over when I get some time.
VB Toolbox Mr VB Toolbox - You got me to relook at the bit I needed to look at!
I've got 20+ years experience of programming in COBOL and SQL(professionally) and Access Basic(for my own use) and I'm now trying adapt in my own time to OO coding! Its taking some adapting but I'm sure I'll get there!
You were right, I didn't have the keyword New in the declaration of the SQL Control... So let me get this right, I needed the 'New' for the declare of the SQL Connection and the SQLControl (which is a self created object).... but no 'new' is required for the SQLCommand. Is that a different sort of Object?
Thanks for your help
Chris Read Excellent! Glad that got you steered in the right direction. :-)
Generally, when you declare an object variable (without new) you're setting aside an empty (null) memory space as a placeholder for that object. When you use the *New* keyword, you're actually creating an instance of that object.
In the case of the SQLCommand [In the SQLControl Class], we've prepared the object, but it hasn't actually been used in this tutorial because we haven't created any queries yet. That was my fault. I should have omitted it from this tutorial, but it *is* used in the next.
The SQLCmd [SqlCommand] will be instantiated with *New* each time that we run a new query.
VB Toolbox Ha! Its hardly your fault that I hadn't watched your next video yet! I'm sure there will be many things I will learn later, but I usually have to try things out while I'm learning.
I do have one question though, - and I hope I'm not going to over-assume your assistance here - But when I created the database I'm trying to connect to in SQL Server management studio I used the Windows Authentication option, so it doesn't have a password as such. I've tried using a null string as a password but that doesn't work.
But at least its telling me its not working now! So the code you showed in the first video is doing its job!
Chris Read No worries, Chris. I'm happy to assist as I'm able. :-)
The Windows Authentication (Trusted Connection) is an easy fix. Simply remove the user and password parts [User/Pwd] of the connection string and replace them with: Trusted_Connection=True;
Love your videos. I appreciate all of your time and hard work. Keep up the good work!
Question - How could I change that connectionstring to connect to a sql database which is located on a shared drive using file browser so that the end user may located and pick their own mdf? Thank you.
DV8 Computers Unfortunately, I’m not in a position to demonstrate this, but you should be able to pass the selected file path from your OpenFileDialog directly into your connection string.
You can store the path in a text file, .ini, or application variable if you want the application to remember it. 🙂
if we are planning to supply values to the sql connection separately than doing it in the declaration part. do we have to type
SQLCon.ConnectionString = { " " }
This was the most elegant and helpful coding I have yet to find on VSTO. However, I am completely stuck on trying to get data from a database into a worksheet. I'm working with an add-in, have your basic form as an action task pane, and I've been able to get my query onto a data grid, but I can't figure out how to export that onto my excel spreadsheet afterwards. I don't suppose you have a tutorial on this? Thanks in advance for your help.
Thank you for the feedback, Sunni! :-)
Unfortunately, I don't have any VB to Excel videos, yet. I wish that I had a simple and straight forward solution for your app, but there are so many different options and controls for working in Excel with VB.
On a positive note, Excel has native database access built in, so pulling database records directly into Excel is surprisingly simple using Microsoft Query. You can write SQL queries directly in, else run Stored Procedures right from Excel/Microsoft Query.
In Excel (2010) --> Select the Data tab --> The click the "From Other Sources" button --> Choose or Create a new Data Source.
Once it connects to the Data Source, the Microsoft Query window will open.
You can then select your columns and tables via the wizard, else cancel and continue editing in MS Query. (I usually just cancel and write my SQL code directly by clicking the "SQL" button in MS Query.
When you're done and see your query output, just click File --> Return Data to Microsoft Excel and select the worksheet to place the query output in.
This will generate a refreshable query within your worksheet.
If you must be able to edit Excel data via VB.NET, then we'll have to take a different approach and probably add a .NET reference to the "Microsoft.Office.Interop" namespace. This will give us access to our Excel controls in VB.NET.
I went ahead and made a small sample project that populates a DataGridView from SQL and then exports the contents of the DataGridView to Excel. If you want a copy of the project, let me know and I'll be happy to share. :-)
Hi! How can i change the conn. string variables (for ex. the location, database name...)? How can i create PUBLIC variables to change this informations, and access there from another forms?
hi, I executed this code but it says login failed for user 'sa'. any username I give would show same message. Please help
For the connection string how can you default to windows user and password login?
Hello, a subtle point, in the lines where you Dim the classes SQLConnection and SQLCommand. The key word 'New' appears in one line but not the other. I was expecting it to be in both lines. I tried running with it in both lines, seems not to make a difference ..............................or does it ????????
+Les Collier Hello, Les. :-) Sorry for the confusion. I got a little ahead of myself in this tutorial. The SQLCommand isn't even used until the next tutorial.
The reason I use *New* for the SQLConnection and not the SQLCommand is because I'm only defining the connection one time (giving it default values that won't be changed). The SQLCommand, on the other hand, will be re-initialized every time a new query or command is run against the database.
When we don't use *New* it only sets aside a _bubble_ of memory, kind of like an empty box. When we use New we fill that box with items like Properties and other useful things.
It's the difference between 'Dog' and 'Yellow Labrador with short hair, brown eyes, and loves to fetch sticks'. One is just an empty idea that could represent any kind of dog, and the _New_ dog is fully defined. I hope that makes sense. :-)
Hi Aaerderimus! Your SQL videos have been of great help. Will you be doing one for working with SQL Transactions as well?
+Oli Milo Funny you should ask. I'm working on a refresh of my SQL series right now. :-D
I'll be looking forward to that! Hope you include SQL Transactions as well, thanks!
Do you have any tutorial on how to make a project with name last name and address but how to write the code?
Thx! I just need to connect VB + SQL 2008. It's simple work! :)
you can also specify the server as "Server=.\SQLEXPRESS" without the MINECRAFT
mine says "the server was not found or not accessible" how to fix this?
Hello, and thank you for sharing the most useful information about Data base programming that I have ever found .I have question and a very big problem . I want to create a setup file for install the program I have built on another system . I have tried very videos and tutorials but any of them wasn't useful and complete . I beg you help me and upload a video
I have a question. Can the information that you store in this SQL server be accessed by other computers that has installed my app?
For example:
Computer 1 put Orange in the SQL database.
..then the other computers or users accessing the database can see the "Orange." Is this possible with SQL server?
Absolutely. This is what SQL Server is designed for. :-) There are many considerations that must be taken for a large scale and multi-user environment, but in the end, SQL Server is designed for this and many high end systems use SQL Server as their back end.
Thank you , it help me a lote
Hi! I do have some problem the error says "Login failed for user 'My username'.". I have a database engine in my server type.
Hello, Jona. :-) You need to make sure that the username and password that you're using in your connection string have been set up in your database. Additionally, you must keep in mind that the password is case sensitive and that you don't have any unexpected characters (i.g., spaces) in your User and Pwd.
Sir, can you make a tutorial on how to validate data from textbox before adding the values to the database?
This would be a good tutorial, but I've been super tied up lately. :-(
There are different ways to handle validation.
One way is enforce data types via SQL parameters (I have videos on Parameterized queries, but they don't show DataType enforcement).
If you learn these, you can Add the params and then set their DBType property to be the type of your choosing.
Another way that doesn't require parameters, is to simple build functions to look over the input data and return a value - either an updated/cleaned value, or a Boolean indicating that the data was proper.
This is great, but one question, when I build my project, the other user cant access it. I have added my PC name (host of the database) to their hosts file.a network related or instance specific error occurred while establishing a connection to SQL Sever. The server was not found or was not accessible. I have added the ports to my firewall as well as the service (even though my firewall is OFF)Thanks,
+Shootingnewb Make certain that the "SQL Server Browser" service is turned on and running on your server machine. You can do this via "services.msc" or via the SQL Server Configuration Manager.
Found very useful, thanks
i subscribe because you make me laught in the good way im not joking or trollin when you show your sql server name xD hahaha you sound so serious and that.. nice dude :)
Hi, this is very helpful series that I plan to watch them all. Quick question? I'm working on capstone project & it requires that we use the datafile (.MDF) SQLserver datafile. That way, you can attach to the project to send with it. Is there a ways to make this connection happen?
Hello! :-) I'm honestly not certain if it's possible to directly interface with the master data file of a SQL server. Are you trying to ship an empty database with your application, or does it need to contain data? How large must it be?
If you are shipping an empty MDF template, it would be best to script generate a new database on the destination server. This would be done with SQL and can be fairly complex but is likely the cleanest way.
If you must ship the MDF, you'll want to ship the LDF (Transaction Log) and then attach them to SQL server on the client side. I'm not sure if that process can be done in VB or if it must be done in the SQL server environment.
Finally, if the database doesn't need to be very large (less than 4GB), the easiest way to ship a database with your app is to use SQL Compact Edition (native to VB). I haven't made a SQL Ce tutorial yet, but I'm planning on doing this.
I hope this points you in the right direction.
VB Toolbox Small database around 5 tables, with almost less than 100 records. Probably database is not more than 10mb. I'm trying to ship the database with the application and it is not going to be in production, just for academic. Your tutorials were really awesome. I'm going to finish them all.
Abdillahi Jibril
Thank you, Abdillahi. :-D In your case, I think that SQL Ce would be perfect for your application. SQL Ce is easy to use and very similar to SQL Server except that it requires no server install and is contained completely in a single database file that can ship with your app. :-) I have a sample app that you're welcome to download and review until I'm able to get working on a tutorial. Let me know if you're interested and I'll link it.
(local) (SQL Server 10.0.1600 - DELL-PC\DELL) is the name of my instance as displayed in SQL Server , when I named the server name in the sqlcontrol code
sqlCon As New SqlConnection With {.ConnectionString = "local;Database=video;Trusted_Connection=True"}
, it showed an error message !!
Please help me on how to make an access database shareable on a local network using vb2010
+Malani Gola All you should have to do is change the "Data Source=" portion of your connection string to point to the network share path where the database file is stored.
Example: "Data Source=\\servername\sharename\Sample.accdb;"
Worked like a charm for me and I greatly appreciate your tutorial.
akafeet77 Awesome! :-) Glad to hear that it helped out.
great tutorial!!!
good work bro.. thanks
hi! Please help me because im having problem with connecting to sql server. I am using vb .net 2012 and i have installed sql server management studio 2012 with database engine. First, I followed your tutorial and it worked fine and i actually finished part 1 and 2 of your tutorial but after I restarted my computer, it didn't work anymore. So I tried to do it again from the start but it didn't worked well. The try catch command isn't working also even though i checked every command many times. I don't see any problem with the codes and i think the sql server is running properly. I might have disabled something very important. Thank You in advance, I'll really appreciate your help.
I Apologies for this comment, I finally found my error on code. It took me a while because it didn't show any error message while running the program.
What i did was this "Private SQLUSER As SQLControl" instead of this "Private SQLUSER As New SQLControl" i didn't notice New. That was a big mistake because my class became useless. The only problem is myself :D, by the way THANK YOU SO MUCH FOR VERY HELPFUL TUTORIALS.
Hello, King Pineda! Sorry for the slow reply, but I'm glad that you got it sorted. :-D
Thank you sir! I've learned a lot from your tutorials. Hope to see more. Thanks in advance.
Very, very Nice!
thanks a lot.
it's helpful..
waiting next tutorial :D
Thanks for this tutorial .. anyway I am having a bit of a problem when I am inserting multiple data only four of it are created ! Why is that ?! Thanks in advance!!
Can you give me an example of your insert?
hi, can i ask what server version you are using? i wonder what server i need to download for this tutorial thanks.
Hello, Melina. :-) I'm using SQL Server 2008 Express for these videos. However, my next series will be on SQL Ce (Compact Edition), which is awesome because the SQLCe database is portable and requires no installation. It's a great alternative to SQL Server if your needs do not require a full server.
Great tutorial, thanks a lot.It is impressive to see that you have helped so many people with their questions also!
your steps worked well for me but I have one question; is there an issue using "Integrated Security=SSPI" rather than supplying a username and password? My app will be used by multiple clients connecting to a central server and I have assumed this method will use windows login credentials so will be secure. thanks
Bernie Milne Greetings, Bernie! Trusted connections (Integrated Security) are more secure, but the reason that many apps rely upon a SQL username/password is due to the fact that managing application access on the database end - especially in larger environments - could be a nightmare. Instead of using a single SQL db account to gain access to the application database, you'd have to grant access and permissions to each Windows client within the SQL server. In a smaller environment this may not be a big problem. For an app that would require very few installs, a Trusted Connection may actually be a better solution.
Most large scale applications use a single SQL login to access the database, and then apply more granular security within the application itself.
Sir I have question. it is necessary to have an sql server authentication?
+John Roish Beduya It is not necessary to use SQL Server Authentication. As an alternative you may use a Trusted Connection [Windows Authentication] by simply replacing the "User=username; and Pwd=password;" sections of the connection string with "Trusted_Connection=True;".
You will want to ensure that your credentials have been added to the proper SQL security roles for your database.
Thank you sir. I really appreciated your help. And a happy new year :D
Sir can I ask again, what's the difference between windows and SQL authentication? and what is more efficient to use for creating database?
+John Roish Beduya The main difference is that Windows authentication is passed from your operating system or domain, whereas SQL credentials are held within SQL server's security system.
As far as efficiency goes, it really depends upon the nature of your system and how you intend to use it. If you're creating a database application that will be distributed to others [clients], then it might not make sense to grant security roles to _your_ windows account which will be inaccessible to the client. For a quick "out of the package" deployment, using a default 'sa' or master security account will allow the end user to forego worrying about individual SQL user accounts.
In most cases, all clients using the db app will be using the same [SQL] account to access the system, but they will not see this. The administrator can change the credentials on the back end as desired, but the end user will not need to know how to connect to SQL server.
The client will access the database with an administrative account, typically, but will be restricted within the application by separate application account (stored within your application database tables).
From an administrative standpoint it doesn't really matter, since it all comes down to the SQL security roles. If the account has the necessary roles applied then it's all the same to the server whether SQL or Windows authentication is used.
thanks for the video
just want ask: what are the benefits to contact to the database with a connection string vs connecting with sql server data tools
thanks again
What's the Username and Password?
No idea how to find the username and password
Functionally, I don't think that one way is really better than another. I mainly do a coded connection class because it helps us understand what's actually going on under the hood of our application, unlike components that are derived from wizards.
One place where the wizards and bindings do win, however, is when working with Reporting. I've found that it's absurdly difficult (which is ridiculous) to work with the Report Designer and custom Datasets.
😭😭😭
how do i make other computers connect though im 9 and i dont know that much
Hi, I currently have VB 2013 downloaded and want to link it to SQL Server Databases. What version of SQL should I download? If possible could you reply with a link to a suitable download? Thanks
Hello, Ryan. The version of SQL server that you'll want to download is really dependent upon your needs. You should be able to connect to most any SQL version with VB, though I'm not sure what version the native ADO.NET library is in VB 2013. Older databases may require an additional ADO.NET download and reference.
If you do not wish to download and run a complete SQL installation you can use the Local Database option in VB. This is a great, lightweight database, though is not as robust as a fun SQL install.
Beyond that, you can download SQL Express (free, 4GB size limit) from Microsoft:
SQL Express 2008 R2: www.microsoft.com/en-us/download/details.aspx?id=30438
SQL Express 2012: www.microsoft.com/en-us/download/details.aspx?id=29062
SQL Express 2014: www.microsoft.com/en-us/download/details.aspx?id=42299
Also, you're not limited to SQL Server with VB. Most all databases (MS Access, MySQL, etc.) have a downloadable ADO.NET library which can be referenced within your project. The syntax is nearly identical with each connector.
I hope that helps. :-)
I like it a lot! Thank you!
Always a pleasure, Barton. I'm very happy that it was useful. :-)
where can i find the server of mine?
PLEASE HELP!!!
Hey, im having a bit of trouble, i cant connect to my database, i keep getting this error
"sql network interfaces error 26 - error locating server/instance specified"...
but i look at my server and my program and they both have the same server/instance.
PLEASE HELP!!!
Michael Chaney Hello, Michael. Because this error could indicate a number of different problems, I can't give a definite answer.
Here are some things to consider:
Is there a typo in your connection string?
Do you have a firewall or other security software enabled that could be blocking you?
Are you trying to connect to a server that is not on your local network?
Can you successfully ping and communicate with the server computer by hostname and IP address?
Make certain that your SQL server is configured [In *SQL Server Configuration Manager*] to allow TCP/IP and Named Pipes connections *ALSO* ensure that the SQL Server Browser service is set to start automatically.
Here are some search results regarding this error:
blogs.msdn.com/b/sql_protocols/archive/2013/07/11/2609615.aspx?PageIndex=13
social.msdn.microsoft.com/Forums/sqlserver/en-US/ed7b78c0-7dcb-4be7-96fa-f85b74f0e413/sql-network-interfaces-error-26-error-locating-serverinstance-specified?forum=sqldataaccess
VB Toolbox
no typo in my string.
i set the firewall to allow the sql port of 1433 and 1434, so nope.
im trying to connect to a database on a different computer on my LAN.
yes i can ping to the server computer.
so idk whats going on...
Hi there! This is really a very helpful video. I just notice an issue when connecting to my DB. When I used a connection string using servername\instancename and username "sa" with the correct password, I am getting a network-related or instance specific error....But when I removed the instance name and only use the server name I am connecting to the DB successfully. Now if I try to connect using ODBC to test, I cannot connect without the instance name. Any ideas why is this happening?
I'm not 100% certain, but I believe that if you're connecting to the default instance of SQL server with ADO.NET it doesn't need or want the instance name.I think that you will only need the instance if you're connecting to a non-default instance. ODBC likely handles the connection slightly differently.
would you mind if I ask what version of VB.Net and SQL did you used in this tutorial?
+daizaree dablo Not at all. :-) I'm using VB.NET 2010 Professional and SQL Server 2008 Express.
thanks for the qiuck respond..=) can I ask some advice from you master if I have a problem in coding using this app?
+daizaree dablo I'm always happy to help if I am able. :-) I can't promise that I'll be able to solve every problem, but we can always try. Feel free to ask and I will respond if I am able.
Thank you master =)
Hello, i'm a beginner and i'm having trouble setting up a decent database.
Do you happen to have a tutorial on this aswel on your profile or a link to a good tutorial that could help me? thx
Hi there, Oliver. Unfortunately, I don't have any Database setup tutorials. A big problem with this is that there are so many setup variables based upon the specific needs of applications and environments. My recommendation would be to check out "Best Practices" for your preferred database platform. Beyond that, data normalization and good indexing practices are always a good thing to check out. :-)
i have done exact as in your video but getting error nullrefrence ???
This is most commonly caused by a missing "New" command when initializing a new object. If you try to use the object for it is created it is NULL and will crash.
Common problem:
Private SQL As SQLControl
Fix:
Private SQL As New SQLControl
Check the error message details to see which object is throwing the error and it should be easy to fix. :-)
thank you very much...
Great tutorial thanks :)
I have a question. My instance name is not like xxx\\SQLEXPRESS its like only XXXX no \\SQLEXPRESS. I couldn't make a connection to sql server :(
Can I assume that XXXX is the name of your server?
You might wish to verify that TCP/IP and NamedPipes are both enabled in your SQL Configuration Manager. Beyond that, you should be able to just specify the XXXX (server hostname) in your connection string.
Oh ok. Thanks for you quick response and thank you for doing this. I'm working with these videos like studying a lesson :)
Also, can you make a video about how to create proper login credentials in mssql please ? It seems i'm having problems at that part thanks
sharkmisdeed What do you mean by "proper" login credentials? Like SQL database user accounts as opposed to table-stored logins?