ExecuteNonQuery problem? [modified]

Emmet_Brown

Hello everyone I've generated an SQL statement in my .cs file as cmd.Connection = conn //this is working, worked before in the same code cmd.CommandText = "INSERT INTO VALUES ('value1','value2')"; conn.Open cmd.ExecuteNonQuery conn.Close the problem is: I'm checking the SQL statement in debug, copying the same query on my MySQL QueryBrowser and executing the same string works in the querybrowser, but in ExecuteNonQuery() doesn't what can be the problem? notes: 1-connection is done, checked, and double checked, a few lines earlier, several SELECT statements are executed and worked 2-connection string is not changed before this code, it was the same before 3-I'm not changing the code while copying to the Querybrowser, taking the text as it is directly from debug window and pasting it to the SQL browser tool. any ideas?

modified on Tuesday, October 19, 2010 2:39 PM

Luc Pattyn · modified on Tuesday, October 19, 2010 2:39 PM

I don't know what you did, but all those SPANs seem to mess up the entire forum page. I would suggest you edit your existing post and refrain from using excessive HTML tags, the one you do want is the PRE tag for showing code. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Maciej Los · modified on Tuesday, October 19, 2010 2:39 PM

The correct statement is:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES ('value1','value2')

The data type must be the same type. For example: if Field1 = string, Field2 = numeric then

INSERT INTO TABLE (FIELD1, FIELD2) VALUES ('value1',123)

sorry, for my language I'm still learning

Emmet_Brown

Thank you but, The statement is forming correctly, I'm copying the text into query browser and executing it's working there

Maciej Los · modified on Tuesday, October 19, 2010 2:39 PM

So, show me how you create connection object and command object or take a look at this code:

'MS Access
'Execute = INSERT, UPDATE, DELETE
Public Function ExecuteDbData(ByVal oConn As System.Data.OleDb.OleDbConnection, ByVal sSQL As String) As Long
    Dim oComm As System.Data.OleDb.OleDbCommand, oTrans As System.Data.OleDb.OleDbTransaction = Nothing
    Dim iRetVal As Integer

    Try
        oConn.Open()
        oTrans = oConn.BeginTransaction()
        oComm = New System.Data.OleDb.OleDbCommand(sSQL, oConn, oTrans)
        iRetVal = oComm.ExecuteNonQuery()
        oTrans.Commit()

    Catch ex As System.Data.OleDb.OleDbException
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")
        Try
            oTrans.Rollback()
            oTrans.Dispose()
        Catch
            'do nothing
        End Try

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")

    Finally
        oConn.Close()
        oTrans = Nothing
        oComm = Nothing
    End Try

    Return iRetVal

End Function

'MS SQL
'Execute = INSERT, UPDATE, DELETE
Public Function ExecuteDbData(ByVal oConn As System.Data.SqlClient.SqlConnection, ByVal sSQL As String) As Long
    Dim oComm As System.Data.SqlClient.SqlCommand, oTrans As System.Data.SqlClient.SqlTransaction = Nothing
    Dim iRetVal As Integer

    Try
        oConn.Open()
        oTrans = oConn.BeginTransaction(System.Data.IsolationLevel.Serializable)
        oComm = New System.Data.SqlClient.SqlCommand(sSQL, oConn, oTrans)
        iRetVal = oComm.ExecuteNonQuery()
        oTrans.Commit()
        
    Catch ex As System.Data.SqlClient.SqlException
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")
        Try
            oTrans.Rollback()
            oTrans.Dispose()
        Catch
            'do nothing
        End Try

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")

    Finally
        oConn.Close()
        oTrans = Nothing
        oComm = Nothing
    End Try

    Return iRetVal

End Function

sorry, for my language I'm still learning

Emmet_Brown · modified on Tuesday, October 19, 2010 2:39 PM

Sorry everyone I figured out that I was working on another dataBase with my querybrowser I just duplicated the databases and that fooled me in the first place I wasn't aware of the difference until 3 hours of crisis sorry for disturbance :) thanks for any help

Pete OHanlon · modified on Tuesday, October 19, 2010 2:39 PM

A few thoughts. First of all, you are leaving yourself wide open to a SQL Injection Attack with this code - use SQL Parameters instead of directly injecting values. Secondly, is it throwing an exception? Set a breakpoint on the code and step through it - see what's happening at this point.

I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx