Query that does not return records in sql server 2012

Dear friends

Greetings to all and grateful in advance for the interest and the scope that can come to solve the following:

With an Xojo application I connect to a SQL SERVER 2012 database

I can correctly make changes to the contents of the fields in the Names Table with UPDATE.
UPDATE Names SET name = ‘JOSE’

but when executing a SELECT, no records are returned, although there are records

Will there be any blockage of the query?

All your answers will be greatly appreciated, to solve why you do not return me records.



Did you define a primary key for your table?

If not, do so and then try again. It should work. (in SQL Server Management Studio, mark one of the fields as key. - I suggest cleaning up any possible duplicate records from the table first).

More info always helps. Xojo version, platform, ODBC or OLDEB, sample code for the query…

a select like that should return rows whether there is or is not a primary key

the Xojo update might fail if you try to edit with out a primary key

but row retrieval should just work

As Tanner said, more info is needed. Does the select query return records in Management Studio (or whatever you use to query the database outside of Xojo)? Are you checking for errors after the query in Xojo?

It’s possible, though extremely unlikely, that the account you’re using to connect from Xojo has UPDATE permission but not SELECT permission.

I hear what you say Norman, but I had this exact issue before and the fix was to add a primary key to the table. Whether it should be that way or not, you surely know better than I do. I was using ADODB and the native client to connect, not the Xojo plugin, nor ODBC. That could have played a role.

at odbc names seems to be a reserved keyword
does it work with [ ] around?

your update clause should have a where part because you update the whole table.

In Management Studio, the query returns records
When I make a query from an XOJO application, no records are returned.

How would I change the permission for selection in the account with which I connect from XOJO to SQL SERVER

[quote=460870:@Raul Juarez Pulache]In Management Studio, the query returns records
When I make a query from an XOJO application, no records are returned.[/quote]

If you’re checking for errors in Xojo, you would know if there is a permission problem. Or, use the same account in Xojo that you’re using in Management Studio to verify if that’s the problem.

But I don’t really think this is the problem. I think it’s something else, and you’re not giving us enough info to help figure it out (like post the full Xojo code you’re using).

Name: pDB
Type: classDBSqlServer

Super: MSSQLServerDatabase

Shared Methods of ClassDBSqlServer

Dim conectado As Boolean
App.pDb = New classDBSqlServer
App.pDb.UserName = "sa"
App.pDb.Password = "4016781"
App.pDb.DatabaseName = "CONSULTAS"

If App.pDb.Connect Then
  conectado= True
  MsgBox("Conexion Satisfactoria a SQL SERVER")
  conectado = False
  MsgBox("Error al conectar a Motor SQL SERVER "+ App.pDb.ErrorMessage)
End If

Return conectado

Methods of ClassDBSqlServer

ProcesaUpd(sql as string) As Boolean
Dim resp As Boolean
Dim stmt As PreparedSQLStatement



If Not Self.Error Then
End If
Return resp
ProcesaCons(sql As integer) As Recordset
Dim stmt As PreparedSQLStatement
Dim rs As RecordSet =stmt.SQLSelect
If Self.Error Then
  MsgBox "Error: No se pudo realizar la consulta"
End If
return rs

command Button Update

Dim sql As string =    "UPDATE nombres SET nombre='LUISA'"
Dim resp As boolean =app.pDb.ProcesaUpd(sql)
IF resp then
  MsgBox "Actualizacion correcta"
  MsgBox "No se pudo realizar la consulta"
End If

Command Button Query

Dim sql As string = "SELECT * FROM Nombres"
Dim rs As RecordSet=app.pDb.Procesacons(sql)
If rs <> nil and rs.RecordCount > 0 Then
  MsgBox "numero de registros: "+str(rs.RecordCount)
  MsgBox "ningun registro"
End If

For starters, I don’t see how this compiles, as you’ve defined the variable sql as an integer.

[quote=460901:@Raul Juarez Pulache]If Self.Error Then
MsgBox “Error: No se pudo realizar la consulta”
End If[/quote]
Here you should add the actual database error message to your message (Self.ErrorMessage).

Here you are not distinguishing between an empty recordset and a nil one. They are different. A nil recordset means the query did not execute correctly. An empty recordset (rs.RecordCount = 0) is a valid recordset, it just doesn’t contain any records.

Thank you so much
Jay Madren

I made a correction, when typing for the forum I did it incorrectly ProcesaCons (sql As integer) As Recordset, when it really is ProcesaCons (sql As string) As Recordset

I have included a code modification in the commandButton

Dim rs As RecordSet = app.pdb.SQLSelect ("SELECT * FROM Names")
If rs = NIL then
   MsgBox "The recordset is NIL"
   MsgBox "The number of records is:" + str (rs.RecordCount)
End if

The message that shows when executing the code is the following:
The number of records is: -1

Any additional comments will be greatly appreciated



Currently only supported by these databases:

ODBCDatabase, although not all ODBC drivers implement this

For databases that do not support this function, RecordCount returns -1. [/quote]

a error/warning with not supported would be better than a -1

test with
SELECT Count(*) AS C FROM Names

or output something here

[code] While Not rs.EOF

For i As Integer = 0 To rs.FieldCount - 1
  ... = rs.IdxField(i + 1).StringValue



Ah, yes! Thanks Markus. Been a while since I developed for SQL Server, and forgot about this.

Raul, as Markus shows, you have to use rs.EOF to determine if the recordset contains any records, and loop through them to determine how many. Or perform a separate COUNT query.

Infinite Thanks
Louis Desjardins
Tanner Lee
Norman Palardy
Jay Madren
Markus Rauch

For your valuable help
really, the query was done correctly, what happened is that recordcount is not compatible with SQL SERVER