Casting with CType

pascalmartin
pascalmartin used Ask the Experts™
on
I want the following paramter to return an Integer, but it returns a NVARCHAR instead.
I am using the DataKey field with another parameter and not with the ProdID which is not my identity field in the DB.
Any Solution? Thanks in advance.

myCom.Parameters.AddWithValue("@ProdID", SqlDbType.Int).Value = CType(PricingGrid.Columns(1), BoundField).DataField
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
My understanding is that AddWithValue always returns a NVARCHAR. It's an implicit conversion. Have you tried Parameters.Add(...) instead? And then say Paramenters("@ProgID").Value = CType(PricingGrid.Columns(1), BoundField).DataField on the next line.
Most Valuable Expert 2012
Top Expert 2008

Commented:
AddWithValue takes the parameter name and a value as arguments.  You cannot specify the expected type.  You will have to create a parameter, and add with Parameters.Add as suggested.

Author

Commented:
Hi, Here is what I did:
Dim ProdIdentity As SqlParameter = New SqlParameter("@ProdID", SqlDbType.Int)
ProdIdentity.Value = CType(PricingGrid.Columns(1), BoundField).DataField
myCom.Parameters.Add(ProdIdentity)

But the method returns the following error message:
Error accessing database.
Failed to convert parameter value from a String to a Int32.

Most Valuable Expert 2012
Top Expert 2008

Commented:
If you are getting that exception, it is because the parameter value is blank, and you can convert a blank string to an integer.  One workaround, with Visual Basic, is to convert with the Val function, since it won't raise any exceptions.

Author

Commented:
How to do that?
Top Expert 2015

Commented:
You add the parameter first before assigning its value

           myCom.Parameters.Add(New SqlParameter("@ProdID", SqlDbType.Int, 4, "prodId"))
            myCom.Parameters("@ProdID").Value = CType(PricingGrid.Columns(1), BoundField).DataField
Most Valuable Expert 2012
Top Expert 2008

Commented:
What value does CType(PricingGrid.Columns(1), BoundField).DataField return?  DataField would be the string that is the name of the column for a BoundField, which wouldn't convert to an integer.

Author

Commented:
What I need to pass is the column value and not the name of the column, therefore
"myCom.Parameters("@ProdID").Value = CType(PricingGrid.Columns(1), BoundField).DataField" is probably the wrong statement because it is still returning string instead of an Int32.

I just want to capture the ProdID values of the bounddatafield named "ProdID"
Top Expert 2015

Commented:
>>"wrong statement because it is still returning string instead of an Int32"
You want the value, not the column title, correct? So is the value of the column already an integer or is it a string?
If a string, then how about:
myCom.Parameters("@ProdID").Value = CType(PricingGrid.DataGridItem.Cells(1).Text, Integer)

Author

Commented:
Hi, the following statement:
myCom.Parameters("@ProdID").Value = CType(PricingGrid.DataGridItem.Cells(1).Text, Integer)
does not work a "DataGridItem" is not a member of "System.Web.UI.WebControls.GridView"

I also tried the following:
myCom.Parameters("@ProdID").Value = PricingGrid.Rows(e.RowIndex).Cells(1).Text
But it won't work either, the DB exception returns:
"Failed to convert parameter value from a String to a Int32."

Most Valuable Expert 2012
Top Expert 2008

Commented:
Try this (assuming that you are not binding the GridView to an SqlDataSource):

Dim drv As DataRowView = CType(PricingGrid.Rows(e.RowIndex).DataItem, DataRowView)
myCom.Parameters("@ProdID").Value = Val(drv("ProdID"))

Author

Commented:
Line: 4723
Error: Sys.WebForms.PageRequestManagerServerErrorException: An SqlParameter with ParameterName '@ProdID' is not contained by this SqlParameterCollection.

To make sure that there is no error in my sub as in my SP, I have strongly typed the "ProdID" value like this: myCom.Parameters("@ProdID").Value = 25
and the code works like a charm, the only challenge is to pass this INT value from the boundfield to the DB.

Author

Commented:
my boudfied is set to visible=false does it matter?

Author

Commented:
Ok, the following works :
myCom.Parameters("@ProdID").Value = PricingGrid.Rows(e.RowIndex).Cells(1).Text
only if my boundfield is visible...that a progress, but i want it invisible??
Most Valuable Expert 2012
Top Expert 2008

Commented:
Did you get a DataRowView from GridViewRow.DataItem?

Author

Commented:
Actually I am binding the gridview from a sqldatasource, I could not get the DataRowView
Most Valuable Expert 2012
Top Expert 2008
Commented:
If you are using an SqlDataSource, then you need a different approach.  You can attach an event handler to the Selected event, and get a DataView from the SqlDataSource:

Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceArguments.Empty), DataView)

Then, you can get the DataRowView from the DataView, corresponding to the selected GridViewRow.  The challenge would be the specifics of where you need this information, and whether this approach is possible.

Author

Commented:
Not satisfied with the answer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial