Update Statement

[dr223]

Hi,

I have these 3 update statements - how can incoroprate them to one please;

query = "UPDATE dbo.TblPracExclude SET prac_enabled  ='" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(4).Value & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

                query = "UPDATE dbo.TblPracExclude SET Notes = '" & _
                          Nz(Replace(DgvPracExcl.Rows(e.RowIndex).Cells(5).Value, "'", "''")) & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()


                query = "UPDATE dbo.TblPracExclude SET SysDatetime ='" & _
                          DateTime.Now.ToString("dd\-MMMM\-yyyy HH:mm:ss") & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

Thanks

[si_the_geek]

Thread moved to the 'Database Development' forum - which is where you should always post SQL based questions (while SQL can be used in VB, it is certainly not specific to VB)


What you want is basic SQL syntax, so take a look at the SQL section of the FAQs at the top of this forum... the tutorial covers it.

Also, why are you not using parameters?

[TysonLPrice]

Update table1 set value1 = 'x', value2 = 'y', value3 = 'z'
where something = something

Just throrwing that out. si_the_geek has the better approach.

[dr223]

Wrote this;

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 & ""

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

Received the error -

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

[TysonLPrice]

I don't thing anyone is going to try and figure out what the string "query" contains unless they have time on their hands. In the immeadiate window key:

?query

and get the contents. Put that into native SQL and syntax check it for the error or post it here.

[si_the_geek]

Have you looked at what your variable query contains when you run it?

If you have you should have been able to spot the issue... if you haven't, you should not be asking us for help until you have (and if it gets to that, you should also be showing us what it contains).

[dr223]

how can I check the variable query at runtime?

Thanks

[TysonLPrice]

how can I check the variable query at runtime?

Thanks

I would think you would do it in IDE. If for some reason you can't put it in a message box but then you can't cut and paste it. Why can't you do it in IDE?

[dr223]

how?

sorry

[TysonLPrice]

how can I check the variable query at runtime?

Thanks

I would think you would do it in IDE. If for some reason you can't put it in a message box but then you can't cut and paste it. Why can't you do it in IDE?