SQL staments not working

[paulthepaddy]

Hey guys

i Have 3 SQL statments, 1 is working fine and the other 2 dont :S

Public Sub RecentWork_add(InvoiceName As String, Type As String)
        Dim insert As New OleDbCommand("INSERT INTO RecentWork (ItemName, DateCreated, DeleteDate, ItemType) " & "VALUES (@ItemName, @DateCreated, @DeleteDate, @ItemType)", Me.Connection)
        Connection.Open()
        With insert.Parameters
            .AddWithValue("@ItemName", InvoiceName)
            .AddWithValue("@DateCreated", Format(Date.Today, "dd:MM:yyyy"))
            .AddWithValue("@DeleteDate", Format(DateAdd(DateInterval.Day, 10, Date.Today), "dd:MM:yyyy"))
            .AddWithValue("@ItemType", Type)
        End With
        insert.ExecuteNonQuery()
        Me.Connection.Close()
        Call Me.RecentWork_Display()
    End Sub

    Public Sub RecentWork_Delete(InvoiceName As String)
        Dim delete As New OleDbCommand("DELETE FROM RecentWork WHERE ItemName = @ItemName", Me.Connection)
        Connection.Open()
        delete.Parameters.AddWithValue("@ItemName", InvoiceName)
        delete.ExecuteNonQuery()
        Connection.Close()
        Call Me.RecentWork_Display()
    End Sub

    Public Sub RecentWork_Keep(InvoiceName As String)
        Dim update As New OleDbCommand("UPDATE RecentWork SET DeleteDate='00:00:0000', ItemName = @ItemNameK WHERE ItemName= @ItemName", Connection)
        Connection.Open()
        With update.Parameters
            .AddWithValue("@ItemNameKeep", (InvoiceName & " (K)"))
            .AddWithValue("@ItemName", InvoiceName)
        End With
        update.ExecuteNonQuery()
        Connection.Close()
        Call Me.RecentWork_Display()
    End Sub

The Insert statement is working fine, so i know its nothing wrong with the connection.

just to note that datecreated and datetime are just strings

thanks

 

[jmcilhinney]

"not working" doesn't really mean much to us. What exactly do you expect and what exactly happens?

 

[Menthos]

Well your RecentWork_Keep won't work with you declaring @ItemNameK in your query string and then attempting to assing the parameter @ItemNameKeep below, so that's an easy fix.

Best advice (beyond actually telling us what happens) is to drop some Try...Catch blocks around your SQL calls to trap any exceptions that are raised.