How to update a SQL Server Database?

[claire_bicknell]

I am trying to implement a feedback form which allows users to leave comments.

This information then needs to be stored in an SQL Server Database.

This is the code i currently have:

Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient


Public Class Leave_your_Comments
    Dim con As New SqlConnection(strConnection)
    Const strConnection As String = "Data Source=PC-CLAIRE;Initial Catalog=ShoppingCentre;Integrated Security=True"
    Dim cn As New SqlConnection()
    Dim CustomersDataSet As New DataSet()
    Dim da As SqlDataAdapter
    Dim dr As DataRow
    Dim cmdBuilder As SqlCommandBuilder

    Dim inc As Integer
    Dim MaxRows As Integer




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



        con.Open()

        da = New SqlDataAdapter("select * FROM dbo.Feedback", con)

        da.Fill(CustomersDataSet, "ShoppingCentre")

        MaxRows = CustomersDataSet.Tables("ShoppingCentre").Rows.Count()
        inc = -1

        con.Close()


    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strSQL As String = "SELECT Username, Comments from Feedback'"

        If inc <> -1 Then


            Dim dsNewRow As DataRow

            dsNewRow = CustomersDataSet.Tables("ShoppingCentre").NewRow()

            dsNewRow.Item("Username") = TextBox3.Text

            dsNewRow.Item("Comment") = TextBox4.Text
            dsNewRow.Item("Date") = DateTimePicker1.Text


            CustomersDataSet.Tables("Shopping Centre").Rows.Add(dsNewRow)

            da.Update(CustomersDataSet, "dbo.Feedback")

            MsgBox("New Record added to the Database")

            Button2.Enabled = False

            'btnAddNew.Enabled = True
            ' btnUpdate.Enabled = True
            ' btnDelete.Enabled = True

            'Me.Hide()
            'frmMainMenu.Visible = True
            'My.Computer.Audio.Play("click.wav", AudioPlayMode.Background)



        End If



    End Sub

The form loads up and i fill out the Username and comments and click the submit button (button2) and nothing happens. The message box doesn't even pops up.

Does anyone have an idea?

I am very very new to this so direct pointers would be extremely appreciated as I have been staring at this for hours and you guys seem to be my only hope.

Thanks

 

[jmcilhinney]

Wouldn't that suggest that 'inc' is -1? I see you set it to -1 and then whether it's not -1.

 

[claire_bicknell]

So what should it be set to?

I am so confused with this now. Much help needed and appreciated.

Thanks

 

[jmcilhinney]

So what should it be set to?

I have no idea because I have no idea what it's intended purpose is. There's no comments in your code and "inc" is not exactly a descriptive variable name.

 

[claire_bicknell]

I got this code from elsewhere. So I am unsure myself currently. I know, i am a pain.

Basically all i want this code to do is allow a user to enter their username and comment and by clicking a submit button, it will automatically be stored in an SQL Server database.

What changes can i make to this code to get it to do this?

 

[jmcilhinney]

If you just remove any references to that 'inc' variable you should probably find that it works.

 

[claire_bicknell]

Right,

I have no removed all references to "inc" and now it breaks here:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim strSQL As String = "SELECT Username, Comments from Feedback'"

        'If inc <> -1 Then

        ' Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = CustomersDataSet.Tables("ShoppingCentre").NewRow()

        dsNewRow.Item("Username") = TextBox3.Text

        dsNewRow.Item("Comment") = TextBox4.Text
        dsNewRow.Item("Date") = DateTimePicker1.Text


        CustomersDataSet.Tables("Shopping Centre").Rows.Add(dsNewRow)

        da.Update(CustomersDataSet, "ShoppingCentre")

        MsgBox("New Record added to the Database")

        Button2.Enabled = False

        'btnAddNew.Enabled = True
        ' btnUpdate.Enabled = True
        ' btnDelete.Enabled = True

        Me.Hide()
        Form1.Visible = True
        'My.Computer.Audio.Play("click.wav", AudioPlayMode.Background)



        ' End If



    End Sub

Particularly this line:

CustomersDataSet.Tables("Shopping Centre").Rows.Add(dsNewRow)

and the error reads:

"Object reference not set to an instance of an object."

Any clue? I am lost!

 

[jmcilhinney]

Tell me what you see:

        dsNewRow = CustomersDataSet.Tables("[COLOR="Blue"]ShoppingCentre[/COLOR]").NewRow()

        dsNewRow.Item("Username") = TextBox3.Text

        dsNewRow.Item("Comment") = TextBox4.Text
        dsNewRow.Item("Date") = DateTimePicker1.Text


        CustomersDataSet.Tables("[COLOR="Red"]Shopping Centre[/COLOR]").Rows.Add(dsNewRow)

        da.Update(CustomersDataSet, "[COLOR="Blue"]ShoppingCentre[/COLOR]")

 

