?
Solved

Sql Server Nulls and nothings

Posted on 2013-01-20
10
Medium Priority
?
305 Views
Last Modified: 2013-01-20
My asp.net 4.0 application accepts user input to fill a form and stores the information in a Sql Server db.  Email is automatically generated and sent to a set of predetermined users. Some of the information on the form is required (for example an equipmentType) and some is optional (for ex YearofMfg and equipmentModel).  Both the required information and the optional information are included in the email.

My app properly collects the information from the db after the form is completed and sends the email. However my app does not handle the optional information properly.  When the optional fields are left blank on input, I want the app to report them as "Not known" in the email. This is not happening. When the optional fields are not left blank, the emails are handled properly.

In fact, the two fields YearofMfg and equipmentModel seem to be handled differently, even though I have set them up similarly.  Both fields allow nulls. When I use SS Management Studio (SSMS) to examine the db, both are shown as nulls for the records, as they should. However my mail send module, attached, leaves the equipmentModel field blank and crashes with an invalid cast when trying to use seemingly identical code to handle YearofMfg.
SendWatchListEmail.vb
0
Comment
Question by:Mark Klein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 38798592
I think is not an issue on SQL-Server, but on your application, as you said you can see nulls on SQL-server.

I suggest to debug your application, or maybe change null by an empty string ("").

Hope it helps.
0
 

Author Comment

by:Mark Klein
ID: 38798609
I have been running my app in debug.  I agree it's an application coding issue
0
 
LVL 15

Expert Comment

by:gplana
ID: 38798621
Just to make the test: Have you tryied to change null by an empty string?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Mark Klein
ID: 38798629
when? After the initial data entry?
0
 
LVL 15

Expert Comment

by:gplana
ID: 38798633
Yes. If it works after this change on your database, you can try to add an if to your application to change the variable that has the null to an empty string.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38798644
when you step through the code, where do you see the issue starting?
I presume it's based on the fact that some db columns are returned as Null (which is <> Nothing), so your vb code conditions are not working as you might think.
0
 

Author Comment

by:Mark Klein
ID: 38798727
In this code segment
  'create the parameters for the query
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@equipmentId", SqlDbType.Int)).Value = EquipmentID
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@equipmentTypeId", SqlDbType.Int))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@makersId", SqlDbType.Int))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@model", SqlDbType.NVarChar, 50))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EMUserId", SqlDbType.UniqueIdentifier))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@asking_price", SqlDbType.Money))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@mfg_year", SqlDbType.NVarChar, 50))

        cmd.Parameters("@equipmentTypeId").Direction = ParameterDirection.Output
        cmd.Parameters("@makersId").Direction = ParameterDirection.Output
        cmd.Parameters("@model").Direction = ParameterDirection.Output
        cmd.Parameters("@EMUserId").Direction = ParameterDirection.Output
        cmd.Parameters("@asking_price").Direction = ParameterDirection.Output
        cmd.Parameters("@mfg_year").Direction = ParameterDirection.Output

        'Execute the Stored Procedure to get equipment info
        cmd.ExecuteNonQuery()
        con.Close()

        'Save the results for later use
        'Dim equipmentTypeId As Integer = CType(cmd.Parameters("@equipmentTypeId").Value, Integer)
        Current.Session("equipmentTypeId") = EquipmentTypeID
        Dim EMUserId As Guid = CType(cmd.Parameters("@EMUserId").Value, Guid)
        System.Web.HttpContext.Current.Session.Item("userId") = EMUserId
        Dim makersId As Integer = CType(cmd.Parameters("@makersId").Value, Integer)
        Current.Session("makersId") = makersId
        Dim equipmentModel As String
        If IsDBNull("@model") = True Or IsNothing("@model") = True Then
            equipmentModel = "Not known"
        Else
            equipmentModel = CType(cmd.Parameters("@model").Value, String)
        End If
        Current.Session("equipmentModel") = equipmentModel
        Dim askingPrice As String = CType(cmd.Parameters("@asking_price").Value, String)
        Current.Session("askingPrice") = askingPrice
        Dim yearOfMfg As String
        If IsDBNull("@mfg_year") = True Or IsNothing("@mfg_year") = True Then
            yearOfMfg = "Not known"
        Else
            yearOfMfg = CType(cmd.Parameters("@mfg_year").Value, String)
        End If
        Current.Session("mfgYear") = yearOfMfg
        cmd.Parameters.Clear()

Open in new window

the problems start with an invalid cast at the line
 yearOfMfg = CType(cmd.Parameters("@mfg_year").Value, String)

Open in new window

when the field was left blank on the data entry form.  The puzzle is because the same code for equipmentModel does not cause an invalid cast, even though the properties are the same for the fields.  Model gets set to Null in the code, according to the debugger, while the above line crashes
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38798801
what about this:
yearOfMfg = ""
if not IsNull(cmd.Parameters("@mfg_year").Value) then
 yearOfMfg= CType(cmd.Parameters("@mfg_year").Value, String)
end if

Open in new window

0
 

Author Comment

by:Mark Klein
ID: 38798881
that worked.  thanks.
0
 

Author Closing Comment

by:Mark Klein
ID: 38798882
simple solution
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question