CODEWITHSUNDEEP
asp.net.netsqlsql-server-2005
Smith
Smith

Reputation: 5961

database update sql not affecting database

i have this code to update a database, but when ever i run it with the right data, it executes without errors but the databse is not update

 Dim conn As New SqlClient.SqlConnection(My.Resources.conn_str)
 Dim SQL As String = "Update vehicle SET make=@make,reg_no=@reg_no,model=@model,year=@year,type=@type,last_service=@last_service Where (id = @id)"
        conn.Open()
        Try
            Dim cmd As New SqlClient.SqlCommand(SQL, conn)
            Try
                cmd.Parameters.AddWithValue("@make", strMake)
                cmd.Parameters.AddWithValue("@reg_no", strRegnNum)
                cmd.Parameters.AddWithValue("@model", strModel)
                cmd.Parameters.AddWithValue("@year", intYear)
                cmd.Parameters.AddWithValue("@type", strType)
                cmd.Parameters.AddWithValue("@last_service", LastService)
                cmd.Parameters.AddWithValue("@id", ID.ToString)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
            Catch ex As Exception
                Return ex.Message
            End Try
        Catch ex As Exception
            Return ex.Message
        Finally
            conn.Dispose()
        End Try

can anyone help me with the reason its not working, as i don get an error message?

thanks

EDIT i replaced the cmd.ExecuteNonQuery() with 

Dim intAffected As Integer = cmd.ExecuteNonQuery()
Debug.Print(intaffected)

and i get 1 in the output window

Upvotes: 0

Views: 461

Answers (1)

Dave Simione
Dave Simione

Reputation: 1441

A few thoughts:

  1. If you have access to SQL Profiler, you can see the query, the values, the result, any triggers, any transactions, etc. This is the easiest way to identify what is going on.
  2. If you don't have access to Profiler, update your query to include the OUTPUT clause, and return the values from inserted.* and deleted.* into a SqlDataReader using ExecuteReader. Check the results.
  3. If the id is an int, don't use ID.ToString() on the parameter.AddWithValue. Use the integer itself, as the AddWithValue method with a string value could cause the ID parameter to be configured as a varchar/nvarchar.

Upvotes: 2

Related Questions