.Net ramblings
# Tuesday, 16 March 2004
Howto: use MySql with .Net

MySQL is a great alternative to SQL Server because it is open source and nearly has the performance to rival SQL Server. Applications using Microsoft Access databases can suffer seriously if the transaction load is anything above minimal. This article is my collection of instructions and tips for getting up and running with MySQL and .NET.

Downloads

  1. Download & Install MySQL 4.0 (16 Mb)

  2. Download & Install ODBC.NET Data Provider (800 k)

  3. Download & Install MyODBC 2.5 (1.5 Mb)
    Note: the newer versions do not work with .NET at time of writing

  4. Optional: Download & Install DBManager, GUI for MySQL tables, highly recommended (3 Mb)

Testing

  • Make sure the MySQL service is running in Windows.

  • Load up the WinMySQLAdmin.exe tool in the bin folder of your MySQL install. This program controls the MySQL server. Feel free to customise any settings you wish, e.g. change the Server Name, Port Number, username & password etc.

  • Load up DBManager and connect to the Server. The default user is root with a blank password. You can create your own users or keep using the root. Create a test database and then right-click the 'Tables' node under the database you just created. Use the 'Table Editor' to define the fields for the table. Click the 'Save' icon to save your changes. Users from MS Access backgrounds may want to read below.

To connect to the database through .NET code:
Add a reference to Microsoft.Data.Odbc.dll, this should be in 'C:\Program Files\Microsoft.NET\Odbc.Net'

using Microsoft.Data.Odbc;
...
stringconnString="'driver={MySql};uid=root;pwd=;server=127.0.0.1;database=test;OPTION=17923";
string sql = "select * from table1 where ShortName = ?";

// declare an OdbcCommand object with a new OdbcConnection
OdbcCommand command = new OdbcCommand(sql, new OdbcConnection(connString));
// add a paramter to the command corresponding to the ? in the sql statement
OdbcParameter dbParam = new OdbcParameter('ShortName", OdbcType.VarChar, 50);
dbParam.Value = "bob";
command.Parameters.Add(dbParam);

DataSet ds = new DataSet();
OdbcDataAdapter dba = new OdbcDataAdapter();
dba.SelectCommand = command;
try
{
  command.Connection.Open();
  dba.Fill(ds);
}
catch(Exception ex)
{
  throw ex;
}
finally
{
  command.Connection.Close();
}

Unfortunately, MS never provided OdbcDataAdapter & OdbcCommand GUI support for Visual Studio so you have to do all that manually, as in the code above. However, there are commercial components that solve this.

The 'System Error' ODBC message
If you get a 'System Error' message thrown in your code using MySQL, with no useful information, you need to catch the line of code causing the error and throw the exception explicitly (i have no idea why, but MS have a kb article about it).

Note on DBManager Queries
This version of MySQL doesn't support Stored Procedures, but you can still save queries to the database. Click the 'Create new query' button in DBManager and enter the SQL you want. There is a query designer but the intellisense doesn't work well and it can be annoying to use. Save the query and it appears as an extra tab after 'DataSheet' in the lower right corner. When you close DBManager, next time you open it, that tab won't be there and it will look like the query was only temporary. They are actually stored permanently, and accessible via the 'Tools' tab on the lower right of the screen.

Notes on the MySQL field types for anyone from an MS Access background

  • MySQL uses the single apostrophe character for quoting values, e.g. select * from table where name = 'bob'. the double quote character causes a syntax error.
  • there is no 'bool' field type as such, use 'TinyInt' and change the size to 1 bit, then use 1 to represent true and 0 for false.
  • the varchar field type is limited to 255 characters, in MS Access you would use a Memo to go beyond this. MySQL has the tinytext, mediumtext and longtext field types to support larger max characters.
  • to force the input to match a list of values, e.g. Categories: Animal / Vegetable / Mineral, use the 'Set' field type, with comma separated values in single quotes: 'Animal', 'Vegetable', 'Mineral'
  • the 'Now()' function is supported in MySQL as a default value for a DateTime or Date field, but when you enter this in DBManager, the value appears as 0000-00-00 00:00:00 in the Table Editor, this seems unusual but the function works as expected.

BTW, if you're looking for .NET web hosts that support MySQL, check out www.webhost.ie


Tuesday, 16 March 2004 23:27:39 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database

Problem setting text for a password asp.net textbox

This short article outlines a quirk of the textbox control in Asp.Net, when set to password mode.  If you try and use myTextBox.Text = ….; it doesn't happen.

You might want to use something like this if you are presenting users with the facility to change a password, and you want to give them the option of changing their password, shown in stars, by simply deleting the stars and typing in a new one.  To accomplish this, use the following syntax:

myTextBox.Attributes("Value") = someString;

I can't think of a good reason ms built it this way at all.  oh for .net 2.0...


Tuesday, 16 March 2004 23:06:12 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Asp.Net

# Monday, 15 March 2004
Windows Forms DataGrid throwing a null reference exception on DataRowView.CancelEdit()

I encountered a frustrating issue using the Windows forms Datagrid today.  This problem was also posted without reply on an ms newsgroup. I'm posting my work around here for the record. 

Problem Description
My windows form had a tabcontrol, with 2 tab pages, each with a datagrid, bound to a table in a dataset.  The problem occurred when the user switched from the second tab to the first, with the last row of the datagrid being left as a new row with default null values. It was in 'edit mode' usually because the user added a new row, or pressed enter, which automatically places the cursor in another empty new row, with the default values. The exception I got was generated by the framework and not from my code, as follows:

Object reference not set to instance of an object, at:

System.Data.DataRowView.CancelEdit() at
System.Windows.Forms.CurrencyManager.CancelCurrentEdit() at
System.Windows.Forms.DataGrid.OnLeave_Grid() at System.Windows.Forms.Control.InvokeMarshaledCallbacks()

The exception is interesting in that the CurrencyManager kicks in when the focus of the DataGrid has left, and cancels the cell currently being edited. 

Solution
I tried several solutions, and eventually worked around the problem by forcing the user to click a button to switch from one tab to the other (I hid the tab change buttons off the screen), and then disabling whichever grid wasn't being used after the button was pressed.  This seemed to change the way the DataGrid cancelled the edit of the last row, and the exception doesn't happen now.

It's not an elegant solution, so I'm open to suggestions. I do think the datagrid should be able to execute CancelCurrentEdit without throwing exceptions, and I would be interested to understand why it happens.  I guess it will be fixed in .Net version 2.


Monday, 15 March 2004 19:12:33 (GMT Standard Time, UTC+00:00)  #    Comments [2]  .Net Windows Forms

# Saturday, 13 March 2004
Howto: configure a Psion Series 5 for internet access with a Nokia 6610 (GPRS)

This took ages to figure out, and plenty of experimenting with different settings, so hopefully this might save someone else the hastle of having to spend half a day to get their Psion 5 (not MX) connected to the net with a Nokia 6610.  I would say it would work with similar settings on other GPRS phones.

The internet settings are for the O2 network in Ireland and will need adjustments for a different network.

  1. install Psion Series 5 Message Suite version 1.52.
  2. Create a new modem called nokia 6610:
     speed 57600
     connection infrared
     fax class auto,
     init string: ATZ
     flow control: hardware (RTS/CTS)
  3. Create new Internet Service called GPRS with the following settings.
     
     connection type: dialup
     uncheck smart dialing
     standard dial-up number: *99#
     manual login unchecked
     username gprs 
     password gprs
     addresses: get IP from server. get dns address from server unchecked.
     primary dns address: 62.40.32.33
     secondary dns address: 62.40.32.34
     login: use login script checked. default 8 bit no partity 1 stop bit,
     delete all content of login script text box, leave blank.
     advanced: enable PPP extensions checked. allow plain text authentication checked.  
  4. On the psion, set Remote Link to Infrared 57600.
  5. Get the GPRS settings for your phone from your network. O2 will send those details to your phone if you request it from http://www1.o2.ie/CM/getcontent/0,1177,txt_html_OTA_Home,00.html. Save these details to your phone.
  6. Activate Infrared on the phone. Connectivity > Infrared.
  7. Point the Infrared port of the phone thin black band on top of phone) to the Infrared port on the psion (1cm band on back of psion) a few inches away.

