SQL Excel

Hi Al!

Exactly. I think as soon as SQL Excel automatically connects to the conenction string passed (by looking up the profile, if needed, but I don't see the necessity...) the user should be able to immediately continue his work on the query.

-regards,
Roland

Hi Roland !

Thanks again.

No probs, will do it by checking first for profile in connection settings file. Then if no match - connect anyway. It should be fine.

I still have to look into that point you were making before about the connection string not making exactly. It seems like it is just the situation where all the parameters are not specifed in the original connection string - so ADO adds the missing parameters with the default values.. Thats what it sounds like anyway. Will check it..

Thanks and have a nice night. Al

Hi Al!

Another minor quirk I've observed now: When invoking SQL Excel with the SQLExcel_LoadMainFormtoGetSQLStatement method and not having any profile set up, there is a nasty error box: Index out of valid bounds (GetProfileNameFromConnectionsArray)

Also there is a problem with resolution 1024x768: The Get SQL Button is so far to the right that you can't see/use it. Maybe overblending controls could help (e.g. the embed button is disabled anyway, so displaying the get SQL button in it's place won't hurt too much...)

-regards,
Roland

Hi Roland !

Thanks for finding that.. You did great testing.
For the error I will fix it this weekend.

Also, for the resolution issue, I am trying to get a good way to re layout the form. There needs to be a few more buttons up the top and no space. also, the form is too big now I think.. Also have to add a menu in as well which will take a bit of space too. I am thinking of reducing all the buttons in size a bit ..

The "Get SQL" resolution will be fixed as part of the main fix.

When I have a better idea of this and will post up the idea..

Thanks and bye for now,
al

hi Roland ! Hope the weekend is going fine.

I did some work on the addin there.

First thing, is that I have the prototype "storing of settings in". So you will have to have a new file - sql_excel_settings.xml in your sql application settings folder (the same folder as the connection_settings.xml file). This file stores your basic settings for the main form. You can download the file from the temp download location. At the moment, the form to update the settings is on a sub tab of the "about" form - but the form does not work yet. So if you want to change the settings you need to manaully change the xml file.

For your changes,
- I made a change to the SQLExcel_LoadMainFormtoGetSQLStatement method so that you wont get any error when you have no profiles set up. Thanks very much for finding this error. Hopefully it is fine now.
- For the other previously reported issue - where you mentioned there was a problem with needing to reconnect (and then lose your SQL Statement), please can you give a bit more information on this. I could not reproduce it.
Logically, there should be no problmem here though as far as I see. The connection to the database is opened up only as needed. So when you are working with the main form you don't have any connection open. When you hit the "exeucte" or paste or embed query functionality the connection is opened automatically.. Ok, I think I might be missing something here so please let me know if this is the case. But when I tested it seemed fine and I can work with the query and then paste without a problem.

Please let me know on these two issues. I have a few more issues listed for you - ado library behaviour (connection string change), screen resolution issue and opening up of the addin for other office applications but I am looking at these seperately and would like to close out the public method issues first.. I think the connection string thing though is something we have to live with..

Ok, thanks a million for the feedback. I will do some more testing on these functions later today but I think they are fine now.
Thanks and bye for now, Al

Hi Al!

Well, when you go into the SQLExcel_LoadMainFormtoGetSQLStatement like that

Dim oSQLExcel As Object

sConnectionString = "provider=SQLOLEDB.1;server=OEBFASRV02;Trusted_Connection=Yes;Database=pubs"
sSQLStatement = "Select * From employee"
Debug.Print "Original Connection String = " & sConnectionString
Debug.Print "Original SQL Statement = " & sSQLStatement

Set oSQLExcel = CreateObject("SqlExcel.Connect"

If oSQLExcel.SQLExcel_LoadMainFormtoGetSQLStatement(sConnectionString, sSQLStatement) Then
Debug.Print "New Connection String = " & sConnectionString
Debug.Print "New SQL Statement = " & sSQLStatement
Else
Debug.Print "GetSQLStatementFromSQLExcel returned False: Function Failed"
End If
Set oSQLExcel = Nothing


there is no way to execute the passed "Select * from employee" inside sql-excel, except changing the profile to the "pubs" profile, losing the query doing that. This doesn't work
as well for other connection strings like "provider=MSDASQL.1;Driver={SQL Server};server=OEBFASRV02;Trusted_Connection=Yes;Database=pubs"

When trying to paste or embed immediately, the form gives the message "There is no active connection open !", so I basically can't do anything without connecting to another profile....

I'm also quite sure that it's not good to have paste/embed available when being invoked by SQLExcel_LoadMainFormtoGetSQLStatement, as it offers an inconsistent way of leaving the form and - alas - already found anm inconsistency: When connecting to another profile after the invocation (which makes paste/embed possible) and changing the query to something else, then pressing paste returns to the caller of SQLExcel_LoadMainFormtoGetSQLStatement, however passing the original query, NOT the changed one..

Concerning the ADODB connection string changing issue, I'd really strongly suggest to use the workaround (storing the "true" connection string somewhere else), as the strangely modified connection string makes it very hard to reuse that connection string somewhere else. For the lookup of the profile, that doesn't have to be as long as connecting to the passed connection string works properly (see above).

Sorry, but I can't offer any more help...

I've also tested the newest version (from 29. june), the error message on startup (when having no profiles set) is gone, thanks for that, there is however still the err message (probably falling into the same category as the "execute immediately after invocation" issues), when hitting the execute button: "Index out of valid bounds (GetDataStringFromConnectionsArray" plus the already known err message right after that: "Operation not allowed on a closed object (CommandExecute_Click)"

-regards,
Roland

Hi Roland

Thanks yet again for the detailed feedback.

I am going to reply on a few of the issues now as well as I can. I should be able to look at them all this weekend and hopefully have the fixes in.

For the main issue

QUOTE:
no way to execute the passed "Select * from employee" inside sql-excel

I reproduced this issue just tonight. It seems like it is connection string / data provider specific. I didnt look at it in detail but did reproduce it which is good for now. With my original test connection strings there was no problem...

For the

QUOTE:
not good to have paste/embed available when being invoked by SQLExcel_LoadMainFormtoGetSQLStatement

I see you point. Originally I thought it was better to have all functionality available but it can break the calling procedure so will look back into this - basically the "GETSQL" function should only return SQL and not offer any other functionality.


For the

QUOTE:
connection string changing issue,

, will look into this. Not clear what to do at this point if it is ADO behaviour. But we must be able to do something.

For the

QUOTE:
err message (probably falling into the same category as the "execute immediately after invocation" issues),

This should be ok when we disable the Execute button when the main form is loaded from the "GETSQL" method. I will still check this out to see what is happening.

I think that covers all your points. Final point is that I will reenable the "Execute" and "Embed" buttons but only when called by other public methods. I think these can be useful too.


Thanks very much for the feedback. Will post back up when I have an update on them - probably on Saturday.. have a nice night,
Al

Hi Al!

Well, please don't disable the Execute button, this is one very useful feature (actually the most important, to check whether your query yields correct results) !!

However, it's going to be fixed as soon as the main issue is resolved (which DB/provider are you using for testing?)

Concerning the third point (connection string change issue), I'd leave ADO as a connection string "storage" and store the passed connection string separately (and get it directly from the profile settings, if the user changed the profile during the session). ADO is not reliable for that purpose....

-regards,
Roland

Thanks Roland, for getting back to me on this.
Yes, Execute should still be there.. Didnt think of it yesterday..
So, "Embed" and "Paste" will be the only things disabled.. I will look into the positioning of "GET SQL".

For this issue then, when you hit the "Close" or "Get SQL" or hit the "X" button, you will get back the paramters. I think they are the 3 situations that need to be handled for this function for it to be done correctly.

For the Execute, it will be fine. Need to see what is going on. I test usually with SQL Server and MYSQL and these are the ones I had working fine with the Get SQL public method.. I test a bunch of other datbase types too when doing main testing.

Thanks for the connection strings idea. So,
(1) If the user uses the same connection string as he invoked the form with - return the connection string from the invokation requestion
(2) If the user changes the connection while using the form - return the connection string from the connection setting file (by passing the profile).

I think that is it..

Thanks a million, have to rush out. have a nice day, Al

hi Roland
fyi, I just put some of the changes in.

I just uploaded a new version of the dll to the temp location.

Summary of changes
- Should be should be able to use execute now when called from public method
- connection string passed back should be the original one - except if you change the connection (then it uses the profile one)
- disabled embed/paste buttons when form is called from public method

There are no errors that I know of. have done initial testing and all seems fine. will test more over the weekend

Open issues are - screen resolution and implementation in Word etc (I think that is it..)

have to rush out. have a nice weekend, al

Hi Al!

Great!

Just tested it, works perfectly! So after getting the screen resolution and Word integration you're definitely into a new release (I hope) !!
Did you have a chance to take a look at DBAddin yet?
Sorry, If I won't answer your postings in the next weeks, I'm leaving for holidays...

-regards,
Roland

hi Roland

Very good!! I am delighted it seems fine. I still have a few fixes for the new settings form but testing has gone fine on everything else.

I will probably have done something for the resolution issue soon enough as I can move some items slightly but word /accesss items will take a while - think I need a full day to do a good job of it. Few weeks from now I would guess..

I have been reading the documention for DB Addins but did not install it yet.. Very sorry man. I will do it and am really looking forward to using it but so busy all the time these days.. Dont want to touch the computer today after this

Have a great holiday and take it easy.
Al

Hi Roland ! I hope you had a very nice holiday.

Sorry, but one of the members (Lorifan) has some questions on DB Addin.
The question is posted under "Query using cells from Excel cells"

Very sorry but have been so busy have not had a chance to try out DB Addin yet or do anything more on SQL Excel.. Will do it but it is taking for ever to get spare time.

Anyway, if you have time please could you take a look at what Lorifan is asking..

Hope all is going great. see you,
Al

Hi Al!

Nice to hear from you, back from Holiday and back to Business...
It was also good to hear that you are working on a new release, when do you think this will be finished?

Did you have a look at the DBAddin in the meantime?

-regards,
Roland

Nice to hear from you Roland !!! I hope all is going great.

I have not been able to do too much recently unfortunately. Very busy with my regular job. But I did do a few things.

I am not sure when the next release will be out but I am hoping within 3 or 4 weeks.. That is a realistic estimate.. Next release will just be the options form and the integration within word. Options part is close but not fully tested but still messing around with the word addin part.
I need to check back with you for that. Am going to look into it again over the weekend. Not able to do it during the week as v busy in job.

I did get a new name for the site though. www.sqldrill.com Think it is a good name. When we open the addin up for the other office applications it is good there is a more generic name.

For DB Addin, I did look the other day the web site but did not install it yet. Sorry, will get to it one of these days.. One thing I noticed was it was not that easy to find the link to the web site. Had to look around a bit to find it. If you want will add a link up here or if you want you can add a forum category in for it.
Better go, have a nice night there.
Al