Solved

Sql Server Nulls and nothings

Posted on 2013-01-20
10
296 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
  • 5
  • 3
  • 2
10 Comments
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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
Comment Utility
I have been running my app in debug.  I agree it's an application coding issue
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
Just to make the test: Have you tryied to change null by an empty string?
0
 

Author Comment

by:Mark Klein
Comment Utility
when? After the initial data entry?
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
that worked.  thanks.
0
 

Author Closing Comment

by:Mark Klein
Comment Utility
simple solution
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now