Why no update

hog

hog

Well-known member
Joined
Mar 17, 2003
Messages
984
Location
UK
Can anyone see why this would not work?

Code: 
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 its active field remains unchanged?
 
You cant use the ? identifier in .NET for passing parameters like that. You need to use named parameters.
Code: 
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)
 
Thanks VolteFace Ill give it a whizz.

You say you cant use the ? though? I have my app riddled with this method and they work, this is the first one that hasnt??
 
OK I tried it like this and still no update:(

Code: 
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??
 
I dont 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.
 
The ? syntax is incorrect in .NET; ADO.NET, more specifically.

Here is an excerpt from the MSDN:

[size=small]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[/size]
 
Originally posted by Heiko
If the "?" syntax is correct, then how can you at execution time distinguish between the two parameters (@contractid, @active) ?
Click to expand...


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.)
 
Last edited by a moderator:
Ah! Heres 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) );
 
The MSDN tends to contain a lot of old outdated information (especially if youre using an old outdated version :p), so make sure that when youre 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 youre using the online version.
 
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).
 
OK firstly :)

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

Im using Access not SQL Server.....


Heiko this is the transaction:

Code: 
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?
 
Im thinking in C#, blah.

Id 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.
 
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 doesnt 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
 
Back
Top