[claire_bicknell]

I have copied your coding in:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim strSQL As String = "SELECT Username, Comments from Feedback'"

        dsNewRow = CustomersDataSet.Tables("ShoppingCentre").NewRow()

        dsNewRow.Item("Username") = TextBox3.Text

        dsNewRow.Item("Comment") = TextBox4.Text
        dsNewRow.Item("Date") = DateTimePicker1.Text


        CustomersDataSet.Tables("Shopping Centre").Rows.Add(dsNewRow)

        da.Update(CustomersDataSet, "ShoppingCentre")



    End Sub

When i run the program an error is returned (the same error as before i believe).

The error is with this line:

CustomersDataSet.Tables("Shopping Centre").Rows.Add(dsNewRow)

"NullReferenceException was unhandled
Object reference not set to an instance of an object."

Any clue?

 

[MattP]

Take a look again at the code he posted. Look at the sections he's color coded and find the difference.

 

[jmcilhinney]

I have copied your coding in

The code I posted is not MY code. It's YOUR code. I simply copied it and, as MattP suggested, highlighted the part that's the issue. Look at the blue bits and compare them to the red bit. Are they the same? If they're not then would that not explain the issue? You can't populate a table and give it a name and then expect to retrieve that same table using a different name.

 

[claire_bicknell]

Ok,

I have now made a correction to that second "Shopping Centre" and amended it to "ShoppingCentre".

When i run it i now get a completely different error. It highlights this line:

da.Update(CustomersDataSet, "ShoppingCentre")

and says:

"InvalidOperationException was unhandled.
Update requires a valid InsertCommand when passed DataRow collection with new rows."

Any clues?

 

[jmcilhinney]

You have provided the SQL code to SELECT data from the database but nowhere have you specified any SQL code to INSERT data into the database. That's what the CommandBuilder was for in the original code that you seem to have removed. The CommandBuilder builds the INSERT, DELETE and UPDATE commands for you so you don't have to do it yourself. Of course, there's nothing to say that you can't create them yourself and in some situations you have to.

Go to the MSDN Library and read about SqlDataAdapter class so you understand how it works. Examples are all well and good but if you don't know what a class is supposed to do in the first place then examples are only so much use. I'd suggest reading about the SqlCommandBuilder class too.

 

[claire_bicknell]

Right,

I have popped the commandbuilder back in and this is my code as it stands:

Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient


Public Class Leave_your_Comments
    Dim con As New SqlConnection(strConnection)
    Const strConnection As String = "Data Source=(local);Initial Catalog=ShoppingCentre;Integrated Security=True"

    Dim inc As Integer
    Dim MaxRows As Integer

    Dim cn As New SqlConnection()
    Dim ds As New DataSet()
    Dim da As SqlDataAdapter
    Dim sql As String





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

        con.Open()

        sql = "SELECT * FROM dbo.Feedback"



        da = New SqlDataAdapter(sql, con)

        da.Fill(ds, "ShoppingCentre")

        con.Close()

        MaxRows = ds.Tables("ShoppingCentre").Rows.Count()
        inc = -1




    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim strSQL As String = "SELECT Username, Comments from Feedback'"

        If inc <> -1 Then

            Dim cb As New SqlCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("ShoppingCentre").NewRow()

            dsNewRow.Item("Username") = TextBox3.Text

            dsNewRow.Item("Comment") = TextBox4.Text
            dsNewRow.Item("Date") = DateTimePicker1.Text


            ds.Tables("ShoppingCentre").Rows.Add(dsNewRow)

            da.Update(ds, "ShoppingCentre")

            MsgBox("New Record added to the Database")

            Button2.Enabled = False

            'btnAddNew.Enabled = True
            ' btnUpdate.Enabled = True
            ' btnDelete.Enabled = True

            Me.Hide()
            Form1.Visible = True
            'My.Computer.Audio.Play("click.wav", AudioPlayMode.Background)



        End If



    End Sub

I am completely befuzzled at the moment. At run time there is no error but when i click the submit button (button2) nothing happens. No error no nothing. I have checked my database and it is definitely not updating my table. What could be the problem?

I am sure it is only a small thing now.

Thanks for your continuing help.

 

[jmcilhinney]

You've still got your 'inc' variable in there. Didn't we already discuss that?