System.IndexOutOfRangeException: with a datareader

Ben Coffman — Wed, 14 Jul 2010 04:14:10 GMT

Error Readout:
System.IndexOutOfRangeException:

Fix:
I encountered the above error when I tried to run the query,

SELECT MAX(expression
)

FROM tables

WHERE predicates;

and then loop through the results using a datareader with code like this,

while(rdr.Read())

{

someLabel.Items.Add(rdr["ColumnName"].ToString());

}

I got the error above, because when you do a "max" sql query it doesn't return any column header.

All you need to do is give the sql result set a column header.

SELECT MAX(expression) as "ColumnName" 


FROM table 


where predicates;

Explanation:
This is another one of my bone head mistakes. It's such a simple little thing I overlook, and gets me so disappointed in myself when I realize I spent time trying to figure this out. It's generally followed by me looking over my shoulder and seeing if any of my peers saw it. I'm mostly blogging this in hopes I won't make a silly mistake like this again.

Why is it always the baby errors that hang me up the most? It can never be something deep in the CLR, or possibly find a mistake in .Net. Nope, noway, not gonna happen .Net is pretty solid...for the most part.

Incorrect syntax near ')'.

Ben Coffman — Wed, 23 Jun 2010 17:41:51 GMT

Error Readout:
Incorrect syntax near ')'

The Problem:
When I was trying to pass in getdate() as a parameter to execute a stored procedure i got the above error.

Example:
exec dbo.storedProcedure
    @param1 = '8D8DF077-7491-491B-AFA2-8F088821A073',
    @param2 = '8D8DF077-7491-491B-AFA2-8F088821A073',
    @param3 = 'Market: SAP 4 Eva',
    @param4 = getdate()

The Solution:
A sql execute statement can't pass a function such as getdate() in through a parameter. To fix this either:

Put getdate() directly in your sql statement that resides in the stored procedure
Pass a null if your table definition allows you to.
Create a temp variable store the value of getdate() in that variable and pass in that variable through the datetime parameter.

bencoffman.com/blog - SQL

System.IndexOutOfRangeException: with a datareader

Incorrect syntax near ')'.