locked
Why is my SQL function not working in Visual Basic 2010? RRS feed

  • Question

  • My project involves working with a database in a datagridview. The function below is the function for adding new entries to the database, with the fields for the new entry being located in text boxes.

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

                Dim sn As Integer
                Dim srn As String
                Dim first As String
                Dim hr As String
                Dim avg As Integer

                sn = Val(txtSN.Text)
                srn = txtSRN.Text
                first = txtFN.Text
                hr = txtHR.Text
                avg = Val(txtAV.Text)

                Dim strsql As String
                strsql = "INSERT INTO Table1 VALUES (@sn,@srn,@first,@hr,@avg)"
                Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
                Dim command As New SqlCommand(strsql, connection)


                Dim prmsn As New SqlParameter("@sn", SqlDbType.Int)
                prmsn.Value = CType(sn, Integer)

                Dim prmsrn As New SqlParameter("@srn", SqlDbType.NChar)
                prmsrn.Value = srn

                Dim prmfirst As New SqlParameter("@first", SqlDbType.NChar)
                prmfirst.Value = first

                Dim prmhr As New SqlParameter("@hr", SqlDbType.NChar)
                prmhr.Value = hr

                Dim prmavg As New SqlParameter("@avg", SqlDbType.Int)
                prmavg.Value = CType(avg, Integer)

                command.Parameters.Add(prmsn)
                command.Parameters.Add(prmsrn)
                command.Parameters.Add(prmfirst)
                command.Parameters.Add(prmhr)
                command.Parameters.Add(prmavg)

                connection.Open()

                Using (connection)
                    command.ExecuteNonQuery()
                    connection.Close()
                End Using

                Try
                    Me.Validate()
                    Me.Table1BindingSource.EndEdit()
                    Me.Table1TableAdapter.Update(Me.Database1DataSet.Table1)
                    MsgBox("Update successful")

                Catch ex As Exception
                    MsgBox("Update failed")
                End Try

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

            DataGridView1.Update()
            DataGridView1.Refresh()

        End Sub

    The results are very weird when this code is used. First off, the datagridview does not immediately add the new entry to it. However, the next time the program is run, the new entry appears. However, the new entry does not actually become part of the database (when I check the table, the entries are never there) but they do appear on the DataGridView. Usually after a few runs of the program, the entries that were inputted disappear completely.

    I do not know what the problem with this code is. Can anyone point me in the right direction?

    Monday, February 13, 2012 11:26 PM

Answers

  •       Table1BindingSource.AddNew()
          Dim Row As database1DataSet.Table1Row = CType(CType(Table1BindingSource.Current, DataRowView).Row, database1DataSet.Table1Row)
          Row.Column1 = 1
          Row.Column2 = "some data"
          ' etc
          Table1BindingSource.EndEdit()
          Table1TableAdapter.Update(Database1DataSet.Table1)
    

    • Proposed as answer by Cor Ligthert Tuesday, February 14, 2012 6:55 AM
    • Marked as answer by Mark Liu-lxf Wednesday, February 22, 2012 5:51 AM
    Tuesday, February 14, 2012 1:07 AM
  • Hi PRS99,

    Welcome to the MSDN forum.

    >> However, my project requires that I use SQL for all my functions.

    My understanding is that you need to all the functions with SQL Query. If you want to refresh data of the DataGridView after inserting the data into database, you can add a refresh method in your project. The refresh method is used to reloading the date form database to datagridview (using SQL Query). You also can use it in the other methods, such as delete method and update method. I think it is easy for you to create a refresh method. :)

    If I misunderstood or you have any additional questions, please feel free to let me know.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Mark Liu-lxf Wednesday, February 22, 2012 5:51 AM
    Wednesday, February 15, 2012 8:35 AM

