Inserting new record

[jemz]

Hi,I have problem on my code,it will not insert to the database can you pease help me on this.Thank you in advance.

Imports System.Data.SqlClient
Imports System.Data

Public Class Form1
    Private m_con As New SqlConnection
    Private m_DA As SqlDataAdapter
    Private m_CB As SqlCommandBuilder
    Private m_dataTable As New DataTable
    Private m_rowPosition As Integer = 0


    Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        m_con.Close()
        m_con.Dispose()

    End Sub

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

        m_con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\book.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        m_con.Open()

        m_DA = New SqlDataAdapter("select * from book", m_con)
        m_CB = New SqlCommandBuilder(m_DA)

        m_DA.Fill(m_dataTable)

    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Me.Close()

    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles      BtnAdd.Click


        m_DA.Fill(m_dataTable)
        Dim adNewRow As DataRow = m_dataTable.NewRow()

        adNewRow("FIRSTNAME") = txtname.Text
        adNewRow("LASTNAME") = txtlname.Text
        m_dataTable.Rows.Add(adNewRow)
        m_DA.Update(m_dataTable)

        m_rowPosition = m_dataTable.Rows.Count - 1

    End Sub
End Class

[G_Waddell]

Hi
This is because your datatable is held in the computers memory - it is not the database table rather a disconnected query result held there for you.

You must either run a command to update the SQL table and then refresh. Or update the Database table and then the datatable as you are doing.

[jemz]

@G_Waddell, Thank you for the reply...Sorry but i don't get what you say...can you please show me what you mean by update the SQL table and refresh...Thank you in advance.

[M.Waqas Aslam]

to insert record you can do this

dim con as new sqlconnection("connection string")
dim cmd as new sqlcommand()
con.open()
cmd.connection = con
cmd.commandtext="insert into table1 (field1,field2) values(@field1,@field2)"
cmd.parameter.addwithvalue("@field1",txtfield1.text)
cmd.parameter.addwithvalue("@field2",txtfield2.text)
cmd.executenonquery()
con.close()

Regards

[G_Waddell]

Datasetrs and datatables are held in your computers memory they are disconnected from the database so you can update them but when you look at the database table the cahnges are not there. You must run either an update or insert query on the database such as M.Waqas Aslam has done in his example.

To break it down you should do something like this:

1. Run an insert (or if modifying an existing record, update query on the Database)
2. Reload the data into your datatable

An alternative is to use the command builder to specify an Update or Insert command for your dataset and when you update the datatable it will run that query.

Sub RefeshData()
    m_dataTable = new Datatable
    m_con.Open()
    m_DA = New SqlDataAdapter("select * from book", m_con)
    m_DA.Fill(m_dataTable)
    m_con.close() 'because dataset etc are disconnected
End sub

sub AddData(ByRef Firstname as string, byref Lastname as string)
    m_con.Open()
    dim mCmd as new sqlcommand()
    mCmd.Connection = m_con
    mCmd.CommandType = CommandType.Text
    mCmd.Text ="Insert INTO book(Firstname, LastName) Values('" &Firstname &"','" &Lastname &"')"
    mCmd.ExecuteNonQuery()
    m_con.close()
    RefreshData()
End sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
m_con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\book.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
 RefreshData()
 End Sub

[jemz]

@G_Waddell,Hi thank you and it's woking now, by the way as i have seen many tutorials they use dataset when should i use that dataset,I am just new in this vb.net i hope you can help.THank you in advance.

[jemz]

@G_Waddell,Hi i tried to make a new forlder "MyDB" and i put it inside on it the book.mdf and when i executed it seems my connection is ivalid.How can i change my connection so that it will point to the folder where my database resides.THank you in advance.

[G_Waddell]

Hi
Did you try
m_con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDB\book.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

[jemz]

Okay i got it,,thank you so much.