VB.NET Database Tutorial - Login Screen For A SQL Database Application (Visual Basic .NET)

[@sonytv9772]

Hello VbToolbox I've paid 110$ for tutorials which are not even close like quality to yours. Amazing member Thank you so much for your tutorials. I really appreciate your work. Very friendly way of explanation!

[@VBToolbox]

Thank you, Paul! :-) I'm grateful for the positive feedback, and I'm very happy that the tutorials have been a help to you.

[@tonnab1]

Excellent explanation. Many thanks. I am using webform instead of windows form and it does still work perfectly apart from a very slight different properties between them.

[@technousr]

This was an awesome tutorial as I appreciate the details that you go through. I did have one follow up question regarding the AuthUser variable and how it would be used. After the user has been authenticated, what would you need to use the AuthUser variable for since you are using a system account to execute any SQL with the database?

[@AzharKalamazhar]

@22:10 Close the door after you leave. Made my day! You are champ :)

[@Cleptra256]

Hello, thanks a lot please for you have really helped me. Im working on my final year VB.Net project for an accounts management system and these clips have been wow. Thank you

[@VBToolbox]

+Samuel Mpwanyi I'm happy to know that the videos have been helpful, Samuel! :-)

[@none2300]

You should have way more views and subscribers than what you currently have!

[@VBToolbox]

Thank you very much, Varosion. That's always very encouraging to hear. :-DI'm trying my best to produce content that people will find helpful in hopes that it will draw more people in. I'm terrible at promoting my material, though, and I don't want to be spammy so I just cross my fingers and hope that people will discover me on their own.

[@pconstantinos57]

By the way I'm using part of you Combo Box tutorial to populate a Combo Box of User Names and your Login Tutorial to check for the authentication of the UserName and Password.

[@Kevisinthahouse]

Really helpful, starting my own project now.

[@mukeshkhan4684]

Kevin Coello I need a small help bro.. Plz help me... U have complete u r project

[@pconstantinos57]

Hello again, In your Login Tutorial you check for the User Name & Password and by the way this is great. I would like to expand on this and grab additional data from the Users table and store it in a few variables. Like Admin Access or Settings which are booleans. Your if statement in the IsAuthenticated function looks at Table(0).Rows(0),Item("UserCount")=1 can I add to that once it returns True or should I call another Function to extract the other data. Its a bit like your Combo Box Tutorial but with a few additional fields. I hope this makes sense. Thanks and keep up the Great work.

[@VBToolbox]

You can definitely load your variables with the data, either via your function (and a more robust query), or using a Sub instead of a Function. Please bear in mind that, while this certainly works, this login method is very simplistic and not especially secure. A sufficiently knowledgeable individual may be able to access the memory space and alter the application variables [e.g., set IsAdmin = True]. Additionally, in a real world scenario you wouldn't want to actually store passwords to a database, much less in plain text. Of course, data encryption and proper storage techniques can get incredibly complex. You can also use SQL credentials (passed via the connection string) and manage table access at the database level.

[@khalidabdulwahid8733]

very informative for beginners like me, could u please tell us how to work with different user authorities like add, del, edit

[@VBToolbox]

I have videos for all of those. 😀

[@mukeshkhan4684]

Really awesome bro.. Thank you so much

[@KhainStakard]

Cheers from Brazil! Awesome video, I really liked your solution using functions, you opened my mind! :)

[@yaelronald2280]

i know Im randomly asking but does anybody know of a trick to log back into an instagram account? I somehow forgot my password. I would appreciate any tips you can offer me!

[@makaigrady2978]

@Yael Ronald instablaster :)

[@yaelronald2280]

@Makai Grady I really appreciate your reply. I found the site thru google and Im trying it out now. I see it takes a while so I will reply here later when my account password hopefully is recovered.

[@yaelronald2280]

@Makai Grady It worked and I now got access to my account again. Im so happy! Thanks so much you really help me out !

[@makaigrady2978]

@Yael Ronald glad I could help :D

[@marksindao]

learned important things here. thanks. God bless

[@benlil7239]

Your videos have been very informative, I was wondering if you can make a in-depth tutorial about vb.net crystal report generation from db sql server with dynamic queries.

[@elewam1]

Hello, Thank you so much for your great and clear tutorial. It's very helpful. I have two questions, can the SQLControl class used in ASP.net? if yes, how to reference it on a page and fill-in a DataGridView for example. Many thanks in adanvce.

