user CType to convert to SqlDbType

[jmeckley]

I am working on a class that will hopefully reduce my coding by dynamically building command objects and returning data tables.

I am stuck on the parameter type. I have stored proc that will retrieve a list of the parameters, their type, and lenght. the type is the text vaule (char, varchar, int, bit, datetime). I want to convert that value to the sqldbtype in vb.net to create the parameter collection for the sp.

The following is erroring out
[tt]While Reader.Read
GetParameters.Add(Reader.Item("Parameter&quot, CType(Reader.Item("DataType&quot, SqlDbType), Reader.Item("Length&quot)
End While[/tt]
I've thought of making a function of case statements that will return the sqldbtype, but that seems clunky. Is there a better way?
thanks for your help

Jason Meckley
Database Analyst
WITF

 

[jmeckley]

I think I may have found something. I need to turn the text value into that type object, then convert it a sqldbtype.

How can I take the text value and convert it to the type vaule?

Jason Meckley
Database Analyst
WITF

 

[Aboo1]

I actually just finished doing something very close to this. However, I'm not using strongly typed objects. If your interested I can email you the code.
It revolves around a ParamArray with three elements. ParamName, ParamValue and ParamDataType.
The Data Type is not required though. Just declare the new parameter with the Param Name and the Value. Assign them to the parameter object, add it to your command object and presto. It works.

 

[jmeckley]

I would be interested to see your project. My email address is jason_meckley@_NOSPAM_witf.org. Remove _NOSPAM_

Jason Meckley
Database Analyst
WITF