v3ga
v3ga

Reputation: 177

Why Doesn't My SQL Query do anything?

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?

Upvotes: 1

Views: 274

Answers (6)

v3ga
v3ga

Reputation: 177

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

Upvotes: 0

Justin
Justin

Reputation: 86789

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.

Upvotes: 1

Wim ten Brink
Wim ten Brink

Reputation: 26682

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.

Upvotes: 0

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 0

RichardOD
RichardOD

Reputation: 29157

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.

Upvotes: 1

Brian
Brian

Reputation: 2261

Try adding a commit.

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

Upvotes: 1

Related Questions