Update Statement

[dr223]

Hi,

I receive the error -

Incorrect syntax
Unclosed syntax near 's'
Unclosed quotation mark after character string ".

query = "UPDATE dbo.TblPracExclude SET " & _
            "prac_enabled = '" & DgvPracExcl.Rows(e.RowIndex).Cells(4).Value & "', " & _
            "Notes = '" & Nz(Replace(DgvPracExcl.Rows(e.RowIndex).Cells(5).Value, " '", "''")) & "', " & _
            "SysDatetime = '" & DateTime.Now.ToString("dd\-MMMM\-yyyy HH:mm:ss") & "', " & _
            "WHERE prac_no = " & _
            DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""

any help please

cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()

[sotoasty]

Change you update to use Parameters. That way you will avoid quoting issues. Also it makes it more readable.

query = "UPDATE dbo.TblPracExclude SET prac_enabled = @prac_enabled, Notes = @Notes, SysDatetime = @SysDateTime WHERE prac_no = @prac_no"
cmd = New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@prac_enabled",DgvPracExcl.Rows(e.RowIndex).Cells(4).Value)
cmd.Parameters.AddWithValue("@Notes",DgvPracExcl.Rows(e.RowIndex).Cells(5).Value)
cmd.Parameters.AddWithValue("@SysDateTime",DateTime.Now)
cmd.Parameters.AddWithValue("@prac_no",DgvPracExcl.Rows(e.RowIndex).Cells(0).Value)
cmd.ExecuteNonQuery()

[Mur16]

You're only searching for " '". You probably have a "'" and are missing it.

[GremlinSA]

#1 problem here is if the notes test has a ' in it.. IE: " It's difficult to find a note that doesn't use the single quote at least once."

This then causes problems with the Query's ... two solutions... Drop them out.. or Double them up....