MSSQLServerPreparedStatement difficulty

Databases
1

I am not able to return a recordset with a MSSQLServerPreparedStatement. However, I can return a recordset with the equivalent database SQLSelect.
This code works and returns a recordset.

If db.Connect Then
  dim rs as RecordSet  = db.SQLSelect("SELECT Cus_No, Cus_Name FROM ARCusFil_SQL where Cus_No = '000000000100' ")
      If rs <> Nil Then
      While Not rs.EOF
        cusnostring = rs.IdxField(1).StringValue.Trim
        rs.MoveNext
      Wend
      rs.Close
    End If
  Else
    MsgBox("Connection error:" + db.ErrorMessage)
  End If

This code does not work. The rs is Nil.

If db.Connect Then
      Dim ps As MSSQLServerPreparedStatement
      ps = db.Prepare("SELECT Cus_No, Cus_Name FROM ARCusFil_SQL Where Cus_No = ? ")  
      ps.Bind(0, "000000000100")
      Dim rs As RecordSet = ps.SQLSelect
      If rs <> Nil Then
      While Not rs.EOF
        cusnostring = rs.IdxField(1).StringValue.Trim
        rs.MoveNext
      Wend
      rs.Close
    End If
  Else
    MsgBox("Connection error:" + db.ErrorMessage)
  End If

Can anyone offer some suggestions as to the problem with my prepared statement code?
Thanks,
pat

2

Make sure you set the bind types
http://documentation.xojo.com/index.php/MSSQLServerPreparedStatement

   Dim ps As MSSQLServerPreparedStatement
   ps = db.Prepare("SELECT Cus_No, Cus_Name FROM ARCusFil_SQL Where Cus_No = ? ")  
   ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
   ps.Bind(0, "000000000100")
3

Norman,
Thank you. That was an error in my posting. The BindType statement is in my code. I just forgot to include it in my post.
I saw that document and I my code is essentially a copy of it.
This is my first exposure to Xojo and to the MSSQLServerPreparedStatement. I am sure it works and that I am just missing something elementary.
pat

Corrected code.

Dim ps As MSSQLServerPreparedStatement ps = db.Prepare("SELECT Cus_No, Cus_Name FROM ARCusFil_SQL Where Cus_No = ? ") ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING) ps.Bind(0, "000000000100") Dim rs As RecordSet = ps.SQLSelect

4

Is ‘Cus_No’ really a String in the DB?

5

Since the original query was Cus_No = ‘000000000100’ with a MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING type it should work.
Have you tried

if rs<>nil then ... your code else msgbox("MSPreparedStatementError:"+db.ErrorMessage) end if

6

You might want to check out this thread. https://forum.xojo.com/21916-mssqlpreparedstatement-not-working/p2#p182954 where we discuss issues with MSSQLServerPreparedStatement. This is the related feedback case: <https://xojo.com/issue/39027>

Unless it’s been fixed recently, the only workaround I could find was to use an ODBC connection.

7

SQLSelect returns a Nil RecordSet from even the simplest query using MSSQLServerPreparedStatement. There are no MSSQLServerDatabase errors. This appears to be a bug that should get some attention and a fix.

<https://xojo.com/issue/39027>

Thanks for the Feedback case @Jim Cramer and your testing and example project @Kem Tekinay. It’s now one one of My Top Cases.