SQL stmt produces Error 0

[holly]

Hi

I have this SQl Stmt in one of my procedure but it produces an error 0 on execute...
Can anyone spot the obvious?

_strSql = " update tbl_scale_event SET " & _
               " EVENT_DATETIME = '" & finalDateTime & "', " & _
               " SCALE_NUMBER = '" & CInt(StrScaleNumber) & "', " & _
               " EVENT_STATUS = '" & CStr(Event_Status) & "', " & _
               " Operator_badge = '" & Strshift & Replace(strBadge, " '", "''") & "', " & _
               " Operator_Lastname = '" & Replace(strLast_Name, "'", "''") & "' " & _
               " MANUGACTURE_DATETIME = '" & (finalDateTime) & "'," & _
               " WHERE " & _
               " Scale_Event_ID = '" & (ObjRSprodidvalues.Fields(0).Value) & "'"
               
     m_Intwndprogress = 30
        'if accepted display users Input/executes and initiates a scale transaction
            objOcn.Execute l_strSql 'execute SQL statement

Thanks

[si_the_geek]

yes I can spot the obvious - error 0 means no error!

[si_the_geek]

oooh.. just spotted the commas (and lack of) at the end of each line. here's at least part of it:

" Operator_Lastname = '" & Replace(strLast_Name, "'", "''") & "', " & _
               " MANUGACTURE_DATETIME = '" & (finalDateTime) & "' " & _
               " WHERE " & _

[holly]

Si_the_geek, I have change my sql and I still receive the error
0......the sql stmt is as follows

l_strSql = " update tbl_scale_event SET " & _
               " EVENT_DATETIME = '" & finalDateTime & "', " & _
               " SCALE_NUMBER = '" & CInt(StrScaleNumber) & "', " & _
               " EVENT_STATUS = '" & CStr(Event_Status) & "', " & _
               " Operator_badge = '" & Strshift & Replace(strBadge, "'", "''") & "', " & _
               " Operator_Lastname = '" & Replace(strLast_Name, "'", "''") & "', " & _
               " MANUFACTURE_DATETIME = '" & finalDateTime & "'" & _
               " WHERE " & _
               " Scale_Event_ID = '" & (ObjRSprodidvalues.Fields(0).Value) & "'"
       Debug.Print l_strSql
     m_Intwndprogress = 30
        'if accepted display users Input/executes and initiates a scale transaction
            objOcn.Execute l_strSql 'execute SQL statement

Thanks

If the error 0 means - no error why do I get a msgbox error...

Thanks

[si_the_geek]

Originally posted by holly

If the error 0 means - no error why do I get a msgbox error...

I feel a spot of deja vu...

oh dear... I'm afraid there is only one reason: because of VERY bad code.
The most likely problems:
1) you are not getting an error, but are still showing the messagebox anyway
2) you are clearing the error (using On Error.. or Err.Clear) before you show the message

No matter what you are doing, an error code of 0 ALWAYS (and I do mean always ) means that there has been no error.

I see nothing wrong with the SQL (but I havent looked very hard).. I'll need to know the error to help any more

[holly]

Hi si_the_geek this is my full procedure

is my code that bad?? Maybe there is something
wrong in the code......the error I receive is 'error 0 in procedure Validate_product_code...

Thanks for your help!!

Private Sub validate_product_code(ByVal strBadge As String, ByVal strLast_Name As String, ByVal mfgDate As String, ByVal Strshift As String)
     'Declare Variables
    Dim l_strSql As String
    Dim tmpTime As Date
    Dim tmpDate As Date
    Dim tmpDateTime As String
    Dim finalDateTime As Date
    Dim StrProductLine As String
    Dim ObjProdCmd As ADODB.Command
    Const Event_Status = "PRODWD"
    'format time and date
   On Error GoTo validate_product_code_Error
 
      Set ObjProdCmd = New ADODB.Command 'Create new command instance
        With ObjProdCmd
            .ActiveConnection = objOcn 'set active connection
            .CommandType = adCmdStoredProc
            .CommandText = "SP_Product_ID"  'Stored Proc
            .Parameters.Append .CreateParameter("@ProductID", adVarChar, adParamInput, 10, txtinformation.Text)
        End With
    Set ObjRSprodidvalues = ObjProdCmd.Execute  'execute SP
       'executes SP to validate product
         
         ObjRSprodidvalues.Fields.Refresh 'refresh RS fields
               
     If (ObjRSprodidvalues.BOF) = True Then
        DoEvents
        Load frmvoidopid
        frmvoidopid.Show vbModal
        Exit Sub:
    Else
        ObjRSprodidvalues.MoveLast 'move to last RS
    End If
        ObjRSprodidvalues.Fields.Refresh 'refresh RS fields
       lblScaleEventID = ObjRSprodidvalues.Fields(0).Value
    
    l_strSql = " update tbl_scale_event SET " & _
               " EVENT_DATETIME = '" & finalDateTime & "', " & _
               " SCALE_NUMBER = '" & CInt(StrScaleNumber) & "', " & _
               " EVENT_STATUS = '" & CStr(Event_Status) & "', " & _
               " Operator_badge = '" & Strshift & Replace(strBadge, "'", "''") & "', " & _
               " Operator_Lastname = '" & Replace(strLast_Name, "'", "''") & "', " & _
               " MANUFACTURE_DATETIME = '" & finalDateTime & "'" & _
               " WHERE " & _
               " Scale_Event_ID = '" & (ObjRSprodidvalues.Fields(0).Value) & "'"
       Debug.Print l_strSql
     m_Intwndprogress = 30
        'if accepted display users Input/executes and initiates a scale transaction
            objOcn.Execute l_strSql 'execute SQL statement
            ObjRSprodidvalues.Fields.Refresh 'refresh the database
            lblTendigitCode = "Product Code" + vbCrLf + "FW" + 
 
     check_Product_Line (StrProductLine) 'Procedure to check the relevant product - line
      m_Intwndprogress = 35
   Exit Sub
 
validate_product_code_Error:
    Call ModError_log 'All error descriptions written to a error log
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure validate_product_code of Form frmscan"
 
End Sub

[si_the_geek]

Originally posted by holly
is my code that bad??

only the error handling!
the rest seems ok, apart from the indenting (which is probably fine on your pc)

Number 1 in my last post is not the issue (your "exit sub" does the job). The trouble is the sub "ModError_log" - somewhere in here the error is reset.

You can either show the message before you call the sub, or fix the sub.

I'm guessing that this sub is used in several projects, so you should try to fix that. Alternatively create a new sub which showns the error and does the logging, eg:

Sub ShowError (SubName as String)
 
Dim ErrMessage as String     'Store the message
   ErrMessage = "Error " & Err.Number & " (" & Err.Description & ")" & vbCr  _ 
                       & "in procedure: " & SubName 
 
   Call ModError_log               'Log the error 
 
   Msgbox ErrMessage           'Show the message
 
End Sub
 
 
'in your "validate_product_code" sub:
...
validate_product_code_Error:
   Call ShowError ("validate_product_code of Form frmscan")
...

[holly]

Si, Thanks...I will have a go at changing the ModError_Log and
will let you know....Your help is greatly appreciated...

Thanks

[si_the_geek]

no worries.. good luck

[randem]

holly

You know, it's very simple thing to do and almost no one does it. Put a debug print immediately after you create the SQL statement. You might be amazed how quickly you can spot simple problems.