asp:SqlDataSource have issues with Decimal DataType

Chetan Sharma 1 Reputation point
2022-04-22T18:01:39.907+00:00

I am trying to change oracle client in my vb.net application from System.Data.OracleClient to System.Data.Oledb & getting Invalid Cast Exception (Conversion from string to type Double is not valid), it used to work fine when provider was System.Data.Oracleclient instead of System.Data.Oledb

Application is using SQLDatasource to connect to DB & it is failing during update operation. Value for parameter is coming from dropdown & we are storing ID instead of actual text from the dropdown. Debugged the code by adding updated event & looked at parameter values & it does have decimal value. Is there a different way to handle decimals in System.Data.OleDB provider??

Parameter in question is defined as in my code <asp:Parameter Name="ROLE_ID" Type="Decimal" />

P.S: All sql code is written in aspx page

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,219 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lan Huang-MSFT 24,461 Reputation points Microsoft Vendor
    2022-04-25T02:26:02.243+00:00

    Hi @Chetan Sharma ,

    Conversion from string to type Double is not valid

    As the error says, maybe you can try Double.TryParse, which is a safe way to convert values to doubles. If the conversion is successful, the method returns true and returns the result double in the out parameter. If the conversion fails, returns false and returns the default value of double (that is, 0).
    https://learn.microsoft.com/en-us/dotnet/api/system.double.tryparse?view=net-6.0
    A simple example:

    Dim result As Double  
    Dim score As String = "75"  
      
    If Double.TryParse(score, result) Then  
       ' result will be a double with the value of 75'  
    Else  
       ' The conversion attempt failed, and result will have a value of 0'  
    End If  
    

    Maybe you can provide detailed code so we can help you better.

    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Chetan Sharma 1 Reputation point
    2022-04-26T21:28:09.007+00:00

    Thanks for your reply. Sorry i was out on a vaction. I am not using Oracle.ManagedDataAccess.Client(instead of System.data.Oledb provider), i cannot use Tryparse as all sql coding is done on client side aspx. Here are some the code snipetts

     <asp:SqlDataSource ID="UserQuery" runat="server" 
            ConnectionString="<%$ ConnectionStrings:Conn1 %>" 
            DeleteCommand="DELETE FROM &quot;TEST_USERS&quot; WHERE &quot;ID&quot; = :ID" 
            InsertCommand="INSERT INTO &quot;TEST_USERS&quot; (&quot;ID&quot;, &quot;APPLICATION_CODE&quot;, &quot;USERNAME&quot;, &quot;FNAME&quot;, &quot;LNAME&quot;, &quot;ROLE_ID&quot;, &quot;ACTIVE_FLAG&quot;, &quot;CREATED_USER&quot;, &quot;CREATED_DATE&quot;, &quot;UPDATED_DATE&quot;, &quot;UPDATED_USER&quot;) VALUES (eval.evaluation_user_seq.nextval, :APPLICATION_CODE, :USERNAME, :FNAME, :LNAME, :ROLE_ID, :ACTIVE_FLAG, :CREATED_USER, :CREATED_DATE, :UPDATED_DATE, :UPDATED_USER)" 
            ProviderName="<%$ ConnectionStrings:Conn1.ProviderName %>" 
            SelectCommand="SELECT * FROM &quot;TEST_USERS&quot; order by ACTIVE_FLAG desc, LNAME, FNAME" 
            UpdateCommand="UPDATE &quot;TEST_USERS&quot; SET &quot;APP_CODE&quot; = :APP_CODE, &quot;USERNAME&quot; = :USERNAME, &quot;FNAME&quot; = :FNAME, &quot;LNAME&quot; = :LNAME, &quot;ROLE_ID&quot; = :ROLE_ID, &quot;ACTIVE_FLAG&quot; = :ACTIVE_FLAG, &quot;UPDATED_DATE&quot; = :UPDATED_DATE, &quot;UPDATED_USER&quot; = :UPDATED_USER WHERE &quot;ID&quot; = :ID">
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Decimal" />
            </DeleteParameters>
            <UpdateParameters> 
                <asp:Parameter Name="ID" Type="Decimal" />
                <asp:Parameter Name="APP_CODE" Type="String" DefaultValue="APSCASE" />
                <asp:Parameter Name="USERNAME" Type="String" />
                <asp:Parameter Name="FNAME" Type="String" />
                <asp:Parameter Name="LNAME" Type="String" />
                <asp:Parameter Name="ROLE_ID" Type="Decimal" DefaultValue="0" />
                <asp:SessionParameter Name="UPDATED_DATE" SessionField="CurrentDate" Type="DateTime" />
                <asp:SessionParameter Name="UPDATED_USER" SessionField="logname" Type="String" /> 
                <asp:Parameter Name="ACTIVE_FLAG" Type="String" />   
    
            </UpdateParameters>
            <InsertParameters>
            ........
            </InsertParameters>
    </asp:SqlDataSource>