CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2021
    Posts
    6

    Update Statement

    Hi,

    I have an update Statement that append the data in the table and create a record in the history table.

    Code:
    Private Sub UpdatedTenantDetails()
    
            Dim conn As SqlConnection = GetDbConnection()
    
            Dim cmd As New SqlCommand
            Dim query As String
    
            Dim Tenant_Read As New System.Data.SqlClient.SqlCommand(("Select nationalID From tblTenants where nationalID = " &
                                        Me.TxtNationalIDUp.Text), conn)
    
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Tenant_Read.ExecuteReader()
                    While Autoreader.Read()
    
                        Dim nationalID As String = Autoreader.GetValue(0)
    
                        For RowID = 0 To DgTenantDetails.Rows.Count - 1
    
                            If nationalID = DgTenantDetails.Rows(RowID).Cells(6).Value Then
    
                                Dim ms As New MemoryStream
                                PictureBox2.Image.Save(ms, PictureBox2.Image.RawFormat)
    
                                Try
                                    query = "UPDATE dbo.tblTenants SET firstname=@firstname, lastname=@lastname, tenantfullname=@tenantfullname, telephone=@telephone, email=@email, nationalID=@nationalID, copyID=@copyID OUTPUT DELETED.firstname,DELETED.lastname,DELETED.tenantfullname,DELETED.telephone,DELETED.email,DELETED.nationalID,DELETED.copyID INTO dbo.TblTenantsHistory WHERE nationalID = @nationalID"
                                    cmd = New SqlCommand(query, conn)
                                    cmd.Parameters.AddWithValue("@firstname", TxtFNameUp.Text)
                                    cmd.Parameters.AddWithValue("@lastname", TxtLNameUp.Text)
                                    cmd.Parameters.AddWithValue("@tenantfullname", TxtFNameUp.Text & " " & TxtLNameUp.Text)
                                    cmd.Parameters.AddWithValue("@telephone", TxtTelUp.Text)
                                    cmd.Parameters.AddWithValue("@email", TxtEmailUp.Text)
                                    cmd.Parameters.AddWithValue("@nationalID", TxtNationalIDUp.Text)
                                    cmd.Parameters.Add(New SqlParameter("@copyID", SqlDbType.Image) With {.Value = ms.ToArray()})
                                    cmd.ExecuteNonQuery()
    
                                Catch ex As Exception
                                    MsgBox(ex.Message, MsgBoxStyle.Information, "RCMS")
                                End Try
    
                            Else
                            End If
    
                        Next RowID
                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "RCMS")
            End Try
    
        End Sub
    I receive the error message

    Code:
    Invalid Column name 'F222422'
    The value is displayed on the TxtNationalIDUp.Text.

    Please can you help!

    Thanks

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,104

    Re: Update Statement

    Did you try to debug this code to see whet values were set to the cmd.Parameters?

    And BTW, is it so convenient for you to "handle" so long strings in code like your
    Code:
    query = "UPDATE dbo.tblTenants SET ..."
    Victor Nijegorodov

  3. #3
    Join Date
    Jul 2021
    Posts
    6

    Re: Update Statement

    Ok -

    I changed it to this

    Code:
     Dim Tenant_Read As New System.Data.SqlClient.SqlCommand(("Select nationalID from tblTenants where nationalID ='" &
                                        Me.TxtNationalIDUp.Text & "' "), conn)
    New error message:

    Code:
    Column name or number of supplied values does not match table definition.
    I checked the tables tbltenants and tblTenantsHistory and they are the same.

    Any assistance!

    Thanks

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,104

    Re: Update Statement

    Again: did you debug your code?
    Did you see in the Debug window the values of your local variables?
    Did you check whether your SQL expression correct? Did your proof it with your real database?
    Victor Nijegorodov

  5. #5
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,271

    Re: Update Statement

    I don't understand why you are using a SqlDataReader and try to update at the same time.

    What I will do is to first get the value of the ID in the particlar datagrid column, then update the rows separately.
    It is also good to ensure you make use of an Using statement when working with database operations so that the resources can be cleaned up properly, as well as set a return value to true or false or number of records updated. This helps you test if the function was successful.

    Something Like this:

    Using con As New SqlConnection(constring)
    Using cmd As New SqlCommand("UPDATE Persons SET City = @City WHERE Name = @Name", con)
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("@Name", name)
    cmd.Parameters.AddWithValue("@City", city)
    con.Open()
    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
    con.Close()
    End Using
    End Using
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured