Comments by Sergey Vaselenko

Sergey Vaselenko 24-Jul-14 4:00am View

SaveToDB loads all the data into Excel table at once.
But you may configure the AutoRefresh property of the Excel query to update the data periodically.

RealTimeToExcel allows updating data in real time and not only in regular tables.
But you should configure real time formulas like
=RTD("gartle.rtd",,"sql-express","table name","index key value","result data field name")

Sergey Vaselenko 8-Jul-14 11:13am View

Select is required for some types of copy/paste operations.
Especially if we have the PasteSpecial method.

Sergey Vaselenko 10-Jun-14 10:23am View

Try to use MonthName(Month(date1), TRUE) instead of DATENAME(MONTH, date1).

Sergey Vaselenko 27-May-14 11:52am View

What technique do you use to import data?
Take a look at similar techniques for importing from Excel
http://www.excel-sql-server.com/excel-import-to-sql-server.htm

Sergey Vaselenko 23-May-14 6:22am View

For such tasks you can use the SaveToDB add-in.
It allows getting and updating web data into Microsoft Excel.
But this page does not parsed correctly as it has no common table for all records.

Sergey Vaselenko 22-May-14 12:57pm View

Excellent solution!
It seems that
PARTITION BY Q.QID ORDER BY QO.IOption
is better than
PARTITION BY Q.QID ORDER BY Q.QID

Sergey Vaselenko 20-May-14 14:32pm View

You may use Excel formulas. Why not?

Sergey Vaselenko 20-May-14 11:21am View

You may combine the data in Excel using Excel formulas or in an external tool using Excel and CSV as data sources. What way is the best for you?

Sergey Vaselenko 21-Mar-14 4:40am View

Try to get a working SQL statement first.

Sergey Vaselenko 9-Mar-14 5:27am View

I can't help with Access SQL. You may change the SQL Server tag to Access to get the help of Access developers.

Sergey Vaselenko 8-Mar-14 11:16am View

What version of SQL Server do you use?
SQL Server 2005 has this function.
http://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx

Sergey Vaselenko 3-Mar-14 13:02pm View

If you need to import web data in Excel, try the SaveToDB add-in at http://www.savetodb.com/download.htm.
It allows getting data from HTML, XML, CSV, and JSON webpages.

Sergey Vaselenko 24-Jan-14 5:38am View

Thank you for your feedback. Please accept the solution.

Sergey Vaselenko 21-Jan-14 15:17pm View

You are right. DATEDIFF is just another solution.

Sergey Vaselenko 21-Jan-14 6:53am View

If you need to allow user to select files, use OpenFileDialog.
If you need to get image files in the path, use DirectoryInfo.
If you need to get image properties when user selects an image in your control, you may set the file name in the property 'Image' and calculate them using your code.

Sergey Vaselenko 21-Jan-14 6:26am View

Do you require an image file list in the specified path only?

Sergey Vaselenko 21-Jan-14 6:23am View

I think you would create the function that allow users to select images.
Call this function on the click event and save the selected files in the property.
The property itself is not good place for UI actions and long time operations.

Sergey Vaselenko 21-Jan-14 5:35am View

The first line shows how to convert an integer value to a datetime value.
Further the datetime value can be converted to a varchar, for example.

The second line shows how to convert a datetime value to an integer one.
The function result is 41657 that confirms that conversions work in both ways correctly.

Sergey Vaselenko 9-Jan-14 6:06am View

You are welcome! If the solution solves your task, please, accept it.

Sergey Vaselenko 8-Jan-14 6:29am View

Is it 10 hours a day in any time frame?
Or you have working hours (from .. till ..)?
What about weekends and holidays?

Sergey Vaselenko 8-Jan-14 6:22am View

You have no the execute command for the SELECT statement like cmd.ExecuteReader in the code sample.
Do you have it in your code?

Sergey Vaselenko 3-Jan-14 6:18am View

Install Microsoft SQL Server Compact 4.0 SP1 from here
http://www.microsoft.com/en-us/download/details.aspx?id=30709

You will have the files in the installation folder as mentioned above.
It is a one file that deployes both x86 and x64 files in contrast to 3.5.

See also an article about private deployment:
http://msdn.microsoft.com/en-us/library/aa983326(v=vs.110).aspx

Sergey Vaselenko 6-Dec-13 7:06am View

The most slowest operations is COM (Excel) function calls from VSTO.

You may try to reduce amount of such operations.
For example, cache data loaded from Excel to avoid the same calls.

Can you use Visual Studio Profiler?
It is the simplest way to find slow codes.

Sergey Vaselenko 5-Dec-13 10:59am View

Our Excel add-in allows working with 3.5 and 4.0. And I want to have the wizard to create both version databases.

Sergey Vaselenko 5-Dec-13 10:49am View

Yes. I need have a wizard to create databases of different versions.

Sergey Vaselenko 4-Dec-13 11:56am View

I have updated the code.
I've also added the REPLACE function to qoute the qoutes.

Why are you specify null values as "null" in the output?

Sergey Vaselenko 4-Dec-13 5:13am View

Is this code working if you specify the database using a regular path instead of |DataDirectory|?

Sergey Vaselenko 21-Nov-13 4:23am View

These joins select rows what you have:

OrderMaster om
join OrderDetails od on om.OMID=od.OMIDFormRef
join OrderDetailsChild odc on od.ODID=odc.OrderDetailsIDRef
join OrderDetailsCancel odca on od.ODID=odca.OrderDetailsIDRef

It seems you should group rows before using ROW_NUMBER().
Also add fields to the ORDER BY clause to get the same order every time.

Sergey Vaselenko 23-Oct-13 14:52pm View

Have you solve your task with PivotTables, Sudheer?

Sergey Vaselenko 22-Oct-13 11:30am View

foreach (PivotCache pc in theWorkbook.PivotCaches())
{
pc.Refresh();
}

Sergey Vaselenko 26-Sep-13 11:29am View

See MsoTriState Enumeration at http://msdn.microsoft.com/en-us/library/ff860737(v=office.14).aspx.
msoTrue = -1
msoFalse = 0
I don't found the corresponding VSTO enumeration.

Sergey Vaselenko 26-Sep-13 9:24am View

msoTrue = -1
msoScaleFromTopLeft = 0
See Shape members at http://msdn.microsoft.com/en-us/library/ff193908(v=office.14).aspx.

Sergey Vaselenko 23-Sep-13 16:31pm View

Hello,
Thank you for warning.
I've removed links. This is useful info for developers.

Comments by Sergey Vaselenko (Top 33 by date)