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.
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!!!
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!
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 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
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!
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!
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. 🙂
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, 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.
Do you need a username and password? If not do you just not put anything after the database in the connection string? I'm referring to the point at about 5:10
Glad you got it sorted, Scott. :-) "Trusted_Connection=True;" should work as well. This is a pretty dated tutorial which I've had plans to reboot. If you're interested I have a newer SQLControl class that you're welcome to check out.: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
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. :-)
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 = { " " }
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?
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;
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.
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 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, 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, 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.
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. :-)
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.
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.
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
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. :-)
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.
+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.
This is very nice tutorial. But i have 1 question. In VB I can make a dynamic control(such as button). but i want to save the location in db. so when user closes and again opens the app, he will see the control in the location that he had chosen before closing.
+alef pe There are a few different ways to handle this. You could save a property to your project via My.Settings or in a config/ini file. If you wish to store it in the database you can simply save the X/Y coordinates as integers in individual columns with the Button name in another column which you could query for. Example: SELECT LocX, LocY FROM controlTable WHERE objName = 'Button1' Then, if records were found, you could pull the LocX, LocY values that are found for Button1 as a DataRow. For example, Dim r As DataRow = SQL.yourDataTable.Rows(0)] Then something like: Button1.Location = New Point(r("LocX"), r("LocY") This is a bit thin an I recommend some record or error catching. Let me know if you need a better sample and if I'm not too busy today I might be able to make something for you. :-)
+VB Toolbox Thank you so much I did it. every thing was OK until I wanted to add event to these buttons(addhandler). The problem is that this Addhandler work with just the last button that i loaded from db. I have a global variable. Dim a as control I wanna move the controls. I use a timer and add event for mousedown and mouseup. these work just for the last one.
This is an old tutorial and I'm not sure if I still have the database anywhere. I was working on a refresh of this SQL series using the new SQLControl class and a different database which I'd planned to release with the source code but I haven't had an opportunity to produce it yet because I've been in the process of moving.
Yep. SQL Server is a separate product as must be downloaded by itself; However, VS 2010 _should_ come with SQL Ce (Compact Edition), which I've used in several videos. :-)
(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 !!
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, 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.
Thank you for this tutorial. I have a problem, When I try to connect this error message appears to me: " A network-related or instance-specific error occurred while establishing a connection to SQL server. The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.(provider:SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)" Please help me!
+s4 games This is a difficult error to resolve because it can be caused by many things; However, the most common cause is that the SQL Server Browser service is stopped on your SQL Server. You can enable the service via SQL Server Configuration Manager OR by running "services.msc" on your SQL Server machine and locating the "SQL Server Browser" service and start it from there.
+VB Toolbox +VB Toolbox You right, Sql server browser and MSSQLSERVER are stopped, I'm trying to. starting them from services.msc and sql management configuration but also other error message appears to me, I don't know why? however thank you so much
+s4 games It's difficult to know why they won't start, but it is possible that security software, firewall, or lack of administrator permissions could be a problem, depending upon your environment. You may wish to check those out, or even look in your event logs to see what's wrong. Also, in SQL Server Management Console, you may wish to verify that your SQL Network and Client Configurations have TCP.IP and Named Pipes protocols enabled.
+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;"
+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.
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 :)
SQL Server, in this example; However, with ADO.NET connectors you can connect to pretty much any database type (SQL, SQL Ce, MySQL, Access, Firebird, etc.), and the code is very similar for all of them. The only real difference is in their respective connection strings.
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...
Hello, John. I appreciate the suggestion. While the library system seems to be a very common theme among various university courses, I'm afraid that it would be difficult to provide a series on this due to time constraints, complexity, and the fact that I'd be duplicating a lot of material. It's much easier for me to focus on basic tasks. Still, I may consider this, but I'd have to research common course-ware for it to be of benefit to students.
Good day Sir. What I am to do is multiple data insertion in sql server unfortunately only four of the data in the txt box is inserted . Watching other tutorials cant find something will fit in to my problem
Greetings, jepoy man. :-) You may be having a problem if the data that you're inserting from the TextBox is either of the wrong format for the database column, or the data length is too great for your column. Verify in your database the column can handle the type of data and length that you are trying to insert.
I managed to get how to insert data in multiple columns ... using some of your tutorials and google as well .Now I got a problem with updating data .. been trying to find solution for almost 2 weeks now ..here is my codes Imports System.Data Imports System.Data.SqlClient Public Class Form1 Dim conn As SqlConnection Dim cmd As SqlCommand Dim dr As SqlDataReader Private Sub btnAddEmployee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddEmployee.Click conn = New SqlConnection("Server=JOJI-PC\SQLEXPRESS;Database=jojidatabase;Trusted_Connection=True;") conn.Open() Try cmd = New SqlCommand("insert into jojitable([FirstName],[MiddleInitial],[LastName],[Age],[BirthDate],[Address],[CivilStatus],[Occupation],[TelephoneNumber],[EmailAddress],[EducationalBackground],[Gender],[GroupNumber]) values ('" & txtFirstName.Text & "','" & txtMiddleInitial.Text & "','" & txtLastName.Text & "','" & txtAge.Text & "','" & txtBirthDate.Text & "','" & txtAddress.Text & "','" & txtCivilStatus.Text & "','" & txtOccupation.Text & "','" & txtTelephoneNumber.Text & "','" & txtEmailAddress.Text & "','" & txtEducationalBackground.Text & "','" & txtGender.Text & "','" & txtGroupNumber.Text & "')", conn) cmd.ExecuteNonQuery() MsgBox("successfully added") Catch ex As Exception MsgBox(ex.Message) End Try conn.Close() End Sub Private Sub btnReadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadData.Click conn = New SqlConnection("Server=JOJI-PC\SQLEXPRESS;Database=jojidatabase;Trusted_Connection=True;") Dim sql As String = "SELECT * FROM jojitable" Dim dataadapter As New SqlDataAdapter(sql, conn) Dim ds As New DataSet() conn.Open() dataadapter.Fill(ds, "jojitable") conn.Close() DataGridView1.DataSource = ds DataGridView1.DataMember = "jojitable" End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Dim conn As New SqlConnection Dim cmd As New SqlCommand conn = New SqlConnection("Server=JOJI-PC\SQLEXPRESS;Database=jojidatabase;Trusted_Connection=True;") conn.Open() Dim sqlUpdate As String = "UPDATE [jojitable] SET ([FirstName], [MiddleInitial], [LastName], [Age], [BirthDate], [Address], [CivilStatus], [Occupation], [TelephoneNumber], [EmailAddress], [EducationalBackground], [Gender]) VALUES ('" & txtFirstName.Text & "','" & txtMiddleInitial.Text & "','" & txtLastName.Text & "','" & txtAge.Text & "','" & txtBirthDate.Text & "','" & txtAddress.Text & "','" & txtCivilStatus.Text & "','" & txtOccupation.Text & "','" & txtTelephoneNumber.Text & "','" & txtEmailAddress.Text & "','" & txtEducationalBackground.Text & "','" & txtGender.Text & "','" & txtGroupNumber.Text & "') WHERE @jijitable" cmd = New SqlCommand cmd.Connection = conn cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text) cmd.Parameters.AddWithValue("@MiddleInitial", txtMiddleInitial.Text) cmd.Parameters.AddWithValue("@LastName", txtLastName.Text) cmd.Parameters.AddWithValue("@Age", txtAge.Text) cmd.Parameters.AddWithValue("@BirthDate", txtBirthDate.Text) cmd.Parameters.AddWithValue("@Address", txtAddress.Text) cmd.Parameters.AddWithValue("@CivilStatus", txtCivilStatus) cmd.Parameters.AddWithValue("@Occupation", txtOccupation) cmd.Parameters.AddWithValue("@TelephoneNumber", txtTelephoneNumber) cmd.Parameters.AddWithValue("@EmailAddress", txtEmailAddress) cmd.Parameters.AddWithValue("@EducationalBackground", txtEducationalBackground) cmd.Parameters.AddWithValue("@GroupNumber", txtGroupNumber) cmd.ExecuteNonQuery() conn.Close() End Sub End Class
You're welcome. :-) I'm curious, are you using parameters? Parameters are very valuable and can also help to preserve your data integrity and sometimes prevent type mismatch errors during updates.
jepoy man Hello, jepoy man. :-) The syntax for SQL UPDATE is different from INSERT. It should look like this: *UPDATE* _tablename_ *SET* _column1_ = _value1_ , _column2_ = _value2_ *WHERE* _filtercolumn_ = _filtervalue_ Example [With Parameters]: UPDATE jojitable SET FirstName=@FirstName, LastName=@LastName WHERE YourFilterColumn=@YourFilterValue *Note: For your filter, I recommend using whatever column is your Primary Key if you only want to update a single record!*
I've been looking at all your videos and there great for someone like me who is basically starting to learn SQL and VS from the ground up. But I have one problem that I can't seem to get through. I have a program like say an Invoice program that requires an Invoice number. I have a database table called Defaults in SQL Server in which there are 2 columns. The first Column is "Description" the 2nd is NextNumber. Under the Description column I have a row with the following: NextInvoiceNumber in the Description column and 235467 in the NextNumber column. I would like to have my invoice program look in the Defaults table and search through the description column for the NextInvoiceNumber description and give me the NextNumber that's in column 2 into a text box and then add 1 to the number and write the number back to the defaults table. I tried using the code from your login screen program but I keep getting an error.
I'll be happy to assist if I can, Pete. :-) What sort of errors are you getting? If you're looking for the highest invoice number in the table and want to add 1 to it (and assuming it's a numeric column) you should be able to SELECT Max(invoicenumber) + 1. If it's an alphanumeric column (char, varchar, etc.) you'll need to do some parsing after Max(invoicenumber) to extract the numeric value and add 1 to it.
The defaults table is separate from the program that calls it. I'm trying to use the code from your login program "IsAuthenticated()" to fined the row in the table and then run the ExecQuery to get the next number. In the RunQuery I have to change the textbox in the Where clause to "WHERE Description=NextInvoiceNumber" and the error I get is "Incorrect Syntax Near Desc". Here is the SQL String SQL.RunQuery("SELECT Count(Desc) as DescCount " & _ "FROM Defaults" & _ "WHERE Desc=NextInvoiceNumber")
Pete Constantinos Is "Desc" an actual column name in Defaults? One problem I see right away is that there is no separating space between "FROM Defaults" and "WHERE Desc...". This string will produce "FROM DefaultsWHERE Desc". That might be your problem.
Desc is the actual column name in the defaults table. Sorry the box where I am writing the comments must have messed the code I sent. But I cut and pasted your code and removed the text boxes and replaced them with "WHERE Desc=NextInvoiceNumber" and this is where I get the Syntax error. I tried using the following SQL Code "SELECT Count(Desc) as DescCount " & _ FROM Defaults " & _ WHERE Desc=NextInvoiceNumber") Should this code return a value of 1?
Pete Constantinos So, both columns (Desc, and NextInvoiceNumber) are located in the Defaults table, correct? DescCount will be 1 if there is only one matching record found in which Desc=NextInvoiceNumber. It seems odd to me that you're comparing two column values from the same table. [TABLE] [COLUMN 1] | [COLUMN2] 4 | 4 = Match Now, according to your first post, your table looks like this: TABLE - [DEFAULTS] COLUMNS - [Description] | [NextNumber] VALUES - NextInvoiceNumber | 235467 If this is correct, your query will not work, because 'NextInvoiceNumber' is not equal to '235467'.
Hey man, wondering if you can help me. I keep getting a Login Failed message box even though all the credentials are correct. I even tried creating a different login on the server and it still failed. Any suggestions? Absolutely love your tutorials though, definitely subscribing :) Many thanks
+Luke Newell Hello, Luke. :-) When you add your security accounts, be sure to apply necessary security roles [db_datareader, db_owner, etc.] to the new account and specify which databases the user will be given access to. If you're an administrator, you could try the built-in admin security account [sa], or you could use a Trusted Connection to instead use your machine or domain credentials without needing to supply a username and password. In either case, you'll need to provide the new user with database and access roles.
+VB Toolbox Ok so i created a new login called "luke" and a simple password. I ticked all boxes under Server Roles, User Mapping, Securables, and Status granting full access to everything for that user login. Then when i try to connect to the server with the SQL Server Authentication (to test it) i still get error message 18456. This is driving me mad! xD
+Luke Newell Hmmm... That is weird for sure. Perhaps it might help if I could see your connection string. Bear in mind, too, that passwords are case sensitive and ensure you are matching the casing.
+VB Toolbox I managed to fix my problem and have begun trying out your second tutorial. Man i am so happy with what youve got going on here, youve helped me a bunch. I tried accessing your tip jar but it says the link is broken or something! Let me know if its working and i will be more than happy to tip for your tutorial. Thanks so much!
It would seem that the error is being generated in the SQLControl class when the SQLDA.Fill(SQLDS) tries to execute. The actual error is "Index out of Range", Can't Find Table 0.
The reason for this would go back to an error in your Query. If the Query produces an error, the DataSet will have no Tables. The best way for me to help here is to see a snapshot of your database and and the Sub running the query. If you don't mind sharing your VB Project, I could get a better look at what you're attempting.
Pete Constantinos With Windows or a program like 7zip, you can zip the whole project folder and share it with a free sharing program like DropBox or Google Drive.
+Edgar Panganiban If you use Windows Authentication, it is unnecessary to supply a username or password in your connection string. Instead, replace the User and Pwd with "Trusted_Connection=True;"
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.
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
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'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. :-)
I never thought I'd nerd out enough to enjoy doing this, but you made it so simple!
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!!!
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!
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 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
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
this video might be old but still saved my life. thanks a lot, man!
Awesome, Jamie. I'm glad that it was helpful! :-)
the best VB Teacher i glad to see VB Pro Programmer and your good series
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! :-)
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 is a very nice tutorial vdo. I am an absolute beginner. Just following your steps now i can build my own app!
Excellent set of video tutorials. Many, many thanks.
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.
Merci :) you are a calm clear and freaky easy to understand....
Many thanks to take the time to share your knowledge.
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
Fantastic tutorial. You've no idea how much this has helped me. Thanks!
I'm very happy that it's been helpful to 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.
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
Fantastic tutorial! Very helpful and doesn't drag on, thank you!
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
Worked and easy to follow! Thank you!
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
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!
thanks bro! great tutorial keep it up...i'm still learning .net
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. 🙂
i love it, you explain it real good
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.
😭😭😭
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.
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 need a username and password? If not do you just not put anything after the database in the connection string? I'm referring to the point at about 5:10
I figured it out. you do:
"server=server;database=database;" & "integrated security=true"
Just in case anyone else ran into this problem
Glad you got it sorted, Scott. :-) "Trusted_Connection=True;" should work as well.
This is a pretty dated tutorial which I've had plans to reboot. If you're interested I have a newer SQLControl class that you're welcome to check out.:
www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
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. :-)
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 = { " " }
Thank you so much, your tutorials are awesome.
Thank you for watching, Paul! :-)
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?
Thank you very for your help. All its working fine. I'll see all your videos
Very helpful and excellently explained! Thanks!
I'm very happy that it has been helpful! Many thanks for the encouraging feedback. :-D
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;
Ya bro. I like your friendly talking and nice tutorial. It helped me. Thanks bro.
thanks this video tutorial really help me out😀😀😀😀
This is the most helpful tutorial and channel. Thumbs up :like: :)
Do you have any tutorial on how to make a project with name last name and address but how to write the code?
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.
Thanks for this. Informative and to the point!
Thank you for this tutorial , I really appreciate your effort to giving and teaching us thru this video
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 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.
you are the best... thanks a lot :) I am from chile
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, 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.
hi, I executed this code but it says login failed for user 'sa'. any username I give would show same message. Please help
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.
For the connection string how can you default to windows user and password login?
thanks to your tutorial. it helps me a lot
this was awesome, super easy
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. :-)
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.
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.
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
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. :-)
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.
Do you have a tutorial on how to add exported sql database to vb.net? I wasn't able to export database from sql server management.
Sorry, valkyre. I'm not certain that I fully understand your question. :-(
Are you talking about backing up the database via VB.NET?
No, What I'm trying to say is, I would like to have an exported sql file like the ms access, I also wasn't able to connect the vb.net to ms SQL server
By "exported" are you talking about a portable/offline database [like Access], instead of an installed SQL Server - something like SQLCe?
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 =)
This is very nice tutorial. But i have 1 question.
In VB I can make a dynamic control(such as button). but i want to save the location in db. so when user closes and again opens the app, he will see the control in the location that he had chosen before closing.
+alef pe There are a few different ways to handle this. You could save a property to your project via My.Settings or in a config/ini file.
If you wish to store it in the database you can simply save the X/Y coordinates as integers in individual columns with the Button name in another column which you could query for.
Example:
SELECT LocX, LocY FROM controlTable WHERE objName = 'Button1'
Then, if records were found, you could pull the LocX, LocY values that are found for Button1 as a DataRow. For example, Dim r As DataRow = SQL.yourDataTable.Rows(0)]
Then something like: Button1.Location = New Point(r("LocX"), r("LocY")
This is a bit thin an I recommend some record or error catching.
Let me know if you need a better sample and if I'm not too busy today I might be able to make something for you. :-)
+VB Toolbox Thank you so much
I did it. every thing was OK until I wanted to add event to these buttons(addhandler).
The problem is that this Addhandler work with just the last button that i loaded from db.
I have a global variable.
Dim a as control
I wanna move the controls.
I use a timer and add event for mousedown and mouseup.
these work just for the last one.
+alef pe What code are you using to add the event handlers?
+VB Toolbox
For event mouse down:
Timer1.Enabled = True
Timer1.Start()
renew()
For event mouse up:
Timer1.Stop()
renew()
The renew() sub:
op = a.Location
cp = MousePosition
The timer1 code:
a.Location = op - cp + MousePosition
+VB Toolbox I found the solution
Thank you
May I ask you other question here?
Perfect! It simply helps me out a lot!
Thanks a lot. :-)
I'm very happy that it was helpful! :-)
Do you mind sharing the database you have for forum for practice?
This is an old tutorial and I'm not sure if I still have the database anywhere. I was working on a refresh of this SQL series using the new SQLControl class and a different database which I'd planned to release with the source code but I haven't had an opportunity to produce it yet because I've been in the process of moving.
+VB Toolbox That's fine. I will wait for your new series. Till then I will complete this one
Awesome vid! thank you for making a very helpful video
Hi there! i just want to ask, do i need to download ms sql server if i already downloaded and installed the visual studio 2010?? thank you..
Yep. SQL Server is a separate product as must be downloaded by itself; However, VS 2010 _should_ come with SQL Ce (Compact Edition), which I've used in several videos. :-)
(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 !!
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! 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.
Thank you for this tutorial. I have a problem, When I try to connect this error message appears to me:
" A network-related or instance-specific error occurred while establishing a connection to SQL server. The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.(provider:SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)"
Please help me!
+s4 games This is a difficult error to resolve because it can be caused by many things; However, the most common cause is that the SQL Server Browser service is stopped on your SQL Server.
You can enable the service via SQL Server Configuration Manager OR by running "services.msc" on your SQL Server machine and locating the "SQL Server Browser" service and start it from there.
+VB Toolbox +VB Toolbox You right, Sql server browser and MSSQLSERVER are stopped, I'm trying to. starting them from services.msc and sql management configuration but also other error message appears to me, I don't know why?
however thank you so much
+s4 games It's difficult to know why they won't start, but it is possible that security software, firewall, or lack of administrator permissions could be a problem, depending upon your environment. You may wish to check those out, or even look in your event logs to see what's wrong.
Also, in SQL Server Management Console, you may wish to verify that your SQL Network and Client Configurations have TCP.IP and Named Pipes protocols enabled.
+VB Toolbox OK, I'll try these methods, thanks
Thanks. It is very helpful.
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;"
Great tutorial! Thumbs up!
Excellent!
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.
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 :)
Thank You. Very useful to me.
What database software are you using
SQL Server, in this example; However, with ADO.NET connectors you can connect to pretty much any database type (SQL, SQL Ce, MySQL, Access, Firebird, etc.), and the code is very similar for all of them. The only real difference is in their respective connection strings.
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...
thanks a lot.
it's helpful..
waiting next tutorial :D
Thank you so much!
mine says "the server was not found or not accessible" how to fix this?
Hi sir, can you make a tutorial like this vb.net + sql server while making a whole system, like library system from scratch?
Hello, John. I appreciate the suggestion. While the library system seems to be a very common theme among various university courses, I'm afraid that it would be difficult to provide a series on this due to time constraints, complexity, and the fact that I'd be duplicating a lot of material. It's much easier for me to focus on basic tasks. Still, I may consider this, but I'd have to research common course-ware for it to be of benefit to students.
good work bro.. thanks
where can i find the server of mine?
Good day Sir.
What I am to do is multiple data insertion in sql server unfortunately only four of the data in the txt box is inserted . Watching other tutorials cant find something will fit in to my problem
Greetings, jepoy man. :-) You may be having a problem if the data that you're inserting from the TextBox is either of the wrong format for the database column, or the data length is too great for your column. Verify in your database the column can handle the type of data and length that you are trying to insert.
Thank you Sir for replying ..
I managed to get how to insert data in multiple columns ... using some of your tutorials and google as well .Now I got a problem with updating data .. been trying to find solution for almost 2 weeks now ..here is my codes
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader
Private Sub btnAddEmployee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddEmployee.Click
conn = New SqlConnection("Server=JOJI-PC\SQLEXPRESS;Database=jojidatabase;Trusted_Connection=True;")
conn.Open()
Try
cmd = New SqlCommand("insert into jojitable([FirstName],[MiddleInitial],[LastName],[Age],[BirthDate],[Address],[CivilStatus],[Occupation],[TelephoneNumber],[EmailAddress],[EducationalBackground],[Gender],[GroupNumber]) values ('" & txtFirstName.Text & "','" & txtMiddleInitial.Text & "','" & txtLastName.Text & "','" & txtAge.Text & "','" & txtBirthDate.Text & "','" & txtAddress.Text & "','" & txtCivilStatus.Text & "','" & txtOccupation.Text & "','" & txtTelephoneNumber.Text & "','" & txtEmailAddress.Text & "','" & txtEducationalBackground.Text & "','" & txtGender.Text & "','" & txtGroupNumber.Text & "')", conn)
cmd.ExecuteNonQuery()
MsgBox("successfully added")
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
End Sub
Private Sub btnReadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadData.Click
conn = New SqlConnection("Server=JOJI-PC\SQLEXPRESS;Database=jojidatabase;Trusted_Connection=True;")
Dim sql As String = "SELECT * FROM jojitable"
Dim dataadapter As New SqlDataAdapter(sql, conn)
Dim ds As New DataSet()
conn.Open()
dataadapter.Fill(ds, "jojitable")
conn.Close()
DataGridView1.DataSource = ds
DataGridView1.DataMember = "jojitable"
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
conn = New SqlConnection("Server=JOJI-PC\SQLEXPRESS;Database=jojidatabase;Trusted_Connection=True;")
conn.Open()
Dim sqlUpdate As String = "UPDATE [jojitable] SET ([FirstName], [MiddleInitial], [LastName], [Age], [BirthDate], [Address], [CivilStatus], [Occupation], [TelephoneNumber], [EmailAddress], [EducationalBackground], [Gender]) VALUES ('" & txtFirstName.Text & "','" & txtMiddleInitial.Text & "','" & txtLastName.Text & "','" & txtAge.Text & "','" & txtBirthDate.Text & "','" & txtAddress.Text & "','" & txtCivilStatus.Text & "','" & txtOccupation.Text & "','" & txtTelephoneNumber.Text & "','" & txtEmailAddress.Text & "','" & txtEducationalBackground.Text & "','" & txtGender.Text & "','" & txtGroupNumber.Text & "') WHERE @jijitable"
cmd = New SqlCommand
cmd.Connection = conn
cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("@MiddleInitial", txtMiddleInitial.Text)
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
cmd.Parameters.AddWithValue("@Age", txtAge.Text)
cmd.Parameters.AddWithValue("@BirthDate", txtBirthDate.Text)
cmd.Parameters.AddWithValue("@Address", txtAddress.Text)
cmd.Parameters.AddWithValue("@CivilStatus", txtCivilStatus)
cmd.Parameters.AddWithValue("@Occupation", txtOccupation)
cmd.Parameters.AddWithValue("@TelephoneNumber", txtTelephoneNumber)
cmd.Parameters.AddWithValue("@EmailAddress", txtEmailAddress)
cmd.Parameters.AddWithValue("@EducationalBackground", txtEducationalBackground)
cmd.Parameters.AddWithValue("@GroupNumber", txtGroupNumber)
cmd.ExecuteNonQuery()
conn.Close()
End Sub
End Class
You're welcome. :-) I'm curious, are you using parameters? Parameters are very valuable and can also help to preserve your data integrity and sometimes prevent type mismatch errors during updates.
jepoy man Hello, jepoy man. :-) The syntax for SQL UPDATE is different from INSERT.
It should look like this:
*UPDATE* _tablename_
*SET* _column1_ = _value1_ , _column2_ = _value2_
*WHERE* _filtercolumn_ = _filtervalue_
Example [With Parameters]:
UPDATE jojitable
SET FirstName=@FirstName, LastName=@LastName
WHERE YourFilterColumn=@YourFilterValue
*Note: For your filter, I recommend using whatever column is your Primary Key if you only want to update a single record!*
I've been looking at all your videos and there great for someone like me who is basically starting to learn SQL and VS from the ground up. But I have one problem that I can't seem to get through.
I have a program like say an Invoice program that requires an Invoice number. I have a database table called Defaults in SQL Server in which there are 2 columns. The first Column is "Description" the 2nd is NextNumber. Under the Description column I have a row with the following: NextInvoiceNumber in the Description column and 235467 in the NextNumber column. I would like to have my invoice program look in the Defaults table and search through the description column for the NextInvoiceNumber description and give me the NextNumber that's in column 2 into a text box and then add 1 to the number and write the number back to the defaults table.
I tried using the code from your login screen program but I keep getting an error.
I'll be happy to assist if I can, Pete. :-) What sort of errors are you getting? If you're looking for the highest invoice number in the table and want to add 1 to it (and assuming it's a numeric column) you should be able to SELECT Max(invoicenumber) + 1. If it's an alphanumeric column (char, varchar, etc.) you'll need to do some parsing after Max(invoicenumber) to extract the numeric value and add 1 to it.
The defaults table is separate from the program that calls it. I'm trying to use the code from your login program "IsAuthenticated()" to fined the row in the table and then run the ExecQuery to get the next number. In the RunQuery I have to change the textbox in the Where clause to "WHERE Description=NextInvoiceNumber" and the error I get is "Incorrect Syntax Near Desc".
Here is the SQL String
SQL.RunQuery("SELECT Count(Desc) as DescCount " & _
"FROM Defaults" & _
"WHERE Desc=NextInvoiceNumber")
Pete Constantinos Is "Desc" an actual column name in Defaults? One problem I see right away is that there is no separating space between "FROM Defaults" and "WHERE Desc...".
This string will produce "FROM DefaultsWHERE Desc". That might be your problem.
Desc is the actual column name in the defaults table. Sorry the box where I am writing the comments must have messed the code I sent. But I cut and pasted your code and removed the text boxes and replaced them with "WHERE Desc=NextInvoiceNumber" and this is where I get the Syntax error. I tried using the following SQL Code "SELECT Count(Desc) as DescCount " & _
FROM Defaults " & _
WHERE Desc=NextInvoiceNumber")
Should this code return a value of 1?
Pete Constantinos So, both columns (Desc, and NextInvoiceNumber) are located in the Defaults table, correct? DescCount will be 1 if there is only one matching record found in which Desc=NextInvoiceNumber. It seems odd to me that you're comparing two column values from the same table.
[TABLE]
[COLUMN 1] | [COLUMN2]
4 | 4 = Match
Now, according to your first post, your table looks like this:
TABLE - [DEFAULTS]
COLUMNS - [Description] | [NextNumber]
VALUES - NextInvoiceNumber | 235467
If this is correct, your query will not work, because 'NextInvoiceNumber' is not equal to '235467'.
Hey man, wondering if you can help me. I keep getting a Login Failed message box even though all the credentials are correct. I even tried creating a different login on the server and it still failed. Any suggestions? Absolutely love your tutorials though, definitely subscribing :) Many thanks
+Luke Newell Hello, Luke. :-) When you add your security accounts, be sure to apply necessary security roles [db_datareader, db_owner, etc.] to the new account and specify which databases the user will be given access to.
If you're an administrator, you could try the built-in admin security account [sa], or you could use a Trusted Connection to instead use your machine or domain credentials without needing to supply a username and password.
In either case, you'll need to provide the new user with database and access roles.
+VB Toolbox Ok so i created a new login called "luke" and a simple password. I ticked all boxes under Server Roles, User Mapping, Securables, and Status granting full access to everything for that user login. Then when i try to connect to the server with the SQL Server Authentication (to test it) i still get error message 18456. This is driving me mad! xD
+Luke Newell If it helps, the error State is 5 and 8 which is invalid userid and invalid password. This doesnt make sense anymore :O
+Luke Newell Hmmm... That is weird for sure. Perhaps it might help if I could see your connection string. Bear in mind, too, that passwords are case sensitive and ensure you are matching the casing.
+VB Toolbox I managed to fix my problem and have begun trying out your second tutorial. Man i am so happy with what youve got going on here, youve helped me a bunch. I tried accessing your tip jar but it says the link is broken or something! Let me know if its working and i will be more than happy to tip for your tutorial. Thanks so much!
It would seem that the error is being generated in the SQLControl class when the SQLDA.Fill(SQLDS) tries to execute. The actual error is "Index out of Range", Can't Find Table 0.
The reason for this would go back to an error in your Query. If the Query produces an error, the DataSet will have no Tables.
The best way for me to help here is to see a snapshot of your database and and the Sub running the query. If you don't mind sharing your VB Project, I could get a better look at what you're attempting.
I have no problem sharing my project, how would you suggest I get it to you?
Pete Constantinos
With Windows or a program like 7zip, you can zip the whole project folder and share it with a free sharing program like DropBox or Google Drive.
I have drop box and I can upload the VBProject there into my shared folder. I will need an email address to allow you access.
Pete Constantinos
Should have a public share where you can right click the Project and Share a link. :-) If not, I'll PM you my email address.
what if I use windows authentication? What will be my user and Password input on these please help.....
+Edgar Panganiban If you use Windows Authentication, it is unnecessary to supply a username or password in your connection string. Instead, replace the User and Pwd with "Trusted_Connection=True;"
you are the beast thanks!!!!!!!!!!!!!!!!!!!!!!!!!!
a big embrasse from Chile
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?