All replies

  • Your code is adding the new record directly to the database, not to the dataset to which the datagridview is bound.  So there is no reason why the datagridview would display the new row.

    Try adding a new record to the bindingsource instead.

    If the new record appears in the datagridview the next time you run the record then it must have been added to the database correctly.  Are you using the Database Explorer to determine whether or not it has been added.  If so you should be aware that there are two databases, one in the project directory which the Database Explorer looks at and a copy in the bin\debug directory which the program works with.

    Check the .xsd file properties to see how the CopyToOutputDirectory is set.  From what you say it is probably set to Copy if Newer.  This means the copy in the project directory will overwrite the working copy if it is newer.  It can be marked newer simply by looking at it so this setting is not advised.  Setting it to Do Not Copy will prevent this but then any schema changes you may make will have to copied to the bin\debug directory manually.

    Monday, February 13, 2012 11:53 PM
  • What line(s) of code would I need to write to add the record to the bindingsource?
    Tuesday, February 14, 2012 12:38 AM
  •       Table1BindingSource.AddNew()
          Dim Row As database1DataSet.Table1Row = CType(CType(Table1BindingSource.Current, DataRowView).Row, database1DataSet.Table1Row)
          Row.Column1 = 1
          Row.Column2 = "some data"
          ' etc
          Table1BindingSource.EndEdit()
          Table1TableAdapter.Update(Database1DataSet.Table1)
    

    • Proposed as answer by Cor Ligthert Tuesday, February 14, 2012 6:55 AM
    • Marked as answer by Mark Liu-lxf Wednesday, February 22, 2012 5:51 AM
    Tuesday, February 14, 2012 1:07 AM
  • Hello PRS99

    Try this

    Me.Table1BindingSource.AddNew() Dim NewTable1Row As Table1DataSet.Table1Row NewTable1Row = CType(CType(Me.Table1BindingSource.Current, DataRowView).Row, Table1DataSet.Table1Row)

    NewTable1Row.FieldName1 = 1

    NewTable1Row.FieldName2 = "Student"

    Or

    NewTable1Row.Item("Student") = "PRS99"

    Me.Validate()

    Me.Table1BindingSource.EndEdit()

    Me.Table1TableAdapter.Update(Me.Table1DataSet.Table1)

    ok

    Tuesday, February 14, 2012 6:36 AM
  • I changed my function. After the

                Using (connection)
                    command.ExecuteNonQuery()
                    connection.Close()
                End Using

    Section I added the code you specified. The entries are added immediately to the datagridview but again they do not appear on the database table itself. The entry appears once at first, but the second time running the program, the entry is duplicated and there are two of it.

    Also, what would a function for deleting a row look like? I tried Table1BindingSource.Remove(Row) where Row is specified, but it does not disappear until the next time I run the program, and it also does not appear in the original database.


    Tuesday, February 14, 2012 4:39 PM
  • Also, what would a function for updating records look like?
    Tuesday, February 14, 2012 4:59 PM
  • I changed my function. After the

                Using (connection)
                    command.ExecuteNonQuery()
                    connection.Close()
                End Using

    Section I added the code you specified. The entries are added immediately to the datagridview but again they do not appear on the database table itself. The entry appears once at first, but the second time running the program, the entry is duplicated and there are two of it.

    Well of course it adds two copies - you still have your code adding a copy.

    Also, what would a function for deleting a row look like?

    Select the row in the datagridview, press Delete and then click the Save button.

    Also, what would a function for updating records look like?

    Calling the TableAdapter Update method saves any changes you may have made to existing rows.

    In my first post I explained how you can get confused because there are two copies of the database.  Have you read and understood what I said there - you have given no indication of how your program is set up.

    Rather than write lots of code as you are doing to add entries to the database it would be far easier to simply bind your textboxes to the same bindingsource as your datagridview.  Then all you have to do is enter the data, call EndEdit on the bindingsource and call the TableAdapters Update method.


    • Edited by Dave299 Tuesday, February 14, 2012 6:28 PM
    Tuesday, February 14, 2012 6:15 PM
  • The method you suggest is making sense to me. However, my project requires that I use SQL for all my functions.
    Tuesday, February 14, 2012 10:55 PM
  • Then this is Homework?

    Renee


    "MODERN PROGRAMMING is deficient in elementary ways BECAUSE of problems INTRODUCED by MODERN PROGRAMMING." Me

    Tuesday, February 14, 2012 11:11 PM
  • Hi PRS99,

    Welcome to the MSDN forum.

    >> However, my project requires that I use SQL for all my functions.

    My understanding is that you need to all the functions with SQL Query. If you want to refresh data of the DataGridView after inserting the data into database, you can add a refresh method in your project. The refresh method is used to reloading the date form database to datagridview (using SQL Query). You also can use it in the other methods, such as delete method and update method. I think it is easy for you to create a refresh method. :)

    If I misunderstood or you have any additional questions, please feel free to let me know.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Mark Liu-lxf Wednesday, February 22, 2012 5:51 AM
    Wednesday, February 15, 2012 8:35 AM
  • Hi PRS99,

    We haven’t heard from you for several days. I’d like to mark the helpful replies as answer firstly. If you have any additional questions, you also can unmark the replay and post your question here. 

    Sorry for any inconvenience and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 5:51 AM