Listen People... let me give you a simple and clear way to do this. With enough research, I have come to understand that there are several ways to insert data into a database table, be it MS Sql, Access, Oracle or the likes.
But right here, am going to show you just how to simply go about this function using VB.net 2008, 2010 or 2012 (which am actually using now).
INSERT RECORD INTO SQL EXPRESS FROM VB.NET
Introduction:
This is a concept every application developer has one time or the other worried about on just how to get it right. Trust me, if you try to google this information, you will get countless number of jargon that will want to make you go nuts. But what I want to do here is simply explain few concepts, then go straight to the real thingy, pardon my word. So, let’s ride.
CONCEPT EXPLANATION:
Database: This is especially what your whole application stands for. It is the warehouse (so to speak) that will hold all your application information, both client (i.e. those using the software from their systems) and server (i.e. the ever busy hardware or software-inclined machine that does all the data service for you) details are stored here. Big words huh? Don't worry... database is just an abstract component that stores the data your program will use.
Table: This is one item or component that makes up a database. it is mostly called an object. So, you are not wrong when you say a table is a database object. There are other objects like; views, Procedures, Synonyms, etc.
Query: This is the message or instruction or command you give to your database to get any information you want from any database object.
SqlCommand: This is the function or method in VB.net, you will use to write the query message you want to give to your database.
SqlConnection: This is the function that will hold the value or link that you will use to connect to the database in your system. (e.g. data source=(local)\SQLEXPRESS; initial catalog=database_table_name; Integrated Security=true). In fact, this is the simplest connection string you can use when you are developing directly on your system.
Imports System.Data.SqlClient: This is called a Namespace. A namespace is simply a general or public method that invokes other sub methods for a certain program function. What this means is, without namespaces, some functions in VB.net will be unavailable. (E.g. without the NameSpace above, you will not see functions like SqlCommand, SqlConnection, etc.). So the namespace above is used for sql databases; Microsoft Sql specifically. Microsoft Access name space is something like this: Imports System.Data.Odbc. Read books please!
Imports System.Data: This is another name space that generally works with data definition and manipulation. Just type it after the first namespace, It'll do its job.
NOTE: Namespaces always come at the beginning of every code document. So even if you have entered other codes, if you want to use a database function, you definitely need to go to the top of the code document and type the desired name spaces. You will see examples as we go on.
Now, Let's enter into what matters. Any concept that comes our way, we shall define as we move on.
And by the way, You'll need some basic SQL knowledge to work with database from VB.net. Commands like INSERT INTO table_name, etc. are SQL codes. So, I'll comment on any that may seem strange.
The Insertion Technique:
Follow the Instructions closely and you shall never go wrong.
'This is a comment, so any line you find this single quote ('), and maybe a green text, just no it's a comment line and should not be typed.
'As I explained earlier, right here in the code below, am declaring the namespaces for my own sql database functions. As a matter of fact, Visual Studio has what is called IntelliSence. This helps to suggest code completion for you even before you finish them. Am saying this for beginners purpose. So once you start typing Imports, it brings the expected text and all you need do is make your choice from the list (if any), 'move on. Cool huh?
Imports System.Data.SqlClient
Imports system.Data
'This part is important in any program. You need to do what is called declaration of variables. You declare every variable (i.e. a data container) you wish to use in your program. So in VB.net, you use the keyword Dim to declare as shown below.
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim constr As String
'Now, what are those junks above, you'll ask. They are declarations for your connection link to the database, command to use in querying the database, and the variable that will hold the connection text, respectively. As explained earlier in concepts definition.
'When you double-click any tool in VB.net, what is called event handler will be generated. So, In most cases, you do not need to type an event handler.
But sometimes, you may want to do so for advance functions or event procedures.
So, Private Sub ...() Handles .. End Sub is an auto generated statement.
You only type your desired codes in between this 'tatements.
Private Sub Add_Student_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'The next line of codes is the connection text, or popularly called connection string. That's why we created a variable called constr to hold this text.
By way of explanation, data source is the place where your sql database is stored. In our case, our SQL database is in our system. So, we say local. You can also replace local with; your computer_name or localhost. E.g localhost\SQLEXPRESS. initial catalog is the database you are working with. Mostly, your database will have more than one table.
Integrated Security is a security placement for your database, saying windows (i.e. the your operating system) should secure your database with its own authentication methods. If you choose a password for your database, you should specify it yourself like this (UserID=ChosenUserName; Password=ChosenPassword") and remove the integrated security.
constr = "data source=(local)\SQLEXPRESS; initial catalog=database_name integrated security=True"
But right here, am going to show you just how to simply go about this function using VB.net 2008, 2010 or 2012 (which am actually using now).
INSERT RECORD INTO SQL EXPRESS FROM VB.NET
Introduction:
This is a concept every application developer has one time or the other worried about on just how to get it right. Trust me, if you try to google this information, you will get countless number of jargon that will want to make you go nuts. But what I want to do here is simply explain few concepts, then go straight to the real thingy, pardon my word. So, let’s ride.
CONCEPT EXPLANATION:
Database: This is especially what your whole application stands for. It is the warehouse (so to speak) that will hold all your application information, both client (i.e. those using the software from their systems) and server (i.e. the ever busy hardware or software-inclined machine that does all the data service for you) details are stored here. Big words huh? Don't worry... database is just an abstract component that stores the data your program will use.
Table: This is one item or component that makes up a database. it is mostly called an object. So, you are not wrong when you say a table is a database object. There are other objects like; views, Procedures, Synonyms, etc.
Query: This is the message or instruction or command you give to your database to get any information you want from any database object.
SqlCommand: This is the function or method in VB.net, you will use to write the query message you want to give to your database.
SqlConnection: This is the function that will hold the value or link that you will use to connect to the database in your system. (e.g. data source=(local)\SQLEXPRESS; initial catalog=database_table_name; Integrated Security=true). In fact, this is the simplest connection string you can use when you are developing directly on your system.
Imports System.Data.SqlClient: This is called a Namespace. A namespace is simply a general or public method that invokes other sub methods for a certain program function. What this means is, without namespaces, some functions in VB.net will be unavailable. (E.g. without the NameSpace above, you will not see functions like SqlCommand, SqlConnection, etc.). So the namespace above is used for sql databases; Microsoft Sql specifically. Microsoft Access name space is something like this: Imports System.Data.Odbc. Read books please!
Imports System.Data: This is another name space that generally works with data definition and manipulation. Just type it after the first namespace, It'll do its job.
NOTE: Namespaces always come at the beginning of every code document. So even if you have entered other codes, if you want to use a database function, you definitely need to go to the top of the code document and type the desired name spaces. You will see examples as we go on.
Now, Let's enter into what matters. Any concept that comes our way, we shall define as we move on.
And by the way, You'll need some basic SQL knowledge to work with database from VB.net. Commands like INSERT INTO table_name, etc. are SQL codes. So, I'll comment on any that may seem strange.
The Insertion Technique:
Follow the Instructions closely and you shall never go wrong.
'This is a comment, so any line you find this single quote ('), and maybe a green text, just no it's a comment line and should not be typed.
'As I explained earlier, right here in the code below, am declaring the namespaces for my own sql database functions. As a matter of fact, Visual Studio has what is called IntelliSence. This helps to suggest code completion for you even before you finish them. Am saying this for beginners purpose. So once you start typing Imports, it brings the expected text and all you need do is make your choice from the list (if any), 'move on. Cool huh?
Imports System.Data.SqlClient
Imports system.Data
'This part is important in any program. You need to do what is called declaration of variables. You declare every variable (i.e. a data container) you wish to use in your program. So in VB.net, you use the keyword Dim to declare as shown below.
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim constr As String
'Now, what are those junks above, you'll ask. They are declarations for your connection link to the database, command to use in querying the database, and the variable that will hold the connection text, respectively. As explained earlier in concepts definition.
'When you double-click any tool in VB.net, what is called event handler will be generated. So, In most cases, you do not need to type an event handler.
But sometimes, you may want to do so for advance functions or event procedures.
So, Private Sub ...() Handles .. End Sub is an auto generated statement.
You only type your desired codes in between this 'tatements.
Private Sub Add_Student_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'The next line of codes is the connection text, or popularly called connection string. That's why we created a variable called constr to hold this text.
By way of explanation, data source is the place where your sql database is stored. In our case, our SQL database is in our system. So, we say local. You can also replace local with; your computer_name or localhost. E.g localhost\SQLEXPRESS. initial catalog is the database you are working with. Mostly, your database will have more than one table.
Integrated Security is a security placement for your database, saying windows (i.e. the your operating system) should secure your database with its own authentication methods. If you choose a password for your database, you should specify it yourself like this (UserID=ChosenUserName; Password=ChosenPassword") and remove the integrated security.
constr = "data source=(local)\SQLEXPRESS; initial catalog=database_name integrated security=True"
'You simply assign the connection text/string (i.e. constr) to the connection variable (i.e. con).
This is what we shall be working with henceforth.
con = New SqlConnection(constr)
'In programming and software development, it is a good practice to always consider all possibilities, especially errors. To this regard, you use the Try and Catch expression to do so. What this does, is simply working with the desired program function and looking out for errors as well.
It is more efficient and powerful than the conditional statements.
Try
'Below, you now open the connection you created earlier. If you don't open the connection, you are not doing anything then. Not opening the connection is like telling a maid to get you a bottle of milk from a locked fridge. Not possible, huh!
con.Open()
'This is just a confirmation that the connection you created worked.
MsgBox("Connection is Opened for you!", MsgBoxStyle.Information)
'This is where lots of people confuse other people. Though there area several ways to insert values, but this I find simple and vivid. This is where the knowledge of SQL comes handy.
You create an Insert statement that sends the values you type or select in your textbox, combobox, radio button, checkbox, date picker, etc., into the desired table in your database.
After the INSERT INTO table_name, you find some funny characters in parentheses, right?
Well, that's the syntax for insertion with this format.
How it works: You first have a single quote ('), then a double quote ("), then a plus or ampersand sign
(+ or &), then you type the object name with the expected property (textbox1.text, as the case may be), then you close all respective character, starting from the last one you typed
[which in this case is the plus sign (+)], then, you type your con variable.
com = New SqlCommand("Insert into table_name values('" + Textbox1.Text + "','" + Combobox1.Text + "')", con)
'This is vital when you don't want to get any result from your SQL query. Just add it whenever you are inserting records into a database table.
com.ExecuteNonQuery()
'And of course, this is a confirmation that you have succeeded.
As time goes by, I should get an article that explains how to code a msgbox. Its that simple.
MsgBox("I don add the record. You wan add again?")
'This is the Catch part of the Try statement. This part is generated when you type Try and press enter.
The msgbox is the error message that displays when any is encountered.
Don't worry about the content, that's Pigeon English, a Nigerian language.
Catch ex As Exception
MsgBox("Connection no gree open for you oo. You no get luck.", MsgBoxStyle.Critical)
'Just like the sample given above on fridge and milk. If the maid finally succeeds in opening the fridge and getting the bottle of milk, it is appropriate she close the fridge, right?
Well, you are also expected to close your connection after you are done.
Some curious minds might ask... aren't we going to need it open for regular data input? Good Question.
Know that, as far as you are still entering data, the truth of the matter is, it is still left open for you. You only close it to avoid overloading and over-stacking both the server and the dataset. crazy huh?
con.Close()
'The rest fellas are just closing tags or statements for their respective entries. Don't worry, they are auto generated as well. You won't need to type them.
End Try
End Sub
End Class
So, that's it. Now we are good to go.
I hope you will find this article very useful and timely, and I want to thank you for reading.
Rexyspy.
MsgBox("Connection is Opened for you!", MsgBoxStyle.Information)
'This is where lots of people confuse other people. Though there area several ways to insert values, but this I find simple and vivid. This is where the knowledge of SQL comes handy.
You create an Insert statement that sends the values you type or select in your textbox, combobox, radio button, checkbox, date picker, etc., into the desired table in your database.
After the INSERT INTO table_name, you find some funny characters in parentheses, right?
Well, that's the syntax for insertion with this format.
How it works: You first have a single quote ('), then a double quote ("), then a plus or ampersand sign
(+ or &), then you type the object name with the expected property (textbox1.text, as the case may be), then you close all respective character, starting from the last one you typed
[which in this case is the plus sign (+)], then, you type your con variable.
com = New SqlCommand("Insert into table_name values('" + Textbox1.Text + "','" + Combobox1.Text + "')", con)
'This is vital when you don't want to get any result from your SQL query. Just add it whenever you are inserting records into a database table.
com.ExecuteNonQuery()
'And of course, this is a confirmation that you have succeeded.
As time goes by, I should get an article that explains how to code a msgbox. Its that simple.
MsgBox("I don add the record. You wan add again?")
'This is the Catch part of the Try statement. This part is generated when you type Try and press enter.
The msgbox is the error message that displays when any is encountered.
Don't worry about the content, that's Pigeon English, a Nigerian language.
Catch ex As Exception
MsgBox("Connection no gree open for you oo. You no get luck.", MsgBoxStyle.Critical)
'Just like the sample given above on fridge and milk. If the maid finally succeeds in opening the fridge and getting the bottle of milk, it is appropriate she close the fridge, right?
Well, you are also expected to close your connection after you are done.
Some curious minds might ask... aren't we going to need it open for regular data input? Good Question.
Know that, as far as you are still entering data, the truth of the matter is, it is still left open for you. You only close it to avoid overloading and over-stacking both the server and the dataset. crazy huh?
con.Close()
'The rest fellas are just closing tags or statements for their respective entries. Don't worry, they are auto generated as well. You won't need to type them.
End Try
End Sub
End Class
So, that's it. Now we are good to go.
I hope you will find this article very useful and timely, and I want to thank you for reading.
Rexyspy.
I think its just wat i needed to solve the misery i have been in for a long time...
ReplyDeleteThank you Rexyspy
Am glad I could help...
ReplyDeleteAnytime.
Cheers!