vb.net UPDATE Query

[lulu79]

I want to perform an UPDATE query but i'm getting this error:

This is my Sub in vb.net:

   Public Sub changeStatus()

        Dim Sqlstr As String
        Dim connectionString As String = "server=xxxx;uid=xxxx;pwd=xxxx;database=xxxx"
        Dim strtTime As String
        Dim endTime As String

        strtTime = DateTime.Today.ToString("yyyy/MM/dd") & " " & "06:15:00"
        endTime = DateTime.Today.ToString("yyyy/MM/dd") & " " & "18:15:00"


        Sqlstr = "   UPDATE    dbo.VAC_LineStatus  "
        Sqlstr = "   SET   SET Status = @Status   "
        Sqlstr = "   WHERE     (Line IN  "
        Sqlstr = "  (SELECT     VAC_JSINFO.LINE  "
        Sqlstr = "   FROM    dbo.VAC_LineStatus INNER JOIN  "
        Sqlstr = "    VAC_JSINFO ON dbo.VAC_LineStatus.Line = VAC_JSINFO.LINE  "
        Sqlstr = "    WHERE      (VAC_JSINFO.COLLECTDATE > '" & strtTime & "')  AND (VAC_JSINFO.COLLECTDATE < '" & endTime & "')  AND   "
        Sqlstr = "   (VAC_JSINFO.KYUERR1 >= 0)  "
        Sqlstr = "    GROUP BY VAC_JSINFO.LINE, VAC_LineStatus.Line    HAVING(SUM((VAC_JSINFO.KYUERR1 + VAC_JSINFO.VISION1 + VAC_JSINFO.KEIJYOU1) * VAC_JSINFO.UNITCOST) > '25')))"
        Sqlstr = "  HAVING  (SUM((VAC_JSINFO.KYUERR1 + VAC_JSINFO.VISION1 + VAC_JSINFO.KEIJYOU1) * VAC_JSINFO.UNITCOST) > '25')))  "

        Using connection As New SqlConnection(connectionString)
            Dim cmdInsert As New SqlCommand(Sqlstr, connection)

            cmdInsert.Parameters.Add("@Status", Data.SqlDbType.NVarChar).Value = 1

            connection.Open()
            cmdInsert.ExecuteNonQuery()
        End Using

    End Sub

When i execute the query in SQL Server, it's working fine but why it get error when trying to execute using vb.net?

Can someone help me please...

[Pgmer]

please post your Query . I mean your final sqlstr.. And one thing i noticed is line number 13 is having 2 times SET key word

[Nutster]

You are assigning, not appending. Use &= instead of = to append the given string literal to the string variable in question. By using equals assignment, you keep replacing the string variable, so only the last assignment survives.

Watch your brackets. In line 21, you have too many closing brackets on your HAVING clause. Remove all the brackets you do not absolutely need; it will make it easier to follow and debug.

Also if you change this to appending the string literals, you will end up putting two HAVING clauses in your SELECT statement. Remove the HAVING clause on line 20 and try again.

Also if you are going to append a lot of strings together, take a look at the StringBuilder class instead of String.

[lulu79]

Thank you all.. The problem has been solved :)

Dim SQLstr As String = " SELECT VAC_JSINFO.LINE, VAC_LineStatus.Status " & vbCrLf & _
                       " FROM VAC_LineStatus INNER JOIN " & vbCrLf & _
                       " VAC_JSINFO ON VAC_LineStatus.Line = VAC_JSINFO.LINE " & vbCrLf & _
                       " WHERE (VAC_JSINFO.COLLECTDATE > '" & strtTime & "')  AND (VAC_JSINFO.COLLECTDATE < '" & endTime & "')  AND (VAC_JSINFO.KYUERR1 >= 0)  " & vbCrLf & _
                       " AND (VAC_LineStatus.Status = 1) " & vbCrLf & _
                       " GROUP BY VAC_JSINFO.LINE, VAC_LineStatus.Line, dbo.VAC_LineStatus.Status  " & vbCrLf & _
                       " HAVING (SUM((VAC_JSINFO.KYUERR1 + VAC_JSINFO.VISION1 + VAC_JSINFO.KEIJYOU1) * VAC_JSINFO.UNITCOST) > '25') "