-
August 16th, 2021, 10:01 AM
#1
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
-
August 16th, 2021, 03:27 PM
#2
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
-
August 16th, 2021, 04:58 PM
#3
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
-
August 17th, 2021, 03:05 AM
#4
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
-
September 2nd, 2021, 03:25 AM
#5
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|