asp:SqlDataSource have issues with Decimal DataType

Question

question

ChetanSharma-5270 asked Apr 22, '22 LanHuang-MSFT commented Apr 27, '22

asp:SqlDataSource have issues with Decimal DataType

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

dotnet-aspnet-webforms

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-04-25T02:26:02.243Z

LanHuang-MSFT answered Apr 25, '22 LanHuang-MSFT edited Apr 27, '22

Hi @ChetanSharma-5270,

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://docs.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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-26T21:28:09.007Z

ChetanSharma-5270 answered Apr 26, '22 LanHuang-MSFT commented Apr 27, '22

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>

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT · Apr 27 at 06:59 AM

Hi @ChetanSharma-5270,
In your code, both ID and ROLE_ID are Decimal, is it only ROLE_ID that reports an error?
Make sure the field you pass in is not NULL,
maybe you can try DbType="Decimal"
https://docs.microsoft.com/en-us/dotnet/api/system.data.dbtype?view=net-6.0
Best regards,
Lan Huang

0 ·

question