Hi all!
I'm trying to learn to use databases. I created a table, but how can I programatically add rows to it, using Visual Basic? What do I need?
Thanks!
~Nic
Printable View
Hi all!
I'm trying to learn to use databases. I created a table, but how can I programatically add rows to it, using Visual Basic? What do I need?
Thanks!
~Nic
I'd say a tutorial, because there are at least three reasonably distinct ways you can do that and mixing them won't work so well. The way I would do it would be with raw SQL. I think there may be some discussion of that approach over in the DB FAQ section, but I'm not sure, and it's probably pretty old. What I like about this approach is that none of it is a black box. You do more work, to be sure, but not all that much more, and you know what's happening. It would look like this:
That's the basic outline, but there are some details missing. For one thing, if you are using Access then it would be an OleDBConnection or an ODBCConnection. If you are using SQL Server then it would be a SQLConnection. Other databases would probably use other things...or you could use the more generic interfaces.Code:Using cn As New someConnection(yourConnectionString)
Using cmd As Command = cn.CreateCommand
Try
'Various stuff.
Catch ex as Exception
'Do something here.
End Try
End Using
End Using
The Command object would be an OleDBCommand or SQLCommand, or whatever, as well. You can set the Command.CommandText property to the SQL you want to run, then run it. So, you could add an INSERT sql command to write to the DB, or a SELECT, or an UPDATE, or whatever. Alternatively, you could use the Command object as an argument to creating a Dataadapter which could read/write a datatable, which could be part of a dataset.
As you can see, there are a fair number of options, depending on the specifics you are trying to accomplish, but those are the basics. That code I showed can be found all over the place in my code, with few variations. There may be a further nested Using block for a dataadapter, or a datareader, but it's pretty much like that.
You can also see why a tutorial on that might be good, because it's not a HUGE problem space, but there are several considerations about how best to go about various things.
The most common approach that you tend to see these days uses datasources and the datasource wizard. This might result in tableadapters and strongly typed datatables and datasets. The intention here was to create something that was more drag-and-drop and hide the messiness. I've used that approach, and I do see the advantages. You can get a lot done with a relatively short learning curve. However, there IS a learning curve, and if you get into odd things it can become quite difficult. The same basic code is being generated for this as for the raw approach, but there is a LOT more work being done to create strongly typed datatables (the typical datatable doesn't know or care what is in a column (though the database sure does), a strongly typed version knows what it can accept, so the code can enforce it).
The third approach would be Entity Framework, which can create the database, create connections, and do all kinds of stuff. I haven't looked at that since early on. It's yet another layer of trying to get you going fast and hide the messiness. I'm not comfortable with that.
So, there are different approaches with different strengths and weaknesses. My opinion is that the raw approach is the way to go, but others might reasonably disagree. Therefore, finding a tutorial might be the best way to go for you. Entity Framework has books written about it, the database wizard will have tutorials, the raw approach should have some, too. Take your pick and go with it.
We need to know a lot more before we can answer the question, and your best bet is to find a tutorial. You should plan to spend 5 or 6 hours studying SQL and that tutorial before being effective, there's really no "easy" way.
For example, what database are you using? Each one uses its own kind of connection string. Some require you to make a network connection, which may involve mucking with your firewall or some other configuration settings. You're not out of the water then. You have to decide how you want to interact with the database. If you paid a lot of money, VS has tools that let you "zero code" build Entity Framework stuff. But from what I've read, it takes a few days of experimentation to learn how to use those "zero code" tools. Or, you could use Entity Framework without the tools. You could use ADO .NET's Command class. You could use ADO .NET's DataAdapter class. There's five or six open-sourced versions of Entity Framework you could use. Or maybe you're using an object/document database like MongoDB and none of that applies. All of these things require you to read and (roughly) memorize about 10-15 pages worth of material before "Hello World" makes any sense.
I've made a series of posts starting here that walks through the "ADO .NET Command class" technique, starting with a brief discussion of SQL. It uses the SQLite database engine, if you are using something else there will be slight differences. I don't think I'm writing the best tutorial there is, but I do try to go only as deep into any topic as you need to get started.
Hi Shaggy Hiker and Sitten Spynne! Thanks for your informative and quick replies. So I believe I'm using SQL.
http://www.dreamincode.net/forums/to...cess-database/
http://www.visual-basic-tutorials.co...FromAccess.htm
According to these websites, it looks like I need a data provider, file path, a connection string, and a data reader. I checked the properties of the database I had and it looks like everything I need is right here, unless I'm incorrect. (Refer to image below.)
Last thing, it does look complicated, but also it looks like all there is to it is executing SQL commands in Visual Basic.
Attachment 138731
Now... how to put this all together. First thing, is all the information in the database properties menu all correct for what I need? (Is DB1's connection string the exact one I would use to add data to the table?)
Also, what is Entity Framework and why do I need it?
You don't NEED Entity Framework. It's a total alternative means of working with a database. Essentially, you are going to be either all EF or no EF, and I favor the latter.
I can't see the whole connection string in that picture, but the first part looks right. Here's a site for connection strings of all sorts:
https://www.connectionstrings.com/
The one you have probably looks a whole lot like one of those.
Both of the links you have show what I was calling the raw approach, which I favor. Both have some dubious practices, though. In the first link, there is no error handling. Database interactions are a situation where using exception handling is a really good idea. Things can go wrong. Once you have them working, they tend not to go wrong as much, but they can still go wrong. Depending on the database, you might have it locked when you try to access it, or it might be otherwise unavailable, both of which will throw exceptions when you attempt to open the connection. Also, the objects are not properly disposed. That doesn't matter so much, since they are local, but it might. The Using constructs will always properly clean up the using object (I showed a connection and a command object as the using objects), regardless of whether or not an exception occurs, even if the Using block is entirely within the Try...Catch. So, the way that example has it isn't wrong, it just isn't ideal.
The second example puts the contents of textboxes straight into the fields of a datarow. That sometimes works fine, but what if the textbox is empty and the field requires an integer? You'd end up with "" in the cell, which would result in an invalid conversion....eventually. A datatable like that won't care, because it holds type Object, which can be anything. The problem will arise when you try to send that to the database.
The weird thing about the second example is the fact that he creates local CommandBuilder objects. Those are kind of cool. With some limitations, if you supply a SELECT command to a dataadapter, then create a CommandBuilder object as shown in the code, the CommandBuilder will create the UPDATE/INSERT/DELETE statements automatically. That's really convenient....except that the code doesn't update the database, so there is no use for the CommandBuilder objects.
Sorry, I didn't have time to read your full message, but I will asap.
I just needed to post this quick question: Why doesn't this work to add a row? I think it does work, but it deletes it after the application is closed.
Code:DB1DataSet.TAB_Data.AddTAB_DataRow(fName, lName, Now)
My guess is, after adding the row, you didn't call the DataSet's AcceptChanges() method and you didn't call the DataAdapter's Update() method, passing it the DataSet.