none
cmd.ExecuteNonQuery keeps on adding vb.net RRS feed

  • Question

  • I have a DataGridView in my winform, my connection string is

        dim con as new sqlconnection("Data source=" & My.Settings.sqlserver & ", " & My.Settings.sqlport & ";Network Library=DBMSSOCN;initial catalog=" & My.Settings.dbname & ";User id=" & My.Settings.Username & ";Password=" & My.Settings.Password & ";")

    i have a button to insert data in the sql db. the code is

        dim cmd as new sqlcommand("insert into db(Firstname) values(@fname)")
        con.opn
        cmd.parametres.add("@fname",sqldbtype.varchar).value=txt.text
        cmd.executenonquery
        con.close
        end sub

    This code works fine, but the DataGridView doesn't update/refresh itself unless i restart the app. So I added this code

        Private sub btn_click
            dim cmd as new sqlcommand("insert into db(Firstname) 
            values(@fname)") con.opn 
            `cmd.parametres.add("@fname",sqldbtype.varchar).value=txt.text
            if cmd.executenonquery() =1 then
            dim cmd as new sqlcommand("Select *from contacts")
            dim adapter as new sqldataadapter
            dim table as datatable
            apater.fill(table)
            dgvw.datasource=table
            con.close
        end sub

    `

    This code is also added on form_load.This code refreshes/updates the dgvw but the problem is, suppose in my textbox , i type "abcde", then it inserts multiple data in the sql db which look like

    "a"(it adds the first data to the db table)
    "ab"
    "abc"
    and so on!!
    What am i doing wrong ? how should i fix this ?? And one last thing, if i remove the adaper.fil and other codes in the cmd.executenonquery if statement, it adds only one data to table as expected! Please help!

    screensht : [link1]https://drive.google.com/open?id=0B9Q3-Og5WXqYOWFMaGRBT2tGRVk

    [link2]https://drive.google.com/open?id=0B9Q3-Og5WXqYbEV2YUVSdW1kWkU
    Saturday, October 14, 2017 2:21 PM

All replies

  • The basics are as follows in the example below.

    I pass in a company name via pCompanyName, pNewIdentifier will contain the new primary key.

    Usage where the new data row is know, if the row was not created yet then create one, set field values and add it to the data source (assuming a DataTable) to the DataGridView e.g. CType(SomeDataGridView.DataSource,DataTable).Rows.Add...

    Dim ops As New DataOperations
    Dim id As Integer = 0
    If ops.AddNewCustomer("Karen's Coffee", id) Then
        ' assuming the DataGridView is populated with a DataTable.
        ' you would set the new DataRow's primary key via (in this case id is the primary key in the table)
        ' NewRow.SetField(Of Integer)("id",id)
        ' where in the above NewRow is the newly added row.
    End If

    Part of a larger class for data operations

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Private mConnectionString As String = "Data Source=KARENS-PC;Initial Catalog=ForumExamples;Integrated Security=True"
    
        Public ReadOnly Property ConnectionString As String
            Get
                Return mConnectionString
            End Get
        End Property
        ''' <summary>
        ''' Add new row
        ''' </summary>
        ''' <param name="pCompanyName"></param>
        ''' <param name="pNewIdentifier">On successful insert this returns the new primary key back to the caller</param>
        ''' <returns></returns>
        Public Function AddNewCustomer(ByVal pCompanyName As String, ByRef pNewIdentifier As Integer) As Boolean
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName) VALUES (@CompanyName); SELECT CAST(scope_identity() AS int);"
                    cmd.Parameters.AddWithValue("@CompanyName", pCompanyName)
                    cn.Open()
                    Try
                        pNewIdentifier = CInt(cmd.ExecuteScalar)
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 14, 2017 3:13 PM
    Moderator
  • seriously ?? I need to create a function now ??!!!!!!!anyways..just tell me why my code is adding multiple datas ??
    Saturday, October 14, 2017 4:09 PM
  • [...] why my code is adding multiple datas ??

    Maybe you handle the TextChanged event of textbox inserting the partial text, which should not be done?



    • Edited by Viorel_MVP Saturday, October 14, 2017 7:43 PM
    Saturday, October 14, 2017 7:43 PM
  • seriously ?? I need to create a function now ??!!!!!!!anyways..just tell me why my code is adding multiple datas ??

    Well first off if I copy and paste your code into Visual Studio there is no way it will compile so that is a start e.g. you used con.opn for opening the connection where it should be con.Open(), you have parametres for Parameters. When performing an add there is zero need to use an SqlDataAdatper.

    As Viorel_ mentioned, since you are doing the insert using an event for the TextBox, sure it will insert multiple rows. That is not the way to go about adding a row. 

    If you were to learn how to use break-point and learn how to step through code using the debugger this would be clear as sunlight. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 14, 2017 11:48 PM
    Moderator
  • Hi Aousaf,

    Please show you entire code about insert data into database and update datagridview here, so it is beneficial to us to know which problem you have in your project.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 17, 2017 7:20 AM
    Moderator