[RESOLVED] Update of Production Record failed : Incorrect syntax near '('.

[Reaction]

Hello,

Could anyone please examine this code please and help with detecting the problem please???

    Dim query As String = "Update PRODUCTION Set(VENDORID,TCAPONUMBER,DATEOUT,DATEREQUIRED,TOTALCOST,TOTALQUANTITY,APPROVEDBY,PREPAREDBY) VALUE(@VENDORID,@TCAPO ,@DATEOUT,@DATEREQUIRED,@TOTALCOST,@TOTALQUANTITY,@APPROVEDBY,@PREPAREDBY)WHERE PRODUCTIONID = @PRODUCTIONID"

        Dim Conn As SqlConnection = New SqlConnection(connectionString)
        Try
            Conn.Open()
            Dim command As SqlCommand = New SqlCommand(query, Conn)
            ' Add the parameters for the UpdateCommand.
            With command.Parameters
                ' Add the parameters for the UpdateCommand. 
                .Add(New SqlParameter("@TCAPO", SqlDbType.Int, 4)).Value = TCAPO
                .Add(New SqlParameter("@DATEOUT", SqlDbType.DateTime, 8)).Value = DateOut
                .Add(New SqlParameter("@DATEREQUIRED", SqlDbType.DateTime, 8)).Value = DateRequired
                .Add(New SqlParameter("@TOTALCOST", SqlDbType.SmallMoney, 5)).Value = TotalCost
                .Add(New SqlParameter("@TOTALQUANTITY", SqlDbType.Int, 4)).Value = TotalQuantity
                .Add(New SqlParameter("@PREPAREDBY", SqlDbType.VarChar, 50)).Value = PreParedBy
                .Add(New SqlParameter("@APPROVEDBY", SqlDbType.VarChar, 50)).Value = ApprovedBy
                .Add(New SqlParameter("@VENDORID", SqlDbType.Int, 4)).Value = vendorID
                .Add(New SqlParameter("@PRODUCTIONID", SqlDbType.Int, 4)).Value = ProductionID
                '.Add(New SqlParameter("@TCAPO", SqlDbType.Int, 4)).Value = TCAPO
            End With
            command.ExecuteNonQuery()
        Catch e As Exception
            Throw New Exception("Update of Production Record failed : " & e.Message)
            Return False
        Finally
            'make sure the Connection is always closed
            Conn.Close()
        End Try

[jmcilhinney]

It says that there's a syntax error.

"Update PRODUCTION Set(VENDORID,TCAPONUMBER,DATEOUT,DATEREQUIRED,TOTALCOST,TOTALQUANTITY,APPROVEDBY,PREPAREDBY) VALUE(@VENDORID,@TCAPO ,@DATEOUT,@DATEREQUIRED,@TOTALCOST,@TOTALQUANTITY,@APPROVEDBY,@PREPAREDBY)WHERE PRODUCTIONID = @PRODUCTIONID"

"VALUE" is not an SQL key word. "VALUES" is.

[Reaction]

Thank you. Did you find another please? I still get the same error message when I run my code. I crosschecked the values . Is it ok to Save a Decimal to a SmallMoney Field In Database?

Reaction

[jmcilhinney]

Actually, I just realised that you're mongrelising UPDATE and INSERT syntax too. An UPDATE statement looks like this:

UPDATE MyTable SET Column1 = @Column1, Column2 = @Column2 WHERE ID = @ID

while an INSERT statement looks like this:

INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2)

Also, don't you find it easier to read when the casing is consistent, with SQL key words in all upper case and object names in title case?

[jmcilhinney]

Also, this:

.Add(New SqlParameter("@TCAPO", SqlDbType.Int, 4)).Value = TCAPO

is kinda long-winded when this:

.AddWithValue("@TCAPO", TCAPO)

will do the same job.

[Reaction]

Thanks. That did the trick )

Reaction