Adding records to a database

Question

Hey

I'm a noob at VB and it's my first semester with VB. I need help with adding a record to a database.

I have a database with multiple tables. How do I add a new record to one of the tables without using the BindingNavigator.

Lets say the table's name is tblDetails and the fields are:

• Name
• Surname
• Cell
• Address

I have a number of text boxes on my form:

• txtName
• txtSurname
• txtCell
• txtAddress

I want to add the details from the text files to the table and a new record.

Could anyone out there please help me. I can't find a single resource on the web that explains how.

Thanks a lot!
Ryan

Answer 1

 You might try the following

How to insert record in sql server from VB

For this one you need to have the connection with sql server
( if you haven't done so then brows the blog you will get to know how you can create a connection to sql server database as well and then try inserting the record)

---

Prateek Regmi Blog for Programmers

Answer 2

Ryan Michael James said:
 How do I add a new record to one of the tables without using the BindingNavigator.

Lets say the table's name is tblDetails and the fields are:

• Name
• Surname
• Cell
• Address

I have a number of text boxes on my form:

• txtName
• txtSurname
• txtCell
• txtAddress

I want to add the details from the text files to the table and a new record.

Thank you Prateek for your friendly help.

Hi Ryan,

Welcome to MSDN forums!

You can add a new record (from TextBox's Text) to database table via two approaches:

I take a MS Access database file D:\myDB.mdb for example and presume these fields (Name, Surname, Cell, Address) are of Text data type.

1. Directly execute Insert T-SQL commands in VB.NET code

Prerequisites: Drag&drop four TextBoxes (txtName, txtSurname, txtCell, txtAddress) and Button1 onto Form1.

Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")

Dim cmd As OleDbCommand

cmd = New OleDbCommand("Insert Into tblDetails(Name, Surname, Cell, Address) Values(@Name, @Surname, @Cell, @Address)", con)

With cmd.Parameters

.Add("@Name", OleDbType.VarChar).Value = txtName.Text

.Add("@Surname", OleDbType.VarChar).Value = txtSurname.Text

.Add("@Cell", OleDbType.VarChar).Value = txtCell.Text

.Add("@Address", OleDbType.VarChar).Value = txtAddress.Text

End With

con.Open()

cmd.ExecuteNonQuery()

End Sub

End Class

   Code sample: How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
   http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/
   Code sample: How to Select/Insert/Delete/Update records in MS Access database in VB.NET
   http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/

2. Update/Insert new record to database table via DataAdapter/CommandBuilder/DataTable objects. 
    CommandBuilder object requires a primary key in your database table tblDetails. e.g. set Name field as Primary key.

Prerequisites: Drag&drop four TextBoxes (txtName, txtSurname, txtCell, txtAddress), DataGridView and Button1 onto Form1.

Imports System.Data.OleDb

Public Class Form1

' Share variables

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")

Dim cmd As OleDbCommand

Dim myAdapter As New OleDbDataAdapter

Dim comBuilder As OleDbCommandBuilder

Dim ds As DataSet

'Display all table records on DataGirdView when opening form

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

ShowData()

End Sub

Public Sub ShowData()

myAdapter.SelectCommand = New OleDbCommand("SELECT * FROM tblDetails", con)

comBuilder = New OleDbCommandBuilder(myAdapter)

If con.State = ConnectionState.Closed Then con.Open()

ds = New DataSet()

myAdapter.Fill(ds, "tblDetails")

DataGridView1.DataSource = ds.Tables("tblDetails")

con.Close()

End Sub

'

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Insert new records into database table

ds.Tables("tblDetails").Rows.Add()

Dim lastRow As Integer = ds.Tables("tblDetails").Rows.Count - 1

Dim myrow As Data.DataRow = ds.Tables("tblDetails").Rows.Item(lastRow)

If txtName.Text.Trim = "" Then

MessageBox.Show("Please input required information!")

TextBox1.Focus()

Else

myrow.Item("Name") = txtName.Text

myrow.Item("Surname") = txtSurname.Text

myrow.Item("Cell") = txtCell.Text

myrow.Item("Address") = txtAddress.Text

ds.Tables("tblDetails").GetChanges()

comBuilder.GetInsertCommand()

myAdapter.Update(ds, "tblDetails")

' Then you can clear all TextBoxes

txtName.Text = ""

txtSurname.Text = ""

txtCell.Text = ""

txtAddress.Text = ""

'Display all table records on DataGirdView after inserting new record

ShowData()

End If

End Sub

End Class

Reference:
Four methods to making simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save) in VB.NET.
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/d504018e-e8b0-4b0a-a3b4-dfbeec1f682b.


Best regards,
Martin Xie

Answer 3

This helped me a lot... Thanks!
Boss

Answer 4

This actually errrored when I used it. I am assuming that it is trying to add a row when the current row is blank

ds.Tables(

"tblProduction").Rows.Add()

Column 'Identification number' does not allow nulls.

How would I fix this?

Answer 5

Martin, can you tell me why this didn't work when I tried it?

Because I have 32 fields to put in, I would like to do it the second way you described but it keeps coming up with an error.

Thanks,

Arg.