How to update/delete/insert records to SQL server wit VB.NET?

How to update/delete/insert records to SQL server wit VB.NET?

Post by Dennis v/d Be » Mon, 08 Jul 2002 07:32:06



Hello,

I am totally lost on this side. I have read many articles with sample
code, but it doesn't seem to work. What I want is the following:

I have setup a page with a datagrid (page1.aspx) which lists my
records from my table (single table inserted in dataset). In the
colomn persoonID a link is present, which links to another page,
page2.aspx. In this page2.aspx I want to view only the selected record
(by persoonID via a QueryString in the URL) with the fields bound to
textboxes, labels and datalists. That works fine. So I fill a dataset
with only 1 record.
Before filling the dataset I create a cmdBuilder to create the
update/insert and delete commands dynamically. (I have also tried to
create update commands manually with parameters.Add, etc., but this
doesn't work either).

Now I want to make changes to the record and update these new values
to the SQL server by clicking the Button 'Update'. After the button is
clicked, the dataset seems to be lost (the variable daPersoon and
dsPersoon are not known anymore). I have debugged the code to check
this. The events which happens after clicking the update button are:
Page_Load -> Button Update_click.

I am looking for some help to solve my problem. I am also looking for
some sample code for the Insert/Delete commands by clicking the
delete/insert button.
I have included my sample code (variables are in Dutch, but that
doesn't matter)

I have also read http://www.gotdotnet.com/quickstart, the Data Access
section,
but update commands are done by datagrid, so it couldn't help me.
I want to have my textboxes, labels and dropdownlists.

Any help is welcome.

Thanks,

Dennis


    Public conn As SqlConnection
    Public strConn As String
    Public persoonID As Integer   'Querystring URL

    Public daPersoon As SqlDataAdapter
    Public dsPersoon As DataSet
    Public cmdSQL As SqlCommand
    Public cmdBuilder As SqlCommandBuilder

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        persoonID = CInt(Request.Params("persoon"))

        strConn = "Persist Security Info=True;User ID=sa;Initial
Catalog=DATABASE;Data Source=SERVER"
        conn = New SqlConnection(strConn)

        If Not IsPostBack Then
            getPersoon()
            bindData()

            'just for test
            DataGrid1.DataSource = dsPersoon
            DataGrid1.DataBind()
        End If
    End Sub

    Private Sub getPersoon()
        cmdSQL = New SqlCommand()
        If persoonID = 0 Then
            'just for test
            persoonID = 5
        End If
        cmdSQL.CommandText = "SELECT * FROM Persoon where persoonID =
" & persoonID

        daPersoon = New SqlDataAdapter(cmdSQL.CommandText, conn)
        dsPersoon = New DataSet()
        cmdBuilder = New SqlCommandBuilder(daPersoon)
        daPersoon.Fill(dsPersoon, "Persoon")
    End Sub
    Private Sub bindData()
        If Not IsDBNull(dsPersoon.Tables("Persoon").DefaultView(0).Row("persoonID"))
Then
            lblPersoonID.Text =
dsPersoon.Tables("Persoon").DefaultView(0).Row("persoonID")
        Else
            lblPersoonID.Text = ""
        End If

        If Not IsDBNull(dsPersoon.Tables("Persoon").DefaultView(0).Row("voornaam"))
Then
            txtVoornaam.Text =
dsPersoon.Tables("Persoon").DefaultView(0).Row("voornaam")
        Else
            txtVoornaam.Text = ""
        End If

        If Not IsDBNull(dsPersoon.Tables("Persoon").DefaultView(0).Row("achternaam"))
Then
            txtAchternaam.Text =
dsPersoon.Tables("Persoon").DefaultView(0).Row("achternaam")
        Else
            txtAchternaam.Text = ""
        End If
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click

        'Check for changes with the HasChanges method first.

        'The variabele dsPersoon is lost here
        If Not dsPersoon.HasChanges(DataRowState.Modified) Then Exit
Sub
        ' Create temporary DataSet variable.
        Dim updDataSet As DataSet
        Dim custCB As SqlCommandBuilder = New
SqlCommandBuilder(daPersoon)

        ' GetChanges for modified rows only.
        updDataSet = dsPersoon.GetChanges(DataRowState.Modified)
        ' Check the DataSet for errors.
        If updDataSet.HasErrors Then
            ' Insert code to resolve errors.

            Exit Sub
        End If

        ' After fixing errors, update the DBMS with the DataAdapter
        ' used to create the DataSet.

        ' daPersoon variable is not known anymore
        daPersoon.Update(updDataSet)
    End Sub

    Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Delete.Click

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button2.Click

    End Sub

 
 
 

How to update/delete/insert records to SQL server wit VB.NET?

Post by Bill » Tue, 09 Jul 2002 04:53:56


Dennis, I just finished a sample Web Application (that I'll be showing at
VSLive in September), that might do what you describe. I think the problem
could be that your DataSet is not persisted between calls to the ASPx page.
What I did to solve this was to save the one row DataSet in the Session
state
    Session("MyDataSet") = dsMyDataSet
In the UpdateButton_Click event, I restored the DataSet
    dsMyDataSet = Session("MyDataSet")

Perhaps this will help...

--
William (Bill) Vaughn
Author, Trainer, Mentor
Beta V Corporation
www.betav.com


> Hello,

> I am totally lost on this side. I have read many articles with sample
> code, but it doesn't seem to work. What I want is the following:

> I have setup a page with a datagrid (page1.aspx) which lists my
> records from my table (single table inserted in dataset). In the
> colomn persoonID a link is present, which links to another page,
> page2.aspx. In this page2.aspx I want to view only the selected record
> (by persoonID via a QueryString in the URL) with the fields bound to
> textboxes, labels and datalists. That works fine. So I fill a dataset
> with only 1 record.
> Before filling the dataset I create a cmdBuilder to create the
> update/insert and delete commands dynamically. (I have also tried to
> create update commands manually with parameters.Add, etc., but this
> doesn't work either).

> Now I want to make changes to the record and update these new values
> to the SQL server by clicking the Button 'Update'. After the button is
> clicked, the dataset seems to be lost (the variable daPersoon and
> dsPersoon are not known anymore). I have debugged the code to check
> this. The events which happens after clicking the update button are:
> Page_Load -> Button Update_click.

> I am looking for some help to solve my problem. I am also looking for
> some sample code for the Insert/Delete commands by clicking the
> delete/insert button.
> I have included my sample code (variables are in Dutch, but that
> doesn't matter)

> I have also read http://www.gotdotnet.com/quickstart, the Data Access
> section,
> but update commands are done by datagrid, so it couldn't help me.
> I want to have my textboxes, labels and dropdownlists.

> Any help is welcome.

> Thanks,

> Dennis


>     Public conn As SqlConnection
>     Public strConn As String
>     Public persoonID As Integer   'Querystring URL

>     Public daPersoon As SqlDataAdapter
>     Public dsPersoon As DataSet
>     Public cmdSQL As SqlCommand
>     Public cmdBuilder As SqlCommandBuilder

>     Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
>         persoonID = CInt(Request.Params("persoon"))

>         strConn = "Persist Security Info=True;User ID=sa;Initial
> Catalog=DATABASE;Data Source=SERVER"
>         conn = New SqlConnection(strConn)

>         If Not IsPostBack Then
>             getPersoon()
>             bindData()

>             'just for test
>             DataGrid1.DataSource = dsPersoon
>             DataGrid1.DataBind()
>         End If
>     End Sub

>     Private Sub getPersoon()
>         cmdSQL = New SqlCommand()
>         If persoonID = 0 Then
>             'just for test
>             persoonID = 5
>         End If
>         cmdSQL.CommandText = "SELECT * FROM Persoon where persoonID =
> " & persoonID

>         daPersoon = New SqlDataAdapter(cmdSQL.CommandText, conn)
>         dsPersoon = New DataSet()
>         cmdBuilder = New SqlCommandBuilder(daPersoon)
>         daPersoon.Fill(dsPersoon, "Persoon")
>     End Sub
>     Private Sub bindData()
>         If Not

IsDBNull(dsPersoon.Tables("Persoon").DefaultView(0).Row("persoonID"))
Quote:> Then
>             lblPersoonID.Text =
> dsPersoon.Tables("Persoon").DefaultView(0).Row("persoonID")
>         Else
>             lblPersoonID.Text = ""
>         End If

>         If Not

IsDBNull(dsPersoon.Tables("Persoon").DefaultView(0).Row("voornaam"))
Quote:> Then
>             txtVoornaam.Text =
> dsPersoon.Tables("Persoon").DefaultView(0).Row("voornaam")
>         Else
>             txtVoornaam.Text = ""
>         End If

>         If Not

IsDBNull(dsPersoon.Tables("Persoon").DefaultView(0).Row("achternaam"))

- Show quoted text -

Quote:> Then
>             txtAchternaam.Text =
> dsPersoon.Tables("Persoon").DefaultView(0).Row("achternaam")
>         Else
>             txtAchternaam.Text = ""
>         End If
>     End Sub

>     Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles btnUpdate.Click

>         'Check for changes with the HasChanges method first.

>         'The variabele dsPersoon is lost here
>         If Not dsPersoon.HasChanges(DataRowState.Modified) Then Exit
> Sub
>         ' Create temporary DataSet variable.
>         Dim updDataSet As DataSet
>         Dim custCB As SqlCommandBuilder = New
> SqlCommandBuilder(daPersoon)

>         ' GetChanges for modified rows only.
>         updDataSet = dsPersoon.GetChanges(DataRowState.Modified)
>         ' Check the DataSet for errors.
>         If updDataSet.HasErrors Then
>             ' Insert code to resolve errors.

>             Exit Sub
>         End If

>         ' After fixing errors, update the DBMS with the DataAdapter
>         ' used to create the DataSet.

>         ' daPersoon variable is not known anymore
>         daPersoon.Update(updDataSet)
>     End Sub

>     Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Delete.Click

>     End Sub

>     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles Button2.Click

>     End Sub


 
 
 

How to update/delete/insert records to SQL server wit VB.NET?

Post by Dennis v/d Be » Tue, 09 Jul 2002 14:52:38


William,

Thanks for your reply. I will try this solution. In the meantime I
have found a solution that works on my sample database for the delete
and update events. The insert event I have still to work out, but it
is the same principle. I have to reopen my dataset and assign the
textbox/labels and dropdownlist values to the dataset and then do the
update query. The delete query works with the ExecuteNonQuery methode.

Regards,

Dennis

The code for the button_update event is:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
        cmdSQL = New SqlCommand()
        cmdSQL.CommandText = "SELECT * FROM Persoon where persoonID =
" & persoonID
        daPersoon = New SqlDataAdapter(cmdSQL.CommandText, conn)
        dsPersoon = New DataSet()

daPersoon.SelectCommand.Connection)
        'Create and append the parameters for the Update command.

SqlDbType.VarChar, 15, "Voornaam"))

DataRowVersion.Current

SqlDbType.VarChar, 30, "Achternaam"))

