Function STR - numeric expression missing

[PMan]

Hallo,

ich bekomme in der Zeile sFfldValue[iSP] = Str(Records.Value(iRecID, sDBfldNames.String(iSP))) beim Compilieren eine Fehlermeldung: syntax error: numeric expression missing in line ... near pos 42 in ...

Code: Select all

 Dim bExist as Boolean, bChange as Boolean
Dim sFfldValue[1] as String
Dim sRecID as String, sSQL as String
Dim sDBfldNames as Strings = New Strings

  If Database.SetCurrentDatabase(sDBName) Then
    sSQL             = "SELECT * FROM " & sTblName & ";"
    sRecID          = Records.Open(sDBName, sSQL)
    sDBfldNames = Records.FieldNames(sRecID)
'    iDS               = Records.Length(sRecID)
  EndIf

     bExist  = False
      bChange = False
      sSQL    = "SELECT * FROM " & sTblName & _
                "WHERE ID_Con = " & sCols.String(1) & ";"
      sRecID  = Records.Open(sDBName, sSQL)
      Print "Field-Name", "File-Value", "DB-Value"
      If Records.Length(sRecID) >= 1 Then
        bExist = True
        For iSP = 1 To iNrCols
          If FieldIsNum(sDBName, sTblName, sDBfldNames.String(iSP)) Then
            sFfldValue[iSP] = Str(Records.Value(iRecID, sDBfldNames.String(iSP)))
          Else
            sFfldValue[iSP] = Records.Value(iRecID, sDBfldNames.String(iSP))
          EndIf
          Print sFfldName[iSP], sCols.String(iSP), sFfldValue[iSP]
          If sCols.String(iSP) <> sFfldValue[iSP] Then
            bChange = True
          EndIf
        Next iSP
      EndIf


Die Funktion FieldIsNum ermittelt zur Laufzeit, ob es sich bei dem entsprechenden Datenfeld um ein numerisches Feld oder ein Stringfeld handelt. Zum Zeitpunkt des Compilierens ist das noch offen, da die Datentabelle aus numerischen und Stringfeldern besteht. Die strenge Prüfung des Compilers bezüglich eines numerischen Inhalts ist also hier meines Erachtens nicht angebracht.

Was kann ich unternehmen,um den Code dennoch zum Laufen zu bringen?

/PMan

[Henning]

Shouldn't this be sRecID? Either close the first opened sRecId before reusing, or Dim a new iRecId to use.

Str(Records.Value(sRecID, sDBfldNames.String(iSP)))

And use Records.First:
If sRecordsId Then
If Records.First(sRecordsId) Then
recCount = Records.Length(sRecordsId)

/Henning

[PMan]

Thank you, Henning, i closed the recordset, but it is not the solution. The Records.first(sRecID) is ok, but not nessary. I have tested, but no change either using or not.

I get another error on the other choise of the if statemant: systax error: assignment expression missing in line ... near pos 37 in ...

It seemed, that Records.Value have a problem with the fieldname as a variable as part of a strings.

[Henning]

Are you really sure you get the fieldnames ok?

When passing a string value in a SQL query, it should have single "ticks"(') around it. If sCols.String(1) is a string...

Code: Select all

      sSQL    = "SELECT * FROM " & sTblName & _
                "WHERE ID_Con = '" & sCols.String(1) & "';"


In the above it is: ID_Con = single quote double quote & sCols.String(1) & double quote single quote ; double quote.

Long ago I learned from B that using Records.First is a must. Without it the recordpointer will have a value pointing to the sky somewhere, and it will have a value even if there is no record. Don't know if that have changed.

/Henning

[PMan]

I am shure that the fieldname aud the value is ok because Print sSQL shows

SELECT * FROM cat_continent WHERE ID_Con = 1;

ID_Con is the ID of the data record and a numeric filed. If there are ticks around it, the select statement would run wrong.

I have Records.First in the actual code, but the Records.Value get errors.

/PMan

[Henning]

Hmmm

This works ok:

Code: Select all

          Dim fld As String
          fld = "artnr"
          ArtRec(rc).arNr = Records.Value(sRecordsId, fld)


What does this print?

Code: Select all

        For iSP = 1 To iNrCols
Print sDBfldNames.String(iSP)
          If FieldIsNum(sDBName, sTblName, sDBfldNames.String(iSP)) Then
            sFfldValue[iSP] = Str(Records.Value(iRecID, sDBfldNames.String(iSP)))



/Henning

[PMan]

Print sDBfldNames.String(iSP)

This print shows the name of the actual datafield.

The datafieldnames are written in a stringsvariable with: sDBfldNames = Records.FieldNames(sRecID)

[Henning]

Will it be different if you insert a temp variable as

Code: Select all

Dim fld As String   ' outside the loop

fld = sDBfldNames.String(iSP)
sFfldValue[iSP] = Str(Records.Value(iRecID, fld))


/Henning

[PMan]

Hi Henning,

a long way ago I found the error.

Because i have some errors in the example, I rebuild the routine with queries. So I can't test your idea in this routine. I write another routine with recordsets and I get no error. Why? I find out, that the values of a recordsets are all strings and you can't convert a string to a string!
That was the error.

Thangs PMan