Jump to content

SQL lack of knowledge


Recommended Posts

Hello guys,

maybe some of you can help me. I manage to connect with sql database and use a variable to search for different things when i do the select from database. My problem is that i'm reading the database with an MsgBox to see the values. But sometimes for the thing i'm filtering the dabase for it shows me different values and i only one to select one of it and move forward with my script. Is there any chance to read the database...view it...and in front of each value to have a thick box to select with value i want for the line.

After i select the the line value i want to stock the values from each column in different variables and then i want to send them as values to write in another database.

Is this possible?

Hope any of you can help me in this matter :)

Apreciate guys.

Link to comment
Share on other sites

  • Moderators

@Ahile07 how about posting your code so we can see just what you're doing, rather than trying to guess? ;)

 

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

@JLogan3o13 please find below my code so far.
 I've got the variables for db, server, ass and user deleted from the code. But so far it connects with the data base and it shows in a msgbox all the line for the SO that i'm inputting. i want to be able to only select one of the line

#include <_SQL.au3>
#include <array.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstantsEx.au3>
#include <SendMessage.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <ColorConstants.au3>
#include <WinAPIGdi.au3>
#include <WinAPISys.au3>

;Connect to DB
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$OADODB = _SQL_Startup()
If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect(-1, $ServerAddress, $DatabaseName, $ServerUserName, $ServerPassword) = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf


;SO variable
Global $soNo = InputBox("Order number", "Please input the SO number. Only the number is required.","", "",500, 150, Default, Default)
        If $soNo = "" Then

        MsgBox ($MB_SYSTEMMODAL, "", "Please fill all fileds. Try again!", 2)
    
    Exit
     Else

;Test query
Global $fullSQL

EndIf

;all
If _Sql_GetTableAsString(-1, "SELECT * FROM [Table].[dbo].[tabletable] WHERE [S/O] LIKE '%"&$soNo&"%'", $fullSQL) = $SQL_OK Then
Else
   MsgBox(0 + 16 + 262144, "SQL Error", _SQL_GetErrMsg())
EndIf

MsgBox(0, "List of values", $fullSQL,1)

 

Edited by Ahile07
Link to comment
Share on other sites

Please read:  How to post code on the forum * and edit your post.

And read this:

 

 

Edited by mLipok

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

@mLipok thank you. I try to make any of those scripts to work. I don't know how :~( When someone is putting a code or examples they never work. All the developers seems to have a problem to explain things :P i'm not a developer. I just play a bit with the code. But if i can't find explanations of how to adapt the code to make it work then i get stuck. Like in the ADO examples. I try to put my database user, pass and server but is not working. Copied the ado.u3 in includes. but nothing.

sorry.

Link to comment
Share on other sites

If you only want to get ONE row, just instruct SQL to do so:

...
;all
If _Sql_GetTableAsString(-1, "SELECT * FROM [Table].[dbo].[tabletable] WHERE [S/O] LIKE '%"&$soNo&"%' LIMIT 1", $fullSQL) <> $SQL_OK Then
   MsgBox(0 + 16 + 262144, "SQL Error", _SQL_GetErrMsg())
EndIf
...

Of course, depending on the DB engine you use, the correct syntax may be different (see for instance http://www.w3schools.com/sql/sql_top.asp).

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Ah, that was unclear until now. Then fill a listbox with the resultset and pick the one you want from there.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

yes. that's pretty much what i want. to be able to return my values as a list box to be able to thick te value that i want to move forward

but i don't know how (yet)

:D

Edited by Ahile07
Link to comment
Share on other sites

First don't use that function to return the resultset as a string, use the function which returns an array. Then look at help to see how easy it is to create and fill a listbox, ...

This is a simple GUI question, no actual link to SQL.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Quote

how i can return array?

There must be such a function in the UDF you use. If not, use the ADO UDF offered by mLipok.

Quote

Do you've got an example of GUI how to return my sql values into it?

Look at examples in the help about how to create and populate a listbox, then select a row within it.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

@jchd can't make the ADO to work.

I'll carry on searching on google or wait for more answers. As your answers are not very helpful mate - same as the ADO. Is not usfeful for someone like me to send me millions of examples if they're not friendly for me...to know how to use them. Usually all the examples that i find on this forums are not working :) but thank you anyway. Apreciate :)

Link to comment
Share on other sites

Dear @Ahile07, please start with the _SQLite_xxx functions in the Help file.

 

First note that SQLite should be installed with Windows 10, which should make the ADO work, but then I may be wrong on this.  If you want SQLite, you need to download it from here: https://www.sqlite.org/download.html. You will need to download the SQLite zip file and unzip the content to a location where you script sees it, like @scriptdir, also, make sure the name of the SQLitexxx.dll in your script matches the name of the file on disk.

At which level is the nature of your question? Do you want to learn to query SQL databases? If SQLite then look for a database management tool and practice your SQL query writing skills there. Suggested:

If you want to learn how to integrate SQL into an AutoIt script, look at the Help File.

Specifically the _SQLite_GetTable2d function.  Your result will be returned as an array.  Now you need to learn about array management.

 

As @jchd, points out, if you want only one row, make SQL do the work for you.

Looking at a single row presents problems:

1. Does your data contain only one row?

2. Is your query written to return only a single row (limit 1)?

3.1 If multiple rows in the result set, how is this ordered?

3.2 Once you have the results in 3.1 - which single row is of interest to you? 

 

Regards from a friend of Lil Bobby Tables;

:)

Skysnake

Edited by Skysnake
full sentences... :)

Skysnake

Why is the snake in the sky?

Link to comment
Share on other sites

Cheers. Apreciate....but not helpful.
Don't want to sound rude but do you have a solution - real solution - facts - not different ways!? Please feel free to show me.

We all know theories...but please...if you have a solution - easy one...please write it here :)

 

Apreciate guys :)

Thank you.

Link to comment
Share on other sites

On 21.07.2016 at 4:57 PM, Ahile07 said:

After i select the the line value i want to stock the values from each column in different variables and then i want to send them as values to write in another database.

Sure.

 

_ADO_Connection_Create
_ADO_Connection_OpenConString
_ADO_Execute
_ADO_Recordset_ToArray
_ADO_RecordsetArray_GetContent
 

now try to open second connection:

_ADO_Connection_Create
_ADO_Connection_OpenConString

and using array which you get from _ADO_RecordsetArray_GetContent you can use a For To Next loop to update each values in second database of cours using:

_ADO_Execute
 

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

  • 3 months later...

Pull values of each column into an array and make a listbox with the array.

If you use "select distinct firstname from mytable" you will get only one copy of each ("distinct") value in the table - so, if there are 3 "John" firstname rows, only one "John" in the result.

Link to comment
Share on other sites

On 22/7/2016 at 5:39 PM, Skysnake said:

First note that SQLite should be installed with Windows 10

This applies to ALL versions of windows since AutoIt v3.3.14.2

EasyCodeIt - A cross-platform AutoIt implementation - Fund the development! (GitHub will double your donations for a limited time)

DcodingTheWeb Forum - Follow for updates and Join for discussion

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...