Insert into syntax
The use of the Insert Into statement syntax is as follows:
INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])
Where is the explanation for each section:
1. Target ==> Table name where the record will be added.
2. field1, field2 ==> Used if you want to bring up all of the fields in the table
3. value1, value2 ==> Value to be added in accordance with the criteria field, any value added should follow the position of the fields in the list, you should separate values with a comma, and the flanking fields is of type text and date with a single quote (').
For more details concern the following example, suppose we have a table which we named "TestTable", consists of the following fields:
Then, based on the example above table, the command to add data to the table "TestTable" is as follows:
“INSERT INTO TestTable Values(‘E.2001.00004’,’Jonathan’,’Banana street’,30,’ 15/05/2001’,True)”
The above command is used when we do not want to input the name of the fields that exist in the table "TestTable", if we want to input the field name, then the command is as follows:
"INSERT INTO TestTable (Id, EmployeeName, EmployeeAddress, EmployeeAge, JoinDate, Certificate) Values ('E.2001.00004', 'Jonathan', 'Banana street', 30, '5/15/2001', True)"
Note the value E.2001.00004, Jonathan, Bananastreet And 05/15/2001 flanked by single quotes, because these values are of type string and date in fieldnya respectively, while the value of 30, and True is not enclosed in quotation one, because the values Number and type 'Yes / No in its fields respectively.
Well, the question now is when we should mention field name when using insert into command?
The answer is, if we do not want to add data to all fields in a table in the database and if the table that we want to add records / data are manifold autonumber field, so if there are manifold AutoNumber field in the table, then we have to use the command insert into with each of the fields mentioned in the table and "does not include" AutoNumber type field name in question.
Insert Into Statement Example Source Program.
Taking the previous example of the source of the application, add some control textbox, label, button and change the datetimepicker and textnya properties in accordance with the following design:
Then double click the insert button, and type the following command:
Private Sub BtnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnInsert.Click
Dim vAdd As String = "INSERT INTO TestTable(Id,EmployeeName,EmployeeAddress,EmployeeAge,JoinDate,Certificate) Values('" & TxtEmployeeId.Text & "','" & _
TxtEmployeeName.Text & "','" & TxtEmployeeAddress.Text & "'," & TxtEmployeeAge.Text & ",'" & DtJoin.Text & "'," & Cmb_Certificate.Text & ")"
Using Conn As New OleDbConnection(ConnStr)
Conn.Open()
Using Cmd As New OleDbCommand(vAdd, Conn)
Cmd.ExecuteNonQuery()
Cmd.CommandType = CommandType.Text
MsgBox("Record Added !", MsgBoxStyle.Information, "Insert Record")
TxtEmployeeId.Text = "" : TxtEmployeeName.Text = "" : TxtEmployeeAddress.Text = ""
TxtEmployeeAge.Text = "" : Cmb_Certificate.Text = ""
End Using
End Using
End Sub
After completion, try to run your project by pressing the F5 key, then enter the value you want to add to the table, and then click the Insert button, if there is no error message will be a message box that confirmed "Record Added" indicating that the record has been added to the table.
Sign up here with your email