SQL lack of knowledge

[Ahile07]

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.

[JLogan3o13]

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

 

[Ahile07]

@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

expandcollapse popup

#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 July 22, 2016 by Ahile07

[mLipok]

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

And read this:

 

 

Edited July 22, 2016 by mLipok

[Ahile07]

@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 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.

[jchd]

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).

[Ahile07]

@jchd if i use that it will just select the one on top. But if my querry returns 3 values i want to be able to select which one i want from those 3.

[jchd]

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

[Ahile07]

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)

Edited July 22, 2016 by Ahile07

[jchd]

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.

[Ahile07]

@jchd how i can return array? Do you've got an example of GUI how to return my sql values into it?

Thanks

[jchd]

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.

[Ahile07]

@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

[Skysnake]

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:

• DB Browser for SQLite
• SQLiteExpert

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 July 22, 2016 by Skysnake
full sentences... :)

[Ahile07]

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.

[Skysnake]

Have you actually read anything on this Forum or the Help File?

Have a nice day now

Skysnake

[Ahile07]

Obviously i read...cause i've got with the code so far!?
Put some logic into it

Good day to you too mate

[mLipok]

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
 

 

[Kneel]

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.

[TheDcoder]

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