Quote from PaulJonestindall on August 25, 2022, 11:26 am

dbpFind will show the first instance of [lastname] that it finds in the "lastname" field.

dbpQuery will list all records in your grid where it finds [lastname] in the "lastname" field.

For example:

SetVar "[lastname]" "Smith"
dbpQuery "001" "propietarios" "lastname = [#34][lastname][#34]"

will find all occurrences of Smith in the "lastname" field and display only those records in your grid.

Quote from joferar333 on August 25, 2022, 1:33 pm

Yes @pauljonestindall new found ok!

Thanks for your help

Best Regards

Quote from ebear on August 25, 2022, 1:48 pm

dbpQuery is very limited...

The better way to Query an Database is to use:

dbpExecSQL "001" "SELECT * FROM propietarios WHERE lastname = '[lastname]' ORDER BY FirstName ASC" "List"

With dbpExecSQL it is possible to use all the possibilities a Database system has look for example at the red part above. Some Query language knowledge is needed, but google is your friend.

Kind regards,

Eric

 

 

Quote from Gaev on August 25, 2022, 2:38 pm

@ebear

Won't dbpQuery followed by a dbpSort achieve the same end result ?

Quote from joferar333 on August 25, 2022, 3:09 pm

Hi @ebear, @gaev

this code not foun for me

 

dbpExecSQL "001" "SELECT * FROM propietarios WHERE lastname = '[lastname]' ORDER BY FirstName ASC" "List"

ERROR

Cannot perform this operation on a closed dataset

And this code yes working for me

 

SetVar "[lastname]" "Smith"
dbpQuery "001" "propietarios" "lastname = [#34][lastname][#34]"

Thans!

Quote from joferar333 on August 25, 2022, 10:04 pm

@pauljonestindall,@gaev,@vadim, @ebear

I'd like to confirm if I'm right...

I use this command to add data to my table called PROPIETARIOS

THE Fields are:
carpeta (numeric)
apellido (string)
nombres (string)
direccion (string)
telefono (numeric)
correo(string)

The numerical fields carpeta and telefono do not include quotes right?
I'm doing it like this...

dbpExecSQL "001" "INSERT INTO propietarios (carpeta,apellido,nombres,direccion,telefono,correo) VALUES ([carpeta],'[apellido]','[nombres]','[direccion]',[telefono],'[correo]');" ""

 

ESPAÑOL

Me gustaria poder confirmar si estoy en lo cierto...

utilizo este comando para agregar datos a mi tabla llamada PROPIETARIOS

LOS Campos son: carpeta (numerico)
apellido (string)
nombres (string)
direccion (string)
telefono (numerico)
correo (string)

Los campos numericos carpeta y telefono no llegan comillas verdad?
yo lo estoy haciendo asi...

dbpExecSQL "001" "INSERT INTO propietarios (carpeta,apellido,nombres,direccion,telefono,correo) VALUES ([carpeta],'[apellido]','[nombre]','[direccion]',[telefono],'[correo]');" ""

Thanks!

Quote from ebear on August 26, 2022, 8:32 am

Hello All,

 

@Gaev you are correct it will have the same effect as I did in my example with dbpExecSQL. The advance of using dbpExecSQL is that you can use all the statements of the SQL language which gives the most advanced possibilities. But some knowledge is needed of the SQL language. Also depending on the database type it is important to know that there are differences in the SQL language.
I'm building with VisualNeo very advanced database applications and the more simpler commands from dBPro are not enough to build these. The dbpExecSQL commands can archive all the possibilities needed for advanced db applications.

@joferar333; dbpExecSQL "001" "SELECT * FROM propietarios WHERE lastname = '[lastname]' ORDER BY FirstName ASC" "List" was only an example. I think that the error is caused because FirstName doesn't exist in you database. I don't know how you table is configurated.

Your statement:

dbpExecSQL "001" "INSERT INTO propietarios (carpeta,apellido,nombres,direccion,telefono,correo) VALUES ([carpeta],'[apellido]','[nombres]','[direccion]',[telefono],'[correo]');" ""

Seems correct to me. Which database are you using???

Best regards,

 

Eric

 

 

 

 

 

 

 

Quote from PaulJonestindall on August 26, 2022, 12:05 pm

@ebear, @gaev

From the start, we don't actually know what kind of database this is, do we? From the type of questions here, don't we want to start with the basics?

@joferar333 Is this an Access MDB that you're working with or some other type of database?

My assumption for now is it's an MDB file. If so, then DBPro has a lot of tools to very easily manipulate the database without a lot of, or even any SQL knowledge. While more advanced features can be used with dbpExecSQL, you still have to keep in mind what type of SQL database your working with whether it's a PostgreSQL, MySQL, etc. As someone here once said, SQL stands for Structured Query Language, NOT Standardized Query Language.

To answer your last question, probably the most confident way to add data to your table would be to add a new record and set the compound variables designated to the database and table in use. And, to be absolutely sure that the data is saved you can add the last line to save your edits to the table.

dbpAddRecord "001" "PROPIETARIOS"
SetVar "[001.PROPIETARIOS.carpeta]" "[number1]"
SetVar "[001.PROPIETARIOS.apellido]" "[string1]"
SetVar "[001.PROPIETARIOS.nombres]" "[name1]"
SetVar "[001.PROPIETARIOS.direccion]" "[string2]"
SetVar "[001.PROPIETARIOS.telefono]" "[number2]"
SetVar "[001.PROPIETARIOS.correo]" "[string3]"
dbpSaveEdits "001" "PROPIETARIOS"

In your list of fields you have the telefono field property as numeric. Just remember you have to take into account different phone number formats that include +, - and spaces (i.e. +01 228-555-1212 vs +02 45 3470 333).

Quote from joferar333 on August 26, 2022, 4:15 pm

Hi friend @ebear AND @PaulJonestindall

First of all, my special thanks to both of them because they take the time and patience to help those of us who do not have the ability to know certain commands with certainty.

As you suppose (PaulJonestindall) the database is MDB and I created it with NeodbPRO I already have most of the commands more or less solved, but what eric asked me + some videos I'm watching are making me see the panorama of using the advanced command that the plugin itself brings...

I have been presented with a problem that I cannot solve, I have created a table called immo, it has only 3 fields file, autoincrement (primary key) folder (numeric) location (string) But when I want to insert it gives me a syntax error in the insert I know I'm making a mistake when entering the autoincremental value, I put it like this, what am I doing wrong?

dbpExecSQL "inmo" "INSERT INTO properties (file,folder,location) VALUES (null,[folder],'[address]');"