none
Message "error " when using new sql command in vb.net 2015 RRS feed

  • Question

  • Dear All,

    Until now , I have no solution in update method by using table adapter (VB.net 2015)

    I  have put on primary key and the other.

    At this this I try to used new sql connection, like my ciode like this

    Imports System.Data

    Imports System.Data.SqlClient

    Public Class Form1

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

            'TODO: This line of code loads data into the 'LatihanDataSet.datamahasiswa' table. You can move, or remove it, as needed.

            Me.DatamahasiswaTableAdapter.Fill(Me.LatihanDataSet.datamahasiswa)

            Dim com1 As SqlConnection

            com1 = New SqlConnection("data source = DESKTOP-T26PHBJ;initial catalog=latihan;integrated security=True")

        End Sub

        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

            Dim a As Integer

            a = DataGridView1.CurrentRow.Index

            With DataGridView1.Rows.Item(a)

                TextBox1.Text = .Cells(0).Value

                TextBox2.Text = .Cells(1).Value

                TextBox3.Text = .Cells(2).Value

            End With

        End Sub

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

            Dim com1 As SqlConnection

            com1 = New SqlConnection("data source = DESKTOP-T26PHBJ;initial catalog=latihan;integrated security=True")

            Dim a As Integer

            a = DataGridView1.CurrentRow.Index

            With DataGridView1.Rows.Item(a)

                .Cells(0).Value = TextBox1.Text

                .Cells(1).Value = TextBox2.Text

                .Cells(2).Value = TextBox3.Text

            End With

            Dim cmd As SqlCommand

            cmd = New SqlCommand("update datamahasiswa set no='" & TextBox1.Text & "',nama='" & TextBox2.Text & "',alamat='" & TextBox3.Text & "', where nama='" & TextBox2.Text & "'", com1)

            With cmd

                .Parameters.AddWithValue("no", TextBox1.Text)

                .Parameters.AddWithValue("nama", TextBox2.Text)

                .Parameters.AddWithValue("alamat", TextBox3.Text)

            End With

            cmd.ExecuteNonQuery()

        End Sub

     But the the problem is 

    cmd.ExecuteNonQuery()  show error

    Please give me the correction one.

    Thanks so much for your time and attention,

    My best regards,

    Muljanto 

    Tuesday, August 21, 2018 9:59 AM

All replies

  • hello, 

    'Dim cmd = New SqlCommand("update datamahasiswa set no='" & TextBox1.Text & "',nama='" & TextBox2.Text & "',alamat='" & TextBox3.Text & "', where nama='" & TextBox2.Text & "'", com1) ' ---> 'here you should replace values with parameters names cmd = New SqlCommand("update datamahasiswa set no=@no,nama=@nama,alamat=@alamat, where nama=@nama", com1) ' and then add parameters and pass their values With cmd .Parameters.AddWithValue("@no", TextBox1.Text) .Parameters.AddWithValue("@nama", TextBox2.Text) .Parameters.AddWithValue("@alamat", TextBox3.Text) End With ' <---

    Good Coding

    Tuesday, August 21, 2018 11:09 AM
  • Hello,

    When using a TableAdapter as per your form Load event add CRUD operations really should go through the TableAdapter/BindingSource components and not mix it up with a SqlCommand.

    Edit, using a simple Customer table with a primary key, the following loads and adds a new record. Of course you would get the values from TextBox controls rather than hard coded values. This way you are using strong typed classes that were generated for you. Does this make sense?

    Public Class Form1
        Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
            Handles CustomersBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.CustomersBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me._NORTHWND_NEW_MDF_CopyDataSet)
    
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 
            '_NORTHWND_NEW_MDF_CopyDataSet.Customers' table. You can move, or remove it, as needed.
            Me.CustomersTableAdapter.Fill(Me._NORTHWND_NEW_MDF_CopyDataSet.Customers)
    
        End Sub
    
        Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) _
            Handles ToolStripButton1.Click
            Dim customersRow As _NORTHWND_NEW_MDF_CopyDataSet.CustomersRow
            customersRow = _NORTHWND_NEW_MDF_CopyDataSet.Customers.NewCustomersRow()
            customersRow.CompanyName = "NorthWestern"
            _NORTHWND_NEW_MDF_CopyDataSet.Customers.Rows.Add(customersRow)
            Me.CustomersTableAdapter.Update(_NORTHWND_NEW_MDF_CopyDataSet.Customers)
        End Sub
    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



    Tuesday, August 21, 2018 11:22 AM
    Moderator
  • he can use SqlCommandBuilder to generate Insert,Update, Delete Commands, cause if i remember the Adapter dont generate those command by default;

    Modifying Data using DbAdapter 


    Tuesday, August 21, 2018 11:38 AM
  • he can use SqlCommandBuilder to generate Insert,Update, Delete Commands, cause if i remember the Adapter dont generate those command by default;

    Modifying Data using DbAdapter 


    TableAdapter components generate all which is needed unless the table in question does not have a primary key which then the Adapter will not generate a update command as it needs a primary key. A SqlCommandBuilder would have the same issue.

    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

    Tuesday, August 21, 2018 3:52 PM
    Moderator
  • Hi,

    Delete ',' and a piece of code that I circled,then run.


    Best Regards,

    Alex


    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.

    Wednesday, August 22, 2018 3:37 AM
  • Dear All,

    I have modified this program, like this :

    Dim com1 = New SqlConnection("data source = DESKTOP-T26PHBJ;initial catalog=latihan;integrated security=True")
            Dim a As Integer
            a = DataGridView1.CurrentRow.Index
            With DataGridView1.Rows.Item(a)
                .Cells(0).Value = TextBox1.Text
                .Cells(1).Value = TextBox2.Text
                .Cells(2).Value = TextBox3.Text
            End With
            Try
                Me.Validate()
                com1.Open()
                Dim cmd = New SqlCommand("update datamahasiswa set no='" & TextBox1.Text & "',nama='" & TextBox2.Text & "',alamat='" & TextBox3.Text & "' where no='" & TextBox1.Text & "'", com1)
                With cmd
                    .Parameters.AddWithValue("no", TextBox1.Text)
                    .Parameters.AddWithValue("nama", TextBox2.Text)
                    .Parameters.AddWithValue("alamat", TextBox3.Text)
                    .ExecuteNonQuery()
                End With
                MsgBox("Update successful")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            com1.Close()

    Thank so much for your inspiration for this one.

    My best regards,

    Muljanto

    Monday, September 10, 2018 3:33 AM