Wednesday, July 13, 2011

MySQL Date Range Query (Using Logical Operators)

Here's another version using logical operators in MySQL.
1:  select date_format(date_purchased,'%m/%d/%Y')as dates,   
2:   url from dbcommerce.tblproducts   
3:    where date_format(date_purchased,'%m/%d/%Y') >= '04/30/2011'  
4:     and date_format(date_purchased,'%m/%d/%Y') <= '05/30/2011'  
5:      order by date_purchased asc;  

MySQL Date Range Query

Here is an example to query MySQL Date Range given the date field is of
type TimeStamp. The date_format() will remove the time part in comparing.
1:  select date_format(date_purchased,'%m/%d/%Y')as dates, url   
2:   from dbCommerce.tblproducts   
3:    where date_format(date_purchased,'%m/%d/%Y')  
4:       between '04/30/2011' and '05/30/2011'  
5:           order by date_purchased asc;  

Tuesday, July 12, 2011

Unable to convert MySQL date/time value to System.DateTime

Solution: Add Allow Zero to your Web.Config or App.Config
1:  <add key="connectionString" value="Database=your_db  
2:  ;Data Source=localhost;User Id=root;  
3:  Password=password;Allow Zero Datetime=True;"/>  
Source: Unable To Convert MySQL DateTime value


Monday, July 11, 2011

Newline for C# equivalent with Environment.Newline

txtOutput.Text = "Select All Nodes" + "\r\n";  
txtOutput.Text = "Select All Nodes" + Environment.Newline; 

Sunday, July 10, 2011

Setup SQL Developer to Connect to a MySQL database!!!

1. Download sql developer from oracle site
2. Copy sql developer to drive C:\
3. Copy msvcr71.dll to %WinDir%\System32 [reference:]
msvcr71.dll is located in: [sqldeveloper\jdk\jre\bin\msvcr71.dll]
4. Download mysql-connector-java-5.0.8-bin.jar zip file from this site: Download MySQL connector Java
5. Extract mysql-connector-java-5.0.8-bin.jar in sql developer lib folder: C:\Users\gnesguerra\Downloads\sqldeveloper-\sqldeveloper\lib
6. Setup sql developer to connect to mysql database using the tutorial from this site: Starting from Setting it Up Paragraph: Configuring SQL Developer for MySQL
   6.1 Click Choose Database button below Port label.
   6.2 Beside the Choose Database button, select the database you want to connect to
from the dropdown control.
   6.3 Zero Date Handling should be set to null
   6.4 Click Connect
7. (Increase Operation Timeout)
   7.1 Tools
   7.2 preferences
   7.3 versioning
      7.3.1 general
      7.3.2 set operation timeout to 1 hour or greater
8. Finished.
You should be able to connect to root username or your mysql db instance
NOTE: I found a small bug running MySQL queries in SQL Developer
When you finished executing a query and you want to execute another query,
the editor returns error message (Query execution was interrupted)
The solution is to delete the persisted settings then switch to Query Builder everytime you create a new query. Then wait for a couple of seconds..Sucks!!!

Wednesday, July 6, 2011

Using Proxy With WebClient

Here is a snippet to use WebProxy. This is useful for scraping or other webrequest activities.
   1:    Dim webclient As New System.Net.WebClient  
   2:    Try  
   3:     'true for bypassOnLocal  
   4:     webclient .Proxy =   
   5:          New System.Net.WebProxy("",true)  
   6:     wc.OpenRead("")  
   7:     lblStatus.Text = "ok"   
   8:    Catch  
   9:     lblStatus.Text = "error proxy"  
  10:    End Try  

Format query result by count() with comma using Format()

I played around with MySQL count() statement. I found it annoying that the count()
will simply return whole numbers. Example: the total record of a table is 32,272.
In MySQL SQL, this will simply return 32272. Below are two sql scripts. The first
one returns whole number,while the second query returns comma separated number.

1:  select count(*) as num_records_notformatted from mytable where name like '%j%';  
2:  Solution:  
3:  select format(count(id),0) as num_records_formatted from mytable   
4:  where name like '%j%';  

Tuesday, July 5, 2011

Scrollbars in WPF Textbox

You can set the scrollbars in a WPF TextBox control using it's properties:
or using the ScrollView class:

Control.InvokeRequired not Found (WPF)

I was doing a little WPF threading recently which involved changing my codes from Winforms to WPF. Surprisingly, I was not able to find the code below in the intellisense:
After googling, I found this link: Why is Dispatcher.CheckAccess() hidden from intellisense?
I wonder why Microsoft hid some features on intellisense. Or it could be a bug.