Why no update

[hog]

Can anyone see why this would not work?

 

Private Sub CheckForClosedContract()

       ' set internal error flag to false

       m_Error = False

       ' create SQL string to obtain active jobs

       m_strSQL = "SELECT COUNT(jobid) AS TotalRecords FROM tblJobs WHERE (contractid = ?) AND (active = ?)"

       ' set properties of the oledbcommand object

       m_odcJob.CommandType = CommandType.Text
       m_odcJob.CommandText = m_strSQL

       ' set the parameter details

       m_odcJob.Parameters.Add("contractid", m_ContractID)
       m_odcJob.Parameters.Add("active", True)

       ' check if the connection is set and open it if not

       If m_odcJob.Connection Is Nothing Then

               m_odcJob.Connection = m_objConn
               m_odcJob.Connection.Open()

       End If

       ' try to obtain the count of active jobs, if there are any return, otherwise contract jobs are all closed
       ' so close contract

       Try

           If CType(m_odcJob.ExecuteScalar(), Integer) Then

               Return

           Else

               ' create SQL string to archive the contract with this contractid

               m_strSQL = "UPDATE tblContracts SET active = 0 WHERE (contractid = ?)"

               ' set properties of the oledbcommand object

               m_odcJob.CommandType = CommandType.Text
               m_odcJob.CommandText = m_strSQL

               ' set the parameter details

               m_odcJob.Parameters.Add("contractid", m_ContractID)

               ' execute the delete query

               m_odcJob.ExecuteNonQuery()

           End If

       Catch objException As Exception

           ShowError("Location:   Class Job" & ControlChars.CrLf & ControlChars.CrLf & _
                     "Procedure:  CheckForClosedContract()" & ControlChars.CrLf & _
                     ControlChars.CrLf & "Error Text: " & objException.Message)

           ' set internal error flag to true

           m_Error = True

       Finally

           ' if the connection is open then close it

           If m_odcJob.Connection.State = ConnectionState.Open Then

               m_odcJob.Connection.Close()

           End If

       End Try

   End Sub

 

When I step through the code it does not error and runs the code m_odcJob.ExecuteNonQuery() but when I check the contracts table it's active field remains unchanged?

[Volte]

You can't use the ? identifier in .NET for passing parameters like that. You need to use named parameters.

m_strSQL = "UPDATE tblContracts SET active = 0 WHERE (contractid = @contractid)"
' set properties of the oledbcommand object
m_odcJob.CommandType = CommandType.Text
m_odcJob.CommandText = m_strSQL

' set the parameter details                
m_odcJob.Parameters.Add("@contractid", m_ContractID)

[hog]

Thanks VolteFace I'll give it a whizz.

 

You say you can't use the ? though? I have my app riddled with this method and they work, this is the first one that hasn't??

[hog]

OK I tried it like this and still no update:(

 

Private Sub CheckForClosedContract()

       ' set internal error flag to false

       m_Error = False

       ' create SQL string to obtain active jobs

       m_strSQL = "SELECT COUNT(jobid) AS TotalRecords FROM tblJobs WHERE (contractid = @contractid) AND (active = @active)"

       ' set properties of the oledbcommand object

       m_odcJob.CommandType = CommandType.Text
       m_odcJob.CommandText = m_strSQL

       ' set the parameter details

       m_odcJob.Parameters.Add("@contractid", m_ContractID)
       m_odcJob.Parameters.Add("@active", -1)

       ' check if the connection is set and open

       If m_odcJob.Connection Is Nothing Then

               m_odcJob.Connection = m_objConn
               m_odcJob.Connection.Open()

       End If

       ' try to obtain the count of active jobs, if there are any return, otherwise contract jobs are all closed
       ' so close contract

       Try

           If CType(m_odcJob.ExecuteScalar(), Integer) Then

               Return

           Else

               ' create SQL string to archive the contract with this contractid

               m_strSQL = "UPDATE tblContracts SET active = 0 WHERE (contractid = @contractid)"

               ' set properties of the oledbcommand object

               m_odcJob.CommandType = CommandType.Text
               m_odcJob.CommandText = m_strSQL

               ' set the parameter details

               m_odcJob.Parameters.Add("@contractid", m_ContractID)

               ' execute the delete query

               m_odcJob.ExecuteNonQuery()

           End If

       Catch objException As Exception

           ShowError("Location:   Class Job" & ControlChars.CrLf & ControlChars.CrLf & _
                     "Procedure:  CheckForClosedContract()" & ControlChars.CrLf & _
                     ControlChars.CrLf & "Error Text: " & objException.Message)

           ' set internal error flag to true

           m_Error = True

       Finally

           ' if the connection is open then close it

           If m_odcJob.Connection.State = ConnectionState.Open Then

               m_odcJob.Connection.Close()

           End If

       End Try

   End Sub

 

Might it have something to do with the call to m_odcJob.ExecuteNonQuery()? Is there something that might need resetting??

[alpineguy]

Where are you setting the value of @contractid ?

[alpineguy]

Nevermind, found it. :) Hmm... *research*

