Outrageously good - so smart and professional - I'm loving every keystroke up here on the west coast of Scotland with a grey sky, 60 mph winds and endless rain. Thank you again. Martin
WOW! You have done an incredible service. You are a truly gifted teacher. I am an old VB6 guy who has dreaded doing anything in .NET. You are my HERO 8-). I especially appreciate that you didn't do all your work with SQL Server and did most of it for Access, and made the necessary video's for SQL where it mattered. Again, I can't thank you enough.
Thank you for the encouraging feedback, Kenneth! :-) I definitely want to refresh this old SQL series as I have a much improved SQLControl class. Still, I'm very happy that you've found the videos to be helpful.
Thank you so much for your time showing us "newbies" the way stuff is done. I appreciate it and I learned a lot. The way you explain is very easy and right to the point
Awesome tutorial. Very smart to use a class for SQL query and avoid code injection. (adopted) And I adopted your code and adapted to populate others combo boxes (5 others) items for dynamic selections. Looks great .. very cool user experience. thanks a lot.
i'm watching this at my internship bc ive got 1 week left to do my final highschool project and i think you'll be a great help tonight when iget at it xD iu explain everything very simple and clear. ill tell u later if i got a good grade ^_^
Many thanks for the feedback, Stephen. :-) I'm very happy that it was helpful, and I'm terribly sorry that I haven't completed the refresh on this old tutorial. I have a much improved version of this, but have not yet had the opportunity to make a new video.
Many thanks for the feedback, Stephen. :-) I'm very happy that it was helpful, and I'm terribly sorry that I haven't completed the refresh on this old tutorial. I have a much improved version of this, but have not yet had the opportunity to make a new video.
This is a great video tutorial. I am able to learn MYSQL and VB in making database. I hope there are more videos about database programming using MYSQL and VB. Salute to you sir!
Thank you, Myke! :-) I do have a DBControl Class for MySQL. If you are interested, please feel free to use it. It works very similarly to my Access and SQL classes. Source: www.dropbox.com/s/gl1o55c5hsxounn/MySQLDB.txt?dl=0
It's amazing... the memory and intelligence you have ! Brand new to this stuff... I follow your line of thinking, I even can understand the need for the code... still, when it comes my turn to do it... blanks (for the necessary code) ! Well... may be it needs much more time to memorize, than you make us think it is ! Perfect... everything and every new project, from yours ! Thank you so much. I still think... if you would write HERE, the code you type on your computer (separated by parts, referring to the classes... etc) it would be much better for us. It would be necessary, for all... to STUDY that code... as we should have to change names, and everything needed to adapt the code... to our own typing ! That would make us THINK, DEDUCE, and MEMORIZE (WHAT and WHY) ! Your... move.
As Visual studio no longer supports local database in Add, New Item, Data, and System.Data.sqlserverce is not on the Reference list, it took a bit of working out to get a sdf file written but perseverance paid off. Thanks again for your efforts.
Still learning some basics. How would you incorporate select statements for multiple combo boxes from different tables? Or do you have a tutorial for this?
I have just started to play with VB and you video's are helpfull. Filling Combobox is piece of cake now :). But i am trying to use the 'FIELD ID' related to the selected value in teh Combobox to update another table. I notice it has to do with Valuemember but how do i get that using your code ? Thanks a lot
I am working my way thought all you great tutorials and so far all have worked flawlessly. I have come to this one and I have a problem. I am using Visual Studio 2017 which didn’t come with SQL CE but I downloaded it. What I don’t have is the Local Database option or the .sdf file type which the program was looking for. I tried using other database file formats including using the Server Based Database option but none of the file formats worked. Do you have any suggestions? Thanks
Roger Fernandes Hello, Roger. I apologize for the late reply. For some reason I don't remember seeing your comment in my inbox and I'm not sure if I ever got that project to you. :-( Let me know if you still want it and I'll be happy to share.
Not that it really matters, but I have VS 2013 for Windows Desktop. There is no SQLCE reference. I right click my project, choose Add (there is no Add Reference), then choose Reference from the next drop down menu, but no SQLCE can be found. I downloaded the 4.0 from the link you provided, but it won't install, it says a newer version is already installed. Any ideas? Got your SQL Server examples working fine though, so I can use that, just interested in being able to provide an App without the user needing to download, install and configure the full MSSQL. One other quick question, I read somewhere that VB.NET executables can be fairly easily dissembled, would this not create a possibly security risk with someone being able to then access your SQL admin credentials? Cheers for the videos, slowing going through them and trying to re-learn VB after not really touching it since VB6.
Great walkthrough. But, what is you have more than one table in the database? Using the table's name instead of the index 0 in the example does not work. How to identify the index in the schema to access a particular table?
Hello! :-) In these examples the indexed tables that are being referenced by index are not representative of the tables in the database, rather they are an outputted DataTable object that consists of the results of the query. You could create multiple containers or output multiple queries into separate datatables; However, for this example, there should only ever be one outputted DataTable. To access other tables in your database, you'd simply do this through your query. Examples: SELECT * FROM table1 or SELECT table1.column1, table1.column2, table2.column1 FROM table1 INNER JOIN table2 ON table1.column1 = table2.column3 WHERE table1.column2 = 'something' In the second example, we called a query on two joined tables but this still produces only one DataTable [SQL.DBDS.Tables(0)]. I am presently rebooting my SQL Server tutorials with a much improved SQLControl class which omits the use of the DataSet and instead uses a DataTable for simplicity. I hope this helps! :-)
Great tutorial. I have spent a couple of days looking for something like this to help and all apart from this one have been rubbish. I have developed it to bring in data from the first combobox into a second. This works on the first attempt but on subsequent attempts it hold the data from the previous. So you end up with a load of duplicated entries. There is a line of the code that clears the first Combobox, but i cant seem to replicate so it clears my second. Any ideas? Thanks Again Jon
Hello, Jon. :-) You should be able to simply put "ComboBox2.Items.Clear" at the top of the method that you're using to populate the second ComboBox. That way it will purge the ComboxBox each time that you attempt to populate it. Let me know if you need any examples or further explanation and I'll be happy to help out if I can. Also, this is a very old tutorial and I have a much improved SQLControl class, as demonstrated in my SQL Server series reboot. Let me know if you'd like a copy of that adapted for SQLCe and I'd be happy to share.
They maybe old put they work well all the same. I am using a MySql database so am not sure if your reboot would be relevant to me. I have tried putting the "combobox.items.clear()" at the top of method and its doesn't seem to work. Using your example its beneath the "Private Sub getfood(food As String)" method. Thanks again
I actually have an adapted Control class for use with MySQL, as well. :-) The DBControl for MySQL is only slightly different, but the VB code to use it is virtually identical to the new videos. Am I understanding correctly, that the first ComboBox is clearing correctly, but the second ComboBox is not? Are you calling it by name when you use .Items.Clear()?
The fist combobox clears under the form loader sub. I them put aother a cbxcontact.itesm.clear() under the cbxcust index change (cbxcust being cbxfood on your version and cbxcontact being txtfoodtype) I have dumped it in other various parts of the code to see if i get any luck. Thanks Jon
It depends upon your needs. If you don't mind pulling duplicate records, it doesn't matter. If you still want to pull a single record you'll just have to pick a unique field to filter by. (Example: WHERE id = '3')
Hi, its me again. What if i want two combo boxes in this for? one for the food type and another for the food name. what i want to do is so select first the food type in the foodtype combo box then all the food names that have the selected food type is seen/fired/filled in the next combo box which is the food name. any help with that? Thanks a lot in advance! your tutorials are really awesome!
Hi there, Anne. :-) Sorry for the slow reply. We can easily create a dynamic query like this based upon the SelectedIndexChange event for the ComboBox. Essentially, you can send the Text property of the selected item of one ComboBox to a new query as the filter. If you like I can produce a sample project for you.
Igor Oliveira I will have to check this when I get home. I use 2010 here, but I have a copy of 2013 Express at home that I never use. I will have to try and see.
Sorry, I just moved and my web server is still offline. This is the latest version of my SQLControl class: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Hello Sir, i'm just learning vb.net just trying to add some to my knowledge in IT... i followed your procedure and your code but when i try to run, this error pop out... "An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll Additional information: Cannot find table 0." i just make my database instead of food i change it to Supplier.. any advice on this..appreciate your feedback..thanks
+Rj Barretto Hello, Rj! Generally, this means that there was an error in the SQL query that caused the Dataset not to Fill properly. My newest version of the SQLControl class has better error sampling and also simplifies the Dataset usage by exchanging it for a Datatable. Let me know if you'd like me to put together a simple sample project for you to demonstrate this.
+Rj Barretto Okay. :-) I made a simple example project for you. PROJECT SCREENSHOT: www.dropbox.com/s/ukvajfr79n1ae2f/SQLComboFill_Revised.png?dl=0 PROJECT SOURCE CODE: www.dropbox.com/s/iyomno9eta50tjb/SQLComboFill.zip?dl=0 Note: Don't forget you update the SQLControl connection string and queries to match your database. I hope it helps!
+VB Toolbox thank you sir for making that simple example, i appreciate it much! didn't know what "vbNewLine" do until i run the application.. :-D i keep on following your video sir and i hope you'll not get tire or doing it... ;-) just one more favor sir, if you don't mind... please do a tutorial video that focuses about "Loops"...im having a hard time understanding it... thank you sir, God bless.. :-)
I love your professional way in programming . thank you so much for this Tutorial it's helped me a lot and I learned of you a lot of things like write comments before write code to remember what is that coding do ? use classes . thank you so much. I just wonder if there class for management database ( add,update,delete,search) to use it for all projects. if not I hope you learn us how we can do that ? I think it will be helpful for us the beginners. I really want to use that operations with classes. All Regards .
Murad Alshami Thank you, sir. :-) I'm happy that the tutorials have been helpful! To my knowledge there are no built in classes for complete database management; However, the SQLControl class that I use in these tutorials does this, and can also be easily modified for your specific needs. Additionally, it's very easy to change it for management of other database types [SQL Server, MS Access, MySQL, etc.]. The only changes that need to be made are in the connection string and namespaces that you use [e.g., SQLCeCommand = OleDbCommand = SQLCommand = MySQLCommand]. Also, the ExecQuery() Sub in this tutorial will successfully run SELECT, INSERT, UPDATE, DELETE, etc. I hope that helps! Take care. :-)
Hi again Mr VB Toolbox First, thanks again for the help you gave me on another of your video; Your pointer to the Trusted_Connection=True option worked a treat and I've made some good progress with my VB objectives in the past few days.... I do have one question about on this video; Is there a considerable difference between the data access libraries you use here (.SQLServerCe) and the (.Client) library I've been using up until now. Although it is my medium term objective to build an application for the College where I teach, right now I'm just working on my own machine so I'm not yet in a client/server situation Thanks again Chris Read, Leeds, England
Chris Read Hello, Chris. :-) Functionally, the two libraries are virtually identical, but each is built to offer that same functionality to different database types. There are .NET connectors for pretty much every major database out there (SQL, SQLCe, Access, MySQL, Firebird, etc.) each has it's own connection library (.dll) and namespace (SqlClient, SqlServerCe) contained within that library. So, in your case, you can't use SqlClient to connect to SQLServerCe or the reverse, even though they are similar and offer the same methods and functions. I hope that makes sense.
Mr. VB Toolbox, your project work very good. Now I need to delete the data "food" I enter a new one and I need a button to add new food, Do you have that tutorial, too.? I mean edit, update, save, delete, and add new to same project.
This is an excellent video with the information clearly explained (one of the best programming videos which i have viewed). I have one question: - i am connecting a MySQL database to Visual Studio. At the moment of 33 mins 27 seconds in the video, you wrote the following code: SQL.SQLDS.Tables(0). What is the code to use in reference to a MySQL database (as the IntelliSense do not include SQLDS (SQL Dataset) in the drop-down menu as I was typing in my application)? If I use the code shown below, I get an error message: "Table is not a member of DataTable" at the line: For Each r As DataRow In DB.DBDT.Table(0).Rows My code is shown below : - Private Sub GetDATASECOLUMN() 'QUERY TABLENAME DB.ExecQuery("SELECT COLUMN NAME FROM MYDATABASE.TABLENAME") 'IF RECORDS ARE FOUND, ADD THEM TO THE COMBOBOX If DB.RecordCount > 0 Then For Each r As DataRow In DB.DBDT.Table(0).Rows cboComboBoxName.Items.Add(r("COLUMN NAME")) Next 'SET THE COMBOBOX TO THE FIRST RECORD cboComboBoxName.SelectedIndex = 0 ElseIf DB.Exception "" Then 'REPORT ERRORS MsgBox(DB.Exception) End If End Sub
You can download 4.0, here: www.microsoft.com/en-us/download/details.aspx?id=17876 Or you can Google "SQLCe 3.5 Download". Each should be freely available from Microsoft. :-)
VB Toolbox Ok! First, I have to 2 combo box. The 1st one labels "Category" the other one "Sub-Category". In Category, I have Organic and Liquid( I dont know if I'm correct :-) ). When I choose Organic, the Sub-Category shows the "Vegetable" and "Fruits". From your tutorial, you used dynamic query between combo box and textbox. This is my example sir. (Note: Sir I already install the SQL Compact 4.0 but when I use the SqlServerCe in my VS 2010 Ultimate in didnt show the import :-) )
I have been enjoying your tutorials very much. I use VB2010 and SQL Server Express2014. I modified your code to use SQL Server 2014. I like what you have shown using Compact Edition. I downloaded SQLCe but cannot VB2010 to recognize it. Can you help me with this? I am sorry about the name. It came because Google would not let sign back in as my old login so I created a new one. I am very serious about using Visual Basic and would very much take advantage of the easier DBMS SQL Server Ce you showed. As I mentioned above, I was successful in modifying your code to access SQL Server 2014 but the compact version looks much easier.
Thank you! Amazing tutorials! it helped me with my thesis. :) is it okay with MS SQL SERVER 2008 EXPRESS. i think there are some code differences if you are using SQL SERVER CE or 2008 Express. i just assumed that.
Thankfully, ADO.NET is pretty standardized so the differences are surprisingly minimal, whether it's SQLce, SQL Server, MS Access, and even MySQL. Really the only differences that I've seen are the calls to the namespaces and the connection strings. :-)
Thank you so much for your concise tutorials. I have watched others but yours are the best by far. I am a newbee to vb 2010. I programmed a little vb about 15 years ago but its all different now. I am attempting to write a program to allow me to store URLs, username and passwords into a database. On form load I am displaying the URLs in a list box. When selected I open the browser and log in with name and password. I had the program about done when I decided the database should be encrypted. That opened up a whole kettle of worms. My question is (How does the user enter and view their records). Do I load arrays and is there an easy way to encrypt/decrypt in VB. Have you written a book? I would like to be able to get your code samples. Thanks Tom M...check ur tip jar
Wow, Tom! I'm hugely appreciative of the contribution. Thank you so much! Regarding encryption, that's always a very tricky devil and there are so many different ways to handle it. My understanding is that, more important than the encryption type used, are the techniques by which they are employed. Unfortunately, I've only scratched the surface when it comes to encryption. I've played around a bit with salted SHA512 and the encryption/decryption process is not excessively difficult, but I think the problem comes to actually storing the encrypted values - especially in a world with increasingly powerful CPUs. If there is access to the data, it can be hammered open. For this reason, I think that passwords should never be stored to the database or anywhere else, rather a password hash can be stored and challenged by the client. Now, I think that later SQL server versions actually offer embedded encryption, but I really have no idea how secure that really is. Unfortunately, I haven't written a book. I think that I'm much too disorganized. ;-P Let me know which code samples you'd like to have me dig up and I'll be happy to share. :-) Thanks again!
So I am running into a small snag. I am using your CB Query to populate an employee table in a CB. Got that no problem. I also am trying to integrate from your SQL Delete Command Video I am then trying to create a button that will delete selected user from the CB, and I am getting an error. Wondering if you can help: Try If cbSOEIDLATAM.Text "" Then If MsgBox("Do you really wish to delete " & cbSOEIDLATAM.Text & "?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then SQL.DataUpdate("DELETE FROM tbl_EmployeesLATAM WHERE Full_Name='" & cbSOEIDLATAM.Text & "; ") End If End If Catch ex As Exception MsgBox("Employee does not Exist!") End Try Error I receive: Unclosed quotation mark after the character string 'Test User; '. Incorrect syntax near 'Test User,'.
+Alcohol Arcade Hey there, Alcohol Arcade! First off, it seems you left off a single quote in & "; ") after the cbSOEIDLATAM.Text in your DELETE expression. That said, I *strongly* recommend employing SQL parameters as they will help tidy up the command string, prevent these types of issues, and help secure against SQL injections when using TextBoxes that supply user input. All of my later videos employ them and they're easy to use and have great benefits. Just out of curiosity, which version of my SQLControl class are you using, or which video did you grab it from? Since my older videos, I've made a number of improvements that may help simplify usage and improve stability. :-)
hi i try to learn vb.net to create a app. i found your video, and i try to write te code but i receibe the msg error on: " Params.ForEach (Sub(x) SQLcmd.Parameters.Add(x))" expression expected "Sub" how i correct this error? i am using vb2008 to program a Motorola MC2810 barcode scanner thanks for your help
tecni210 Hi there! Sorry for the delayed response. Unfortunately, VB2008 doesn't handle Lambda expressions quite as elegantly as later versions. The easy fix is to convert the Lambdas into a basic For Each loops. Example: For Each p As SqlCeParameter In SQLCmd.Parameters SQLCmd.Parameters.Add(p) Next
The code works awesome bro I subs and liked, but I have a problem here. I hope you can help me. I have 2 tables. Products table has Customer and Products as column inside it and a Customer table has Customer column inside it. I have 2 comboboxes, 1 is for Customer and 1 is for Products. I wanna do is when I click a customer, only the products with the customer name will appear, how will I query that? Thanks if you can help.
+Foxseiz Hello, Foxseiz. :-) This can be achieved by having a Sub to query and Fill each ComboBox. Example: [Note: I'm using the latest SQLControl Class] Step 1: Fill the Customers ComboBox [ComboBox1] Private Sub GetCustomers() ' REFRESH COMBOBOX ComboBox1.Items.Clear() ' RUN QUERY SQL.ExecQuery("SELECT customer FROM Customers;") ' REPORT & ABORT ON ERRORS If SQL.HasException(True) Then Exit Sub ' FILL CUSTOMER COMBOBOX For Each r As DataRow In SQL.DBDT.Rows ComboBox1.Items.Add(r("customer").ToString) Next ' SELECT FIRST CUSTOMER If ComboBox1.Items.Count > 0 Then ComboBox1.SelectedIndex = 0 End Sub Step 2: Fill the Products ComboBox [ComboBox2] when a Customer is selected Private Sub GetProducts() ' REFRESH COMBOBOX ComboBox2.Items.Clear() ' ABORT IF NO CUSTOMER SELECTED If String.IsNullOrWhiteSpace(ComboBox1.Text) Then Exit Sub ' ADD SQL PARAMETER TO FILTER PRODUCTS BY CUSTOMER SQL.AddParam("@cust", ComboBox1.Text) ' RUN QUERY SQL.ExecQuery("SELECT product FROM Products WHERE customer=@cust;") ' REPORT & ABORT ON ERRORS If SQL.HasException(True) Then Exit Sub ' FILL PRODUCTS COMBOBOX For Each r As DataRow In SQL.DBDT.Rows ComboBox2.Items.Add(r("product").ToString) Next ' SELECT FIRST PRODUCT If ComboBox2.Items.Count > 0 Then ComboBox2.SelectedIndex = 0 End Sub Finally, add the first Sub GetCustomers() to the Form1_Shown event, and add GetProducts() to the ComboBox1_SelectedIndexChanged event so it will re-query for each customer selection. I hope this helps! :-) If you don't have the latest SQLControl Class, you may find it here: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Hey there Mr. A.. it's me again. Nobody explains like you do so I am here to learn just a little more about Combo boxes and updating across multiple forms. As you know, I have a Multiform project with many tables that support an inventory form. I have forms for data entry to the supporting tables like vendor, model, manufacturer. The vendor form for for example, is working properly and the datagrid is updating whenever I add a new vendor. The problem I have is that when I return to the main form and go to select the new vendor I have just created, it is not in the combo box dropdown yet. The only way to get the vendor combobox on the main to update is to close the app and start again. I have searched all over the vb forums and can't find a solution that actually works. I found many people trying to solve this issue, and lots of "try refresh, use requery" 'reload the adapter" but nothing that actually works. It is such a common task, I know someone knows how to do this so I am hoping it's you. Logically I think the update should happen either on close of the vendor form or on the combobox selectedindexchanged event, but just can't get it. Your help is greatly appreciated and since this is such a common issue I would think it is a great teaching subject.
What I've done to handle this sort of thing is have a Public Sub on the parent form, which I call from the child form. To do this, I added an Object variable to the child form and send the Parent form into that object. Then the child form has access to the Parent's controls. In Form1 (Parent): Public Sub UpdateMyControls() ' Do your refresh End Sub Public Sub CreateTheChildForm() Dim ChildForm As New Form2 ' Use your form type ChildForm.frmParent = Me ChildForm.Show() End Sub In Form2 (Child): Public frmParent As Object Private Sub SomeActionThatUpdatesYourBox() ' Your code here.... ' Call Sub on Parent Form frmParent.UpdateMyControls() End Sub We essentially send a handle of our parent form to our child form so that it can access its controls. Note: When you use an Object variable, Intellisense won't give hints about the Objects available features so you have to type "UpdateMyControls()" exactly as it is written in Form1.
Not completely sure I understand how this works. I am getting an error when the code executes for this line: frmMain.UpdateMyControls() NullReferenceException was unhandled Object variable or With block variable not set There is also a recommendation in the error to use New, but New is already in the childform DIM statement, so I am not sure if that is relevant. Here's my code for the parent form (frmMain) and form2 (frmUsers) In frmMain (Parent): Public Sub UpdateMyControls() ' Do your refresh Me.UsersTableAdapter.Fill(AssetDBDataSet.Users) End Sub Public Sub CreateTheChildForm() Dim ChildForm As New frmUsers ' Use your form type ChildForm.frmMain = Me ChildForm.Show() End Sub In frmUsers(Child): Public frmMain As Object Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click Try Me.Validate() Me.UsersBindingSource.EndEdit() Me.UsersTableAdapter.Update(Me.AssetDBDataSet.Users) MsgBox("Update successful") Catch ex As Exception MsgBox("Update failed") End Try frmMain.UpdateMyControls() Me.Close() End Sub (I know that I can change the name of the childform etc., I just wanted to get this to run with as few changes as possible first.)
Thanks. This tutorial enabled me to place much more easily readable SQL statements within my code. Does anyone know what would you do if you wanted to use LIKE and a wildcard in your statement by the way? I've been experimenting with adding more than one parameter but if no value is given for that parameter then it should return all of the results for that parameter. I'm not having much success unfortunately. As an example say you have 3 comboboxes populated with the items below from a database, when clicking on a button a listbox should be populated with the selections made in the comboboxes forming the search criteria: Item ItemType Colour Apple Fruit Red Carrot Vegetable Orange Apricot Fruit Orange Cherry Fruit Red Say you might want to run a search for all the fruits in the list but no value is specified for colour, later you run a search on colour but leave the ItemType empty. Would it even be possible to create a single "catch all" SELECT statement that allows the various combinations of parameters to either contain a value or return everything when no value is specified? I can't seem to find a way to get the parameter to contain a wildcard and still end up with valid SQL when the query is executed.
Hello, Llewellyn. :-) I have done this very thing in a few of my other tutorials. Unfortunately, my SQL tutorials are a bit stale and are in bad need of a refresh. In a nutshell, this is all you need: ' ADD PARAMS & RUN QUERY SQL.AddParam("@users", "%" & txtFilter.Text & "%") SQL.ExecQuery("SELECT username FROM members WHERE username LIKE @users;") If no value is supplied, the WHERE will look like "username LIKE %%", which is a full wildcard search and will return all records unfiltered. If I supplied a value in my TextBox [e.g., "A"], my WHERE will look like "username LIKE %A%" and will be my catch all for any items containing that letter. At present, I'm using this SQLControl class: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Awesome, that works perfectly! I've been working through the tutorials in my spare time and I'm learning a great deal from them. Thank you for the effort you're putting into the tutorials as well as for taking the time to reply to my comment!
@VB Toolbox thank u for this amazing video i have a problem to display dynamic value from mysql database to a label i have a counter nd i save the value of this counter in my database and i want to display the last value in a textbox in my vb application but i fail every time can u help me plz ?
+Ahmed Jbeli Is the counter column an auto-incrementing identity field, or is it just a number value that you store? If it's just a numeric value that you store use "SELECT MAX(yourcountercolumn) FROM yourtable". This will return the highest value of the counter column.
i think i find a solution i made an au refresh every second and it works now :) thank u for replying and if u have another proposition tell me :) thank u again :)
+Ahmed Jbeli I'm glad that you found a solution. :-) Select Max(columnName) should still work to grab the highest number. If you want to immediately capture the last added ID after an INSERT expression, you should be able to use SELECT SCOPE_IDENTITY().
I am brand new to vb.net but am advanced/expert level in VBA. I feel like this could be accomplished so much easier in Excel, I am tryin to learn .net to be ready for job interviews. any tips for me?
Hey there, James. :-) That's a difficult question to answer... I guess my advice is pretty generic, but I would first make certain that you acquire a broad understanding of basics of VB.NET usage and syntax. Since you're already good with VBA, this should come naturally as they're similar in structure. Play around with the common tools and objects of form design and learn how they can communicate and interact with one another. Learn to extend the capabilities of your applications by importing various namespaces. Beyond that, as you well know, programming is very nebulous so you'd do well to focus on the finer points of the specific *types* of programming jobs that you intend to apply for. For example, if you want to develop database apps, there's not much point in burning a great deal of time on working with GDI or sockets apps. It's nice to have a vast knowledge of your preferred programming language, but it's easy to get bogged down in learning about things that will never apply to your desired field.
Thanks for this video. It's exactly what I've been looking for. I'm getting this error when running this portion: Private Sub DatabaseCon() 'Query Database DatabaseConnection.ExecuteQuery("SELECT Program_Name FROM Program_list") MsgBox(DatabaseConnection.Exception) "Could not find installable ISAM" Everything else seems to be ok, but my combo box won't populate. I'm connecting to an Access database local to my PC, but did not copy it into my project. Can you help? Thanks! Jon
Hello, Jon. :-) I'm unfamiliar with that error, but a quick Google search turned up this Microsoft article (support.microsoft.com/kb/209805). I'm not sure if that applies to your version of Access, but it might be worth looking into.
Hey thanks, I did see that article. Looks like modification of the registry may be part of the solution, which will be a challenge for me. Thanks again!!!
jonathan kervin lol I'll try not to let them kill me. ;-) Definitely be careful tweaking the registry if you're unfamiliar with it. While it's not usually too daunting if you have straight-forward instructions, tweaking the wrong thing can be very bad.
thanks for all your videos, they have helped me a lot. I was trying to copy what you did only use my own database, and I think that's where I messed something up. anyways, what I would like to do in the long run, is to fill the combobox with all available tables, then basted on that selection, fill the next box (combobox) with all available columns. Can ya help me out?
Indeed I can. :-) I have just such a project, but I'll need to figure out where I put it. lol What you'll be doing is SELECTing from the INFORMATION_SCHEMA to return the tables and columns. I'll see if I can find that example for you.
Brice Drilling I couldn't find my original source, so I'm just going to modify the code from this tutorial to produce the desired effect and put a copy on DropBox. Should just be a couple minutes.
i did all thing like u said and i got the result when t tried with msgbox RecordCount but after when i typed this code i got an error: Private Sub GetMatricule() ExcutQuery("SELECT Matricule FROM Operatrices") 'If records are found, put them in the combo If RecordCount > 0 Then For Each rtb As DataRow In ds.Tables(0).Rows MatriculeComboBox.Items.Add(rtb(" Matricule ")) Next 'set combo in the first record MatriculeComboBox.SelectedIndex = 0 ElseIf Exeption "" Then MsgBox(Exeption) End If i didn't use a classe but i wrote the code directly into the form code igot this error msg NB Matricule Column is a primary key in Operatrices table and FK in FichChr table ---------------------------------------------------------------------------------------------------------------------- La colonne ' Matricule ' n'appartient pas à la table Table ---------------------------------------------------------------------------------------------------------------------- L'exception System.ArgumentException n'a pas été gérée HResult=-2147024809 Message=La colonne ' Matricule ' n'appartient pas à la table Table. Source=System.Data StackTrace: à System.Data.DataRow.GetDataColumn(String columnName) à System.Data.DataRow.get_Item(String columnName) à Hemodia_Project.Fichechron.GetMatricule() dans C:\Users\USER\Documents\Projets\Hemodia Project\Hemodia Project\Fichechron.vb:ligne 72 à Hemodia_Project.Fichechron.Fichechron_Load_1(Object sender, EventArgs e) dans C:\Users\USER\Documents\Projets\Hemodia Project\Hemodia Project\Fichechron.vb:ligne 87 à System.Windows.Forms.Form.OnLoad(EventArgs e) à DevComponents.DotNetBar.Office2007RibbonForm.OnLoad(EventArgs e) à System.Windows.Forms.Form.OnCreateControl() à System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible) à System.Windows.Forms.Control.CreateControl() à System.Windows.Forms.Control.WmShowWindow(Message& m) à System.Windows.Forms.Control.WndProc(Message& m) à System.Windows.Forms.Form.WmShowWindow(Message& m) à DevComponents.DotNetBar.Office2007RibbonForm.WndProc(Message& m) à System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) InnerException:
It's a bit difficult for me to read the French, however, it looks like it is looking for Matricule in "Table" instead of "Operatrices". Your SQL looks accurate, so there must be something else we are overlooking. Is the new error exactly the same? Do you have any other queries executing?
qui peut m'aider avec l'instruction INSERT enregistrer l'objet DateTimePicker dans une table access? IN English: who can help me with the INSERT statement save the DateTimePicker object in an access table?
Hello Thanks for great telling for this video. I have a problem . I am very new at coding and I try to apply this thing ComboBox to ComboBox but I couldn't get first ComboBox ID value(in your case fruit ComboBox I need to show Fruit name on ComboBox but I need ID for other ComboBox ) for apply it second ComboBox query filter. How can I show it as parameter to second ComboBox?. I added my code. Thanks again for your effort. www.dropbox.com/s/zl2nioryp4abggq/ComboBox%20From%20Dynamic%20Query.txt?dl=0
My guess is that cboCategory.SelectedValue is coming back without a value, so it's failing to filter. I used cbxCategory.Text instead of SelectedValue and it worked in my test environment. I added a couple of tables to my database [Category & SubCategory] with columns [Category.CategoryName, SubCategory.ParentCategory, SubCategory.SubCategoryName]. SubCategory.ParentCategory contains the name from Category.CategoryName for the ComboBox filter. This is my code [using the updated SQLControl] that works: Private Sub GetCategory() ' CLEAR CATEGORIES cbxCategory.Items.Clear() ' QUERY CATEGORIES SQL.ExecQuery("SELECT CategoryName FROM Category;") ' REPORT & ABORT ON ERRORS If SQL.HasException(True) Then Exit Sub For Each r As DataRow In SQL.DBDT.Rows cbxCategory.Items.Add(r("CategoryName")) Next ' SELECT FIRST ITEM If cbxCategory.Items.Count > 0 Then cbxCategory.SelectedIndex = 0 End Sub Private Sub GetSubCategory() ' CLEAR SUB-CATEGORIES cbxSubCategory.Items.Clear() ' ABORT IF NO ITEMS SELECTED ON PARENT If String.IsNullOrEmpty(cbxCategory.Text) Then Exit Sub ' QUERY SUB-CATEGORIES SQL.AddParam("@CategoryID", cbxCategory.Text) SQL.ExecQuery("SELECT SubCategoryName FROM SubCategory WHERE ParentCategory=@CategoryID;") ' REPORT & ABORT ON ERRORS If SQL.HasException(True) Then Exit Sub For Each r As DataRow In SQL.DBDT.Rows cbxSubCategory.Items.Add(r("SubCategoryName")) Next ' SELECT FIRST ITEM If cbxSubCategory.Items.Count > 0 Then cbxSubCategory.SelectedIndex = 0 End Sub Private Sub cbxCategory_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbxCategory.SelectedIndexChanged GetSubCategory() End Sub I hope that helps! :-)
Outrageously good - so smart and professional - I'm loving every keystroke up here on the west coast of Scotland with a grey sky, 60 mph winds and endless rain. Thank you again. Martin
WOW! You have done an incredible service. You are a truly gifted teacher. I am an old VB6 guy who has dreaded doing anything in .NET. You are my HERO 8-). I especially appreciate that you didn't do all your work with SQL Server and did most of it for Access, and made the necessary video's for SQL where it mattered. Again, I can't thank you enough.
Thank you for the encouraging feedback, Kenneth! :-) I definitely want to refresh this old SQL series as I have a much improved SQLControl class. Still, I'm very happy that you've found the videos to be helpful.
Thank you so much for your time showing us "newbies" the way stuff is done. I appreciate it and I learned a lot. The way you explain is very easy and right to the point
Awesome tutorial. Very smart to use a class for SQL query and avoid code injection. (adopted) And I adopted your code and adapted to populate others combo boxes (5 others) items for dynamic selections. Looks great .. very cool user experience. thanks a lot.
i'm watching this at my internship bc ive got 1 week left to do my final highschool project and i think you'll be a great help tonight when iget at it xD iu explain everything very simple and clear. ill tell u later if i got a good grade ^_^
This is one of the best lessons i have learnt..Kudos buddy
Another great helpful video. Please continue producing these educational video. We appreciate your tutorial on VB.net and databases.
Thank you, Abdillahi. :-) Makes me very happy that they're helping people.
Thank you for a wonderful tutorial. This helped me a lot. Your instructions are so clear and easy to follow. 2 Thumbs Up for you.
Many thanks for the feedback, Stephen. :-) I'm very happy that it was helpful, and I'm terribly sorry that I haven't completed the refresh on this old tutorial. I have a much improved version of this, but have not yet had the opportunity to make a new video.
Many thanks for the feedback, Stephen. :-) I'm very happy that it was helpful, and I'm terribly sorry that I haven't completed the refresh on this old tutorial. I have a much improved version of this, but have not yet had the opportunity to make a new video.
This is a great video tutorial. I am able to learn MYSQL and VB in making database. I hope there are more videos about database programming using MYSQL and VB. Salute to you sir!
Thank you, Myke! :-) I do have a DBControl Class for MySQL. If you are interested, please feel free to use it. It works very similarly to my Access and SQL classes.
Source: www.dropbox.com/s/gl1o55c5hsxounn/MySQLDB.txt?dl=0
You are an angel :-)
It's amazing... the memory and intelligence you have !
Brand new to this stuff... I follow your line of thinking, I even can understand the need for the code... still, when it comes my turn to do it... blanks (for the necessary code) !
Well... may be it needs much more time to memorize, than you make us think it is !
Perfect... everything and every new project, from yours !
Thank you so much.
I still think... if you would write HERE, the code you type on your computer (separated by parts, referring to the classes... etc) it would be much better for us.
It would be necessary, for all... to STUDY that code... as we should have to change names, and everything needed to adapt the code... to our own typing !
That would make us THINK, DEDUCE, and MEMORIZE (WHAT and WHY) !
Your... move.
This is an excellente tutorial very professional congratulations! you area very good teacher
i love your keystroke sound. thank you so much for your great videos 👍
thanks buddy!!! you save my life!!! Love you
+Christian Yacobus Haha! Awesome! Always happy to save a life! ;-D
Thank you for this video! You have save my life!
Naia Banks-Frias I'm very happy that the video was helpful, Naia! :-)
oh my god!!!!!!!!!!!!!!!!!!!!!!!
It's very helpful~
thank you!
Great tutorial, thanks !
Great tutorial!! thumbs up for you.
Thank you this was perfect
Thank you for watching, Beverly. :-) I'm glad that the tutorial was helpful!
As Visual studio no longer supports local database in Add, New Item, Data, and System.Data.sqlserverce is not on the Reference list, it took a bit of working out to get a sdf file written but perseverance paid off. Thanks again for your efforts.
Very Good!
Still learning some basics. How would you incorporate select statements for multiple combo boxes from different tables? Or do you have a tutorial for this?
I have just started to play with VB and you video's are helpfull. Filling Combobox is piece of cake now :). But i am trying to use the 'FIELD ID' related to the selected value in teh Combobox to update another table.
I notice it has to do with Valuemember but how do i get that using your code ?
Thanks a lot
I am working my way thought all you great tutorials and so far all have worked flawlessly. I have come to this one and I have a problem. I am using Visual Studio 2017 which didn’t come with SQL CE but I downloaded it. What I don’t have is the Local Database option or the .sdf file type which the program was looking for. I tried using other database file formats including using the Server Based Database option but none of the file formats worked. Do you have any suggestions? Thanks
Hi, thank you so much for the tutorial, any chance you can upload again the project you sent to Anne, that has 2 combobox?
Roger Fernandes Hello, Roger. I apologize for the late reply. For some reason I don't remember seeing your comment in my inbox and I'm not sure if I ever got that project to you. :-( Let me know if you still want it and I'll be happy to share.
You didn't but I figured it out. Thanks anyway!
Thank god you speak english
when we need multiple data at textbox so what will we do?
Not that it really matters, but I have VS 2013 for Windows Desktop. There is no SQLCE reference. I right click my project, choose Add (there is no Add Reference), then choose Reference from the next drop down menu, but no SQLCE can be found. I downloaded the 4.0 from the link you provided, but it won't install, it says a newer version is already installed. Any ideas?
Got your SQL Server examples working fine though, so I can use that, just interested in being able to provide an App without the user needing to download, install and configure the full MSSQL.
One other quick question, I read somewhere that VB.NET executables can be fairly easily dissembled, would this not create a possibly security risk with someone being able to then access your SQL admin credentials?
Cheers for the videos, slowing going through them and trying to re-learn VB after not really touching it since VB6.
Great walkthrough. But, what is you have more than one table in the database? Using the table's name instead of the index 0 in the example does not work. How to identify the index in the schema to access a particular table?
Hello! :-) In these examples the indexed tables that are being referenced by index are not representative of the tables in the database, rather they are an outputted DataTable object that consists of the results of the query. You could create multiple containers or output multiple queries into separate datatables; However, for this example, there should only ever be one outputted DataTable.
To access other tables in your database, you'd simply do this through your query.
Examples:
SELECT * FROM table1
or
SELECT table1.column1, table1.column2, table2.column1
FROM table1 INNER JOIN table2 ON table1.column1 = table2.column3
WHERE table1.column2 = 'something'
In the second example, we called a query on two joined tables but this still produces only one DataTable [SQL.DBDS.Tables(0)].
I am presently rebooting my SQL Server tutorials with a much improved SQLControl class which omits the use of the DataSet and instead uses a DataTable for simplicity.
I hope this helps! :-)
Great tutorial. I have spent a couple of days looking for something like this to help and all apart from this one have been rubbish.
I have developed it to bring in data from the first combobox into a second. This works on the first attempt but on subsequent attempts it hold the data from the previous. So you end up with a load of duplicated entries. There is a line of the code that clears the first Combobox, but i cant seem to replicate so it clears my second. Any ideas?
Thanks Again Jon
Hello, Jon. :-) You should be able to simply put "ComboBox2.Items.Clear" at the top of the method that you're using to populate the second ComboBox. That way it will purge the ComboxBox each time that you attempt to populate it.
Let me know if you need any examples or further explanation and I'll be happy to help out if I can.
Also, this is a very old tutorial and I have a much improved SQLControl class, as demonstrated in my SQL Server series reboot. Let me know if you'd like a copy of that adapted for SQLCe and I'd be happy to share.
They maybe old put they work well all the same. I am using a MySql database so am not sure if your reboot would be relevant to me.
I have tried putting the "combobox.items.clear()" at the top of method and its doesn't seem to work. Using your example its beneath the "Private Sub getfood(food As String)" method.
Thanks again
I actually have an adapted Control class for use with MySQL, as well. :-) The DBControl for MySQL is only slightly different, but the VB code to use it is virtually identical to the new videos.
Am I understanding correctly, that the first ComboBox is clearing correctly, but the second ComboBox is not? Are you calling it by name when you use .Items.Clear()?
The fist combobox clears under the form loader sub. I them put aother a cbxcontact.itesm.clear() under the cbxcust index change (cbxcust being cbxfood on your version and cbxcontact being txtfoodtype)
I have dumped it in other various parts of the code to see if i get any luck.
Thanks Jon
Thanks ur video. i have a question. If the Food_Name can be duplicate, am i need to change the coding?
It depends upon your needs. If you don't mind pulling duplicate records, it doesn't matter. If you still want to pull a single record you'll just have to pick a unique field to filter by. (Example: WHERE id = '3')
Hi, its me again. What if i want two combo boxes in this for? one for the food type and another for the food name. what i want to do is so select first the food type in the foodtype combo box then all the food names that have the selected food type is seen/fired/filled in the next combo box which is the food name. any help with that? Thanks a lot in advance! your tutorials are really awesome!
Hi there, Anne. :-) Sorry for the slow reply. We can easily create a dynamic query like this based upon the SelectedIndexChange event for the ComboBox. Essentially, you can send the Text property of the selected item of one ComboBox to a new query as the filter.
If you like I can produce a sample project for you.
will you? that would be awesome! can you teach me how? thanks!
anne sandiego
Here is an updated copy of the project for you: www.dropbox.com/s/e3b7yyhsiaorxii/AnneS_ComboFill.zip?dl=0
I hope it helps. :-)
hey, user need mysql to use the program ?
Igor Oliveira You can similarly use MySQL, SQL Server, SQLCe, MS Access, FireBird, and more. :-) In this tutorial we use SQL Ce (Compact Edition).
No find system.data.sqlserverce in my visual studio
Igor Oliveira What version?
Visual studio 2013, but I found the answer in another comment of yours, downloaded from the microsoft Web site
VB Toolbox There's no "local database" in add new item, only dataset and service-based databasewhich to choose?
Igor Oliveira I will have to check this when I get home. I use 2010 here, but I have a copy of 2013 Express at home that I never use. I will have to try and see.
Ok, thanks, caught in the data base, is totally different and I don't know how to add elements to the table, I almost didn't create the table
Please can send me the class code, if you don't mind.
The link is not functional
Sorry, I just moved and my web server is still offline.
This is the latest version of my SQLControl class:
www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
I MANAGED TO
I am so happy.
Now, how to add new "food"? and delete/edit the food type
Can you create tutorial of how to use select dynamic queries with 3 ComboBoxs?
Hello Sir,
i'm just learning vb.net just trying to add some to my knowledge in IT...
i followed your procedure and your code but when i try to run, this error pop out...
"An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll
Additional information: Cannot find table 0."
i just make my database instead of food i change it to Supplier..
any advice on this..appreciate your feedback..thanks
+Rj Barretto Hello, Rj! Generally, this means that there was an error in the SQL query that caused the Dataset not to Fill properly.
My newest version of the SQLControl class has better error sampling and also simplifies the Dataset usage by exchanging it for a Datatable.
Let me know if you'd like me to put together a simple sample project for you to demonstrate this.
+VB Toolbox if you do so, I would be more greatful, sir...thank you..
+Rj Barretto Okay. :-) I made a simple example project for you.
PROJECT SCREENSHOT: www.dropbox.com/s/ukvajfr79n1ae2f/SQLComboFill_Revised.png?dl=0
PROJECT SOURCE CODE: www.dropbox.com/s/iyomno9eta50tjb/SQLComboFill.zip?dl=0
Note: Don't forget you update the SQLControl connection string and queries to match your database.
I hope it helps!
+VB Toolbox thank you sir for making that simple example, i appreciate it much!
didn't know what "vbNewLine" do until i run the application.. :-D
i keep on following your video sir and i hope you'll not get tire or doing it... ;-)
just one more favor sir, if you don't mind...
please do a tutorial video that focuses about "Loops"...im having a hard time understanding it...
thank you sir, God bless.. :-)
I love your professional way in programming .
thank you so much for this Tutorial it's helped me a lot and I learned of you a lot of things like write comments before write code to remember what is that coding do ? use classes .
thank you so much.
I just wonder if there class for management database ( add,update,delete,search) to use it for all projects.
if not I hope you learn us how we can do that ?
I think it will be helpful for us the beginners.
I really want to use that operations with classes.
All Regards .
Murad Alshami Thank you, sir. :-) I'm happy that the tutorials have been helpful! To my knowledge there are no built in classes for complete database management; However, the SQLControl class that I use in these tutorials does this, and can also be easily modified for your specific needs.
Additionally, it's very easy to change it for management of other database types [SQL Server, MS Access, MySQL, etc.]. The only changes that need to be made are in the connection string and namespaces that you use [e.g., SQLCeCommand = OleDbCommand = SQLCommand = MySQLCommand].
Also, the ExecQuery() Sub in this tutorial will successfully run SELECT, INSERT, UPDATE, DELETE, etc.
I hope that helps! Take care. :-)
thank you Sir , You do Great we learn from you a lot .
Hi again Mr VB Toolbox
First, thanks again for the help you gave me on another of your video; Your pointer to the Trusted_Connection=True option worked a treat and I've made some good progress with my VB objectives in the past few days....
I do have one question about on this video; Is there a considerable difference between the data access libraries you use here (.SQLServerCe) and the (.Client) library I've been using up until now. Although it is my medium term objective to build an application for the College where I teach, right now I'm just working on my own machine so I'm not yet in a client/server situation
Thanks again
Chris Read, Leeds, England
Chris Read Hello, Chris. :-) Functionally, the two libraries are virtually identical, but each is built to offer that same functionality to different database types. There are .NET connectors for pretty much every major database out there (SQL, SQLCe, Access, MySQL, Firebird, etc.) each has it's own connection library (.dll) and namespace (SqlClient, SqlServerCe) contained within that library. So, in your case, you can't use SqlClient to connect to SQLServerCe or the reverse, even though they are similar and offer the same methods and functions.
I hope that makes sense.
Mr. VB Toolbox, your project work very good. Now I need to delete the data "food" I enter a new one and I need a button to add new food, Do you have that tutorial, too.? I mean edit, update, save, delete, and add new to same project.
This is an excellent video with the information clearly explained (one of the best programming videos which i have viewed). I have one question: - i am connecting a MySQL database to Visual Studio. At the moment of 33 mins 27 seconds in the video, you wrote the following code: SQL.SQLDS.Tables(0). What is the code to use in reference to a MySQL database (as the IntelliSense do not include SQLDS (SQL Dataset) in the drop-down menu as I was typing in my application)? If I use the code shown below, I get an error message: "Table is not a member of DataTable" at the line: For Each r As DataRow In DB.DBDT.Table(0).Rows
My code is shown below : -
Private Sub GetDATASECOLUMN()
'QUERY TABLENAME
DB.ExecQuery("SELECT COLUMN NAME FROM MYDATABASE.TABLENAME")
'IF RECORDS ARE FOUND, ADD THEM TO THE COMBOBOX
If DB.RecordCount > 0 Then
For Each r As DataRow In DB.DBDT.Table(0).Rows
cboComboBoxName.Items.Add(r("COLUMN NAME"))
Next
'SET THE COMBOBOX TO THE FIRST RECORD
cboComboBoxName.SelectedIndex = 0
ElseIf DB.Exception "" Then
'REPORT ERRORS
MsgBox(DB.Exception)
End If
End Sub
Sir PLEASE HELP ME! I dont have SqlServerCe in my VS 2010 Ultimate? Where can I get it Sir???
You can download 4.0, here: www.microsoft.com/en-us/download/details.aspx?id=17876
Or you can Google "SQLCe 3.5 Download". Each should be freely available from Microsoft. :-)
VB Toolbox THANK YOU VERY MUCH SIR! A THOUSAND THANKS TO YOU! :'(
Sir! Do you have tutorials on how to dynamic query two combo box?
Kiri YuSuna
I may have a project that could help, somewhere. :-) Can you give me a short example of what you need?
VB Toolbox Ok! First, I have to 2 combo box. The 1st one labels "Category" the other one "Sub-Category". In Category, I have Organic and Liquid( I dont know if I'm correct :-) ). When I choose Organic, the Sub-Category shows the "Vegetable" and "Fruits". From your tutorial, you used dynamic query between combo box and textbox. This is my example sir. (Note: Sir I already install the SQL Compact 4.0 but when I use the SqlServerCe in my VS 2010 Ultimate in didnt show the import :-) )
I have been enjoying your tutorials very much. I use VB2010 and SQL Server Express2014. I modified your code to use SQL Server 2014. I like what you have shown using Compact Edition. I downloaded SQLCe but cannot VB2010 to recognize it. Can you help me with this?
I am sorry about the name. It came because Google would not let sign back in as my old login so I created a new one. I am very serious about using Visual Basic and would very much take advantage of the easier DBMS SQL Server Ce you showed. As I mentioned above, I was successful in modifying your code to access SQL Server 2014 but the compact version looks much easier.
Thanks
Thank you! Amazing tutorials! it helped me with my thesis. :)
is it okay with MS SQL SERVER 2008 EXPRESS. i think there are some code differences if you are using SQL SERVER CE or 2008 Express. i just assumed that.
Thankfully, ADO.NET is pretty standardized so the differences are surprisingly minimal, whether it's SQLce, SQL Server, MS Access, and even MySQL. Really the only differences that I've seen are the calls to the namespaces and the connection strings. :-)
Thanks a lot! Amazing tutorials! :)
Thank you so much for your concise tutorials. I have watched others but yours are the best by far. I am a newbee to vb 2010. I programmed a little vb about 15 years ago but its all different now. I am attempting to write a program to allow me to store URLs, username and passwords into a database. On form load I am displaying the URLs in a list box. When selected
I open the browser and log in with name and password. I had the program about done when I decided the database should be encrypted. That opened up a whole kettle of worms. My question is (How does the user enter and view their records). Do I
load arrays and is there an easy way to encrypt/decrypt in VB. Have you written a book? I would like to be able to get your code samples. Thanks Tom M...check ur tip jar
Wow, Tom! I'm hugely appreciative of the contribution. Thank you so much!
Regarding encryption, that's always a very tricky devil and there are so many different ways to handle it. My understanding is that, more important than the encryption type used, are the techniques by which they are employed. Unfortunately, I've only scratched the surface when it comes to encryption. I've played around a bit with salted SHA512 and the encryption/decryption process is not excessively difficult, but I think the problem comes to actually storing the encrypted values - especially in a world with increasingly powerful CPUs. If there is access to the data, it can be hammered open. For this reason, I think that passwords should never be stored to the database or anywhere else, rather a password hash can be stored and challenged by the client. Now, I think that later SQL server versions actually offer embedded encryption, but I really have no idea how secure that really is.
Unfortunately, I haven't written a book. I think that I'm much too disorganized. ;-P Let me know which code samples you'd like to have me dig up and I'll be happy to share. :-)
Thanks again!
So I am running into a small snag. I am using your CB Query to populate an employee table in a CB. Got that no problem.
I also am trying to integrate from your SQL Delete Command Video
I am then trying to create a button that will delete selected user from the CB, and I am getting an error. Wondering if you can help:
Try
If cbSOEIDLATAM.Text "" Then
If MsgBox("Do you really wish to delete " & cbSOEIDLATAM.Text & "?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
SQL.DataUpdate("DELETE FROM tbl_EmployeesLATAM WHERE Full_Name='" & cbSOEIDLATAM.Text & "; ")
End If
End If
Catch ex As Exception
MsgBox("Employee does not Exist!")
End Try
Error I receive: Unclosed quotation mark after the character string 'Test User; '.
Incorrect syntax near 'Test User,'.
+Alcohol Arcade Hey there, Alcohol Arcade!
First off, it seems you left off a single quote in & "; ") after the cbSOEIDLATAM.Text in your DELETE expression.
That said, I *strongly* recommend employing SQL parameters as they will help tidy up the command string, prevent these types of issues, and help secure against SQL injections when using TextBoxes that supply user input. All of my later videos employ them and they're easy to use and have great benefits.
Just out of curiosity, which version of my SQLControl class are you using, or which video did you grab it from? Since my older videos, I've made a number of improvements that may help simplify usage and improve stability. :-)
hi i try to learn vb.net to create a app. i found your video, and i try to write te code but i receibe the msg error on: " Params.ForEach (Sub(x) SQLcmd.Parameters.Add(x))"
expression expected "Sub"
how i correct this error?
i am using vb2008 to program a Motorola MC2810 barcode scanner
thanks for your help
tecni210 Hi there! Sorry for the delayed response. Unfortunately, VB2008 doesn't handle Lambda expressions quite as elegantly as later versions. The easy fix is to convert the Lambdas into a basic For Each loops.
Example:
For Each p As SqlCeParameter In SQLCmd.Parameters
SQLCmd.Parameters.Add(p)
Next
The code works awesome bro I subs and liked, but I have a problem here. I hope you can help me.
I have 2 tables. Products table has Customer and Products as column inside it and a Customer table has Customer column inside it. I have 2 comboboxes, 1 is for Customer and 1 is for Products. I wanna do is when I click a customer, only the products with the customer name will appear, how will I query that? Thanks if you can help.
+Foxseiz Hello, Foxseiz. :-) This can be achieved by having a Sub to query and Fill each ComboBox.
Example: [Note: I'm using the latest SQLControl Class]
Step 1: Fill the Customers ComboBox [ComboBox1]
Private Sub GetCustomers()
' REFRESH COMBOBOX
ComboBox1.Items.Clear()
' RUN QUERY
SQL.ExecQuery("SELECT customer FROM Customers;")
' REPORT & ABORT ON ERRORS
If SQL.HasException(True) Then Exit Sub
' FILL CUSTOMER COMBOBOX
For Each r As DataRow In SQL.DBDT.Rows
ComboBox1.Items.Add(r("customer").ToString)
Next
' SELECT FIRST CUSTOMER
If ComboBox1.Items.Count > 0 Then ComboBox1.SelectedIndex = 0
End Sub
Step 2: Fill the Products ComboBox [ComboBox2] when a Customer is selected
Private Sub GetProducts()
' REFRESH COMBOBOX
ComboBox2.Items.Clear()
' ABORT IF NO CUSTOMER SELECTED
If String.IsNullOrWhiteSpace(ComboBox1.Text) Then Exit Sub
' ADD SQL PARAMETER TO FILTER PRODUCTS BY CUSTOMER
SQL.AddParam("@cust", ComboBox1.Text)
' RUN QUERY
SQL.ExecQuery("SELECT product FROM Products WHERE customer=@cust;")
' REPORT & ABORT ON ERRORS
If SQL.HasException(True) Then Exit Sub
' FILL PRODUCTS COMBOBOX
For Each r As DataRow In SQL.DBDT.Rows
ComboBox2.Items.Add(r("product").ToString)
Next
' SELECT FIRST PRODUCT
If ComboBox2.Items.Count > 0 Then ComboBox2.SelectedIndex = 0
End Sub
Finally, add the first Sub GetCustomers() to the Form1_Shown event, and add GetProducts() to the ComboBox1_SelectedIndexChanged event so it will re-query for each customer selection.
I hope this helps! :-) If you don't have the latest SQLControl Class, you may find it here: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
+VB Toolbox Awesome bro, it's working fine, thank you for this!
Hey there Mr. A.. it's me again. Nobody explains like you do so I am here to learn just a little more about Combo boxes and updating across multiple forms. As you know, I have a Multiform project with many tables that support an inventory form. I have forms for data entry to the supporting tables like vendor, model, manufacturer. The vendor form for for example, is working properly and the datagrid is updating whenever I add a new vendor.
The problem I have is that when I return to the main form and go to select the new vendor I have just created, it is not in the combo box dropdown yet. The only way to get the vendor combobox on the main to update is to close the app and start again.
I have searched all over the vb forums and can't find a solution that actually works. I found many people trying to solve this issue, and lots of "try refresh, use requery" 'reload the adapter" but nothing that actually works.
It is such a common task, I know someone knows how to do this so I am hoping it's you.
Logically I think the update should happen either on close of the vendor form or on the combobox selectedindexchanged event, but just can't get it. Your help is greatly appreciated and since this is such a common issue I would think it is a great teaching subject.
What I've done to handle this sort of thing is have a Public Sub on the parent form, which I call from the child form.
To do this, I added an Object variable to the child form and send the Parent form into that object. Then the child form has access to the Parent's controls.
In Form1 (Parent):
Public Sub UpdateMyControls()
' Do your refresh
End Sub
Public Sub CreateTheChildForm()
Dim ChildForm As New Form2 ' Use your form type
ChildForm.frmParent = Me
ChildForm.Show()
End Sub
In Form2 (Child):
Public frmParent As Object
Private Sub SomeActionThatUpdatesYourBox()
' Your code here....
' Call Sub on Parent Form
frmParent.UpdateMyControls()
End Sub
We essentially send a handle of our parent form to our child form so that it can access its controls.
Note: When you use an Object variable, Intellisense won't give hints about the Objects available features so you have to type "UpdateMyControls()" exactly as it is written in Form1.
Not completely sure I understand how this works. I am getting an error when the code executes for this line:
frmMain.UpdateMyControls()
NullReferenceException was unhandled
Object variable or With block variable not set
There is also a recommendation in the error to use New, but New is already in the childform DIM statement, so I am not sure if that is relevant.
Here's my code for the parent form (frmMain) and form2 (frmUsers)
In frmMain (Parent):
Public Sub UpdateMyControls()
' Do your refresh
Me.UsersTableAdapter.Fill(AssetDBDataSet.Users)
End Sub
Public Sub CreateTheChildForm()
Dim ChildForm As New frmUsers ' Use your form type
ChildForm.frmMain = Me
ChildForm.Show()
End Sub
In frmUsers(Child):
Public frmMain As Object
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Try
Me.Validate()
Me.UsersBindingSource.EndEdit()
Me.UsersTableAdapter.Update(Me.AssetDBDataSet.Users)
MsgBox("Update successful")
Catch ex As Exception
MsgBox("Update failed")
End Try
frmMain.UpdateMyControls()
Me.Close()
End Sub
(I know that I can change the name of the childform etc., I just wanted to get this to run with as few changes as possible first.)
Thanks. This tutorial enabled me to place much more easily readable SQL statements within my code.
Does anyone know what would you do if you wanted to use LIKE and a wildcard in your statement by the way? I've been experimenting with adding more than one parameter but if no value is given for that parameter then it should return all of the results for that parameter. I'm not having much success unfortunately.
As an example say you have 3 comboboxes populated with the items below from a database, when clicking on a button a listbox should be populated with the selections made in the comboboxes forming the search criteria:
Item ItemType Colour
Apple Fruit Red
Carrot Vegetable Orange
Apricot Fruit Orange
Cherry Fruit Red
Say you might want to run a search for all the fruits in the list but no value is specified for colour, later you run a search on colour but leave the ItemType empty. Would it even be possible to create a single "catch all" SELECT statement that allows the various combinations of parameters to either contain a value or return everything when no value is specified?
I can't seem to find a way to get the parameter to contain a wildcard and still end up with valid SQL when the query is executed.
Hello, Llewellyn. :-) I have done this very thing in a few of my other tutorials. Unfortunately, my SQL tutorials are a bit stale and are in bad need of a refresh.
In a nutshell, this is all you need:
' ADD PARAMS & RUN QUERY
SQL.AddParam("@users", "%" & txtFilter.Text & "%")
SQL.ExecQuery("SELECT username FROM members WHERE username LIKE @users;")
If no value is supplied, the WHERE will look like "username LIKE %%", which is a full wildcard search and will return all records unfiltered. If I supplied a value in my TextBox [e.g., "A"], my WHERE will look like "username LIKE %A%" and will be my catch all for any items containing that letter.
At present, I'm using this SQLControl class:
www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Awesome, that works perfectly! I've been working through the tutorials in my spare time and I'm learning a great deal from them. Thank you for the effort you're putting into the tutorials as well as for taking the time to reply to my comment!
thanks
@VB Toolbox
thank u for this amazing video
i have a problem to display dynamic value from mysql database to a label
i have a counter nd i save the value of this counter in my database
and i want to display the last value in a textbox in my vb application
but i fail every time
can u help me plz ?
+Ahmed Jbeli Is the counter column an auto-incrementing identity field, or is it just a number value that you store?
If it's just a numeric value that you store use "SELECT MAX(yourcountercolumn) FROM yourtable". This will return the highest value of the counter column.
it's an increment one
well it's an electronic counter of the energy consumption
i think i find a solution
i made an au refresh every second
and it works now :)
thank u for replying
and if u have another proposition tell me :)
thank u again :)
+Ahmed Jbeli I'm glad that you found a solution. :-)
Select Max(columnName) should still work to grab the highest number.
If you want to immediately capture the last added ID after an INSERT expression, you should be able to use SELECT SCOPE_IDENTITY().
I am brand new to vb.net but am advanced/expert level in VBA. I feel like this could be accomplished so much easier in Excel, I am tryin to learn .net to be ready for job interviews. any tips for me?
Hey there, James. :-) That's a difficult question to answer... I guess my advice is pretty generic, but I would first make certain that you acquire a broad understanding of basics of VB.NET usage and syntax. Since you're already good with VBA, this should come naturally as they're similar in structure.
Play around with the common tools and objects of form design and learn how they can communicate and interact with one another. Learn to extend the capabilities of your applications by importing various namespaces.
Beyond that, as you well know, programming is very nebulous so you'd do well to focus on the finer points of the specific *types* of programming jobs that you intend to apply for. For example, if you want to develop database apps, there's not much point in burning a great deal of time on working with GDI or sockets apps. It's nice to have a vast knowledge of your preferred programming language, but it's easy to get bogged down in learning about things that will never apply to your desired field.
Thanks for this video. It's exactly what I've been looking for. I'm getting this error when running this portion:
Private Sub DatabaseCon()
'Query Database
DatabaseConnection.ExecuteQuery("SELECT Program_Name FROM Program_list")
MsgBox(DatabaseConnection.Exception)
"Could not find installable ISAM"
Everything else seems to be ok, but my combo box won't populate. I'm connecting to an Access database local to my PC, but did not copy it into my project. Can you help?
Thanks!
Jon
Hello, Jon. :-) I'm unfamiliar with that error, but a quick Google search turned up this Microsoft article (support.microsoft.com/kb/209805). I'm not sure if that applies to your version of Access, but it might be worth looking into.
Hey thanks, I did see that article. Looks like modification of the registry may be part of the solution, which will be a challenge for me.
Thanks again!!!
If work doesn't kill me today, I'll try out an Access connection and see if I can get it to populate properly. :-)
Haha don't let that happen. Awesome, thanks!
jonathan kervin lol I'll try not to let them kill me. ;-) Definitely be careful tweaking the registry if you're unfamiliar with it. While it's not usually too daunting if you have straight-forward instructions, tweaking the wrong thing can be very bad.
thanks for all your videos, they have helped me a lot. I was trying to copy what you did only use my own database, and I think that's where I messed something up. anyways, what I would like to do in the long run, is to fill the combobox with all available tables, then basted on that selection, fill the next box (combobox) with all available columns. Can ya help me out?
Indeed I can. :-) I have just such a project, but I'll need to figure out where I put it. lol What you'll be doing is SELECTing from the INFORMATION_SCHEMA to return the tables and columns. I'll see if I can find that example for you.
VB Toolbox it would be greatly appreciated. THANKS!
Brice Drilling I couldn't find my original source, so I'm just going to modify the code from this tutorial to produce the desired effect and put a copy on DropBox. Should just be a couple minutes.
Brice Drilling Here you go. :-)
www.dropbox.com/s/c945g2byjgvhd9s/BRICE_ComboFill%20-%20Tables.zip
i did all thing like u said and i got the result when t tried with msgbox RecordCount
but after when i typed this code i got an error:
Private Sub GetMatricule()
ExcutQuery("SELECT Matricule FROM Operatrices")
'If records are found, put them in the combo
If RecordCount > 0 Then
For Each rtb As DataRow In ds.Tables(0).Rows
MatriculeComboBox.Items.Add(rtb(" Matricule "))
Next
'set combo in the first record
MatriculeComboBox.SelectedIndex = 0
ElseIf Exeption "" Then
MsgBox(Exeption)
End If
i didn't use a classe but i wrote the code directly into the form code
igot this error msg
NB Matricule Column is a primary key in Operatrices table and FK in FichChr table
----------------------------------------------------------------------------------------------------------------------
La colonne ' Matricule ' n'appartient pas à la table Table
----------------------------------------------------------------------------------------------------------------------
L'exception System.ArgumentException n'a pas été gérée
HResult=-2147024809
Message=La colonne ' Matricule ' n'appartient pas à la table Table.
Source=System.Data
StackTrace:
à System.Data.DataRow.GetDataColumn(String columnName)
à System.Data.DataRow.get_Item(String columnName)
à Hemodia_Project.Fichechron.GetMatricule() dans C:\Users\USER\Documents\Projets\Hemodia Project\Hemodia Project\Fichechron.vb:ligne 72
à Hemodia_Project.Fichechron.Fichechron_Load_1(Object sender, EventArgs e) dans C:\Users\USER\Documents\Projets\Hemodia Project\Hemodia Project\Fichechron.vb:ligne 87
à System.Windows.Forms.Form.OnLoad(EventArgs e)
à DevComponents.DotNetBar.Office2007RibbonForm.OnLoad(EventArgs e)
à System.Windows.Forms.Form.OnCreateControl()
à System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
à System.Windows.Forms.Control.CreateControl()
à System.Windows.Forms.Control.WmShowWindow(Message& m)
à System.Windows.Forms.Control.WndProc(Message& m)
à System.Windows.Forms.Form.WmShowWindow(Message& m)
à DevComponents.DotNetBar.Office2007RibbonForm.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
InnerException:
Try removing the spaces from " Matricule " in this line:
MatriculeComboBox.Items.Add(rtb(" Matricule "))
+VB Toolbox it stills the same error
It's a bit difficult for me to read the French, however, it looks like it is looking for Matricule in "Table" instead of "Operatrices". Your SQL looks accurate, so there must be something else we are overlooking. Is the new error exactly the same? Do you have any other queries executing?
+VB Toolbox the table called Operatrices and the Colmn which i wanna to get it in the Combo called Matricule
The only error I can see in the code above is " Matricule ". It should be "Matricule". Are you certain that the error is coming from GetMatricule()?
qui peut m'aider avec l'instruction INSERT enregistrer l'objet DateTimePicker dans une table access? IN English: who can help me with the INSERT statement save the DateTimePicker object in an access table?
Hello
Thanks for great telling for this video. I have a problem . I am very new at coding and I try to apply this thing ComboBox to ComboBox but I couldn't get first ComboBox ID value(in your case fruit ComboBox I need to show Fruit name on ComboBox but I need ID for other ComboBox ) for apply it second ComboBox query filter. How can I show it as parameter to second ComboBox?. I added my code. Thanks again for your effort.
www.dropbox.com/s/zl2nioryp4abggq/ComboBox%20From%20Dynamic%20Query.txt?dl=0
My guess is that cboCategory.SelectedValue is coming back without a value, so it's failing to filter. I used cbxCategory.Text instead of SelectedValue and it worked in my test environment.
I added a couple of tables to my database [Category & SubCategory] with columns [Category.CategoryName, SubCategory.ParentCategory, SubCategory.SubCategoryName].
SubCategory.ParentCategory contains the name from Category.CategoryName for the ComboBox filter.
This is my code [using the updated SQLControl] that works:
Private Sub GetCategory()
' CLEAR CATEGORIES
cbxCategory.Items.Clear()
' QUERY CATEGORIES
SQL.ExecQuery("SELECT CategoryName FROM Category;")
' REPORT & ABORT ON ERRORS
If SQL.HasException(True) Then Exit Sub
For Each r As DataRow In SQL.DBDT.Rows
cbxCategory.Items.Add(r("CategoryName"))
Next
' SELECT FIRST ITEM
If cbxCategory.Items.Count > 0 Then cbxCategory.SelectedIndex = 0
End Sub
Private Sub GetSubCategory()
' CLEAR SUB-CATEGORIES
cbxSubCategory.Items.Clear()
' ABORT IF NO ITEMS SELECTED ON PARENT
If String.IsNullOrEmpty(cbxCategory.Text) Then Exit Sub
' QUERY SUB-CATEGORIES
SQL.AddParam("@CategoryID", cbxCategory.Text)
SQL.ExecQuery("SELECT SubCategoryName FROM SubCategory WHERE ParentCategory=@CategoryID;")
' REPORT & ABORT ON ERRORS
If SQL.HasException(True) Then Exit Sub
For Each r As DataRow In SQL.DBDT.Rows
cbxSubCategory.Items.Add(r("SubCategoryName"))
Next
' SELECT FIRST ITEM
If cbxSubCategory.Items.Count > 0 Then cbxSubCategory.SelectedIndex = 0
End Sub
Private Sub cbxCategory_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbxCategory.SelectedIndexChanged
GetSubCategory()
End Sub
I hope that helps! :-)