[@VBToolbox]

Hello, Mag Ele. I tried to make it work some time ago, but only had limited success. I think that the problem that I was running into was related to IIS or or Windows permissions issues. Part of me is sure it's possible, but if it is, it's certainly not a simple thing to accomplish.

[@monib1209]

Hi VB Toolbox great tutorials easy to follow along and get it done, thank you for that. Just a quick question on connection string. I have a ms access db in the project solution, would you know the connection string for that. Its not running on server. Thank you

[@rchrisnelson]

Great tutorial worked perfect. In addition to the log in I would like to return the value of the access level from the sql table. If the access level =1 then I would open form1 and if access level =2 then I would open form2. Any help would be greatly appreciated. Thank you.

[@VBToolbox]

This should be a snap. :-) The access levels could be stored directly in the users table and returned with a successful log on, else - if you have a more granular permissions table - queried by an indexed value (userID, username, etc.) at any time. [i.e., SELECT userID, accessLevel, screenname FROM useraccess WHERE userID =@user].

[@SimperYT]

Thank you so much for this tutorial. It really helped me a lot. You Are Awesome!

[@rexjanbevmercado3149]

i always watch your video it is very helpful and i wish you notice this but i need your help, make a video of login screen with a MS Access like continue of other tutorial of MS Access THanks A lot

[@GoOnlineMate]

Hello, Thank you so much for your great and clear tutorial. I would like to ask for your kind help. Would you make an another video to demonstrate how to connect SQL SERVER by client computer. Please just make a simple apps for login through server sql server from client pc. Thank you. I tried by connection string changing but failed.

[@AzharKalamazhar]

Client computer settings: 1. Go to sql server configuration>> See on Left pane >> You'll see "SQL Server Network configuration" Double click it and you'll see Protocols of "xxxxx">> Enable it 2. Forward port number 1433 both inbound and outbound if required from the firewall 3. Do it same on the other PC. Hope It'l work

[@markrenzomiraflores9788]

Hi, I been learning from your tutorials since my friend recommend it to me. and i would like to ask if there's anyway i can use the Collation in MS Access 2013? i need to make my log-in form case sensitive and i can't find a way to make it happen. please help

[@אלישאול-ס8נ]

I enjoyed very much, thank you, keep doing good movies

[@alexsaptetrei]

Last active has wrong field; no mention about sql injection by using direct string from text boxes, other than that I found it very informative

[@AardaerimusDAritonyss]

This one predated the SQL Full reboot series quite a bit, and I believe SQL injection and parameterization is covered fairly extensively in those. This one could definitely use a redo, as well. 😄

[@edwardesmalin8784]

The wait is over,thank u so much sir for you"re kindness=)...

[@VBToolbox]

Very happy that I could help, Edward. :-)

[@edwardesmalin8784]

hehe. thanks again sir.

[@soheilamiri1707]

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

[@Asmondien]

Hi! Thanks for your tuts! Could you point me in the direction how to log out a user? - having separate vb file for global variable - that i store for second form. I was looking into logging out, but what I got doesn't work - it doesn't clear the variables. Hope you can help. Thanks!

[@chaehyungwon565]

Do you have a video on how to create sql Database?

[@jamilaosmanova6458]

VB Toolbox Great tutorial. I wonder if you can help me out here. I would like to let people change their password. how would you set that up on SQL database and VB? Many thanks in advance

[@VBToolbox]

Hi there, Jamila. Sorry for the slow response. Are you talking about changing an actual SQL password, or a password stored in a SQL table? To change actual SQL users you'll want to check out SQL's 'ALTER USER'. To change password stored in a table (assuming no encryption is being used) you can simply run a SQL command something like: UPDATE _userstable_ SET userpwdfield =@newpassword WHERE username =@user Note: It's best to use parameterized queries [@variablename]. Some of my later DB videos show you how to use these parameters. They help prevent query string breaking and SQL injection attacks.

[@stijndemyttenaere9098]

Hey vb Thank you for your tutorials, they are à real help for me. I receive an error Message "option strict on disallows operands of type object for operator '='. When I give THE next line: line " If SQL.SQLDS.Tables(0).Rows(0).Item("UserCount") = 1 Then Return True" Could you me find out whats THE problem is? Thank you

[@VBToolbox]

