none
Update query problem RRS feed

  • Question

  • HI eveyone

    i have a datagridview which have a column called "Numero" same exist in database after fill that column i some time make an update and first it goes normal and after i dont unserstand in update once make the update once no

    this my code

      For i = 0 To DataGridView1.Rows.Count - 1
                    Dim usb As New SqlCommand("select Numseq from DetailReceptionFrs where Numero='" & TextBox1.Text & "' and Codearticle='" & DataGridView1.Rows(i).Cells(1).Value & "'", cn)
                    Dim ttt As New SqlDataAdapter(usb)
                    ttt.Fill(qqs23)
    
    
                    If cn.State = ConnectionState.Open Then
                        cn.Close()
                    End If
                    cn.Open()
    
                    Using cmd1 As New SqlCommand("update DetailReceptionFrs set NumeroEnsemble=@NumeroEnsemble,Marge=@Marge where Numero='" & TextBox1.Text & "' and Numseq='" & qqs23.Rows(i).Item("Numseq") & "'", cn)
                        With cmd1.Parameters
                            .Add("@NumeroEnsemble", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(7).Value)
                            .Add("@Marge", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(8).Value)
                        End With
                        cmd1.ExecuteNonQuery()
                    End Using
    
    
                Next

    nothing return for error just i enter the new value and refresh nothing happing

    what i can do please help?

    Monday, September 16, 2019 4:37 PM

Answers

  • i find it 

    its all about the datatable i give it "i" like in datagridview that gave a non desired line to be updated

    DataGridView1.Rows(i).Cells(7).Value

    Numseq='" & qqs23.Rows(i).Item("Numseq")

    i changed to a new datatable every time i incremented and that works fine

    • Marked as answer by Houssem12 Tuesday, September 17, 2019 9:51 AM
    Tuesday, September 17, 2019 9:46 AM

All replies

  • See if the value of cmd1.ExecuteQuery is e.g. 1 or 0.

    Console.WriteLine(cmd1.ExecuteNonQuery())


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, September 16, 2019 5:00 PM
    Moderator
  • thanks for help

    its 1

    Tuesday, September 17, 2019 7:11 AM
  • Hi,

    Is your DataGridView's data comes from database?

    If so, you can try my code, change DataGridView1.Rows.Count - 1 to DataGridView1.Rows.Count - 2, may be it will be helpful.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= D:\Local Database\190918.mdf"
        Dim cn As SqlConnection
        Dim ttt As SqlDataAdapter
        Dim qqs23 As New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim i As Integer
            For i = 0 To DataGridView1.Rows.Count - 2
                Using cn = New SqlConnection(constr)
                    cn.Open()
                    Dim strsql As String = "select Numseq from DetailReceptionFrs where Numero='" & TextBox1.Text & "' and Codearticle='" & DataGridView1.Rows(i).Cells(1).Value & "'"
    
                    Dim usb As New SqlCommand(strsql, cn)
                    Dim ttt As New SqlDataAdapter(usb)
                    ttt.Fill(qqs23)
                    Using cmd1 As New SqlCommand("update DetailReceptionFrs set NumeroEnsemble=@NumeroEnsemble,Marge=@Marge where Numero='" & TextBox1.Text & "' and Numseq='" & qqs23.Rows(i).Item("Numseq") & "'", cn)
                        With cmd1.Parameters
                            .AddWithValue("@NumeroEnsemble", DataGridView1.Rows(i).Cells(1).Value.ToString.Trim)
                            .AddWithValue("@Marge", DataGridView1.Rows(i).Cells(2).Value.ToString.Trim)
                        End With
                        cmd1.ExecuteNonQuery()
                    End Using
                End Using
            Next
            Using cn = New SqlConnection(constr)
                cn.Open()
                ttt = New SqlDataAdapter("Select * From DetailReceptionFrs", cn)
                qqs23.Clear()
                ttt.Fill(qqs23)
                DataGridView1.DataSource = qqs23
            End Using
    
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using cn = New SqlConnection(constr)
                cn.Open()
                ttt = New SqlDataAdapter("Select * From DetailReceptionFrs", cn)
                qqs23.Clear()
                ttt.Fill(qqs23)
                DataGridView1.DataSource = qqs23
            End Using
        End Sub
    End Class
    

    If you have anything else about this issue, please feel free to contact us.

    Best Regards,

    Julie


    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, September 17, 2019 7:27 AM
    Moderator
  • thank you for your help 

    i've already test it many time and not work 

    please help


    • Edited by Houssem12 Tuesday, September 17, 2019 8:07 AM
    Tuesday, September 17, 2019 8:02 AM
  • i find it 

    its all about the datatable i give it "i" like in datagridview that gave a non desired line to be updated

    DataGridView1.Rows(i).Cells(7).Value

    Numseq='" & qqs23.Rows(i).Item("Numseq")

    i changed to a new datatable every time i incremented and that works fine

    • Marked as answer by Houssem12 Tuesday, September 17, 2019 9:51 AM
    Tuesday, September 17, 2019 9:46 AM
  • thanks for help

    its 1

    Returning 1 means the UPDATE query updated one record even though you say it didn't work it did as 1 means a record was inserted while 0 or -1 would mean it failed.

    If you were to add parameters for the two parts in the WHERE I can provide a method (a NuGet helper package) to peek at the UPDATE query to try it outside of code.

    where Numero='" & TextBox1.Text & "' and Numseq='" & qqs23.Rows(i).Item("Numseq") & "'"

    Lastly, I seen people post here that have two databases and think they are pointing to the right one and are not or they are using a database that resides in the Bin\Debug folder with the database property "Copy to output directory" set to copy always which should be copy if newer.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, September 17, 2019 9:48 AM
    Moderator
  • i fixed some of it but if it duplicated it made another value not what i want

    Tuesday, September 17, 2019 12:59 PM
  • i have a hard time work with duplicated value in datagridview and duplicated in database

    how i can differe between them in update query

    Tuesday, September 17, 2019 4:15 PM