.Net ramblings
# Sunday, 01 January 2012
Locating the TypeGuessRows registry key on Windows 64-bit operating systems

Data truncated to 255 characters with Excel Jet/ODBC driver: http://support.microsoft.com/kb/189897

Solution: http://support.sas.com/kb/31/765.html
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Set to 0 to avoid the truncation problem!


Sunday, 01 January 2012 22:19:39 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General | Database

# Tuesday, 14 September 2010
Gridview Delete via LinqDataSource fails with ChangeConflictException: Row not found or changed
if you use SqlMetal to generate DataContext classes against an SQL database, you might run into some problems with GridView and deleting a row, when using a LinqDataSource.
this was a very frustrating problem to track down, apparently there is a bug in the LinqDataSource with datetime fields.  i kept getting this error: ChangeConflictException: Row not found or changed
and there was no apparent reason why it was happening, because the same code worked for other tables.  I eventually narrowed it down to the only difference between the two tables, a non nullable datetime field. changing this field to nullable removed the problem.
this thread was useful in troubleshooting the problem.
i also found that calling DataBind() on the LinqDataSource before re-binding the GridView helped get rid of this error message.


Tuesday, 14 September 2010 12:50:05 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Database

# Sunday, 11 January 2009
SQL 2005 cannot drop user with owned schema
i notice when i backup a database and restore it to another server, the user IDs are different and i have to delete the users and add them back in to the database.  however last time i tried this the drop failed because the user owned a schema.  i right-clicked on the user's properties and the tick box for 'db_owner' was shaded in a read only state.  so i couldn't delete the user and couldn't remove their schema ownership.  took a while to figure out that the way to do this is via the Database > Security > Schemas menu, then change the schema owner back to itself, e.g. db_owner.  then the user is free to be deleted.


Sunday, 11 January 2009 15:28:04 (GMT Standard Time, UTC+00:00)  #    Comments [2]  Database

# Wednesday, 10 September 2008
Alter a column with a default value
say you wanted to change a BIT column to a NVARCHAR column, and the BIT column has a default value set to 0 or 1.  you can't run the following statement or you get a dependent object error.
alter table TABLE1 alter column COL1 NVarChar(MAX)
so i found this solution here, after you've run the query below, you can alter the column as above.

DECLARE @df SYSNAME
SET @df = 
 (SELECT OBJECT_NAME(cdefault) 
  FROM SYSCOLUMNS 
  WHERE id = OBJECT_ID('dbo.TABLE1') 
   AND name = 'COL1') 
IF @df IS NOT NULL 
 BEGIN 
  EXEC sp_rename @df, 'df_to_drop', 'OBJECT' 
  ALTER TABLE dbo.TABLE1 DROP CONSTRAINT df_to_drop 
 END


Wednesday, 10 September 2008 10:46:01 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Friday, 09 May 2008
SQL: String Split Function
I don't know why this isn't part of the built-in functions, especially in sql 2005 but anyway, here it is thanks to this groups post:
CREATE FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
Then you can do something like this:
select Items from dbo.Split(@List, ',')


Friday, 09 May 2008 12:40:24 (GMT Daylight Time, UTC+01:00)  #    Comments [6]  Database

# Tuesday, 18 September 2007
Profiler For SQL 2005 Express
wow, i never knew this existed: http://sqlprofiler.googlepages.com/
thanks to nikolay.zhebrun
it works great.


Tuesday, 18 September 2007 13:43:58 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Database

LINQ Sql Using Skip And Take
Here is the sql that is generated by a DataContext when you use Skip() and Take() to efficiently select records for the grid:
SELECT TOP 10 [t1].[Name], [t1].[Address], [t1].[Tel1], [t1].[Tel2], [t1].[Email], [t1].[DateCommenced], [t1].[Comments], [t1].[Active], [t1].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Name], [t0].[Address], [t0].[Tel1], [t0].[Tel2], [t0].[Email], [t0].[DateCommenced], [t0].[Comments], [t0].[Active], [t0].[Fax]) AS [ROW_NUMBER], [t0].[Name], [t0].[Address], [t0].[Tel1], [t0].[Tel2], [t0].[Email], [t0].[DateCommenced], [t0].[Comments], [t0].[Active], [t0].[Fax]
FROM [dbo].[Table1] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [50]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

it's not immediately obvious why they use the subquery like this, but i'm sure they have been very thorough in optimising LINQ. 


Tuesday, 18 September 2007 13:22:32 (GMT Daylight Time, UTC+01:00)  #    Comments [1]  .Net General | Asp.Net | Database

# Friday, 31 August 2007
SQL: order by with parameters of different datatype
It's fair enough that SQL won't accept a parameterised query like below, because it cannot verify that the parameter is referring to a valid column.
select * from table order by @OrderBy
the work around then is to use a case statement like so:
select * from table order by case 
when @OrderBy = 'Column1' then Column1
when @OrderBy = 'Column2' then Column2
end
but i ran into a problem with this approach, where sql raises an error if the datatypes of the columns are not all the same, e.g. you may want to sort by an Int or NVarChar column.  the datatype precedence rules applied to the case statement are well explained in this post on google groups.  the solution posted by Erland Sommarskog is to have a separate case statement for each clause.  so the more robust approach is like so:
select * from table order by 
case when @OrderBy = 'Column1' then Column1 end,
case when @OrderBy = 'Column2' then Column2 end




Friday, 31 August 2007 12:36:35 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Database

# Wednesday, 16 May 2007
Sql Triggers for Dummies
i always found the syntax of SQL triggers very difficult to remember, so i'm just posting a sample one here.  it performs a cascade delete on a table that doesn't have referential integrity (because there are 2 potential foreign keys).
ALTER TRIGGER [dbo].[Table1CascadeDeleteTable2]
ON [dbo].[Table1]
FOR DELETE
AS
BEGIN
declare @Ref int;
SELECT @Ref = Ref FROM Deleted;
delete from Table2 where Ref = @Ref;
END


Wednesday, 16 May 2007 18:36:37 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Tuesday, 01 May 2007
SQL: format numeric to x decimal places
i found this was very hard to figure out.  i read lots of approaches to hack the data into char(5) etc to give you "23.54" using Round() etc.  but that seemed messy.  the key is to cast to type Numeric(size, decimalPlaces).  for example:
CONVERT(Numeric(10,2), (Count(ID) * 100. / @Total)) as Percentage

the above will display "23.54" (for example), including rounding, as a number.


Tuesday, 01 May 2007 12:53:15 (GMT Daylight Time, UTC+01:00)  #    Comments [3]  Database

# Tuesday, 17 April 2007
SQL: optional parameters in stored procedure where clause
I found this out today, if you want a stored procedure to handle optional values without limiting the result set when the parameter is NULL, you can use a CASE/WHEN statement to ignore the parameter in the where clause if it is null.  you just set ColumnX=ColumnX via the CASE statement, which will always be true, effectively resulting in (TRUE AND {remaining condition})
Procedure dbo.Select_Totals_For_Year
(
@Name NvarChar(200),
@Year int
)
AS
BEGIN
select Name, Count(ID) AS [Total]
FROM Table1
WHERE Name = @Name AND
Year = (CASE WHEN @Year is null THEN Year ELSE @Year END)
group by Name
order by Name
END

Tuesday, 17 April 2007 14:43:30 (GMT Daylight Time, UTC+01:00)  #    Comments [6]  Database

# Friday, 13 April 2007
SQL: Display full name in "Surname, Firstname" format.
not the most elegant solution you'll ever see, but i find it very useful when i don't want to store several columns for the different parts of a person's name in a database table.
it takes the surname based on the last space in the name.  so "Paddy Joe Gonzales" will be listed as "Gonzales, Paddy Joe".
Right(FullName, PatIndex('% %', Reverse(FullName))) + ', ' + Left(FullName, Len(FullName) - PatIndex('% %', Reverse(FullName)))    


