• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 782
  • Last Modified:

Want to pass null to an SQL decimal field when the corresponding textbox is blank or non-numeric

Hi experts,

I have an ASP.Net textbox which gathers an input interest rate from the user, but it can be left blank. When it is so, I would like to pass nulls to the corresponding SQL database field.

See attached code snippet. When the data is not numeric, the add statement fails. I want to avoid this failure, but whats more, I want to fill the data base field with nulls when this condition occurs.

Can somebody help me?

Thanks,

taduh


If Not IsNumeric(txtboxAPR.Text) Then
            OrderDataSource.UpdateParameters.Add("APRRate", txtboxAPR.Text)
        Else
            OrderDataSource.UpdateParameters.Add("APRRate", Decimal.Parse(txtboxAPR.Text))
        End If

Open in new window

0
taduh
Asked:
taduh
  • 6
  • 4
  • 2
  • +3
1 Solution
 
BrandonGalderisiCommented:
The APRRate parameter should have a default of NULL in it's definition and then you would just not supply the parameter.
0
 
BrandonGalderisiCommented:
To have it accept NULL, you would add "= NULL" after the variable's datatype

Ex:

create procedure up_DoStuff
@AprDate numeric(11,5) = null
as
0
 
taduhFinancial Systems AnalystAuthor Commented:
I'm not following you; I've attached the code for my entire DB update routine in the attached code  snippet. Can you tell me how I would change this code to achieve what you're saying to do?

Thanks,

taduh
 Sub Update_Order_Data()
 
        'Specify the connection string for the Orders Table.
 
        Dim OrderDataSource As New SqlDataSource()
 
        OrderDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("CDARSConnectionString").ToString()
 
        OrderDataSource.SelectCommandType = SqlDataSourceCommandType.Text
        OrderDataSource.UpdateCommandType = SqlDataSourceCommandType.Text
 
 
        'Check to see if a record for the order already exists in the Order Table.
        'If so, update the data from the web form on the existing Order record.
        OrderDataSource.UpdateCommand = "IF EXISTS(SELECT 1 FROM tbl_Orders Where Order_ID = @OrderID)" & _
                                            " UPDATE tbl_Orders " & _
                                            " Set Order_Type_Code = @OrderType," & _
                                                 "Order_CDARS_Account_Number = @CDARSAcctNum," & _
                                                 "Order_Date = @OrderDate," & _
                                                 "Order_Institution_No = @Institution," & _
                                                 "Order_Branch_No = @Location," & _
                                                 "Order_Customer1_TaxID = @TaxID1," & _
                                                 "Order_Customer1_Portfolio_Number = @Port1," & _
                                                 "Order_Customer1_Class = @CustClass," & _
                                                 "Order_Customer2_Name = @Name2," & _
                                                 "Order_Customer2_TaxID = @TaxID2," & _
                                                 "Order_Customer2_DOB = @DOB2," & _
                                                 "Order_Debited_Account_Type_code = @FromAcctType," & _
                                                 "Order_Debited_Account_No = @FromAcctNum," & _
                                                 "Order_CDARS_Account_Title = @AcctTitle," & _
                                                 "Order_Contact_First_Name = @ContactFName," & _
                                                 "Order_Contact_Last_Name = @ContactLName," & _
                                                 "Order_Contact_MI = @ContactMI," & _
                                                 "Order_Contact_Phone_No = @ContactPhone," & _
                                                 "Order_Amount = @Amount," & _
                                                 "Order_Placement_Date = @DatePlaced," & _
                                                 "Order_APR_Rate = @APRRate," & _
                                                 "Order_APY_Rate = @APYRate," & _
                                                 "Order_Term_Code = @Term," & _
                                                 "Order_Interest_Pay_Freq_Code = @IntPayFreq," & _
                                                 "Order_Interest_Paid_Method_Code = @IntPayVia," & _
                                                 "Order_Interest_Paid_Account_No = @IntPayAcctNum," & _
                                                 "Order_Cust_Sig = @CustSig," & _
                                                 "Order_Comments = @Comments " & _
                                          "Where Order_ID = @OrderID"
 
        'Assign the parameter names to the webform data.  
        OrderDataSource.UpdateParameters.Add("OrderID", txtboxOrderNum.Text)
        OrderDataSource.UpdateParameters.Add("OrderType", radioOrderType.SelectedValue)
        OrderDataSource.UpdateParameters.Add("CDARSAcctNum", txtboxCDARSAcctNum.Text)
        OrderDataSource.UpdateParameters.Add("Institution", dropdownInst.SelectedValue)
        OrderDataSource.UpdateParameters.Add("Location", dropdownLocations.SelectedValue)
        OrderDataSource.UpdateParameters.Add("OrderDate", txtboxDate.Text)
        OrderDataSource.UpdateParameters.Add("TaxID1", txtboxTaxID1.Text)
        OrderDataSource.UpdateParameters.Add("Port1", txtboxPort.Text)
        OrderDataSource.UpdateParameters.Add("Name2", txtboxName2.Text)
        OrderDataSource.UpdateParameters.Add("TaxID2", txtboxTaxID2.Text)
        OrderDataSource.UpdateParameters.Add("DOB2", txtboxDOB2.Text)
        OrderDataSource.UpdateParameters.Add("CustClass", RadioCustClass.SelectedValue)
        OrderDataSource.UpdateParameters.Add("FromAcctType", RadioFromAcctType.SelectedValue)
        OrderDataSource.UpdateParameters.Add("FromAcctNum", txtboxFromAcctNum.Text)
        OrderDataSource.UpdateParameters.Add("AcctTitle", txtboxAcctTitle.Text)
        OrderDataSource.UpdateParameters.Add("ContactLName", txtboxContactLName.Text)
        OrderDataSource.UpdateParameters.Add("ContactFName", txtboxContactFName.Text)
        OrderDataSource.UpdateParameters.Add("ContactMI", txtboxContactMI.Text)
        OrderDataSource.UpdateParameters.Add("ContactPhone", txtboxContactPhone.Text)
 
        If IsNumeric(TxtboxAmt.Text) Then
            OrderDataSource.UpdateParameters.Add("Amount", Decimal.Parse(TxtboxAmt.Text))
        Else
            OrderDataSource.UpdateParameters.Add("Amount", TxtboxAmt.Text)
        End If
 
        OrderDataSource.UpdateParameters.Add("DatePlaced", txtboxDatePlaced.Text)
 
 
        If Not IsNumeric(txtboxAPR.Text) Then
            OrderDataSource.UpdateParameters.Add("APRRate", txtboxAPR.Text)
        Else
            OrderDataSource.UpdateParameters.Add("APRRate", Decimal.Parse(txtboxAPR.Text))
        End If
 
        If txtboxAPY.Text = "" Then
            OrderDataSource.UpdateParameters.Add("APYRate", 0)
        Else
            OrderDataSource.UpdateParameters.Add("APYRate", Decimal.Parse(txtboxAPY.Text))
        End If
        OrderDataSource.UpdateParameters.Add("Term", RadioTerm.SelectedValue)
        OrderDataSource.UpdateParameters.Add("IntPayFreq", RadioIntPayFreq.SelectedValue)
        OrderDataSource.UpdateParameters.Add("IntPayVia", dropdownIntPayVia.SelectedValue)
        OrderDataSource.UpdateParameters.Add("IntPayAcctNum", txtboxIntPayAcctNum.Text)
        OrderDataSource.UpdateParameters.Add("CustSig", chkboxCustSig.Checked.ToString)
        OrderDataSource.UpdateParameters.Add("Comments", txtboxComments.Text)
 
 
 
        Dim Orderrowsupdated = 0
 
        'Attempt to update the record in the Order table.
        Try
            Orderrowsupdated = OrderDataSource.Update()
 
            'Problem with update if we wind up in the Catch/Exception area
        Catch ex As Exception
            MsgBox(OrderDataSource.UpdateCommand)
            MsgBox("Update of existing Order Data in Order Table was not successful. Contact your System Administrator.")
 
            'If successful, clear the OrderData Source"
        End Try
 
        'If successful, rowsupdated will equal 1; Message the user of successful update to Order Table.
        If Orderrowsupdated = 1 Then
            MsgBox("Order data has been updated for Order # " & txtboxOrderNum.Text)
            OrderDataSource = Nothing
        Else
            MsgBox("Order data update has encountered an unexpected error for Order # '" & txtboxOrderNum.Text & "'" & " Contact your System Administrator.")
        End If
 
 
    End Sub

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
samtran0331Commented:
Try modifying the original code you posted to be the following:
If Not IsNumeric(txtboxAPR.Text) Then
            OrderDataSource.UpdateParameters.Add("APRRate", System.DBNull.Value)
        Else
            OrderDataSource.UpdateParameters.Add("APRRate", Decimal.Parse(txtboxAPR.Text))
        End If