It's possible that, with Option Strict enabled, it is trying to evaluate two different data types and failing. If Option Strict is not a project requirement, then you can turn it off (I usually turn it off) in your project Properties. *[On the menu bar: Project --> **_projectname_** Properties --> Compile tab --> Option Strict = Off].* If it is required, you can try converting the output with CInt or .ToString: *If CInt(SQL.SQLDS.Tables(0).Rows(0).Item("UserCount")) = 1* or *If SQL.SQLDS.Tables(0).Rows(0).Item("UserCount").ToString = "1"*

[@paragbhagde5309]

Hi Even i have got the same error n even i have tried the solution which u hav given still im getting the same error cannot find the table 0 plz help

[@akocisrael]

Thank you for this great tutorial... I manage to make login form using this tutorial but I'm having problems on the password text field... I tried to add a single quote and double quote on the last part of my correct password and it throws a error on the SQL Latin part ... but it is working properly if i'm not doing this test.. hope you can help me.. thanks!

[@VBToolbox]

Are you actually inputting the quotes as part of the password? In either case, I recommend using a parameterized query.

[@akocisrael]

Yes, I tried that .. and that's when the error occurs ... Sorry but I'm new to VS 2010 and I dont know whats "Parameterirzed query" or maybe I can google it :) thanks for the reply!

[@VBToolbox]

akocisrael I have a tutorial for Parameterized Queries on my channel if you search the database tutorials. :-)

[@CubingChris]

"Give me likes.. and stuff" :'D Thanks fo the vid! was very helpful :)

[@LegacyCS2]

Can you possibly make a tutorial on how to setup that management thing?

[@VBToolbox]

SQL Server Management Studio? I'm not entirely certain if that's available on the Express edition, but if you have the full version of SQL Server, it's included in your installation media and you should be able to customize your installation and select only the SMS to install.

[@nakamaluffy-san3278]

Hello Vb Toolbox, First Thank you for creating this tutorials and also for the reboot. And also I would like to ask you this big problem of mine right now. I seems to get the logic but I'm so much confused about how the syntax goes(I refer to your "reboot series" then I watch and used this "Login Screen For A SQL Database Application") . So far this is my code: Private Function IsAuthenticated() As Boolean 'CLEAR EXISTING RECORDS If SQL.DBDT IsNot Nothing Then SQL.DBDT.Clear() End If SQL.ExecQuery("SELECT COUNT(AccountID) As UserCount " & _ "FROM tblAccounts " & _ "WHERE Username= '" & txtUsername.Text & "' " & _ "AND Password= '" & txtPassword.Text & "' COLLATE_SQL_Latin1_General_CP1_CS_AS") If SQL.DBDT.Rows.Count = 1 Then Dim r As DataRow = SQL.DBDT.Rows(0).Item("UserCount") TextBoxClear() Me.Hide() frmMainAdmin.Show() Return True ElseIf SQL.DBDT.Rows.Count = 0 Then Dim r As DataRow = SQL.DBDT.Rows(0).Item("UserCount") TextBoxClear() Me.Hide() frmMainUser.Show() Return True End If MsgBox("Invalid User Credentials.", MsgBoxStyle.Critical, "LOGIN FAILED") Return False End Function So here's what I would like to happen in my system. I have 3 forms (login form, admin form, user form) and I would like to know in the login form if the person is an an admin(then the admin form pop ups) 'or' a user(then the user form pop ups) . Can you help me with this. PS: I would like to contact you personally in email but it seems you did not provide any means of your information but still I'm Thankful for your efforts.

[@rosefrost491]

hi, can you show us how to check the datatable to see if the user that is logging on is an admin or not? Thanks!

[@katrinapetry]

Can this tutorial apply to creating a asp.net web forms application instead of a windows form? i was able to successfully follow your tutorial for the windows form but would prefer to create a login for a web form.

[@HazardEdit]

very helpful!

[@L1feForStyle]

Hello sir, I've got a question. I'm working on a program called math hero. This is a program designed for children to be better at their mathematics. There are different levels, different speed thingys etc. We also want them to log in and register. This login worked perfect but can I build further from this for a register? I was looking for a video about registring but since I'm not that familiar with programming I wanted to ask if you could make a video using this code in the video and add a registration tool to it. This will help me out quite a lot! If not, can we swtich e-mails and help me this way? I look forward to your response! Kind regards, L1fe / Nick

[@SamiraAbdAllah]

thank you

[@chaehyungwon565]

Share with us how you created you database

[@LegacyCS2]