Friday, 13 April 2007 14:13:30 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Monday, 02 April 2007
How to get YYYY-MM date formats in SQL
not exactly rocket science, but i always forget this sort of thing. 
CONVERT(char(7), GetDate(), 121)

this gives a date like "2007-03".  it works because SQL formats the dates like YYYY-MM-DD etc., so you can just take the first X characters to take as much of the date as you want.

another handy one i use is to get DD/MM/YYYY format:

Convert(NVarChar(10), GetDate(), 103)

the full list of date styles is on msdn2

Monday, 02 April 2007 14:28:57 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Friday, 09 March 2007
Really simple and affordable web server monitoring
If you run a web server, chances are you have some form of automated monitoring system in place.  If you use MOM or another enterprise level thing then this post won't be of much relevance.  If, like me, you have simpler requirements, read on.

I have been caught out a few times with my web sites being down because Windows Server 2003 automatically installed an update and something went wrong and IIS got stopped, or like this morning at 4am, SQL 2005 SP2 failed to install and left the SQL Service offline.  i didn't find out till i got a phone call from a client.

My datacenter provide very good ping monitoring with SMS alerts etc., but this is not a complete solution because the web site may have a configuration error, and it will still respond to pings.  similarly, you can't just check for an OK HTTP status code because your error ASPX page may not be configured to send an error HTTP status code.

I have used various online web site monitoring services, with varying degrees of success / satisfaction.  My current provider are InternetVista.com and for €70 a year i get a 10 minute check for a single HTTP site, with a keyword match on the contents of the page, and an email/sms alert if the match is not found.  You can pay for extra and more frequent checks, but €70 is as much as i think the service is worth.  To have this level of checking done on 10 sites would cost a lot, so to save a few quid i wrote a very simple aspx page that does a series of tests on all the resources i want to verify on the server, e.g. SQL Server, MS Access, IIS web sites.  The aspx code is listed below, i wrote it inline rather than compiled/dll because it is easier to deploy in an existing web site without any risk of any side effects (dll collisions), it should be straight forward to understand for a c# programmer.  let me know if you have questions.  It runs in a few miliseconds on my server so i'm not worried about polling all these resources every 10 mins.



Run_Server_Tests.aspx code:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Net" %>

<script RunAt="server">

/* Server Monitoring Script:
* - test SQL databases by running an sql string against an SQL connection string
* - test Access databases by running an sql string against a JET connection string
* - test web sites by Regex matching a search string against the contents of a HttpWebRequest
*/

enum TestType {Sql_Server, Ms_Access, Http_Request } // different types of supported requests

/// <summary>
/// Container class to represent a 'test' object for a resource on the server.
/// </summary>
class TestObject
{
public TestType Type; // e.g. Sql_Server.
public string TestString; // e.g. connection string for a database. or URI for http request.
public string TestParam; // e.g. sql string for a database. or search string for a http request.

public TestObject(TestType type, string testString, string testParam)
{
this.Type = type;
this.TestString = testString;
this.TestParam = testParam;
}
}