[alpineguy]

You might want to chack out how to make odbcparameters at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbcparameterclasstopic.asp

 

In VB, I think the "?" syntax is correct.

[Heiko]

I don't see any transaction.

Could that be the problem?

 

What if you copy your m_odbcjob.commandtext - just before executing - into the clipboard, then switch to the databases SQL interface, paste the SQL and execute it.

[Heiko]

If the "?" syntax is correct, then how can you at execution time distinguish between the two parameters (@contractid, @active) ?

 

:-)

[Volte]

The ? syntax is incorrect in .NET; ADO.NET, more specifically.

 

Here is an excerpt from the MSDN:

 

The .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a Command of CommandType.Text. In this case, named parameters must be used. For example:

 

SELECT * FROM Customers WHERE CustomerID = @CustomerID

[alpineguy]

If the "?" syntax is correct, then how can you at execution time distinguish between the two parameters (@contractid, @active) ?

 

Weird, I saw it in a lot of .NET Documentation earlier today. I figure if Microsoft uses it, it should be valid, right? :P

 

(edit: Maybe enumerate? *snicker* That would be messy.)

Edited June 24, 2003 by alpineguy

[alpineguy]

Ah! Here's something:

m_odcJob.Parameters.Add("@contractid", m_ContractID)

m_odcJob.Parameters.Add("@active", -1)

 

The .Add() method is crazy-overloaded, and I think the arguments you want to give it are .Add( String, Object ); In the second example, you pass to it '-1', which is a base type and not an object. I can only assume that the ContractID will also be a number, so make sure you turn that into an object first, as well.

 

So, this turns into (assuming m_ContractID is an int):

 

m_odcJob.Parameters.Add("@contractid", Convert.ToInt32(m_ContractID) );

m_odcJob.Parameters.Add("@active", Convert.ToInt32(-1) );

[Volte]

The MSDN tends to contain a lot of old outdated information (especially if you're using an old outdated version :p), so make sure that when you're reading something that it is clear that it for ADO.NET; I have seen VBScript documentation in the MSDN that came with my VS.NET, so watch out for stuff like that. Even moreso if you're using the online version.

[Volte]

Explicit conversion to 'Object' is unnecessary, and everything is derived from Object anyway. Explicit conversion *from* Object to anything else is generally needed (especially when using Option Strict).

[hog]

OK firstly :)

 

Yes I can confirm that my code works fine elsewhere usin ?

 

I'm using Access not SQL Server.....

 

 

Heiko this is the transaction:

 

m_strSQL = "UPDATE tblContracts SET active = 0 WHERE (contractid = @contractid)"

' set properties of the oledbcommand object

m_odcJob.CommandType = CommandType.Text
m_odcJob.CommandText = m_strSQL

' set the parameter details

m_odcJob.Parameters.Add("@contractid", m_ContractID)

' execute the delete query

m_odcJob.ExecuteNonQuery()

 

This approach works in other sections of my app?

[alpineguy]

I'm pretty sure int itself is not an object. Int32, however, is.

[hog]

Sorry? where is int?

[alpineguy]

I'm thinking in C#, blah.

 

I'd still try replacing:

m_odcJob.Parameters.Add("@contractid", m_ContractID)

 

With:

m_odcJob.Parameters.Add("@contractid", OdbcType.Int).Value = m_ContractID

 

That is, if ContractID is an integer.

[jfackler]

A quote from Microsoft Press, Microsoft ADO.net Step by Step copyright 2002 by Rebecca Riordan:

 

"Unfortunately, the two Data Providers supplied in the .NET Framework use different syntax. OleDbCommand objects use a question mark (?) as a placeholder for a parameter:

 

SELECT * FROM Customers WHERE CustomerID = ?

 

SqlDbCommand objects use named parameters, prefixed with the @ character:

 

SELECT * FROM Customers WHERE CustomerID = @custID

 

....you must then add each of the parameters to the Parameters collection of the Command object."

 

"The Parameters collection provides a number of methods for configuring the colleciton at run time.....Note that because the OleDbCommand doesn't suport named parameters, the parameter will be subtituted in the order they are found in the Parameters collection. Because of this, it is important that you configure the items in the collection correctly." i.e. in the correct order.

 

 

Jon

[Heiko]

OK, Sorry. I was talking SqlDbCommand.