Uhm, i have a question... How do i get this SQL server management thingy to work. I started the program everything is fine, but i wanna connect to that SQL server.. But! It asks for username and password, am i gonna have like Xampp or a database from that phpmyadmin? Can you help me please

[@LegacyCS2]

Wait, im so confused... How is there so much in your server management?? How do i make new folders in there?

[@duncanwoodbridge2685]

Can you maybe assist a get tge following error on my connectionstring: Connectionstring is not member

[@HikaroRyu]

So....like many people have commented I am having the same issue of IndexOtOfRangeException was unhandled. Cannot find table 0.". I even created the table he used and still get the same result, to see if it shed some light and to what is going wrong. On another note I do get the result that UserCount will be 1, so im guessing that it mean mean that their is a user with that username and password in the table or query im executed. I hope someone can offer some insight or some other method to implement the validation of password and username without to much of change on the code

[@VBToolbox]

HikaroRyu I hope you were able to sort this out. I’m so sorry I wasn’t able to help with this given my situation. :(

[@anneyeong897]

Hi, Im having an error in this line. " If SQL.SQLDS.Tables(0).Rows(0).Item("UserCount") = 1 Then" it says "IndexOtOfRangeException was unhandled. Cannot find table 0." what should i do? im a beginner so i dont really know what im doing. can you help me please?

[@miguelmojica7681]

Im having the same problem! did you figure it out?

[@tohfu22]

I followed this procedure, but I cant connect my vb into sql database

[@keimpx]

What would my connection string be if I'm connecting to a local service based database in the same folder as the VB.NET project? Would I simply use the connection string given to me in the data sources dialog? Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database\database.mdf;Integrated Security=True

[@VBToolbox]

Sounds correct to me. Unfortunately, I don't have one to test. Check out www.connectionstrings.com/sql-server-2008/ for a list of connection strings related to SQL. Hopefully, that will be of some help. :-)

[@keimpx]

VB Toolbox Thanks VB Toolbox! Your tutorials are really helpful! Subscribed!

[@VBToolbox]

Keimpx And thank *YOU*! I appreciate the encouragement and feedback. :-D

[@keimpx]

Would it be possible for you to do a follow-up video on your Classes tutorial? I really enjoyed that one and I think that expanding on the employee ID application and maybe integrating some of your other tutorials in with it to make a functional real-world application (i.e DLL, database, another class) would be a nice maybe multi-part tutorial to follow along with.

[@Heinz19946]

Excepción no controlada del tipo 'System.NullReferenceException' en WindowsApplication2.exe Información adicional: Referencia a objeto no establecida como instancia de un objeto. help plis!

[@Memento2747]

COLLATE utf8mb4_bin; is the last portion of the Case sensitive Query if you are using MySQL Server. Had to find it.

[@Jon27111807]

How do you create a class connection, if you create a sql database within Visual Studio? Using a Microsoft SQL Server Database File (SqlClient)

[@VBToolbox]

+Jonathan Taylor Are you talking about a local database, like a SQL Compact Edition (SQLCe) file [.sdf]?

[@Jon27111807]

Its a .mdf file, so in my server explorer view, my database is called Users.mdf with a drop down list and shows my table folder and views ect.

[@VBToolbox]

+Jonathan Taylor What method did you use to create it the .mdf and which version of VB.NET are you using? I'm using Visual Studio 2010 SP1. I've not worked directly with an .MDF file in VB.NET, so I need to know if I will be able to test this.

[@VBToolbox]

+VB Toolbox Actually, I think I may have just figured it out. Let me test this a bit. :-)

[@Jon27111807]

VB Toolbox awesome I hope you do :)

[@Aat8974]

sir i have Trouble in SQl.SqlDs He Say SQL is Private So i went Solution

[@codemaster1120]

how could i display a users information in a another form?

[@VBToolbox]

Hmmm... Thought I had a demonstration of this in another video, but I can't remember which one. ;-P It's really fairly simple. As long as the second form is public you should be able to show [Form2.Show()] and populate the available fields and/or public variables.

[@admiralstyle8179]

I got this at System.Threading.ThreadHelper.ThreadStart()Server Error Code1042 A first chance exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

[@Matthew-rm7es]

My Button Dosent Work On The Form

[@lestertablan]

Hello, I'm having error message. Can you help me? For Each i As Object In SQL.SQLDS.Tables(0).Rows "Cannot Find table 0" Invalid Column Name