Saturday, 13 March 2004 23:10:23 (GMT Standard Time, UTC+00:00)  #    Comments [0]  General

Introduction...

Hi, welcome to my blog.  This is where I will post solutions to problems I encounter while writing software for the .Net platform, in the hope that they may be of some use to people trawling google for an answer to the same problem.  I realise that I wouldn't be half the developer I am today if it weren't for the likes of Charles Carroll's aspNG community, GotDotNet, ms newsgroups, asp.net, windowsforms.com, to name but a few.  I have drawn on these resources for advice and technical assistance many times and this is my way of returning some of that generosity to the community at large, (as well as a space for me to rant about stuff and things).

a little about me
I've been developing in .Net since Beta 2 in 2001 and I write in C#.  I did my undergrad in University of Limerick in Computer Systems, and now I am self employed, doing contracts, training courses, developing software, etc. in Limerick, Ireland.

Flying Simon's Super-10 kite at CastleGregory, Ireland 2002Outside work, I like surfing, table-tennis, flying big kites, cooking and other stuff, so from time to time, you may see the odd post that is nothing to do with .Net...


Saturday, 13 March 2004 19:12:01 (GMT Standard Time, UTC+00:00)  #    Comments [3]  General

404 woes in Windows Server

I've recently started running Windows Server 2003 on my .Net development machine, and so far I love it, hasn't crashed yet, not like win2k, but there was bound to be some config issues..

problem description:
but i ran into problems opening a web project in visual studio 2002, i'm running .Net Framework 1.0 and 1.1 together.  VS would say "404 Not Found" and the two locations need to map to the same folder blah blah blah.  I checked out http://localhost/myproj/myproj.csproj and it did indeed give a 404 error, even though the file was definitely there.

solution:
i trawled google for a while and found an article that describes a similar problem, due to unknown mime-types in IIS 6. it suggested adding in a new mime-type for whatever file type you were using. i checked my mime-types list and .csproj was there already, and .webinfo.  i don't know of other files used in loading a web project, but i added in a new mime-type for .* and put "application/octet-stream" in with it, and it works fine now.

if anyone knows why this is, or how else to work around, please enlighten me, but as long as the above works, that's enough for me ;-)


Saturday, 13 March 2004 12:40:56 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Windows Server