DataRowVersion.Current

SqlDbType.Int, 4, "PersoonID"))

DataRowVersion.Original

        'Assign the SqlCommand to the UpdateCommand property of the
SqlDataAdapter.
        daPersoon.UpdateCommand = cmdBuilder

        daPersoon.Fill(dsPersoon, "Persoon")
        dsPersoon.Tables("Persoon").Rows(0)("Voornaam") =
txtVoornaam.Text
        dsPersoon.Tables("Persoon").Rows(0)("Voornaam") =
txtVoornaam.Text
        dsPersoon.Tables("Persoon").Rows(0)("Achternaam") =
txtAchternaam.Text
        daPersoon.Update(dsPersoon, "Persoon")
        conn.Close()
        'Refresh the test datagrid
        getPersoon()
        DataGrid1.DataSource = dsPersoon
        DataGrid1.DataBind()
End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnDelete.Click
        cmdSQL = New SqlCommand()
        cmdSQL.CommandText = "SELECT * FROM Persoon where persoonID =
" & persoonID
        daPersoon = New SqlDataAdapter(cmdSQL.CommandText, conn)
        dsPersoon = New DataSet()
        cmdBuilder = New SqlCommand("Delete from Persoon where
PersoonID = " & persoonID, conn)

        cmdBuilder.ExecuteNonQuery()
End sub


Quote:> Dennis, I just finished a sample Web Application (that I'll be showing at
> VSLive in September), that might do what you describe. I think the problem
> could be that your DataSet is not persisted between calls to the ASPx page.
> What I did to solve this was to save the one row DataSet in the Session
> state
>     Session("MyDataSet") = dsMyDataSet
> In the UpdateButton_Click event, I restored the DataSet
>     dsMyDataSet = Session("MyDataSet")

> Perhaps this will help...

> --
> William (Bill) Vaughn
> Author, Trainer, Mentor
> Beta V Corporation
> www.betav.com