Odd Error Upon SqlDataReader.Read() "Invalid length parameter passed to the substring func

An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error:
"Invalid length parameter passed to the substring function."

Here's the code in question:

orderConnection.Open()
orderReader = orderCommand.ExecuteReader()
setControls(orderReader)
...
Private Sub setControls(ByVal dr As SqlDataReader)
 If (dr.Read()) Then '<--*******problem line*******

The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?Can you post the Sql the reader is running as well as the relevant fields from the record that is crashing?|||It turns out that the stored procedure the DataReader was using expected names to be in first-name, space, last-name format and the record in question had only a single name (my company isn't normalizing their databases for some reason).

As a general question, though, do DataReaders not actually call a stored procedure until .Read() is called? I mean, I would have expected things to error out at the call to ExecuteReader(). I know that a DataReader forges a forward-only direct connection to the database, but I'm confused as to the underlying mechanics of the function calls.|||I'm not at dba but I think that the ExecuteReader creates a cursor and points it before the first record. When you call Read() it fetches the next record and at that time evaluates the calculated fields such as the one using the substring function.

OdbcCommand Parameter Failure

Hi,

I tried to create a utility method to accept the name of a stored procedure and an array of OdbcParameter objects. When I try to add the parameters from the array in the code, the parameters aren't recognized:

foreach (OdbcParameter prmin prm_array)cmd.Parameters.add (prm);

OR

cmd.Parameters.AddRange (prm_array);

However, when I hardcode the parameters without using the argument of the function, it works perfectly:

cmd.Parameters.Add ("@.login", OdbcType.VarChar, 50,"Joe");cmd.Parameters.Add ("@.password", OdbcType.VarChar, 50,"Bob");

I know the method calling the utility function is passing the parameters correctly; in fact, if I hard code the parameters in the utility function but add them with a foreach like above or using the AddRange function, it just does not recognize the parameters.

I've worked too long on this so I'm using an inelegant solution, but this nuance is very frustrating. Any possible insight?

~ mellamokb

Is the cmd object an ODBCCommand object. If not, this sure won't work.|||

Yep. I only use Odbc.... objects.

~ mellamokb

|||

can you post the entire function, this would be helpful

thanks

|||Yes, if it was not recognized, what runtime exception or compile time error was thrown?|||

I got it to work by reconstructing the parameters from an OdbcParameter array passed and by using the AddWithValue command:

foreach (OdbcParameter prmin prm_array) cmd.Parameters.AddWithValue(prm.ParameterName, prm.Value);

I'll call that good enough; but it doesn't make sense why cmd.Parameters.Add(prm_object) is different than cmd.Parameters.Add([parameter_name], [datatype], [datasize], [value]) when the prm_object and the hard-coded parameters are the same information.

This situation is independent of the function it is in; I've generalized to any situation, and there's no reason why it would work in a modified test case but not in the utility function I've created.

But I've seen stranger things, I guess.

Kevin Yu: The runtime exception is simply that the command cannot find the needed parameter, as if I hadn't passed it at all, when I use Add(prm_object); but using the four parameter version of Add works fine, even if the four parameters are the same as the four used to construct the new OdbcParameter object.

~ mellamokb