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
“SELECT * FROM Names”
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).
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
MsgBox("Conexion Satisfactoria a SQL SERVER")
conectado = False
MsgBox("Error al conectar a Motor SQL SERVER "+ App.pDb.ErrorMessage)
Methods of ClassDBSqlServer
ProcesaUpd(sql as string) As Boolean
Dim resp As Boolean
Dim stmt As PreparedSQLStatement
If Not Self.Error Then
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"
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"
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"
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”
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
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)
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
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.
For your valuable help
really, the query was done correctly, what happened is that recordcount is not compatible with SQL SERVER