Strange Error (query format)

[micro]

I have this sub:

In Globals quote = Chr(39)

Public Sub ReadAllFind(DataTable, Colname, Cod)    'ricerca fatta per Cod nel campo Data <Colname>
Dim i, y As Number
Dim s
    SQL.CreateConnection(AppPath & "\Archivi\" & DataTable & ".sql", SQL.PassSQL)
    y = SQL.GetTableCount(DataTable)
    If y > 0 Then
        SQL.Connection.BeginTransaction
        SQL.Command.AddParameter(Colname)
        SQL.Command.SetParameter(Colname, Cod)
        SQL.Command.CommandText = "SELECT * FROM " & DataTable & " WHERE " & ColName  & " = " & quote & Cod & quote
        SQL.Data.Value = SQL.Command.ExecuteReader
        If SQL.Data.ReadNextRow = True Then
            For i = 0 To y - 1
                s = s & SQL.Data.GetValue(i) & CRLF
            Next i
        Else
        End If
        SQL.Data.Close
        SQL.Connection.EndTransaction
    End If
    SQL.CloseConnection(AppPath & "\Archivi\" & DataTable & ".sql", False)
    Return s
End Sub

but an error is displays, see attached image.
In practice the last quote is omitted, why?

Cod is a data formatting as string (es: 21/02/2014)

 

[mjcoon]

I have this sub:

In Globals quote = Chr(39)

Public Sub ReadAllFind(DataTable, Colname, Cod)    'ricerca fatta per Cod nel campo Data <Colname>
Dim i, y As Number
Dim s
    SQL.CreateConnection(AppPath & "\Archivi\" & DataTable & ".sql", SQL.PassSQL)
    y = SQL.GetTableCount(DataTable)
    If y > 0 Then
        SQL.Connection.BeginTransaction
        SQL.Command.AddParameter(Colname)
        SQL.Command.SetParameter(Colname, Cod)
        SQL.Command.CommandText = "SELECT * FROM " & DataTable & " WHERE " & ColName  & " = " & quote & Cod & quote
        SQL.Data.Value = SQL.Command.ExecuteReader
        If SQL.Data.ReadNextRow = True Then
            For i = 0 To y - 1
                s = s & SQL.Data.GetValue(i) & CRLF
            Next i
        Else
        End If
        SQL.Data.Close
        SQL.Connection.EndTransaction
    End If
    SQL.CloseConnection(AppPath & "\Archivi\" & DataTable & ".sql", False)
    Return s
End Sub

but an error is displays, see attached image.
In practice the last quote is omitted, why?

Cod is a data formatting as string (es: 21/02/2014)

In saying "In practice the last quote is omitted" do you mean that the "'" (which I would call an apostrophe) at the end of the CommandText is not visible if you breakpoint and examine in the IDE?

Since the apostrophe does not terminate a Basic string, you could just include it in the fixed portion, e.g.

SQL.Command.CommandText = "SELECT * FROM " & DataTable & " WHERE " & ColName  & "='" & quote & Cod & "'"

BTW I would not put a space after the "=" (though looking at some of my own code I sometimes do!).

I am certain that the BeginTransaction/EndTransaction are not required since you are not changing the data and no rollback can be invoked.

If you attach a complete piece of test code then we could actually run it for ourselves...

Mike.

 

[micro]

resolved
Cod contained more number of null characters (0) on the right.
Wrong conversion from byte to string.
thanks anyway

 

[mjcoon]

Thanks for clarification!

Mike.