Open in new window

0
 
taduhFinancial Systems AnalystAuthor Commented:
I get a squiggly line under System.DBNull.Value.
0
 
Mark WillsTopic AdvisorCommented:
Actually, quite like your code. and wouldn't bother anout all that decimal.parse, would simply add it as txtboxAPR.Text

Then change your SQL statement :

instead of :    Order_APR_Rate = @APRRate
do  :    Order_APR_Rate = case when isnumeric(@APRRate) > 0 then @APRRate else NULL end
0
 
Anthony PerkinsCommented:
>>I get a squiggly line under System.DBNull.Value.<<
System.DBNull.Value is correct.  So you will have to tell us the syntax error you are getting with it.
0
 
jabcocoCommented:
Don't know a lot about VB syntax but take a look at the "ConvertEmptyStringToNull" property.

Try this.
Ref:http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.parameter.convertemptystringtonull.aspx

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.parameter.aspx

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.formparameter.aspx
Dim formParam As New FormParameter("lastname","LastNameBox")
formParam.ConvertEmptyStringToNull = True
OrderDataSource.UpdateParameters.Add(formParam)

Open in new window

0
 
taduhFinancial Systems AnalystAuthor Commented:
acperkins:

The syntax error I get is:

Value of type ''System.DBNull" cannot be converted to "String".

taduh
0
 
Anthony PerkinsCommented:
I am afraid I have no idea.
0
 
samtran0331Commented:
Do you have the update parameters defined on the aspx page?
...something like:

<UpdateParameters>
     <asp:Parameter Name="ProductTypeID" Type="Int32" />
....

If yes, then that might be conflicting with what you're trying to do in the codebehind...
0
 
samtran0331Commented:
Looking over your last code example, you can ignore my last comment.
I see you're working with the SqlDataSource control...and instantiating it in the codebehind.

If you're going to work with database code in code behind, I would suggest you not use a SqlDataSource object....that object was meant to be for those developers that don't want to work in codebehind that think everything can be done with declarative tags/controls in the aspx.

If you're going to do db stuff in codebehind, I would do it using a SqlCommand...below is a sample using Northwind, you can see that it is similar to using a SqlDataSource, but in my opinion, you have a lot more control...and it is more "proper"
    Public Shared Function InsertTest(ByVal LastName As String, ByVal FirstName As String, ByVal Title As String) As String
        Using MyConn As New SqlConnection(ConfigurationManager.ConnectionStrings("CONNECTIONSTRING_IN_WEB_CONFIG").ConnectionString)
            Dim MyTrans As SqlTransaction
            MyConn.Open()
            MyTrans = MyConn.BeginTransaction
            Dim MyCmd As New SqlCommand("INSERT INTO Employees (LastName,FirstName,Title) VALUES (@LastName,@FirstName,@Title);", MyConn, MyTrans)
            With MyCmd
                .Parameters.Add("@LastName", SqlDbType.VarChar).Value = LastName
                .Parameters.Add("@FirstName", SqlDbType.VarChar).Value = FirstName
                If Title.Trim() = "" Then
                    .Parameters.Add("@Title", SqlDbType.VarChar).Value = System.DBNull.Value
                Else
                    .Parameters.Add("@Title", SqlDbType.VarChar).Value = Title
                End If
            End With
            Try
                MyCmd.ExecuteNonQuery()
                MyTrans.Commit()
                Return "Added successfully."
            Catch ex As Exception
                MyTrans.Rollback()
                Return ex.Message.ToString()
            Finally
                If MyConn.State = ConnectionState.Open Then MyConn.Close()
            End Try
        End Using
    End Function

Open in new window

0
 
taduhFinancial Systems AnalystAuthor Commented:
samtran,

Do you have any syntax for converting a value in a textbox to
1) A date
2) A decimal

I'm erroring out on this line:

.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = txtboxDate.Text

Thanks,

taduh
0
 
samtran0331Commented:
vb (namespace Microsoft.VisualBasic) has both an IsDate and IsNumeric functions...you just need to make sure the input fits first...

If IsDate(txtboxDate.Text) = True Then
.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = Ctype(txtboxDate.Text,Date)
Else
.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = System.DBNull.Value
End If

0
 
taduhFinancial Systems AnalystAuthor Commented:
Thanks samtran,
I figured out the last question, but you helped me over the big hurdle.
taduh


0
 
taduhFinancial Systems AnalystAuthor Commented:
Thanks samtran!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now