VSTO & .NET & Excel

June 20, 2007

Writing to Memo fields with ADO.NET

Filed under: .NET & Excel, VSTO & Excel — Dennis M Wallentin @ 12:05 am

At present I’m working with the new version of my commercial add-in to Excel – SQL Tester. I took the decision to completely re-write it in VB.NET, i e it will be a managed COM Add-ins for Excel 2000 and later. Hopefully it will be available by the end of June.

Anyway, in some services it offer it requires to handling strings variables that exceed 255 characters. In general large text data is not an issue but it becomes an issue when we want to write (save) them to tables in Microsoft Databases (aka Access Databases).

To handle large text data we need to add fields of the type ‘Memo’ (some developers call it “Blob”) to the targeting tables. Compared with the Text field type, which can only store <=255 characters, the Memo field type can store up to about 60,000 characters.

Next we need to consider it when creating the code that writes the large text data to the database.  By using a name for the parameter in the SQL statement and by adding a Parameter Object it’s solved in an easily way.

The following example shows how it can be solved and here I use a class that contains the relevant function (one of many functions in the class).

The function requires 4 strings variables where the last one – ‘Code’ – contains the large textdata.

The SQL statement includes 4 fields where the ‘Code_Text’ represents the Memo field and the statement ends with the name ‘@Code_Text’.  In the final step the Parameter object is created and the command is executed.

 Write data to Memo fields with ADO.NET.

Most controls that offer databinding can handle large text data well. In the present solution I use a control that is first populated with the large text data and it’s easy to get the large text data from that control. However, if it turns out that I need to retrieve the large text data directly from the table I will publish the solution here.

However, if someone already has a solution for it then please posts it here.

If You want a copy of the code it can be downloaded here.

Kind regards,
Dennis

Advertisements

2 Comments »

  1. Dennis,

    you should always use parameters instead of concatenating strings in queries. Maybe the risk is less of a risk in Office applications than web applications, but when you concatenate sql strings you always add the risk of sql injection attacks and problems when a value contains ‘-characters.

    When you use paramters ADO takes care of all this for you.

    There’s a handy method called Parameters.AddWithValue (I think). It’s alot easier to use than the long way you use.

    ADO.NET also have good support for named parameters even for Access. Instead of using ?, you can add real names. In SQL Server it’s common to use @MyParam.

    I hope this is helpful.

    Regards // Johan @ Spider

    Comment by tfsjohan — June 20, 2007 @ 12:21 am

  2. Hej Johan,

    Many thanks for Your kind input – highly appreciated.

    As for the SQL injection it’s not a critical issue in the project where it’s used. I have no experience or knowledge about Web applications.

    I will take a closer look on the ADO.NET Parameters and update the case accordingly.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — June 20, 2007 @ 12:41 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: