Why Doesn't My SQL Query do anything?

Question

Hi there I got this query string I have to run so I can update a distinct row in a sql server table:

    servidorSql.ConnectionString = conexao
    Dim insert As String = "UPDATE [Equipamentos] SET [ID_Cliente]=@ID_Cliente,      [Identificacao] = @Identificacao, [Fabricante] = @Fabricante, [Modelo] = @Modelo, [Versao_Software] = @Versao_Software, [Localizacao] = @Localizacao WHERE [ID_Equipamento]=@ID_Equipamento"

    Dim sqlquery As SqlCommand = New SqlCommand(insert, servidorSql)

    Try
        servidorSql.Open()
        sqlquery.Parameters.AddWithValue("@ID_Cliente", ID_Cliente)
        sqlquery.Parameters.AddWithValue("@ID_Equipamento", ID_Equipamento)
        sqlquery.Parameters.AddWithValue("@Identificacao", Identificacao)
        sqlquery.Parameters.AddWithValue("@Fabricante", Fabricante)
        sqlquery.Parameters.AddWithValue("@modelo", modelo)
        sqlquery.Parameters.AddWithValue("@versao_Software", versao_Software)
        sqlquery.Parameters.AddWithValue("@localizacao", localizacao)
        sqlquery.ExecuteNonQuery()
        servidorSql.Close()

    Catch e As SqlClient.SqlException
        MsgBox(e.ToString())
    End Try

The problem is that it doens't do anything, it doesn't update the table and it doesn't give me any exception. "ID_Equipamento" is key and identity of the table and I've tried to run the query with the field being update or not, and it's the same. Any suggestion?

Answer 1

Try adding a commit.

Even if the database autocommits, it's almost always better to explicitly take care of it.

Answer 2

Try using SQL Server Profiler- that will give you much more of a clue as to what is happening. Chances are you are passing in an ID that doesn't exist.

Answer 3

The sqlquery.ExecuteNonQuery() instruction returns the number of rows affected, so you can check it also, something like:

int rowsAffected = sqlquery.ExecuteNonQuery();
Debug.WriteLine("Rows affected: " + rowsAffected);

You can also check the ID_Equipamento variable.

Debug.WriteLine(ID_Equipamento);

Maybe this variable doesn't hold the value of the primary key for your table

Answer 4

You're not inserting anything. And I doubt you're even modifying things. You're executing an UPDATE statement. Check if you're updating the record with different values than the ones stored in your database. Of course, @ID_Equipamento might be the ID of a non-existing record, in which case you're not updating anything. That won't generate an error but just tells you it modified 0 records.

Answer 5

The problem it's in the query it self! Couldn't figure ou very well why, but doens't matter.

The problem is solved.... Thanks anyway for your help. Aprecciated

Answer 6

Capture an SQL Profiler trace to confirm that the parameter that the ID your passing in is definitely the ID that you think it is. If you also select the RowCounts column you can see the number of rows updated.