void Page_Load(object sender, EventArgs e)
{
List<TestObject> tests = new List<TestObject>();

tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB1;Integrated Security=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB2;Integrated Security=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB3;Integrated Security=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB4;Integrated Security=True", "select top 10 * from Table1"));

tests.Add(new TestObject(TestType.Ms_Access, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\Database\DB5.mdb;Persist Security Info=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Ms_Access, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\Database\DB6.mdb;Persist Security Info=True", "select top 10 * from Table1"));

tests.Add(new TestObject(TestType.Http_Request, "http://mysite1.ie/", "Site 1"));
tests.Add(new TestObject(TestType.Http_Request, "http://mysite2.ie/", "Site 2"));
tests.Add(new TestObject(TestType.Http_Request, "https://mysite3.ie/", "Site 3"));
tests.Add(new TestObject(TestType.Http_Request, "https://mysite4.ie/", "Site 4"));
tests.Add(new TestObject(TestType.Http_Request, "https://mysite5.ie/", "Site 5"));

int numCompleted = 0;
int numFailed = 0;

// write the HTML header. (a result is flushed to the client after each test finishes.)
Flush(@"
<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>
<html>
<head>
<title>Server Test</title>
<meta name='ROBOTS' content='NOINDEX,NOFOLLOW'>
<link rel='stylesheet' type='text/css' href='ServerTestStyles.css' />
</head>
<body>");

foreach(TestObject test in tests)
{
try
{
switch(test.Type)
{
case TestType.Sql_Server:
runQuerySql(test.TestParam, test.TestString);
break;
case TestType.Ms_Access:
runQueryOleDb(test.TestParam, test.TestString);
break;
case TestType.Http_Request:
string pageContents = new WebClient().DownloadString(test.TestString);
if(!Regex.IsMatch(pageContents, test.TestParam, RegexOptions.IgnoreCase))
throw new Exception("Search string not found: " + test.TestParam);
break;
default:
throw new Exception("Test type not handled " + test.Type);
}
Flush(String.Format("<span class='pass'>Pass</span> &nbsp; <span class='type'>{0}</span> &nbsp; {1} <hr />", test.Type, test.TestString));
numCompleted++;
}
catch(Exception ex)
{
Flush(String.Format("<span class='fail'>Fail</span> &nbsp; {1} <span class='type'>{0}</span><BR><span class='error'>{2}</span><hr />", test.Type, test.TestString, ex.Message));
numFailed++;
}
}
if(numFailed > 0)
Flush(String.Format("<h1>{0} errors occured</h1>", numFailed));
else
Flush(String.Format("<h1>All Good!</h1>", numFailed)); // if you use this page with an automated monitoring service, look for "All Good" in the page contents. otherwise an error occured
Flush("</body></html>");
}

/// <summary>
/// Method to run an sql string against an sql database
/// </summary>
public static DataSet runQuerySql(string sql, string connString)
{
SqlConnection conn = new SqlConnection(connString);
DataSet ds = new DataSet();
SqlDataAdapter dba = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, conn);

try
{
dba.SelectCommand = cmd;
dba.Fill(ds, "Table");
return (ds);
}
catch(Exception e)
{
throw e;
}
finally
{
cmd.Connection.Close();
conn.Close();
}
}

/// <summary>
/// Method to run an sql string against an Access database
/// </summary>
public static DataSet runQueryOleDb(string sql, string connString)
{
OleDbConnection conn = new OleDbConnection(connString);
DataSet ds = new DataSet();
OleDbDataAdapter dba = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand(sql, conn);

try
{
dba.SelectCommand = cmd;
dba.Fill(ds, "Table");
return (ds);
}
catch(Exception e)
{
throw e;
}
finally
{
cmd.Connection.Close();
conn.Close();
}
}

/// <summary>
/// Flush output to the browser (useful to indicate which tests are causing any delay)
/// </summary>
/// <param name="output"></param>
private void Flush(string output)
{
Response.Write(output);
Response.Flush();
}

</script>

ServerTestStyles.css:  (just to make the output more legible)

body
{
font-size: 90%;
font-family: Calibri, Helvetica, Sans-Serif;
padding: .5em;
}

hr
{
color: #87ceeb;
background-color: #87ceeb;
margin: .3em 0 .3em 0;
padding: 0;
height: 1px;
}

.pass
{
color: Blue;
font-weight: bold;
}
.fail
{
color: Red;
font-weight: bold;
}
.type
{
color: purple;
font-weight: bold;
}
.error
{
color: Red;
font-size: small;
}

I have configured the test in InternetVista to search for "All Good" in the url for the test page.  If this isn't present, i'll get an SMS/email alert and i can go and see what exactly is wrong.  It should be fairly easy to add other test types if you have different resources you need to check on.
Enjoy.


Friday, 09 March 2007 17:43:24 (GMT Standard Time, UTC+00:00)  #    Comments [3]  .Net General | Asp.Net | Database | General

# Wednesday, 28 February 2007
SQL Control Flow in stored procedures
coming from the c# world of strict control flow and built-in bail-out for unhandled exceptions, i was caught out by something in SQL 2005 recently.
many of my stored procedures take this form:
    update sometable set whatever = @whatever
    exec SP_History 'joe bloggs', 'order taken', '€500'
i was assuming that if the update statement failed, the subsequent SP_History SP would not be executed.  however i was wrong, and i now have the following code where i want the SP to stop executing if something goes wrong:
if @@ERROR <> 0 
    RETURN @@ERROR    -- bail out
the exception is still caught by .net, any handling code you may have will be unaffected there by the return statement.


Wednesday, 28 February 2007 15:54:09 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database

# Thursday, 22 February 2007
HowTo: Get Date Part Only from SQL DateTime value
CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS SmallDateTime)

Cheers to this blogger, he explains why it works.

Thursday, 22 February 2007 17:30:08 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database

# Monday, 15 January 2007
LIKE problems with oledb query
if you're querying an MS access database from within access, you can use ? and # as wildcard single character/digit placeholders.
however, if you're querying via OleDb, then you have to use _
this took me ages to figure out. thanks to this useful entry in msdn2.


Monday, 15 January 2007 12:33:47 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General | Database

# Thursday, 05 October 2006
HowTo: Automate SQL Express Backup
There is a thoroughly excellent article about this on the SQL DBA Tips web site.  Don't forget to enable modify permissions for the account that is running SQL Express, e.g. Network Service.
just posting it here in case i ever forget where it is.


Thursday, 05 October 2006 11:31:25 (GMT Daylight Time, UTC+01:00)  #    Comments [1]  Database

# Monday, 30 January 2006
ADOX + Excel: bogus worksheets
a web site i'm working on imports excel documents, and does some processing on them for importing into a database.  I use the code from this post to do the importing, and it works nicely.  I recently came across a problem where i was encountering duplicate records, and it took me ages to figure out why.  Apparently a 'named range' of cells in a worksheet is treated as a Table by ADOX.  so you get more than you bargain for when you iterate through the tables in the resulting DataSet. 
I was able to work around the problem by discarding any tables that do NOT end in the dollar $ character.


Monday, 30 January 2006 15:30:12 (GMT Standard Time, UTC+00:00)  #    Comments [1]  .Net General | Asp.Net | Database

# Thursday, 15 December 2005
Serializing an object in Xml, with .Net 2.0
i was experimenting with how .Net 2.0 does xml serialization of objects, and i got it serializing nicely with the following code. 
XmlSerializer xs = new XmlSerializer(typeof(PageCollection));
xs.Serialize(fs, pages); // 'fs' is a FileStream to my xml file, and 'pages' is a collection class of objects

the problem was when i tried to deserialize it, like so:
XmlSerializer xs = new XmlSerializer(typeof(PageCollection));
pages = xs.Deserialize(fs) as PageCollection;

i got this error:

xmlns=''> was not expected

i found this post on google which described the same errors, and a workaround (by adding an empty namespace) but it didn't work for me.  perhaps it is a difference in the serialization process with with .Net 2.0.  what fixed it for me was setting an XmlRootAttribute for the class i was serializing. like so:
[XmlRootAttribute("CmsPages", Namespace = "http://www.whatever.com/Cms", IsNullable = false)]
hope this helps someone else out there with the same problem.
Thursday, 15 December 2005 13:28:01 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General | Asp.Net | Database

# Friday, 02 December 2005
ADO.NET timeouts when filling a DataAdapter in the middle of a transaction

I had a transaction comprised of about 5 commands, and in the middle of it, i needed to do a SELECT on a table to know what values to insert for one of the commands.  I encountered a timeout just after i called Fill on the DataAdapter.  it took me a while to figure out that it was because the transaction had already taken out a lock on the table i was selecting from.  makes sense now, but i spent ages on it!  just posting it here in case anyone else runs into the same problem.


Friday, 02 December 2005 12:26:22 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General | .Net Windows Forms | Asp.Net | Database

# Wednesday, 28 September 2005
Removing rows from a dataset (i.e. achieving TOP functionality when you can't use SQL)

This might sound really obvious, but i couldn't find a better way.  Normally i would use TOP in the SQL query to limit the number of records i want to retrieve, but in my case, this value is parameterised and Access won't allow me to parameterise that value.  I tried using a DataView but TOP isn't one of it's supported functions.  So i just loop through the dataset and keep removing rows until the right number of records is reached.

int maxItems = 5;
while(ds.Tables[0].Rows.Count > MaxItems)
    ds.Tables[0].Rows.RemoveAt(MaxItems);

Wednesday, 28 September 2005 11:01:17 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Database

# Tuesday, 26 April 2005
An SQL Query to find duplicate values
SELECT <KEY_COLUMN>, COUNT(*)
FROM <TABLE> GROUP BY <KEY_COLUMN> HAVING COUNT(*) > 1

Tuesday, 26 April 2005 17:00:35 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Monday, 04 April 2005
MS Access: using the LIKE function with a parameter
I wanted to do something like SELECT * from MyTable WHERE [Action] LIKE 'Hello%' but using a parameter instead of hard-coding the 'Hello' into the query. using VS and OleDb, the syntax for this wasn't obvious, what finally works is: SELECT * from MyTable WHERE ([Action] LIKE ? + '%')
Monday, 04 April 2005 17:51:14 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Wednesday, 23 March 2005
SQL left join with a where clause produces strange results

I was doing a straight forward left join to include all the records of a table, regardless of whether they had records in the related table.  However, i added a where clause, which produced very strange results.  Some of the foreign records were not included in the results, specifically those that had no related records at all in the main table.  This was very frustrating to pin down, but i finally found out that the where clause in a left or right join should not use the 'where' syntax.

instead, it should use:

SELECT
    SuperMarketGroups.GroupName,
    SuperMarketGroups.ID, 
    ProductsInGroups.GroupProductCode, 
    ProductsInGroups.MinimumStoreRating, 
    ProductsInGroups.ProductID, 
    (IIf([ProductID] Is Null,'False','True')) AS GroupIncluded
FROM 
    SuperMarketGroups LEFT JOIN 
    ProductsInGroups ON
        (SuperMarketGroups.ID = ProductsInGroups.GroupID 
            AND (ProductsInGroups.ProductID Is Null OR ProductsInGroups.ProductID=?)
        )

note that there is no 'where' clause.  the extra condition is specified with the join columns. this solved my problem perfectly.


Wednesday, 23 March 2005 16:54:28 (GMT Standard Time, UTC+00:00)  #    Comments [4]  Database

Howto: Export a dataset to Excel (c# / asp.net)

In my web applications, i occassionaly need to allow the user to export a dataset as an excel file.  I was using a control written by Prashant Nayak posted on Code Project but he released a new version which was problematic for me, so i looked at other solutions.

obinna igbokwe  from  www.dedicatedsolutions.co.uk posted a good approach which creates a DataGrid object and binds it to the dataset, and then Renders the output of the control to the HttpResponse stream.  This works very well.  I have adapted his code to C# and added an option to specify a filename for the excel file.

Here is the code:

using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Whatever
{
 /// 
 /// This class provides a method to write a dataset to the HttpResponse as
 /// an excel file. 
 /// 
 public class ExcelExport
 {
  public static void ExportDataSetToExcel(DataSet ds, string filename)
  {
   HttpResponse response = HttpContext.Current.Response;
   
   // first let's clean up the response.object
   response.Clear();
   response.Charset = "";
   
   // set the response mime type for excel
   response.ContentType = "application/vnd.ms-excel";
   response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
   
   // create a string writer
   using (StringWriter sw = new StringWriter())
   {
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
     // instantiate a datagrid
     DataGrid dg = new DataGrid();
     dg.DataSource = ds.Tables[0];
     dg.DataBind();
     dg.RenderControl(htw);
     response.Write(sw.ToString());
     response.End(); 
    }
   }
  }
 }
}

Wednesday, 23 March 2005 16:47:02 (GMT Standard Time, UTC+00:00)  #    Comments [72]  Asp.Net | Database

# Sunday, 16 January 2005
SQL - change object owner for multiple objects

Tibor Karaszi showed how to do this time saving technique on a newsgroup post. You can use the sp_changeobjectowner stored procedure in the Master database to change the owner of an object one at a time, but there is a semi-automatic way to do it.

Open query analyser, change the selected database to the on you want to work on. The following query generates a list of exec commands for all the sprocs and UDFs in the database:


SELECT 'EXEC sp_changeobjectowner ''dbo.' + ROUTINE_NAME + ''', ''thenewuser'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'

Execute it, then click the top left corner of the output window, to select all the rows. Copy and paste the rows into a new query window and execute.

To change table owners, modify the query and repeat the proces:

SELECT 'EXEC sp_changeobjectowner ''dbo.' + TABLE_NAME + ''', ''thenewuser'''
FROM INFORMATION_SCHEMA.TABLES

Sunday, 16 January 2005 18:37:40 (GMT Standard Time, UTC+00:00)  #    Comments [2]  Database

# Sunday, 10 October 2004
SQL Server - Access denied messages using Server=(local) with named instances

I'm developing a .net application that uses a sql server database.  i develop it on a desktop and a laptop, and i want to configure the app to use the local instance of sql server, rather than the named instances: LAPTOP\LAPTOP and DESKTOP\DESKTOP, because if the laptop is not on the network then it can't connect to any other db.  I tried connecting with the following connection string:

Server=(local); initial catalog=MyDB; UID=sa; PWD=whatever; 

but it didn't work,  "SQL Server does not exist or access denied".  I found out after a lot of searching that this is because i had installed a 'named instance' of sql server rather than the 'default instance'.  This is a tick box during the sql server install.  I also found out that its easy to change it back to a default instace.  all you do is run the sql server install again, and choose the default instance. you then have 2 sql server installations (they don't conflict).  if you look in the add/remove programs, there are 2 entries, one 'microsoft sql server' and another 'microsoft sql server (INSTANCE NAME)'.  if you need to copy the database from the instance to the default, do that via backup from the instance and restore to the default database. then just uninstall the instance sql server from control panel > add/remove programs. 

i also wasn't able to log in with "Server=localhost". i had to use "Server=(local)". 

 


Sunday, 10 October 2004 11:24:36 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Saturday, 03 April 2004
ADO.NET parameter quirks with ODBC and OleDb

The OleDb Provider

If anyone is using the Oledb provider with .Net, when you are adding parameters to an OleDbCommand in code, make sure you add them in the order that they belong in the sql string. 

For example, if your sql string is:
"insert into table1 (username, password) values(?,?)"
and you have parameters added to the command (usually done through a configure OleDbDataAdapter wizard), called "@Username" and "@Password".  Your code must add the parameters in the correct order as follows:
this.oledbCommand1.Parameters("@Username").Value = username;
this.oledbCommand1.Parameters("@Password").Value = password;

If you don't do it this way, you may get a jet error: "No value given for one or more required parameters.". 

This quirk does not apply for the Sql provider in ADO.NET, because you can name the parameters in the sql string, e.g. 
"insert into table1 (username, password) values(@Username, @Password)"

The ODBC Provider

If you're using the ODBC provider with .NET (probably for MySQL), there is one oddity that you should know about too.  In sql strings for odbcCommand objects, you can't use the @ParamName syntax.  You have to use question marks as placeholders for the parameters as follows:
"insert into table1 (username, password) values(?,?)"
Similarly, you have to add the parameters in the right order. 


Saturday, 03 April 2004 18:07:08 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# 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