Sql Update command VB.Net With Date

Question

I have an SQL update command that I can't get to work. i want it to update the fields where the date is equal to the current date. It works for my INSERT and SELECT statements. But I get a missing operator error when using it for my Delete statement below.

     Cmd.Connection = conn
     Cmd.CommandText = "UPDATE tbl_Expenditure SET E_Stock =" & NewEStock & "," & "E_Total =" & ETotal & "WHERE [E_Date] = #" & thisMonth & "/" & Day & "/" & thisYear & "#;"
    Cmd.ExecuteNonQuery()

Ive tried searching this site as well as others and can't seem to find an answer.

This is my error

Syntax error (missing operator) in query expression '95WHERE [E_Date] = #4/1/2015#'.

Thanks for any help

The error message contains the answer. 95WHERE Is not a valid keyword. — Pittsburgh DBA
– Pittsburgh DBA, Commented Apr 28, 2015 at 20:07
This code is crazy-vulnerable to sql injection attacks. It's practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 29, 2015 at 13:28

Joel Coehoorn · Accepted Answer · 2015-04-29 13:51:56Z

2

Using conn As New SqlConnection("connection string here"), _
      cmd As New SqlCommand("UPDATE tbl_Expenditure SET E_Stock = @Stock, E_Total = @Total WHERE [E_Date] = @Date;", conn)

    'Guessing at column types here
    cmd.Parameters.Add("@Stock", SqlDbType.Int).Value = NewEStock
    cmd.Parameters.Add("@Total", SqlDbType.Decimal, 8, 2).Value = ETotal
    cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = New DateTime(thisYear, thisMonth, Day)

    cmd.ExecuteNonQuery()
End Using

This fixes a HUGE security issue in the code, it has a performance benefit in allowing Sql Server to cache your query plan, and it solves your problem in that is makes it much easier to spot silly syntax mistakes like the missing space in front of the WHERE clause.

edited Apr 29, 2015 at 13:51

answered Apr 29, 2015 at 13:34

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zohar Peled Over a year ago

cmd.Parameters.Add("@Total", SqlDbType.Decimal, 8, 2).Value = @ETotal should be cmd.Parameters.Add("@Total", SqlDbType.Decimal, 8, 2).Value = ETotal.

Collectives™ on Stack Overflow

Sql Update command VB.Net With Date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related