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).
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.
[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).
SetupNuevaBD
Dim conectado As Boolean
App.pDb = New classDBSqlServer
App.pDb.Host="DESKTOP-UMB3MKD"
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")
Else
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
stmt=Self.Prepare(sql)
stmt.SQLExecute
If Not Self.Error Then
resp=true
Else
resp=false
End If
Return resp
ProcesaCons(sql As integer) As Recordset
Dim stmt As PreparedSQLStatement
stmt=Self.Prepare(sql)
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"
Else
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)
Else
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.
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"
Else
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
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.