sql delete function

[swathys]

hi,

Below is my code to delete the transaction. It suppose to delete the data once the transaction but the data remain there....I don't find there is a problem with the code but some how it doesn't work.

It capture the data in the table but it returns = 0 at retvalue = myCommand.ExecuteNonQuery()

Public Function Delete_PaymentDetails(ByVal AccountNo As String, ByVal TransDateTime As String) As Boolean
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim strSQL As String = ""
        Dim connStr As String
        Dim dr As SqlDataReader
        Dim retvalue As Integer
        Dim tmpData() As String
        Dim tmpDate As Date
        Dim tmpDate2 As String
        Dim tmpTime As String

        connStr = "server=(local); database=Payment; Trusted_Connection=yes"
        myConnection = New SqlConnection(connStr)

        If myConnection.State = 0 Then
            myConnection.Open()
        End If

        
        tmpData = TransDateTime.Split(" ")
        tmpDate = Trim(tmpData(0))
        tmpDate2 = Format(tmpDate, "dd-MMM-yyyy")
        tmpTime = IIf(Len(Trim(tmpData(1))) < 8, "0" & Trim(tmpData(1)), Trim(tmpData(1))) & " " & Trim(tmpData(2))

        strSQL = "Delete FROM Update_PaymentDetails " _
                & "Where AccountNo = '" & AccountNo & "' " _
                & "And TransactionDate = '" & tmpDate2 & "' " _
                & "And TransactionTime = '" & tmpTime & "'"

        myCommand = New SqlCommand(strSQL, myConnection)
        retvalue = myCommand.ExecuteNonQuery()

        myConnection.Close()

    End Function

can anyone help me!!!!!!!!!!!!

[Pgmer]

Hi,

Try to get the strsql before you perform retvalue = myCommand.ExecuteNonQuery()
and try to execute that in sql server, so you will come to know if any syntax error or something.

[Jx_Man]

same as pgmer, you must ensure that your query is correct.

[vanzhyme]

try to use this

try to add a asterisk after the delete

strSQL = "Delete * FROM Update_PaymentDetails " _
& "Where AccountNo = '" & AccountNo & "' " _
& "And TransactionDate = '" & tmpDate2 & "' " _
& "And TransactionTime = '" & tmpTime & "'"

[swathys]

Thank you so much. Its working now...there is a problem with my query.

[Pgmer]

:) Sometime it happnes dont worry..
Happy coding..:)