I have not programmed since Fortran in College. Object oriented programming has always been a hurdle. I have been using Excel to manage data for one of my hobbies and went down the Excel rabbit hole. I have now decided to write a VB program to manage the data. Among all of the sources I have relied upon to introduce various areas of the program your tutorials are by far superior to anything else I have found. Thank you!
This was amazing. I'm using MySQL and I was able to adapt easily. One could Google literally for weeks before coming up with this volume of information. Clearly stated, concise and complete. Thank you so much!!!
Awesome! I'm very glad it was helpful. 🙂 I think I actually had an old MySQL tutorial somewhere and a control class adaptation for it, though it sounds like you've already discovered how easy it is to switch between them all - MySQL, SQL, Access, Firebird, etc... Fun stuff! I miss working with this.
I first watched this and your other excellent videos a few years ago, in the context of MS-Access database, similar kinds of ideas. But now, I need to migrate that Access database to SQL. I've been spending the better part of the last few weeks getting up to speed by myself on using SQL and migrating my Access database to SQL and I'm proud to say that I now understand the business of creating tables with primary key and identity set at the get go, otherwise, SQL Server will not allow to change the field (column) once it's created. Thank you so much for having taken the time to do this and your other excellent videos.
I am trying to incorporate this code into a project I am working with and trying to figure out what the best way to return the ID of a new record when running an INSERT query
Hello, Ian! I *think* I had incorporated this feature in the SQLControl class in this project (dropbox link below). You should see it as an optional sub-query with @@IDENTITY, which must be selected/executed in the same session as your INSERT. For this reason, I embedded the option in the ExecQuery sub. I hope that helps, or at least gives you an idea of what to use. 🙂 Dropbox Link: www.dropbox.com/s/1o74yhqkuzq6r9r/SQLTutorial%20PT4.zip?dl=0
At 29:21 you write "New SqlServerCe.SqlCeCommandBuilder(SQL.SQLDA).GetUpdateCommand", I am using a different database where I am writing my SQL adapters, connections, etc. as "SqlDataAdapter" and the like. When I try to simply write "New SqlServer.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand" I don't get anything. It appears that I perhaps don't have the correct pathing for my version of VB? Any help would be appreciated.
Hi, It is a great series of video that leads beginners to write their code in a more professional way. I used to learn VB 6.0 on my own and now I would like to shift my IDE to Visual Studio, so I could keep my programming skills updated as the trend move forward. It will be very appreciated if you can make a series of video demonstrating how to make data export and Print a report in VS. I have not idea how to use those Print controls so far. Daniel from China
Another Question I used this Lesson to make a form with 2 datagridviews tied to 2 different tables where my search text boxes search both datagrids. I have a loadgrid and a loadgrid2 sub each sub has "SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand" But I can only save changes in 1 datagrid the second datagrid reverts back after I hit save. Do I need 2 unique DBDA or DBDS?
To keep them independent of each other, you'll probably want to instantiate two instances of the SQLControl class, since each one has its own DataTable(updated version)/DataSet (old version) and DataAdapter. Once a query is executed, these objects are overwritten with the last query that ran. That means that the UpdateCommand associated with the initial DataAdapter is also destroyed. Example: Private SQL1 As New SQLControl Private SQL2 As New SQLControl Doing that, you may use them independently of each other.
Great Video! It rolls so much info into a single training. Do you have a video on how we can accomplish The updates if using multiple tables? That is what I am trying to do
If I recall correctly, this was a limitation of DGV updates, in that the updates only work for one table. It may be possible to build some kind of workaround that dumps the desired column data into separate table-connected DGVs and kicks off an update chain from there. Could be a fun experiment, but I have no idea if that work or how efficient it would be.
+Guillermo Enrique Cedeño Ojeda If you have no reference, you may not be able to read the necessary library Namespace @ 9:23 and your control class will not be able to talk to the database. SQLServer does not require a reference, but SQLServerCe generally does.
Oh i see, thanks. I ask becouse im working wit sql server express edition and i dont use the sql server compact so im not sure if this video will work wit that
+Guillermo Enrique Cedeño Ojeda Oooh! Okay. You can still use this video but you don't need to add the reference at 3:55 . Instead, simply use "Imports System.Data.SqlClient" at 9:23 . Then on the objects [SqlCeConnection, SqlCeDataAdapter, SqlCeCommand] just remove "Ce" from all of them and it should work. :-)
Hello, im doing this wit the Sql server express edition, everything is runing fine by just not ading the "Ce" into the commands, but i dont know what to write in the 28:51 becouse there is no SQLServer command :/
+Guillermo Enrique Cedeño Ojeda I just found it by doing some research: SQL.SQLDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand . Btw Did you made a video validating the entrances in a datagridview?
If I may ask, how could I insert the data in this DataGridView to a separate or new table to review changes made to the original table? For example, if someone, using the application, made changes to your Food_Type "fruit" under ID "4". This way, you can tell if the end-user made a change to your database. I hope this makes sense & thanks for your help as always!
Hi i am unable to excute the same on sql server "SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand" to update the datagridview & database
Hello, You've done a fantastic job at creating these VB.NET videos, I've learned so much so fast. Thank You I'm finally moving from the VB6 world to the VB.NET world, yes I know I'm a little behind but in a production world it takes a long time to move from one to the other. My question is I would like to know how to use the DataGridView to add records to a SQL Server database table, also show the DataGridView control with default column headers and set columns to a specific default width. I hope this makes sense. Thanks
I very much appreciate the encouraging words, Pete. :-) I know that I'm using SQL Ce in this tutorial; However, updating a SQL server database is mostly identical as the ADO.NET library for SQL should contain the same methods and functions that we used in this tutorial. The main difference is that you'll use the *SqlClient* namespace, rather than *SqlServerCe* and your connection string will be slightly different. (I'll be happy to assist with that if you need). I know that you can specify the datagridview column names and width, but I'm not sure if setting a datasource will override those values. I'll have to test that and let you know.
If you want to alter your column sizes, you'll want to set the *AutoSizeColumnsMode* property to None, either in the Properties dialog or programmatically _[dgvTest.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None]_ You can then specify a custom Column Width by Index or Column name. Example: _DataGridView.Columns("ColumnName").Width = 150_
I'm using MS Visual Studio 2015. No natter how many times I try to disable the save button on initial load of datagrid, it still shows as active upon startup
@@finleybutler8780 I tried that initially and for some reason it still is active upon entry to window. It's an app for my own use so I'm not that concerned. Will continue to investigate. Thanks for the reply
I'm getting the following error: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. And it refers to this line of code. sql.sqlda.UpdateCommand = New SqlClient.SqlCommandBuilder(sql.sqlda).GetUpdateCommand I'm using SQL Sever so I modified the part where you use the compact edition. Can you tell what I'm doing wrong?
I am building a program to update a database with data from an imported excel sheet but i and i like your method to save the database updates. I tried to incorporates your example to my project but I am not able to copy the rows from the datagrid view with the excel spreadsheet to the grid that has your example. they are located within the same form. Any help?
Nice tutorial. Thumbs up. Learnt several lessons here. Though I still can't understand some few things here because I am an advanced newbie and do not use custom private class, however I will understand later with much practice.
Update: I finally got what I wanted. All I needed to add to my codes were the 'dataadapter.updatecommand=......' and 'dataadapter.update (ds.tables(0))'. I previously knew how to populate a datagrid using dataset, hence easier for me to flow with the tutorial. I will learn to create and implement custom class in my next projects.
Hi there, I'm new with database and must say that your tutorial is awesome. I have a question for you please - if I have 2 tables in a dataset, do I have to create another SQLControl object?
Charles Lim The only reasons that I'd create more than one SQLControl object is if I wanted to preserve the contents of a dataset (protect it from further queries) or if I was connecting to multiple databases. That said, I'm curious about how/why you're generating multiple data tables in the dataset.
I'm working on a Windows app that maintain a table of community events and where new events will added from time to time, a table of registered members of the community whose members will continue to increase and a third table to capture participants (who are members) of each event. Does it make sense to use multiple tables or is there a better approach? Appreciate any advice.Thanks.
Glad you're still here Aardaerimus! I followed your XNA VB.NET tutorials and I loved it! I also noticed that you stated those tutorials were outdated, which is a shame! Will you ever make new XNA tutorials?
Hello, Brandon! I really, really want to say that I plan to continue the GP tutorials, but I'm in a tight position there. The XNA game engine that my friend and I were working on has been dramatically overhauled (much better), but I'm not sure that I want to make a tutorial on the entire process and I'm not exactly sure how to continue. If I just tailor XNA tutorials our specific homemade game library the viewership would be sorely limited and VB game design is already an incredibly tight niche. On the other hand, documenting the entire process again - like I did in Adventures in XNA - is very tedious, and again the niche is small so there aren't a lot of viewers to make it worthwhile. I very much enjoy sharing what I learn and probably would anyway, but I haven't had quite the push on the game programming front that I originally had. I do miss it though, and it was very rewarding. It's been a long time since I've played with GP and I'd love to get back into it given that I could muster the time and energy to do so. I really need to get a forum going where viewers could interact and share ideas and solve problems independent of tutorial releases. As much as I love helping, it can be exhausting spending so much of my time trying to interact with all of my viewers and assist every individual with design issues. Anyway, here's hoping. :-) If you want it, I'd be happy to share the updated game engine project with you. Kalamus did some major redesigning and essentially rebuilt the entire boilerplate into a .DLL in C#, which I then translated into VB.NET. All the end developer really needs to do is write the screens, menus, and file handlers.
VB Toolbox I understand completely. I thought you had vanish completely and here you are, on another UA-cam channel (which I found from your website). Thank you for your offer on the updated game engine, but I won't be needing it. I rather code my own without any influence of another project.
Brandon D I'm the same way. I hate borrowing code because I want to understand how it all works, and the best way to do that is to use your own. Just know that the source is freely available, should you reconsider. Never know, you might get some ideas. :-) This channel started as sort of a low commitment test. I had a ton of requests by people wanting to learn the basics of VB and didn't feel that it really fit into my GP channel routine. I was really strapped for time so I was going to just post short "quick tips" type videos and see if there was any interest. Additionally, I had released a couple database tutorials on my main channel a long time back and they had a lot of interest so I thought, "Maybe I'll toss a couple more up on the new channel." I was really surprised at how rapidly it gained interest so I kinda got sucked in. More bad news for my poor, neglected main channel... :-P
VB Toolbox Yeah, not a bad idea! Your main channel will never be forgotten, I assure you that. With loads of helpful videos, how could anyone forget? With a simple search of "XNA 4.0 tutorials", your videos were listed on the first page. I mean, that's how I found your channel when I started GP. I noticed it was in VB.NET, and since I was a C# programmer, I just simply converted your code to C#, which wasn't hard at all. If your main channel was not present to this day, I don't I would've gotten into GP at all. So thanks!
Brandon D Awesome! :-D So, now I have to know - what kind of projects are you working on?? I've seen only a handful of projects in the works since I started that channel.
Great tutorial - though I'm having a problem when running the update query. I've figured out that the issue is because I'm formatting the data in the select query e.g. "Select Format([Variable],'£ #,###.##') as [Variable] from Table" If I just run it as "Select [Variable] from Table" update works fine, however Iwould like the data to show as formatted in the table when editing for neatness. Is there a way to do this without impacting the update functionality?
Hi VB Toolbox! I need to filtrate the data of my datagridview, and this video is what all I need. But I have some problems, first, my database is created with phpmyadmin, and the second thing is that I haven't got the System.Data.SqlServerCe reference on my visual studio 2013. To complete this tutorial with my database, what things must I change? Thanks!
Regarding the database created with phpmyadmin, it shouldn't matter what tools are used to create your database. As long as you have sufficient permissions and credentials to access your database you should be fine. Where SQLCe is concerned - unfortunately, I don't have a copy of VB 2013 to play with to check what version (if any) of SQLCe is included in that version of Visual Studio. That said, SQLCe can be downloaded for free from Microsoft. Just make sure to check the system requirements and download the SDK as well so that you can easily include it in your projects. The latest version I've used with 2010 is SQLCe 4.0. Once installed you can reference the provided dlls in your project, although the process may vary slightly for your version of VB.
VB Toolbox Finally I'm not using System.Data.SqlServerCe, I'm using System.Data.SqlClient. The code hasn't got errors, but when I debug the project, the exception message box says me this: "Error associated with specific network or instance while a connection to the SQL Server is established. Server not found or it was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named piples Provider, Error 40 - Could not open a connection to SQL Server)" When you write in the class "Data Source=MyDB.sdf;" I have to put here my connection string, I say it well?
The connection strings are different for different types of databases. It may help you to Google ADO.NET connections strings. There are numerous pages that will assist with determining the proper connection string for your database type. You also need to use the appropriate ADO.NET connector for your database.
VB Toolbox I do the connection to the test server and the database by adding server connection and data source on the server explorer bar, I don't make it with code. The connection string that I put is the string that the server connection gives to me. I don't understand what's the error that I have with the server connection. I haven't got installed Microsoft SQL Server, I have my database with apache-mysql-phpmyadmin. Can you do another tutorial using comboboxes and textboxes for filtrate the data of the datagridview? Thanks for all!
Can this be done with a datatable instead of a data set. I'm trying to do this with Microsoft Access. I followed your other tutorial and was able to get it show to the datagridview. I took it a step further and was able to pull information from different cells within the grid. But I cannot get the update grid procedure to work properly. I can upload my project onto dropbox if you need to look at the code to assist. Very much appreciate for any help that I receive. I have always been a fan of your work!
Ok, so first off, don't laugh. But I was able to get the update function to work with some rigging..... < I laugh at that statement..... xD Anyways. This is the code that I used to accomplish this: Private Sub dgvData_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEndEdit currentname = dgvData.CurrentCell.Value.ToString If currentname = "" Then currentname = " " End If Access.ExecQuery("UPDATE Members " & vbCrLf & _ "SET " & dgvData.Columns(index1).Name & "='" & currentname & "' " & vbCrLf & _ "WHERE ID =" & dgvData.Rows(index2).Cells(0).Value) Access.ExecQuery("Select * from members") If Access.NotEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub dgvData.DataSource = Access.DBDT End Sub Private Sub dgvData_CellEnter(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEnter ' currentname = dgvData.CurrentCell.Value.ToString index1 = dgvData.CurrentCell.ColumnIndex index2 = dgvData.CurrentCell.RowIndex End Sub I collect the new name, and use a sort of dynamic update function to go through and update the database. This does not work for adding information.... only changing. So I am hoping to hear from you to see if there is a simpler way of doing this. In the meantime, this code does throw an error or two if the program does not have time to go through and finish the commands, I am still working on that bit as well.
Hello, First of all thanks for the tutorials, I wondered if whether is possible to list all the info in listbox or datagrid, and if I press a line for example 7, a new form will be opened with all the info of the column, and could be updated there instead from the data grid, can you give me a clue on how make such thing !! ?? thank you for kind help !
Both selections are fairly simple to accomplish. I believe I have some samples somewhere, but I will have to dig them up. If you include a primary key or unique column in your query to fill the listbox or DataGridView, you can then use the object's SelectedIndexChanged event to re-query for the item details [send to the new form and use as a parameter].
I can try, if you will have time to make a tutorial that would be amazing, I think there are alot of people who find it useful !! cause I really struggle in this thing.. i mean when trying to think this logicly i find something is missing in my mind.. like how can you read data on a list, and then the one returned is "divided" like so hard for me ahaha .. thanks for the answer you really help us !
Hi, I love your tutorials, they give a real solid basis the my VB project and have referred to them countless times. I am an bit stuck on this section however, I can not seem to get any data to load into the DataGridView. It will load the column headers but no row data. I have tested the statement in MySQL work bench (I'm using a MySQL server) and it works as expected. Any ideas on where to start looking? Thanks Jon
Hello, Jon. First off, I have an improved version of the SQLControl specifically for MySQL. Class Text: www.dropbox.com/s/gl1o55c5hsxounn/MySQLDB.txt?dl=0 With this class the query to grid should be as simple as in the latest SQL Server tutorials. Example: [In your Form class] Private MySQL As New DBControl Public Sub LoadGrid() SQL.ExecQuery("SELECT * FROM Products;") ' REPORT & ABORT ON ERRORS If SQL.HasException(True) Then Exit Sub DataGridView1.DataSource = MySQL.DBDT End Sub If errors are found in your query it should report them and exit without filling the grid, otherwise the data will be return to the grid. I hope this helps! :-)
That's great, it has bought up and exception message that was not being bought up before. Can you confirm on the code above that SQL should be MySQL? Thanks Jon
Oops! Yep. I forgot to change those. Sorry. They should match the name of your object variable [Private *MySQL* As New DBControl]. MySQL.ExecQuery() If MySQL.HasException...
If I had multiple datagridviews on a form with their separate save button. Would I still be using SQLSQLDA.Update(SQLDS) in each button to update each table? I'm thinking it wouldn't work this way, because I'm having trouble.
Problem is that each time a new query or other command is run, the Dataset is flushed and repopulated because they're all using the same instance of the SQLControl class (SQL.SQLDA, etc.) What you should be able to do, is generate multiple instances of the SQLControl class and assign them individually to your datagrids. That way each will have its own managed DataSet which wouldn't be interfered with by subsequent commands. You could do it a couple of different ways: Private Grid1SQL as New SQLControl Private Grid2SQL as New SQLControl etc... Alternatively, you could use a Dictionary collection to maintain an indexed list of generated SQLControls. Example: Private dictSQL as New Dictionary(Of String, SQLControl) Then you can use the DataGridView name as the key (String) to reference its own instance of SQLControl. One possible down side would be that each stored dataset would consume memory in relation to the size of the datasets returned in their respective queries. If you have 10 Datagrid views, each with a very large number of returned records in their datasets it might impact performance. Naturally, it's best practice to query on indexed fields and limit the returned data via filtering where possible.
Unfortunately, the DataGridView only supports single, non-joined table updates. If you want to update multiple tables, you'll need to either re-run the query and command builder, or use multiple instances of the control class, so you have multiple data adapters/command builders to work with independently. Example: Private SQL1 As New SQLControl Private SQL2 As New SQLControl
Hi. First of all Kudos on wonderful series. Coming from VB6 domain, I always wondered how to have same functionality in VB.Net, as Flexgrid -VB6 combination offered i.e. Adding Rows & Columns on the fly and have the ability to put dynamic values to any cell. Please advise how can this be possible using VB.Net and DataGridView. Thanks in advance. PJ
this tutorial is just great, but I have a question, what if I have several tables then I use listbox to display those tables and also datagridview to display the data from the tables, how to make change on the data and save it to the database ? is it doable ? in this case I'm using access databse I'm a newbie and I always get confused when I have to use several tables anyway, you're doing a great job, thank you so much
Hello, please I am stuck at Params.Foreach(sub(x) MyCmd.p Parameters.Add(x)) My visual studio app give an error, highlights that 'sub' and complains that 'Expression expected'. Pls help out.
hi,sir your tutorials are simply understandebal and great , i need some more helps with acces data base i.e haw to use numaric desbibal and date expretion to insert and select
Hi, I'm following your courses line by line, I'm fairly new to VB and SQL programming. I'm using MySql instead of SQL Server CE. I have a problem with the following line: SQL.SQLDA.UpdateCommand = New MySqlCommandBuilder(SQL.SQLDA).UpdateCommand I'm getting the following error: InvalidOperationExeption was unhandled Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. I can't find out what's wrong here. Do you have any idea? regards, Mark Hofland
Awesome video! Very well explained. Subscribed! Just an idea: I'd love to see any vids/ideas you have on integrating Outlook with MSAccess. My current application uses an Access table linked to Outlook Contacts. It has severe limitations. It can't enforce Referential Integrity for foreign keys. For example, my "Projects" table (linked to Contacts via CustomerID) allows entry of a CustomerID that doesn't exist in Contacts. There are other issues. I can code some work-arounds that are ugly and inelegant. I'm guessing there are much better ways. I wouldn't have any issues if I could sync my phone contacts to an Access table. But, it appears impossible for iPhone (at least as far as I know) Anyway, love the video. I'm looking forward to watching your other ones this afternoon.
*Really i appreciate your videos and first of all "THANKS"* *First Q: I saw in this video, you created a database and table in a different way that you created in previous videos and from the sql management studio itself. Now the question is what is the different between both ways? or it is the same?* *Second Q: If i want my project work correctly in another computer what will i do? because i knew you must first install sql server in the other computer, then change the path of the database according the folder you want to save your project in the other computer, and transfer the database your folder location. Here i want a video from you to explain this operation and i'll be so appreciated to your job*.
Use the following codes as connection string and you won't have problems: If you use Windows Authentication to login SQL Server Dim MyConn as New SQLClientConnection ("Server=localhost; Database=mydb.mdf; Integrated Security = True") But if you use SQL Authentication method, use Dim MyConn As New SQLClientConnection ("Server=localhost; Database = mydb.mdf; user=sa; password = mypassword; Integrated Security = False") Note: Replace the mydb.mdf with your database name and change those username and password to your login credentials. Also, if your database is in SQL Compact version, use 'SQLServerce' instead of 'SQLClient'.
Great tutorial! Thanks. I downloaded the files and repeated what you did in the tutorial. I'm unable to link the class to a form code. VS 2015 is unable to find the SQLControl class. I'm using OLEDb and MS SQL.
Hmm It should work fine in VB 2015, though the OleDB isn't necessary unless you're using MS Access or Excel. Make sure you're not in C#. If I get a chance, I will try to double check my old project in 2015. I'd also like to recommend checking out the updated SQLControl class in my most recent SQL tutorial. It has been improved a bit.
Thanks for the awesome videos! I got this working perfectly after I added my ID field to my Query. now my Question is I have tried to combine this with another of your lessons "VB.NET Tutorial - SQL Database Search Form With Wildcards (Visual Basic .NET)" the issue I am having is while I am in a defined or narrowed down results from the search boxes If I hit save it crashes. I am assuming it's because I am in a SUB?
Hello, Ben! Sorry for the slow reply. When you filter your results, you're processing a new query which overwrites the existing DataTable and DataAdapter, so the Data Adapter's UpdateCommand must be regenerated with the new [search] query. On a side note, I strongly recommend using the SQLControl from the newest SQL tutorials as it's been significantly improved from these very old tutorials. :-) Here is a link to the most recent version: www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Hi there, i've gotta say you really did a great job. I've been away for VB for 7 years and my manager wanted me to pick up VB again and using SQL. Thanks to you I'm very close on starting to create a program for my office. But I'm having a little trouble when I try to replace SqlServerCe by directly calling the database on my office server. (I'm not using any local database.) I'm getting error when I try to execute this command: SQL.SQLDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand ====================================================== Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. ====================================================== I'm sure something is wrong when i try to change it, but I just want to know is it possible to update the database on server instead of local database by using data grid view? Thank you for your effort and help sir.
+Toomba87 Hey there! :-) When updating via a DataGridView, ensure that you're returning your table's Primary Key column with your query. Another important thing to note is that DGV Updates will not work with multi-table (relational) queries. I made you a simple sample project with line by line notes, using SQL Server. Project Download: www.dropbox.com/s/5tpdsbxawcpwg71/SQLDGVUpdate.zip?dl=0 I hope that helps!
Hello, VB Toolbox. I was wondering if you could help me out again. I've been looking up a way for the program to check the SQL server if the user has updated their password more than once. I originally made the table called T_User have a column called ChPass as int. The integer 0 or 1 would indicate whether the user has updated their account never before or the only one time they can. The code I'm about to post keeps giving the error of the password already being updated, even when the password has never been updated. Can you please help me in the right direction? (Someone told me that I have to convert the string to integer at some point). Thank you. Code: Apply Button If Username.Text "" Then CheckChPass() If Password.Text.Length >= 8 And Email.Text.Length >= 9 Then Dim UpdateCmd As String = "UPDATE T_User " & _ "SET PassWD='" & Password.Text & "', " & _ "ChPass='" & "1" & "' " & _ "WHERE UserID='" & Username.Text & "' " & _ "AND Email='" & Email.Text & "' " If SQL.DataUpdate(UpdateCmd) = 0 Then MsgBox("Invalid account information.", MsgBoxStyle.Critical, "Login failed!") Else MsgBox("Password sucessfully changed!", MsgBoxStyle.Information) Username.Clear() Password.Clear() Email.Clear() End If Else MsgBox("Please check the length of the new password.", MsgBoxStyle.Critical) End If Else MsgBox("Please check Username and Email.", MsgBoxStyle.Information) End If ------------------------------------------------------------------------------------------------------------------------------------------------------ CheckChPass SQL.RunQuery("SELECT ChPass FROM T_User WHERE UserID='" & Username.Text & "' " & _ "AND Email='" & Email.Text & "' ") If SQL.SQLDataset.Tables.Count > 0 Then MsgBox("Already changed password once before!", MsgBoxStyle.Critical) Username.Clear() Password.Clear() Email.Clear() Exit Sub Else End If
+kevin inskeep First off, the CheckChPass is always going to return the message if there are no errors in the query, because you're counting the *Tables* returned. A successful query will always return a table even if no records were found. You could try changing this to SQL.SQLDataset.Tables(0).Rows.Count if you want to see if any matches were made. That said, I strongly recommend using parameterized queries and commands. Also, we should be able to improve and simplify this quite a bit by using the newest version of the SQLControl class. If you'd like me to assist with this I'd be happy to email you.
That would be great. I just needed to know what the problem was and which direction to take towards fixing the issue. If you have the time to help me fix the code, then please do email me. I don't want to take up a lot of your time. Thank you.
Actually, SQL Ce (v3.5) is included with the Visual Studio installation. If you want v4.0, you can download it [free] from Microsoft. There is also a second installer which makes SQLce 4.0 projects available in the project selection list. One thing that I recently discovered is that VS 2013 no longer has SQL Ce, rather it has "LocalDB". It's similar in usage.
Hello This is a great tutorial. I make it, but i have a problem. I update the database and after thet i open the program and teh databse updated. But when i open the database and open the table in the Visual studio program i couldn't find my data in the table. And after that i run the program and i couldn't find my data int the grib too. So its deleted. Why? What am i doing wrong? What am i going to do? Thank you (sorry about my language faults)
+Imre Romsics Hello, Imre. :-) 1.) Select your database file in your Solution Explorer 2.) In the Properties panel change "Copy to Output Directory" to "Copy if Newer" This will prevent your debug database from being overwritten with the template each time you debug. Note: If you make changes to the template database it will overwrite the debug database next time that you debug.
+VB Toolbox Thank you your fast answer. But in the database Properties "Copy to Output Directory" is already "Copy if Newer". So i think not its the problem.
Hi! I'm a very new vb user and I tried to run but i got an error in this line: Params.ForEach(Sub(x) SQLCmd.Parameters.Add(x)) Error: Expression expected. And Sub is underlined. Can someone help me?
Are you, by chance, using an older version of VB.NET [e.g., 2008]? I know that in versions prior to 2010 lambda expressions weren't a thing, in which case you'll just replace that with a standard *For Each* loop. I also recommend checking out the newest SQL tutorials, as well. The SQLControl has been improved and optimized quite a bit since this old tutorial was made.
Sorry, I have a problem I use MySql and I have a error Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. This problem is caused by anything Thanks a lot.
Hi sir, I am using vb2008, and I tried to create as you said in the tutorial. But I am getting error in sub function Public Sub ExecQuery(ByVal Query As String) Try SqlCon.Open() 'create sql commond sqlcmd = New SqlCeCommand(Query, SqlCon) 'load parameters in to sql command Params.ForEach (Sub(x) sqlCmd.Parameters.Add(x)) It is not allowing me to use Public Sub ExecQuery(Query As String)by default it takes ByVal By this I am not able to use params.ForEach(Sub(x) how to fix it?
ive been trying to save a label but it wont work it resets every time i push the load button?? this is my script: load button: lbl_cash.Text = My.Settings.Savecash lbl_cps.Text = My.Settings.Savecps Save button: My.Settings.Savecash = lbl_cash.Text My.Settings.Savecps = lbl_cps.Text
+VB Toolbox Sorry about all of this but the problem seems to still be there, The label is still reseting when i hit the load button... Its supposed to be at 1.6 coins and it is but only for like 1 second and then back to zero
Hi, thanks for a great tutorial. I followed step by step all of creation, but I have a problem. I create a database and insert 2-3 record. In datagrid I view only those records and not the empty one at the end, to be able to add another. Also It does not work even DEL to delete a record directly from the grid. I followed step by step all programming lines ... what is the problem? Help please ....
+Job 3.14 Hi there. If you cannot see the empty row to add items to your DataGridView, simply check the DataGridView properties window and set *AllowUserToAddRows* to *True* . :-) To delete, do the same thing for the *AllowUserToDeleteRows* property. Finally, verify that the *ReadOnly* property is set to *False*
Hi! Your vids are very useful :) I just want to consult because I'm having a problem with the program I'm creating right now. I followed your vids how to Update a Database From a DataGridView (using SQLCEcommand) and how to Connect & Query a Microsoft Access Database in which you use OLEdbcommand.I'm just wondering if I could include the SQLCEcommands that you used in updating a Dbase to my program which are composed of OLEDBcommands. Do you think that's gonna be possible? If yes, can you teach me how and what should I change on my program to make it work. Totally appreciate your feedback. Thanks ;)
+Dana Patricia Cirot Hello, Dana. :-) The code is much the same for both SQLCE Command and OLEDB Command; However, this process is much more complicated with OLEDB because, unlike SQLCE, it is much more strict with DataType validation and you'll have to employ work-arounds to resolve the errors by enforcing the Data Types in your command parameters. It took me a while to make this work, but I think that I may have some source code somewhere for this. I will have to try and find it.
+VB Toolbox hi thanks for the response. I would really appreciate if you'll be able to find the the source code you may have. Looking forward to that. Thanks :)
+Dana Patricia Cirot Here we go! :-) [Using my DBControl class with Access] In a Form with a DataGridView [dgvUpdate]: Private Sub LoadGrid() Access.ExecQuery("SELECT username, password, email FROM members;") dgvUpdate.DataSource = Access.DBDT Dim builder As New OleDb.OleDbCommandBuilder(Access.DBDA) builder.QuotePrefix = "[" builder.QuoteSuffix = "]" Access.DBDA.UpdateCommand = builder.GetUpdateCommand End Sub For the Save button: Private Sub cmdSave_Click(sender As System.Object, e As System.EventArgs) Handles cmdSave.Click Access.DBDA.Update(Access.DBDT) End Sub I hope that helps!
+VB Toolbox thanks for this but I've been trying to use these code on my program but when I run it and try to modify the username directly on the datagrid (I have ticked the Enable editing by the way so I can edit it) then when I click save button I'm getting an error that says "update requires a valid UpdateCommand when passed Datarow collection with modified rows."
These are the codes that I have currently. Can you kindly check if I'm missing something or if there's something that needs to be corrected. I've been doing this for my thesis project.Hopefully I can get a feedback from you. Sorry for being so desperate I just really needed some help :) Here's the codes: Public Class Form1 Private Access As New DBControl Private Function NotEmpty(ByVal Text As String) As Boolean Return Not String.IsNullOrEmpty(Text) End Function Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown 'RUN A QUERY Access.ExecQuery("SELECT * FROM table1 ORDER BY StudentName ASC") If NotEmpty(Access.exception) Then MsgBox(Access.exception) : Exit Sub 'FILL DATAGRID dgvdata.DataSource = Access.DBDT 'FILL COMBOBOX For Each R As DataRow In Access.DBDT.Rows cbxName.Items.Add(R("StudentName")) Next 'DISPLAY FIRST NAME FOUND If Access.recordcount > 0 Then cbxName.SelectedIndex = 0 End Sub Private Sub SearchStudentName(ByVal Name As String) 'ADD PARAMETERS & RUN QUERY Access.AddParam("@user","%" & Name & "%") Access.ExecQuery("SELECT StudentName, Address,Guardian,Contacts,Course,Yearlevel,Type, Gender " & _ "FROM table1 " & _ "where StudentName LIKE @user") 'REPORT & ABORT ON ERRORS If NotEmpty(Access.exception) Then MsgBox(Access.exception) : Exit Sub 'FILL COMBOBOX dgvdata.DataSource = Access.DBDT End Sub Private Sub cmdFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFind.Click SearchStudentName(txtfind.Text) End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'EXECUTE QUERY AND POPULATE GRID Access.ExecQuery("SELECT * FROM table1") Loadgrid() 'DISABLE SAVE BUTTON 'btnsave.Enabled = End Sub Private Sub Loadgrid() Access.ExecQuery("SELECT StudentName, Address,Guardian,Contacts,Course,Yearlevel,Type, Gender FROM table1") dgvdata.DataSource = Access.DBDT Dim builder As New OleDb.OleDbCommandBuilder(Access.DBDA) builder.QuotePrefix = "[" builder.QuoteSuffix = "]" Access.DBDA.UpdateCommand = builder.GetUpdateCommand End Sub Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click Access.DBDA.Update(Access.DBDT) 'REFRESH GRID DATA Loadgrid() End Sub End Class
Hi thanks for your video, sometimes I am getting this problem when updating(saving). Concurrency violation: the UpdateCommand affected 0 of ... Could you plz help, thanks
The beauty of SQL Ce is that it's incredibly portable, self-contained, and requires no SQL Server installation. The database can be easily packaged with the project. It does have some limitations (max size [4GB] per .sdf, lack of stored procedures, max connections [32 (v3.5), 128? (4.0)]. As for the database updates, the only significant differences are going to be with the connection string and namespace calls. Everything else should be the same. :-)
Hello, Thanks for this great tutorial. I learned a lot from it. I have one problem however... When I add a new row in the datagridview and the loadgrid is called it doesnt show the NEW ID. How can I adjust this? Thanks in advance!!!
+Johnny Hermes (Bodylojohn) The problem in this example is that LoadGrid is using the existing dataset that is populated from the Form_Load event, so it's not getting re-queried after the Update. Try rerunning SQL.ExecQuery() at the top of the LoadGrid sub to refresh the dataset and see if that works. :-)
Hello, is a very nice tutorial, i tried to download it with the link that you provided but it looks like the link is not working. Is it posible to download the examples on another webpage. Thanks.
hello friend, remember me? i have a very important quest, how can i get a gridview to look like this one containing in this project? here is the dropbox link: www.dropbox.com/sh/3lp1slqdd2vzwa4/AACs_v8QfQlkINnHPpEnPzZOa?dl=0
Howdy, Keith. :-) For now you'll have to key it in along with the tutorial. I keep planning to make a source code repository for these videos, but can never seem to find the time. If you really need the source, I can probably track it down for you.
Hello! :-) With ADO.NET the syntax is nearly identical. The only changes are to the namespaces called and the connection string. You only need to change the following: Imports System.Data.SqlServerCe --> Imports System.Data.SqlClient SqlCeConnection --> SqlConnection SqlCeCommand --> SqlCommand SqlCeDataAdapter --> SqlDataAdapter SqlCeParameter --> SqlParameter Make sure that you're using a correct Connection String for SqlConnection: www.connectionstrings.com/sql-server/
im using vb.net 2010 with database Microsoft Access 2007. i want to edit then update the datagridview. how can i do that. the video that i watching is not finish. SIR PLEASE HELP ME. THANK YOU for Advance
naruto uzumaki Hello, Naruto. I'll be happy to help if I can. I need a little more clarification, though. Are you trying to Update Access from the DataGridView, or are you trying to Query Access to a DataGridView, or are you just trying to Refresh the DataGridView after updating Access?
Hello, im trying this with the Access DB, i use the db control code from another yours video. I have a module with a search procedure , this search load a datagrid , so far so good. I need now to delete or change in a cell from grid something , i m trying to do like the video but....can you help me plz??? I thing the problem is when i have the parameters wich you say? in form wich have the grid? the grid loads from module I write the code and the "try for update grid" in module , i tried in load form, but nothing help plzzzz..... Public Sub searchbeforeinsert(ByVal mysearchbeforeinsert As String) Exception = "" Try ' ADD Parameters & run query Access.AddParams("@user", mysearchbeforeinsert) ' query Access.ExecQuery("SELECT Products.ProductID As [Κωδικός Προιοντος],Products.CodeProduct As [Κωδικός Προιόντος],Colors.Color As Χρώμα,[Size].[Size] As Μέγεθος,Products.ProductName As [Ονομασια Προιοντος], ProductCategories.CategorieName, " & _ " Products.TotalStock As [Απόθεμα], Products.UnitPrice As [Τιμή Χονδρικής], Products.TelikiTimi As [Τιμή Λιανικής], Products.BarCode , Products.Photo " & _ "FROM (((Colors INNER JOIN Products ON Colors.ColorID = Products.ColorID) INNER JOIN [Size] ON Products.SizeID = [Size].SizeID) INNER JOIN " & _ "ProductCategories ON Products.CategorieID = ProductCategories.CategorieID) WHERE (Products.ProductID = @mysearchbeforeinsert)") 'report & abort on errors If NoErrors(True) = False Then Exit Sub ' fill data grid with filter product categories If Access.RecordCount > 0 Then ' OrderDetails.DataGridView1.DataSource = Access.DBds.Tables(0) ' Access.DBda.UpdateCommand = New OleDb.OleDbCommandBuilder(Access.DBda).GetUpdateCommand For Each R As DataRow In Access.DBdt.Rows OrderDetails.DataGridView2.Rows.Add(R(("Κωδικός Προιοντος").ToString), (R("Κωδικός Προιόντος").ToString), (R("Χρώμα").ToString), (R("Μέγεθος").ToString), (R("Τιμή Χονδρικής").ToString)) ' try for update grid OrderDetails.DataGridView1.DataSource = Access.DBds.Tables(0) Access.DBda.UpdateCommand = New OleDb.OleDbCommandBuilder(Access.DBda).GetUpdateCommand Next End If If Access.RecordCount = 0 Then MsgBox("Δεν βρέθηκε η κατηγορία searchbeforeinsert " & OrderDetails.cmbcategorie.Text.ToString & " προιόντος που αναζητήσατε ") End If Catch ex As Exception Exception = ex.Message End Try 'OrderDetails.cmbcategorie.SelectedIndex = 0 End Sub
+Eletheria Kourteridou I have a sample project for updating MS Access from a DataGridView; However, when updating a Database from a DataGridView it is limited to single-table queries. In your example, you are using a relational query [INNER JOIN] so it will not work properly. :-( Sample Project Download: www.dropbox.com/s/i7xqu6sjantdpdx/AccessDGVUpdate.zip?dl=0
+VB Toolbox Thanks again for your answer, any suggestion for this issue? maybe, if i read all records from grid, line by line and after update the records in db? some example? any idea?
Teufack Alain Unfortunately, joined table updates cannot be performed with the DGV command builder. You will have to write a query manually and pass the desired DGV items through that targeting the table/s you wish to update.
hello sir. i am creating a application for database storing. i have database named database1.sdf and in that database there are 5 tables named Base_plate, Alloy, Bead_type, Difficulty_factor and price_Factor. i want to update all the table at the same time. while tring this i got the error in sqlcecommandbuilder i.e. "The DataAdapter.SelectCommand property needs to be initialized." please help me out. here is my code.. Imports System.Data.OleDb Imports System.Data Imports System.Data.SqlServerCe Public Class Admin Dim update As New SqlCeDataAdapter ' sql connection strings Dim SQLCon As String = "Data Source=Database1.sdf" Dim sqlstr As String = "Select * from Base_Plate " Dim sqlstr1 As String = "Select * from Alloy " Dim sqlstr2 As String = "Select * from Bead_Factor " Dim sqlstr3 As String = "Select * from Difficulty_Factor " Dim sqlstr4 As String = "Select * from Price_Factor " ' sql variable of base Dim adapter As New SqlCeDataAdapter(sqlstr, SQLCon) Dim ds As New DataSet() ' sql variable of alloy Dim adapter1 As New SqlCeDataAdapter(sqlstr1, SQLCon) Dim ds1 As New DataSet() ' sql variable of bead Dim adapter2 As New SqlCeDataAdapter(sqlstr2, SQLCon) Dim ds2 As New DataSet() 'sql variable of difficulty Dim adapter3 As New SqlCeDataAdapter(sqlstr3, SQLCon) Dim ds3 As New DataSet() 'sql variable of price Dim adapter4 As New SqlCeDataAdapter(sqlstr4, SQLCon) Dim ds4 As New DataSet() Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click update.Update(ds) LoadGrid() End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Me.Visible = False LoginForm1.Visible = True End Sub Private Sub Admin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load LoadGrid() Button2.Enabled = False End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click End Sub Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged Button2.Enabled = True End Sub Private Sub LoadGrid() '************** base datagrid ******************** adapter.Fill(ds, "Base_Plate") DataGridView1.DataSource = ds.Tables(0) DataGridView1.Rows(0).Selected = True '***************** alloy datagrid ********************* adapter1.Fill(ds1, "Alloy") DataGridView2.DataSource = ds1.Tables(0) DataGridView2.Rows(0).Selected = True '***************** bead datagrid ********************* adapter2.Fill(ds2, "Bead_Factor") DataGridView3.DataSource = ds2.Tables(0) DataGridView3.Rows(0).Selected = True '***************** difficulty datagrid ********************* adapter3.Fill(ds3, "Difficulty_Factor") DataGridView4.DataSource = ds3.Tables(0) DataGridView4.Rows(0).Selected = True '***************** Price datagrid ********************* adapter4.Fill(ds4, "Price_Factor") DataGridView5.DataSource = ds4.Tables(0) DataGridView5.Rows(0).Selected = True update.UpdateCommand = New SqlServerCe.SqlCeCommandBuilder(update).GetUpdateCommand End Sub Thanx in advance. :)
+Rafael Carulla I apologize for the slow update on this. Got very busy at work. I was unable to find my project, so I just made you a new one. I hope it helps! PROJECT DOWNLOAD: www.dropbox.com/s/i7xqu6sjantdpdx/AccessDGVUpdate.zip?dl=0
To my knowledge, you cannot update multiple tables from the same DataGridView. You can query multiple tables to a DataGridView, but not update. You'll need to create your own command routines to update them.
I think that it depends upon your needs. Transferring file data could be done via HTTP/Web controls, or binary streams via TCP Sockets. In either case it could be quite a process. I haven't tried a sockets file transfer yet. Might be worth learning, for sure. I did do an experiment with image downloads via web controls, a while back. Seems like I did a tutorial on that once, but I can't remember for certain.
I'm getting the following error: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. And it refers to this line of code. sql.sqlda.UpdateCommand = New SqlClient.SqlCommandBuilder(sql.sqlda).GetUpdateCommand I'm using SQL Sever so I modified the part where you use the compact edition. Can you tell what I'm doing wrong?
Hello, Michael. Does your table have a Primary Key column? If so, try returning it to your DGV with your query. If not, consider adding a Primary Key to your table. Note: The values of that column must be unique. Most often, an ID column with an auto-incrementing number is used.
USE [dbname] create table Food(RollNo int identity(100,1) primary key, food_name varchar(20),food_type varchar(20)) I also had the same problem, created a new table as above, and now works fine.
I have not programmed since Fortran in College. Object oriented programming has always been a hurdle. I have been using Excel to manage data for one of my hobbies and went down the Excel rabbit hole. I have now decided to write a VB program to manage the data. Among all of the sources I have relied upon to introduce various areas of the program your tutorials are by far superior to anything else I have found. Thank you!
This was amazing. I'm using MySQL and I was able to adapt easily. One could Google literally for weeks before coming up with this volume of information. Clearly stated, concise and complete. Thank you so much!!!
Awesome! I'm very glad it was helpful. 🙂
I think I actually had an old MySQL tutorial somewhere and a control class adaptation for it, though it sounds like you've already discovered how easy it is to switch between them all - MySQL, SQL, Access, Firebird, etc... Fun stuff! I miss working with this.
This video is over 4 years old and still so helpful. I like your whole series and I am subscribed.
I first watched this and your other excellent videos a few years ago, in the context of MS-Access database, similar kinds of ideas. But now, I need to migrate that Access database to SQL. I've been spending the better part of the last few weeks getting up to speed by myself on using SQL and migrating my Access database to SQL and I'm proud to say that I now understand the business of creating tables with primary key and identity set at the get go, otherwise, SQL Server will not allow to change the field (column) once it's created. Thank you so much for having taken the time to do this and your other excellent videos.
I am trying to incorporate this code into a project I am working with and trying to figure out what the best way to return the ID of a new record when running an INSERT query
Hello, Ian! I *think* I had incorporated this feature in the SQLControl class in this project (dropbox link below). You should see it as an optional sub-query with @@IDENTITY, which must be selected/executed in the same session as your INSERT. For this reason, I embedded the option in the ExecQuery sub. I hope that helps, or at least gives you an idea of what to use. 🙂
Dropbox Link:
www.dropbox.com/s/1o74yhqkuzq6r9r/SQLTutorial%20PT4.zip?dl=0
At 29:21 you write "New SqlServerCe.SqlCeCommandBuilder(SQL.SQLDA).GetUpdateCommand", I am using a different database where I am writing my SQL adapters, connections, etc. as "SqlDataAdapter" and the like. When I try to simply write "New SqlServer.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand" I don't get anything. It appears that I perhaps don't have the correct pathing for my version of VB? Any help would be appreciated.
Awesome tutorial, worked seamlessly for me. Saved me from hari cari, and probably extra days of trial and error.
this really helped me a lot! I hope more tutorials be uploaded :)
THANK YOU! You may have just shed some light on why my program wasn't doing this. Hopefully now I should be able to make it work. Subscribed! :)
Hi, It is a great series of video that leads beginners to write their code in a more professional way.
I used to learn VB 6.0 on my own and now I would like to shift my IDE to Visual Studio, so I could keep my programming skills updated as the trend move forward.
It will be very appreciated if you can make a series of video demonstrating how to make data export and Print a report in VS. I have not idea how to use those Print controls so far.
Daniel from China
Another Question I used this Lesson to make a form with 2 datagridviews tied to 2 different tables where my search text boxes search both datagrids. I have a loadgrid and a loadgrid2 sub each sub has "SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand"
But I can only save changes in 1 datagrid the second datagrid reverts back after I hit save.
Do I need 2 unique DBDA or DBDS?
To keep them independent of each other, you'll probably want to instantiate two instances of the SQLControl class, since each one has its own DataTable(updated version)/DataSet (old version) and DataAdapter. Once a query is executed, these objects are overwritten with the last query that ran. That means that the UpdateCommand associated with the initial DataAdapter is also destroyed.
Example:
Private SQL1 As New SQLControl
Private SQL2 As New SQLControl
Doing that, you may use them independently of each other.
Great Video! It rolls so much info into a single training. Do you have a video on how we can accomplish The updates if using multiple tables? That is what I am trying to do
If I recall correctly, this was a limitation of DGV updates, in that the updates only work for one table.
It may be possible to build some kind of workaround that dumps the desired column data into separate table-connected DGVs and kicks off an update chain from there. Could be a fun experiment, but I have no idea if that work or how efficient it would be.
Hi, could you please tell me why to do the system.data.sqlserverCe thing in 3:55 ? What happen if i dont do that?
+Guillermo Enrique Cedeño Ojeda If you have no reference, you may not be able to read the necessary library Namespace @ 9:23 and your control class will not be able to talk to the database.
SQLServer does not require a reference, but SQLServerCe generally does.
Oh i see, thanks. I ask becouse im working wit sql server express edition and i dont use the sql server compact so im not sure if this video will work wit that
lol visual 2015 dont even have it..
+Guillermo Enrique Cedeño Ojeda Oooh! Okay. You can still use this video but you don't need to add the reference at 3:55 .
Instead, simply use "Imports System.Data.SqlClient" at 9:23 . Then on the objects [SqlCeConnection, SqlCeDataAdapter, SqlCeCommand] just remove "Ce" from all of them and it should work. :-)
really? all right good to know , i was geting crazy wit that haha
Hello, im doing this wit the Sql server express edition, everything is runing fine by just not ading the "Ce" into the commands, but i dont know what to write in the 28:51 becouse there is no SQLServer command :/
+Guillermo Enrique Cedeño Ojeda I just found it by doing some research:
SQL.SQLDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand . Btw Did you made a video validating the entrances in a datagridview?
Thanks for this Video. Do you need to add reference to SQL for a regular sql Database?
If I may ask, how could I insert the data in this DataGridView to a separate or new table to review changes made to the original table? For example, if someone, using the application, made changes to your Food_Type "fruit" under ID "4". This way, you can tell if the end-user made a change to your database. I hope this makes sense & thanks for your help as always!
Hi i am unable to excute the same on sql server "SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand" to update the datagridview & database
Hello,
You've done a fantastic job at creating these VB.NET videos, I've learned so much so fast. Thank You
I'm finally moving from the VB6 world to the VB.NET world, yes I know I'm a little behind but in a production world it takes a long time to move from one to the other.
My question is I would like to know how to use the DataGridView to add records to a SQL Server database table, also show the DataGridView control with default column headers and set columns to a specific default width.
I hope this makes sense.
Thanks
I very much appreciate the encouraging words, Pete. :-) I know that I'm using SQL Ce in this tutorial; However, updating a SQL server database is mostly identical as the ADO.NET library for SQL should contain the same methods and functions that we used in this tutorial. The main difference is that you'll use the *SqlClient* namespace, rather than *SqlServerCe* and your connection string will be slightly different. (I'll be happy to assist with that if you need).
I know that you can specify the datagridview column names and width, but I'm not sure if setting a datasource will override those values. I'll have to test that and let you know.
If you want to alter your column sizes, you'll want to set the *AutoSizeColumnsMode* property to None, either in the Properties dialog or programmatically _[dgvTest.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None]_
You can then specify a custom Column Width by Index or Column name. Example:
_DataGridView.Columns("ColumnName").Width = 150_
I'm using MS Visual Studio 2015. No natter how many times I try to disable the save button on initial load of datagrid, it still shows as active upon startup
Have you tried setting the button's "enabled" property to false?
@@finleybutler8780 I tried that initially and for some reason it still is active upon entry to window. It's an app for my own use so I'm not that concerned. Will continue to investigate. Thanks for the reply
@@edbohnemann5932 That is odd. Good luck and you're welcome :)
I'm getting the following error: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. And it refers to this line of code.
sql.sqlda.UpdateCommand = New SqlClient.SqlCommandBuilder(sql.sqlda).GetUpdateCommand I'm using SQL Sever so I modified the part where you use the compact edition. Can you tell what I'm doing wrong?
I think you select multiple table that's why you get that error. That is also my problem.
I am building a program to update a database with data from an imported excel sheet but i and i like your method to save the database updates. I tried to incorporates your example to my project but I am not able to copy the rows from the datagrid view with the excel spreadsheet to the grid that has your example. they are located within the same form.
Any help?
Nice tutorial. Thumbs up. Learnt several lessons here. Though I still can't understand some few things here because I am an advanced newbie and do not use custom private class, however I will understand later with much practice.
Update: I finally got what I wanted. All I needed to add to my codes were the 'dataadapter.updatecommand=......' and 'dataadapter.update (ds.tables(0))'.
I previously knew how to populate a datagrid using dataset, hence easier for me to flow with the tutorial.
I will learn to create and implement custom class in my next projects.
Great tutorial, thank you!
Hi there,
I'm new with database and must say that your tutorial is awesome. I have a question for you please - if I have 2 tables in a dataset, do I have to create another SQLControl object?
Charles Lim The only reasons that I'd create more than one SQLControl object is if I wanted to preserve the contents of a dataset (protect it from further queries) or if I was connecting to multiple databases.
That said, I'm curious about how/why you're generating multiple data tables in the dataset.
I'm working on a Windows app that maintain a table of community events and where new events will added from time to time, a table of registered members of the community whose members will continue to increase and a third table to capture participants (who are members) of each event. Does it make sense to use multiple tables or is there a better approach? Appreciate any advice.Thanks.
Sir I dont have SqlServerCe in my VS 2010 Ultimate? Where can I get it? Please HELP ME!
Glad you're still here Aardaerimus! I followed your XNA VB.NET tutorials and I loved it! I also noticed that you stated those tutorials were outdated, which is a shame! Will you ever make new XNA tutorials?
Hello, Brandon! I really, really want to say that I plan to continue the GP tutorials, but I'm in a tight position there. The XNA game engine that my friend and I were working on has been dramatically overhauled (much better), but I'm not sure that I want to make a tutorial on the entire process and I'm not exactly sure how to continue.
If I just tailor XNA tutorials our specific homemade game library the viewership would be sorely limited and VB game design is already an incredibly tight niche. On the other hand, documenting the entire process again - like I did in Adventures in XNA - is very tedious, and again the niche is small so there aren't a lot of viewers to make it worthwhile.
I very much enjoy sharing what I learn and probably would anyway, but I haven't had quite the push on the game programming front that I originally had. I do miss it though, and it was very rewarding. It's been a long time since I've played with GP and I'd love to get back into it given that I could muster the time and energy to do so. I really need to get a forum going where viewers could interact and share ideas and solve problems independent of tutorial releases. As much as I love helping, it can be exhausting spending so much of my time trying to interact with all of my viewers and assist every individual with design issues.
Anyway, here's hoping. :-) If you want it, I'd be happy to share the updated game engine project with you. Kalamus did some major redesigning and essentially rebuilt the entire boilerplate into a .DLL in C#, which I then translated into VB.NET. All the end developer really needs to do is write the screens, menus, and file handlers.
VB Toolbox
I understand completely. I thought you had vanish completely and here you are, on another UA-cam channel (which I found from your website). Thank you for your offer on the updated game engine, but I won't be needing it. I rather code my own without any influence of another project.
Brandon D I'm the same way. I hate borrowing code because I want to understand how it all works, and the best way to do that is to use your own. Just know that the source is freely available, should you reconsider. Never know, you might get some ideas. :-)
This channel started as sort of a low commitment test. I had a ton of requests by people wanting to learn the basics of VB and didn't feel that it really fit into my GP channel routine. I was really strapped for time so I was going to just post short "quick tips" type videos and see if there was any interest. Additionally, I had released a couple database tutorials on my main channel a long time back and they had a lot of interest so I thought, "Maybe I'll toss a couple more up on the new channel." I was really surprised at how rapidly it gained interest so I kinda got sucked in. More bad news for my poor, neglected main channel... :-P
VB Toolbox
Yeah, not a bad idea! Your main channel will never be forgotten, I assure you that. With loads of helpful videos, how could anyone forget? With a simple search of "XNA 4.0 tutorials", your videos were listed on the first page. I mean, that's how I found your channel when I started GP. I noticed it was in VB.NET, and since I was a C# programmer, I just simply converted your code to C#, which wasn't hard at all.
If your main channel was not present to this day, I don't I would've gotten into GP at all. So thanks!
Brandon D
Awesome! :-D So, now I have to know - what kind of projects are you working on?? I've seen only a handful of projects in the works since I started that channel.
Great tutorial - though I'm having a problem when running the update query.
I've figured out that the issue is because I'm formatting the data in the select query
e.g.
"Select
Format([Variable],'£ #,###.##') as [Variable]
from Table"
If I just run it as "Select [Variable] from Table" update works fine, however Iwould like the data to show as formatted in the table when editing for neatness.
Is there a way to do this without impacting the update functionality?
Nevermind - I found a way to format it in the datagridview properties
Thanks for the tutorial nonetheless
Hi VB Toolbox! I need to filtrate the data of my datagridview, and this video is what all I need. But I have some problems, first, my database is created with phpmyadmin, and the second thing is that I haven't got the System.Data.SqlServerCe reference on my visual studio 2013. To complete this tutorial with my database, what things must I change? Thanks!
Regarding the database created with phpmyadmin, it shouldn't matter what tools are used to create your database. As long as you have sufficient permissions and credentials to access your database you should be fine.
Where SQLCe is concerned - unfortunately, I don't have a copy of VB 2013 to play with to check what version (if any) of SQLCe is included in that version of Visual Studio. That said, SQLCe can be downloaded for free from Microsoft. Just make sure to check the system requirements and download the SDK as well so that you can easily include it in your projects. The latest version I've used with 2010 is SQLCe 4.0. Once installed you can reference the provided dlls in your project, although the process may vary slightly for your version of VB.
VB Toolbox Finally I'm not using System.Data.SqlServerCe, I'm using System.Data.SqlClient. The code hasn't got errors, but when I debug the project, the exception message box says me this:
"Error associated with specific network or instance while a connection to the SQL Server is established. Server not found or it was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named piples Provider, Error 40 - Could not open a connection to SQL Server)"
When you write in the class "Data Source=MyDB.sdf;" I have to put here my connection string, I say it well?
The connection strings are different for different types of databases. It may help you to Google ADO.NET connections strings. There are numerous pages that will assist with determining the proper connection string for your database type.
You also need to use the appropriate ADO.NET connector for your database.
VB Toolbox I do the connection to the test server and the database by adding server connection and data source on the server explorer bar, I don't make it with code. The connection string that I put is the string that the server connection gives to me. I don't understand what's the error that I have with the server connection. I haven't got installed Microsoft SQL Server, I have my database with apache-mysql-phpmyadmin.
Can you do another tutorial using comboboxes and textboxes for filtrate the data of the datagridview? Thanks for all!
Can this be done with a datatable instead of a data set. I'm trying to do this with Microsoft Access. I followed your other tutorial and was able to get it show to the datagridview. I took it a step further and was able to pull information from different cells within the grid. But I cannot get the update grid procedure to work properly. I can upload my project onto dropbox if you need to look at the code to assist. Very much appreciate for any help that I receive. I have always been a fan of your work!
Ok, so first off, don't laugh. But I was able to get the update function to work with some rigging..... < I laugh at that statement..... xD Anyways. This is the code that I used to accomplish this:
Private Sub dgvData_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEndEdit
currentname = dgvData.CurrentCell.Value.ToString
If currentname = "" Then
currentname = " "
End If
Access.ExecQuery("UPDATE Members " & vbCrLf & _
"SET " & dgvData.Columns(index1).Name & "='" & currentname & "' " & vbCrLf & _
"WHERE ID =" & dgvData.Rows(index2).Cells(0).Value)
Access.ExecQuery("Select * from members")
If Access.NotEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub
dgvData.DataSource = Access.DBDT
End Sub
Private Sub dgvData_CellEnter(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEnter
' currentname = dgvData.CurrentCell.Value.ToString
index1 = dgvData.CurrentCell.ColumnIndex
index2 = dgvData.CurrentCell.RowIndex
End Sub
I collect the new name, and use a sort of dynamic update function to go through and update the database. This does not work for adding information.... only changing. So I am hoping to hear from you to see if there is a simpler way of doing this. In the meantime, this code does throw an error or two if the program does not have time to go through and finish the commands, I am still working on that bit as well.
Hello, First of all thanks for the tutorials,
I wondered if whether is possible to list all the info in listbox or datagrid,
and if I press a line for example 7, a new form will be opened with all the info of the column, and could be updated there instead from the data grid, can you give me a clue on how make such thing !! ?? thank you for kind help !
Both selections are fairly simple to accomplish. I believe I have some samples somewhere, but I will have to dig them up. If you include a primary key or unique column in your query to fill the listbox or DataGridView, you can then use the object's SelectedIndexChanged event to re-query for the item details [send to the new form and use as a parameter].
I can try, if you will have time to make a tutorial that would be amazing, I think there are alot of people who find it useful !! cause I really struggle in this thing.. i mean when trying to think this logicly i find something is missing in my mind.. like how can you read data on a list, and then the one returned is "divided"
like so hard for me ahaha .. thanks for the answer you really help us !
Hi, I love your tutorials, they give a real solid basis the my VB project and have referred to them countless times.
I am an bit stuck on this section however, I can not seem to get any data to load into the DataGridView. It will load the column headers but no row data. I have tested the statement in MySQL work bench (I'm using a MySQL server) and it works as expected.
Any ideas on where to start looking?
Thanks Jon
Hello, Jon. First off, I have an improved version of the SQLControl specifically for MySQL.
Class Text:
www.dropbox.com/s/gl1o55c5hsxounn/MySQLDB.txt?dl=0
With this class the query to grid should be as simple as in the latest SQL Server tutorials.
Example:
[In your Form class]
Private MySQL As New DBControl
Public Sub LoadGrid()
SQL.ExecQuery("SELECT * FROM Products;")
' REPORT & ABORT ON ERRORS
If SQL.HasException(True) Then Exit Sub
DataGridView1.DataSource = MySQL.DBDT
End Sub
If errors are found in your query it should report them and exit without filling the grid, otherwise the data will be return to the grid.
I hope this helps! :-)
That's great, it has bought up and exception message that was not being bought up before. Can you confirm on the code above that SQL should be MySQL?
Thanks Jon
Oops! Yep. I forgot to change those. Sorry.
They should match the name of your object variable [Private *MySQL* As New DBControl].
MySQL.ExecQuery()
If MySQL.HasException...
Thanks for a great tutorial. Much appreciated!
+Lientjie van der walt And thank you for the encouraging comment! :-)
If I had multiple datagridviews on a form with their separate save button. Would I still be using
SQLSQLDA.Update(SQLDS) in each button to update each table? I'm thinking it wouldn't work this way, because I'm having trouble.
Problem is that each time a new query or other command is run, the Dataset is flushed and repopulated because they're all using the same instance of the SQLControl class (SQL.SQLDA, etc.)
What you should be able to do, is generate multiple instances of the SQLControl class and assign them individually to your datagrids. That way each will have its own managed DataSet which wouldn't be interfered with by subsequent commands.
You could do it a couple of different ways:
Private Grid1SQL as New SQLControl
Private Grid2SQL as New SQLControl
etc...
Alternatively, you could use a Dictionary collection to maintain an indexed list of generated SQLControls.
Example: Private dictSQL as New Dictionary(Of String, SQLControl)
Then you can use the DataGridView name as the key (String) to reference its own instance of SQLControl.
One possible down side would be that each stored dataset would consume memory in relation to the size of the datasets returned in their respective queries. If you have 10 Datagrid views, each with a very large number of returned records in their datasets it might impact performance. Naturally, it's best practice to query on indexed fields and limit the returned data via filtering where possible.
This tutorial is great!! thanks a lot!! just one question: what should I do if I want to work with and update more than one table? thanks again!
Unfortunately, the DataGridView only supports single, non-joined table updates. If you want to update multiple tables, you'll need to either re-run the query and command builder, or use multiple instances of the control class, so you have multiple data adapters/command builders to work with independently.
Example:
Private SQL1 As New SQLControl
Private SQL2 As New SQLControl
Hi. First of all Kudos on wonderful series. Coming from VB6 domain, I always wondered how to have same functionality in VB.Net, as Flexgrid -VB6 combination offered i.e. Adding Rows & Columns on the fly and have the ability to put dynamic values to any cell. Please advise how can this be possible using VB.Net and DataGridView. Thanks in advance.
PJ
Very good and easy tutorial. Thanks
this tutorial is just great, but I have a question, what if I have several tables then I use listbox to display those tables and also datagridview to display the data from the tables, how to make change on the data and save it to the database ? is it doable ? in this case I'm using access databse
I'm a newbie and I always get confused when I have to use several tables
anyway, you're doing a great job, thank you so much
Hello, please I am stuck at Params.Foreach(sub(x) MyCmd.p
Parameters.Add(x))
My visual studio app give an error, highlights that 'sub' and complains that 'Expression expected'.
Pls help out.
Wonderful !
The code is clean with comments, it's a good work ;-)
It work well too with VB 2012
Hello, Arnaud Franoux. :-) It's good to know that it's working in VB 2012. Thank you much for the feedback!
hi,sir
your tutorials are simply understandebal and great ,
i need some more helps with acces data base i.e haw to use numaric desbibal and date expretion to insert and select
Hi, I'm following your courses line by line, I'm fairly new to VB and SQL programming. I'm using MySql instead of SQL Server CE. I have a problem with the following line:
SQL.SQLDA.UpdateCommand = New MySqlCommandBuilder(SQL.SQLDA).UpdateCommand
I'm getting the following error:
InvalidOperationExeption was unhandled
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
I can't find out what's wrong here. Do you have any idea?
regards,
Mark Hofland
+Mark Hofland , thats my problem too did you find any solution for this tip ???
+Ahmed Aboelez Hi Ahmed, you will need a primary key column in your SELECT statement.So, one of your columns needs to be a primary key.
thanks , thats right the problem was in primary key now thats really good
how can insert values from forms in one reports /visual basic 2010 ....thanks
Awesome video! Very well explained. Subscribed!
Just an idea: I'd love to see any vids/ideas you have on integrating Outlook with MSAccess. My current application uses an Access table linked to Outlook Contacts. It has severe limitations. It can't enforce Referential Integrity for foreign keys. For example, my "Projects" table (linked to Contacts via CustomerID) allows entry of a CustomerID that doesn't exist in Contacts. There are other issues. I can code some work-arounds that are ugly and inelegant. I'm guessing there are much better ways. I wouldn't have any issues if I could sync my phone contacts to an Access table. But, it appears impossible for iPhone (at least as far as I know)
Anyway, love the video. I'm looking forward to watching your other ones this afternoon.
*Really i appreciate your videos and first of all "THANKS"*
*First Q: I saw in this video, you created a database and table in a different way that you created in previous videos and from the sql management studio itself. Now the question is what is the different between both ways? or it is the same?*
*Second Q: If i want my project work correctly in another computer what will i do? because i knew you must first install sql server in the other computer, then change the path of the database according the folder you want to save your project in the other computer, and transfer the database your folder location. Here i want a video from you to explain this operation and i'll be so appreciated to your job*.
Use the following codes as connection string and you won't have problems:
If you use Windows Authentication to login SQL Server
Dim MyConn as New SQLClientConnection ("Server=localhost; Database=mydb.mdf; Integrated Security = True")
But if you use SQL Authentication method, use
Dim MyConn As New SQLClientConnection ("Server=localhost; Database = mydb.mdf; user=sa; password = mypassword; Integrated Security = False")
Note: Replace the mydb.mdf with your database name and change those username and password to your login credentials.
Also, if your database is in SQL Compact version, use 'SQLServerce' instead of 'SQLClient'.
Great tutorial! Thanks. I downloaded the files and repeated what you did in the tutorial. I'm unable to link the class to a form code. VS 2015 is unable to find the SQLControl class. I'm using OLEDb and MS SQL.
Hmm It should work fine in VB 2015, though the OleDB isn't necessary unless you're using MS Access or Excel. Make sure you're not in C#. If I get a chance, I will try to double check my old project in 2015. I'd also like to recommend checking out the updated SQLControl class in my most recent SQL tutorial. It has been improved a bit.
Thanks for the awesome videos! I got this working perfectly after I added my ID field to my Query. now my Question is I have tried to combine this with another of your lessons "VB.NET Tutorial - SQL Database Search Form With Wildcards (Visual Basic .NET)" the issue I am having is while I am in a defined or narrowed down results from the search boxes If I hit save it crashes. I am assuming it's because I am in a SUB?
Hello, Ben! Sorry for the slow reply. When you filter your results, you're processing a new query which overwrites the existing DataTable and DataAdapter, so the Data Adapter's UpdateCommand must be regenerated with the new [search] query.
On a side note, I strongly recommend using the SQLControl from the newest SQL tutorials as it's been significantly improved from these very old tutorials. :-)
Here is a link to the most recent version:
www.dropbox.com/s/qv2ywbrfsjpdahy/SQLControl.txt?dl=0
Hi there, i've gotta say you really did a great job.
I've been away for VB for 7 years and my manager wanted me to pick up VB again and using SQL. Thanks to you I'm very close on starting to create a program for my office.
But I'm having a little trouble when I try to replace SqlServerCe by directly calling the database on my office server. (I'm not using any local database.)
I'm getting error when I try to execute this command:
SQL.SQLDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand
======================================================
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
======================================================
I'm sure something is wrong when i try to change it, but I just want to know is it possible to update the database on server instead of local database by using data grid view?
Thank you for your effort and help sir.
+Toomba87 Hey there! :-) When updating via a DataGridView, ensure that you're returning your table's Primary Key column with your query.
Another important thing to note is that DGV Updates will not work with multi-table (relational) queries.
I made you a simple sample project with line by line notes, using SQL Server.
Project Download:
www.dropbox.com/s/5tpdsbxawcpwg71/SQLDGVUpdate.zip?dl=0
I hope that helps!
+VB Toolbox Thank you sir! I shall begin to try out with your code, huzzah!
Hello, VB Toolbox. I was wondering if you could help me out again. I've been looking up a way for the program to check the SQL server if the user has updated their password more than once. I originally made the table called T_User have a column called ChPass as int. The integer 0 or 1 would indicate whether the user has updated their account never before or the only one time they can. The code I'm about to post keeps giving the error of the password already being updated, even when the password has never been updated. Can you please help me in the right direction? (Someone told me that I have to convert the string to integer at some point). Thank you.
Code:
Apply Button
If Username.Text "" Then
CheckChPass()
If Password.Text.Length >= 8 And Email.Text.Length >= 9 Then
Dim UpdateCmd As String = "UPDATE T_User " & _
"SET PassWD='" & Password.Text & "', " & _
"ChPass='" & "1" & "' " & _
"WHERE UserID='" & Username.Text & "' " & _
"AND Email='" & Email.Text & "' "
If SQL.DataUpdate(UpdateCmd) = 0 Then
MsgBox("Invalid account information.", MsgBoxStyle.Critical, "Login failed!")
Else
MsgBox("Password sucessfully changed!", MsgBoxStyle.Information)
Username.Clear()
Password.Clear()
Email.Clear()
End If
Else
MsgBox("Please check the length of the new password.", MsgBoxStyle.Critical)
End If
Else
MsgBox("Please check Username and Email.", MsgBoxStyle.Information)
End If
------------------------------------------------------------------------------------------------------------------------------------------------------
CheckChPass
SQL.RunQuery("SELECT ChPass FROM T_User WHERE UserID='" & Username.Text & "' " & _
"AND Email='" & Email.Text & "' ")
If SQL.SQLDataset.Tables.Count > 0 Then
MsgBox("Already changed password once before!", MsgBoxStyle.Critical)
Username.Clear()
Password.Clear()
Email.Clear()
Exit Sub
Else
End If
+kevin inskeep Hello, Kevin. I'll try to assist with this today. :-)
+kevin inskeep First off, the CheckChPass is always going to return the message if there are no errors in the query, because you're counting the *Tables* returned. A successful query will always return a table even if no records were found. You could try changing this to SQL.SQLDataset.Tables(0).Rows.Count if you want to see if any matches were made.
That said, I strongly recommend using parameterized queries and commands. Also, we should be able to improve and simplify this quite a bit by using the newest version of the SQLControl class. If you'd like me to assist with this I'd be happy to email you.
That would be great. I just needed to know what the problem was and which direction to take towards fixing the issue. If you have the time to help me fix the code, then please do email me. I don't want to take up a lot of your time. Thank you.
Do you have a video on downloading and installing SQLServerCE? I am trying the make the transition from Access. Love the video by the way.
Actually, SQL Ce (v3.5) is included with the Visual Studio installation. If you want v4.0, you can download it [free] from Microsoft. There is also a second installer which makes SQLce 4.0 projects available in the project selection list.
One thing that I recently discovered is that VS 2013 no longer has SQL Ce, rather it has "LocalDB". It's similar in usage.
Hello
This is a great tutorial. I make it, but i have a problem.
I update the database and after thet i open the program and teh databse updated.
But when i open the database and open the table in the Visual studio program i couldn't find my data in the table. And after that i run the program and i couldn't find my data int the grib too. So its deleted. Why? What am i doing wrong? What am i going to do?
Thank you
(sorry about my language faults)
+Imre Romsics Hello, Imre. :-)
1.) Select your database file in your Solution Explorer
2.) In the Properties panel change "Copy to Output Directory" to "Copy if Newer"
This will prevent your debug database from being overwritten with the template each time you debug.
Note: If you make changes to the template database it will overwrite the debug database next time that you debug.
+VB Toolbox
Thank you your fast answer.
But in the database Properties "Copy to Output Directory" is already "Copy if Newer". So i think not its the problem.
Hi! I'm a very new vb user and I tried to run but i got an error in this line:
Params.ForEach(Sub(x) SQLCmd.Parameters.Add(x))
Error: Expression expected.
And Sub is underlined. Can someone help me?
Are you, by chance, using an older version of VB.NET [e.g., 2008]? I know that in versions prior to 2010 lambda expressions weren't a thing, in which case you'll just replace that with a standard *For Each* loop.
I also recommend checking out the newest SQL tutorials, as well. The SQLControl has been improved and optimized quite a bit since this old tutorial was made.
Thx, VBToolBox! Great tutorial!
Sorry, I have a problem
I use MySql and I have a error Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. This problem is caused by anything
Thanks a lot.
Hi sir,
I am using vb2008, and I tried to create as you said in the tutorial. But I am getting error in sub function
Public Sub ExecQuery(ByVal Query As String)
Try
SqlCon.Open()
'create sql commond
sqlcmd = New SqlCeCommand(Query, SqlCon)
'load parameters in to sql command
Params.ForEach (Sub(x) sqlCmd.Parameters.Add(x))
It is not allowing me to use Public Sub ExecQuery(Query As String)by default it takes ByVal
By this I am not able to use params.ForEach(Sub(x)
how to fix it?
ive been trying to save a label but it wont work it resets every time i push the load button?? this is my script:
load button:
lbl_cash.Text = My.Settings.Savecash
lbl_cps.Text = My.Settings.Savecps
Save button:
My.Settings.Savecash = lbl_cash.Text
My.Settings.Savecps = lbl_cps.Text
+Plaha After setting your My.Settings value, did you invoke My.Settings.Save()?
VB Toolbox ohh shit i forgot / stupid me haah
thanks so much for reminding me :D
+Plaha No worries, Plaha! :-D
+VB Toolbox Sorry about all of this but the problem seems to still be there, The label is still reseting when i hit the load button... Its supposed to be at 1.6 coins and it is but only for like 1 second and then back to zero
+Plaha Is your timer event overwriting your previously saved value?
Hi, thanks for a great tutorial. I followed step by step all of creation, but I have a problem. I create a database and insert 2-3 record. In datagrid I view only those records and not the empty one at the end, to be able to add another. Also It does not work even DEL to delete a record directly from the grid.
I followed step by step all programming lines ... what is the problem? Help please ....
+Job 3.14 Hi there. If you cannot see the empty row to add items to your DataGridView, simply check the DataGridView properties window and set *AllowUserToAddRows* to *True* . :-)
To delete, do the same thing for the *AllowUserToDeleteRows* property.
Finally, verify that the *ReadOnly* property is set to *False*
Many Many thanks for your Help !! Solved my problem ! I thought I had to act on the database and not on the grid ! Thanks a lot !
Hi! Your vids are very useful :) I just want to consult because I'm having a problem with the program I'm creating right now. I followed your vids how to Update a Database From a DataGridView (using SQLCEcommand) and how to Connect & Query a Microsoft Access Database in which you use OLEdbcommand.I'm just wondering if I could include the SQLCEcommands that you used in updating a Dbase to my program which are composed of OLEDBcommands. Do you think that's gonna be possible? If yes, can you teach me how and what should I change on my program to make it work. Totally appreciate your feedback. Thanks ;)
+Dana Patricia Cirot Hello, Dana. :-) The code is much the same for both SQLCE Command and OLEDB Command; However, this process is much more complicated with OLEDB because, unlike SQLCE, it is much more strict with DataType validation and you'll have to employ work-arounds to resolve the errors by enforcing the Data Types in your command parameters.
It took me a while to make this work, but I think that I may have some source code somewhere for this. I will have to try and find it.
+VB Toolbox hi thanks for the response. I would really appreciate if you'll be able to find the the source code you may have. Looking forward to that. Thanks :)
+Dana Patricia Cirot Here we go! :-) [Using my DBControl class with Access]
In a Form with a DataGridView [dgvUpdate]:
Private Sub LoadGrid()
Access.ExecQuery("SELECT username, password, email FROM members;")
dgvUpdate.DataSource = Access.DBDT
Dim builder As New OleDb.OleDbCommandBuilder(Access.DBDA)
builder.QuotePrefix = "["
builder.QuoteSuffix = "]"
Access.DBDA.UpdateCommand = builder.GetUpdateCommand
End Sub
For the Save button:
Private Sub cmdSave_Click(sender As System.Object, e As System.EventArgs) Handles cmdSave.Click
Access.DBDA.Update(Access.DBDT)
End Sub
I hope that helps!
+VB Toolbox thanks for this but I've been trying to use these code on my program but when I run it and try to modify the username directly on the datagrid (I have ticked the Enable editing by the way so I can edit it) then when I click save button I'm getting an error that says "update requires a valid UpdateCommand when passed Datarow collection with modified rows."
These are the codes that I have currently. Can you kindly check if I'm missing something or if there's something that needs to be corrected. I've been doing this for my thesis project.Hopefully I can get a feedback from you. Sorry for being so desperate I just really needed some help :) Here's the codes:
Public Class Form1
Private Access As New DBControl
Private Function NotEmpty(ByVal Text As String) As Boolean
Return Not String.IsNullOrEmpty(Text)
End Function
Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
'RUN A QUERY
Access.ExecQuery("SELECT * FROM table1 ORDER BY StudentName ASC")
If NotEmpty(Access.exception) Then MsgBox(Access.exception) : Exit Sub
'FILL DATAGRID
dgvdata.DataSource = Access.DBDT
'FILL COMBOBOX
For Each R As DataRow In Access.DBDT.Rows
cbxName.Items.Add(R("StudentName"))
Next
'DISPLAY FIRST NAME FOUND
If Access.recordcount > 0 Then cbxName.SelectedIndex = 0
End Sub
Private Sub SearchStudentName(ByVal Name As String)
'ADD PARAMETERS & RUN QUERY
Access.AddParam("@user","%" & Name & "%")
Access.ExecQuery("SELECT StudentName, Address,Guardian,Contacts,Course,Yearlevel,Type, Gender " & _
"FROM table1 " & _
"where StudentName LIKE @user")
'REPORT & ABORT ON ERRORS
If NotEmpty(Access.exception) Then MsgBox(Access.exception) : Exit Sub
'FILL COMBOBOX
dgvdata.DataSource = Access.DBDT
End Sub
Private Sub cmdFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFind.Click
SearchStudentName(txtfind.Text)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'EXECUTE QUERY AND POPULATE GRID
Access.ExecQuery("SELECT * FROM table1")
Loadgrid()
'DISABLE SAVE BUTTON
'btnsave.Enabled =
End Sub
Private Sub Loadgrid()
Access.ExecQuery("SELECT StudentName, Address,Guardian,Contacts,Course,Yearlevel,Type, Gender FROM table1")
dgvdata.DataSource = Access.DBDT
Dim builder As New OleDb.OleDbCommandBuilder(Access.DBDA)
builder.QuotePrefix = "["
builder.QuoteSuffix = "]"
Access.DBDA.UpdateCommand = builder.GetUpdateCommand
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Access.DBDA.Update(Access.DBDT)
'REFRESH GRID DATA
Loadgrid()
End Sub
End Class
Hi thanks for your video, sometimes I am getting this problem when updating(saving).
Concurrency violation: the UpdateCommand affected 0 of ...
Could you plz help, thanks
+Karim Gamal Hello, Karim! Which event are you using for your Update? Are you immediately refreshing your grid after saving?
What is the benefit of SQLCE? How would this be done without SQL CE?
The beauty of SQL Ce is that it's incredibly portable, self-contained, and requires no SQL Server installation. The database can be easily packaged with the project. It does have some limitations (max size [4GB] per .sdf, lack of stored procedures, max connections [32 (v3.5), 128? (4.0)].
As for the database updates, the only significant differences are going to be with the connection string and namespace calls. Everything else should be the same. :-)
Very nice! Ty for the video!
Hello,
Thanks for this great tutorial.
I learned a lot from it.
I have one problem however...
When I add a new row in the datagridview and the loadgrid is called it doesnt show the NEW ID.
How can I adjust this?
Thanks in advance!!!
+Johnny Hermes (Bodylojohn) The problem in this example is that LoadGrid is using the existing dataset that is populated from the Form_Load event, so it's not getting re-queried after the Update. Try rerunning SQL.ExecQuery() at the top of the LoadGrid sub to refresh the dataset and see if that works. :-)
Hello, is a very nice tutorial, i tried to download it with the link that you provided but it looks like the link is not working. Is it posible to download the examples on another webpage. Thanks.
Try this one. I think it's a bit newer and has the updated SQLControl. www.dropbox.com/s/5tpdsbxawcpwg71/SQLDGVUpdate.zip?dl=0
Thank you very much, you are the best :)
hello friend, remember me? i have a very important quest, how can i get a gridview to look like this one containing in this project? here is the dropbox link: www.dropbox.com/sh/3lp1slqdd2vzwa4/AACs_v8QfQlkINnHPpEnPzZOa?dl=0
Great Vidio - Really Helps. Thanks SOO much!
newby can I download this program or do I have to type it in?
Howdy, Keith. :-) For now you'll have to key it in along with the tutorial. I keep planning to make a source code repository for these videos, but can never seem to find the time. If you really need the source, I can probably track it down for you.
nice job man..here i can see you using compact edition but i am using ms sql server..can you help me with a tutorial video on that??
Hello! :-) With ADO.NET the syntax is nearly identical. The only changes are to the namespaces called and the connection string.
You only need to change the following:
Imports System.Data.SqlServerCe --> Imports System.Data.SqlClient
SqlCeConnection --> SqlConnection
SqlCeCommand --> SqlCommand
SqlCeDataAdapter --> SqlDataAdapter
SqlCeParameter --> SqlParameter
Make sure that you're using a correct Connection String for SqlConnection: www.connectionstrings.com/sql-server/
thanks...so nice of you..i will try it out and come back again...
im using vb.net 2010 with database Microsoft Access 2007. i want to edit then update the datagridview. how can i do that. the video that i watching is not finish. SIR PLEASE HELP ME. THANK YOU for Advance
naruto uzumaki Hello, Naruto. I'll be happy to help if I can. I need a little more clarification, though. Are you trying to Update Access from the DataGridView, or are you trying to Query Access to a DataGridView, or are you just trying to Refresh the DataGridView after updating Access?
thank you sir for your response . im trying to update access from the datagridview.
hello sir. can you help me with the code, for datagridview?
thank you, thank you, thank you
it really helped ! :D
Hello, im trying this with the Access DB, i use the db control code from another yours video. I have a module with a search procedure , this search load a datagrid , so far so good.
I need now to delete or change in a cell from grid something , i m trying to do like the video but....can you help me plz???
I thing the problem is when i have the parameters wich you say? in form wich have the grid? the grid loads from module
I write the code and the "try for update grid" in module , i tried in load form, but nothing
help plzzzz.....
Public Sub searchbeforeinsert(ByVal mysearchbeforeinsert As String)
Exception = ""
Try
' ADD Parameters & run query
Access.AddParams("@user", mysearchbeforeinsert)
' query
Access.ExecQuery("SELECT Products.ProductID As [Κωδικός Προιοντος],Products.CodeProduct As [Κωδικός Προιόντος],Colors.Color As Χρώμα,[Size].[Size] As Μέγεθος,Products.ProductName As [Ονομασια Προιοντος], ProductCategories.CategorieName, " & _
" Products.TotalStock As [Απόθεμα], Products.UnitPrice As [Τιμή Χονδρικής], Products.TelikiTimi As [Τιμή Λιανικής], Products.BarCode , Products.Photo " & _
"FROM (((Colors INNER JOIN Products ON Colors.ColorID = Products.ColorID) INNER JOIN [Size] ON Products.SizeID = [Size].SizeID) INNER JOIN " & _
"ProductCategories ON Products.CategorieID = ProductCategories.CategorieID) WHERE (Products.ProductID = @mysearchbeforeinsert)")
'report & abort on errors
If NoErrors(True) = False Then Exit Sub
' fill data grid with filter product categories
If Access.RecordCount > 0 Then
' OrderDetails.DataGridView1.DataSource = Access.DBds.Tables(0)
' Access.DBda.UpdateCommand = New OleDb.OleDbCommandBuilder(Access.DBda).GetUpdateCommand
For Each R As DataRow In Access.DBdt.Rows
OrderDetails.DataGridView2.Rows.Add(R(("Κωδικός Προιοντος").ToString), (R("Κωδικός Προιόντος").ToString), (R("Χρώμα").ToString), (R("Μέγεθος").ToString), (R("Τιμή Χονδρικής").ToString))
' try for update grid
OrderDetails.DataGridView1.DataSource = Access.DBds.Tables(0)
Access.DBda.UpdateCommand = New OleDb.OleDbCommandBuilder(Access.DBda).GetUpdateCommand
Next
End If
If Access.RecordCount = 0 Then
MsgBox("Δεν βρέθηκε η κατηγορία searchbeforeinsert " & OrderDetails.cmbcategorie.Text.ToString & " προιόντος που αναζητήσατε ")
End If
Catch ex As Exception
Exception = ex.Message
End Try
'OrderDetails.cmbcategorie.SelectedIndex = 0
End Sub
+Eletheria Kourteridou I have a sample project for updating MS Access from a DataGridView; However, when updating a Database from a DataGridView it is limited to single-table queries.
In your example, you are using a relational query [INNER JOIN] so it will not work properly. :-(
Sample Project Download:
www.dropbox.com/s/i7xqu6sjantdpdx/AccessDGVUpdate.zip?dl=0
+VB Toolbox Thanks again for your answer, any suggestion for this issue? maybe, if i read all records from grid, line by line and after update the records in db? some example? any idea?
Please a tutorial "VB.NET Database Tutorial - Update a Database From a DataGridView (Visual Basic .NET) " using an inner join query
Teufack Alain Unfortunately, joined table updates cannot be performed with the DGV command builder.
You will have to write a query manually and pass the desired DGV items through that targeting the table/s you wish to update.
@@VBToolbox please help to perform it. I am a beginer.
Sorry, I just found the problem:
In my select statement I forgot to include the primary key column.
hello sir. i am creating a application for database storing. i have database named database1.sdf and in that database there are 5 tables named Base_plate, Alloy, Bead_type, Difficulty_factor and price_Factor. i want to update all the table at the same time. while tring this i got the error in sqlcecommandbuilder i.e. "The DataAdapter.SelectCommand property needs to be initialized."
please help me out. here is my code..
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlServerCe
Public Class Admin
Dim update As New SqlCeDataAdapter
' sql connection strings
Dim SQLCon As String = "Data Source=Database1.sdf"
Dim sqlstr As String = "Select * from Base_Plate "
Dim sqlstr1 As String = "Select * from Alloy "
Dim sqlstr2 As String = "Select * from Bead_Factor "
Dim sqlstr3 As String = "Select * from Difficulty_Factor "
Dim sqlstr4 As String = "Select * from Price_Factor "
' sql variable of base
Dim adapter As New SqlCeDataAdapter(sqlstr, SQLCon)
Dim ds As New DataSet()
' sql variable of alloy
Dim adapter1 As New SqlCeDataAdapter(sqlstr1, SQLCon)
Dim ds1 As New DataSet()
' sql variable of bead
Dim adapter2 As New SqlCeDataAdapter(sqlstr2, SQLCon)
Dim ds2 As New DataSet()
'sql variable of difficulty
Dim adapter3 As New SqlCeDataAdapter(sqlstr3, SQLCon)
Dim ds3 As New DataSet()
'sql variable of price
Dim adapter4 As New SqlCeDataAdapter(sqlstr4, SQLCon)
Dim ds4 As New DataSet()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
update.Update(ds)
LoadGrid()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Visible = False
LoginForm1.Visible = True
End Sub
Private Sub Admin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
LoadGrid()
Button2.Enabled = False
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
End Sub
Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
Button2.Enabled = True
End Sub
Private Sub LoadGrid()
'************** base datagrid ********************
adapter.Fill(ds, "Base_Plate")
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.Rows(0).Selected = True
'***************** alloy datagrid *********************
adapter1.Fill(ds1, "Alloy")
DataGridView2.DataSource = ds1.Tables(0)
DataGridView2.Rows(0).Selected = True
'***************** bead datagrid *********************
adapter2.Fill(ds2, "Bead_Factor")
DataGridView3.DataSource = ds2.Tables(0)
DataGridView3.Rows(0).Selected = True
'***************** difficulty datagrid *********************
adapter3.Fill(ds3, "Difficulty_Factor")
DataGridView4.DataSource = ds3.Tables(0)
DataGridView4.Rows(0).Selected = True
'***************** Price datagrid *********************
adapter4.Fill(ds4, "Price_Factor")
DataGridView5.DataSource = ds4.Tables(0)
DataGridView5.Rows(0).Selected = True
update.UpdateCommand = New SqlServerCe.SqlCeCommandBuilder(update).GetUpdateCommand
End Sub
Thanx in advance. :)
Hi, your tutorial is great, I new in VB.NET and maybe you can help me, I need same work with Microsoft Access Database. Is possible??
+Rafael Carulla Absolutely, and the process is pretty much identical. :-)
I think I have an example project somewhere, but I will have to find it.
Thanks a lot I hope you find that, anyway I will try with this example
+Rafael Carulla I apologize for the slow update on this. Got very busy at work. I was unable to find my project, so I just made you a new one. I hope it helps!
PROJECT DOWNLOAD: www.dropbox.com/s/i7xqu6sjantdpdx/AccessDGVUpdate.zip?dl=0
Is that code can update multiple tables?
To my knowledge, you cannot update multiple tables from the same DataGridView. You can query multiple tables to a DataGridView, but not update. You'll need to create your own command routines to update them.
VB Toolbox thanks for the response sir.
Hey,
Can you make a Video where downloading a Patch and this showing in %? I hope you unterstand what I mean^^
I think that it depends upon your needs. Transferring file data could be done via HTTP/Web controls, or binary streams via TCP Sockets. In either case it could be quite a process. I haven't tried a sockets file transfer yet. Might be worth learning, for sure. I did do an experiment with image downloads via web controls, a while back. Seems like I did a tutorial on that once, but I can't remember for certain.
Aren't you Jacob Lewis from the other Blender Tutorial?? Anyways thanks a lot Dude.
Nope. Different guy, but I'm glad you pointed him out. Can always use more Blender tutorials! :-D
I'm "Aardaerimus" (my original channel).
thanks prof ,
I'm getting the following error: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. And it refers to this line of code.
sql.sqlda.UpdateCommand = New SqlClient.SqlCommandBuilder(sql.sqlda).GetUpdateCommand I'm using SQL Sever so I modified the part where you use the compact edition. Can you tell what I'm doing wrong?
Hello, Michael. Does your table have a Primary Key column? If so, try returning it to your DGV with your query. If not, consider adding a Primary Key to your table. Note: The values of that column must be unique. Most often, an ID column with an auto-incrementing number is used.
USE [dbname]
create table Food(RollNo int identity(100,1) primary key, food_name varchar(20),food_type varchar(20))
I also had the same problem, created a new table as above, and now works fine.