[@lestertablan]

VB Toolbox I fixed the error sir. thanks! :)

[@ahmedkhawaja7524]

+Lester Tablan Hi i am facing same issue, how did u fix it please?

[@sumanrai2008]

source code is not available in the provided link, can you please provide source code?

[@invalidcheats2733]

i get error at imports

[@Darkyinkia]

My app doesn't like that collate line :3 I did it step by step, only two things I can think of: 1- my database isn't case sensitive 2- It doesn't like SQL ce 3.5? There was an error parsing the query. [ Token line number = 1,Token line offset = 85,Token in error = COLLATE ]

[@AardaerimusDAritonyss]

Intriguing. I hadn't tried collation on SQLCe. That's good to know. It's important to have a means of forcing case sensitivity for passwords, although I'm also in the process of building an encryption DLL. I'm curious if I can just encrypt the password and then save it to the DB thus alleviating the need for collation.

[@AardaerimusDAritonyss]

I just read a Microsoft article saying that SQL Ce version 4.0 supports case sensitive collation, though I'm still unsure of whether it can be used as a query option.

[@Darkyinkia]

How much of an adjustment will I be making going from 3.5 to 4.0? I've just started getting a footing and I'd rather not swap if my brain is going to get fizzled :D

[@AardaerimusDAritonyss]

Darkyin Not entirely certain, just yet. Just installed it yesterday, but if I understand correctly, it's not much different other than that it has some performance improvements and allows more simultaneous connections (256), whereas 3.5, I think, only allowed like 40-50, or something like that.

[@dzeemechannel8775]

Sir how to resolve error. "cannot find table 0"?

[@dzeemechannel8775]

how to resolve this error sir? An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll Additional information: Cannot find table 0.

[@VBToolbox]

+daizaree dablo This means that there was an error in your query and it didn't generate a DataTable. When you try to get information from Table(0) and it doesn't exist it will crash with this error. If you use the new SQLControl class it is much better at finding and handling query exceptions. www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0

[@nimbusmultiservices2298]

but what is the solution for it, same error I am facing (An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll)

[@VBToolbox]

The reason that is occurs is because the query did not return any information (DataTables) to the DataSet, so when we try to call the first table [Table(0)] in the array, there is no table there and it crashes. The important thing to do first is find out why no data was returned from your query. This usually means that there was an error in the SQL. Using the updated SQLControl class, it is easier to determine if there was an error or if there were simply no records found. I'm not sure at what step you're getting the error, so it's hard to say exactly what the proper response should be, but as a general example you could use something like this: ' Run a query SQL.AddParam("@filter", MyTextBox.Text) SQL.ExecQuery("SELECT mycolumn FROM mytable WHERE myfiltercolumn=@filter;") ' Report & Abort on Errors If SQL.HasException(True) Then Exit Sub ' Load a DataGridView From Query Results MyDGV.DataSource = SQL.DBDT

[@Jellsan]

sir im having troubles in this line " If SQL.SQLDS.Tables(0).Rows(0).Item("UserCount") = 1 Then Return True" its says "Index Out of Range Exeption was unhandled" "Cannot find table 0."

[@VBToolbox]

Hmmm... I believe that indicates that your query is failing. You may wish to check and make certain you're query string is correct and that you have access to the table/s you're querying. If you need any help, let me know.

[@anneyeong897]

VB Toolbox Hi, im experiencing the same problem. i checked my query string, it's exactly the same as yours. what should i do now? Im a beginner in programming by the way. thanks in advance!

[@VBToolbox]

anne sandiego Hi there, Anne. Are you still having trouble with this? If you are, try catching your Query string to a message box [MsgBox(Query)] and see if it is formatting your query string properly.

[@anneyeong897]

VB Toolbox nope.. Thanks Sir!

[@CZF-ci8uw]

Well....four years later and here I am benefitting from your videos! (Thank you so much!) I get the same error. I took the query and put it into SSMS and it worked, equaling 1. Do you have any other suggestions?

[@K3Community]

You failed on the code. "Last Active: " & i.Item("website") :P So the website showed up as last active also. But good tutorial anyways!

[@VBToolbox]

Oh noes! Where was that? Guess I need to stop making tutorials when I'm tired. ;-P

[@K3Community]

VB Toolbox At around 42 minutes :P But thats fine, maybe I was the only one which saw this. Its a secret:P Keep making tutorials;) Good job :D