Sql Server Nulls and nothings

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
Mark KleinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
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
Mark KleinAuthor Commented:
I have been running my app in debug.  I agree it's an application coding issue
0
gplanaCommented:
Just to make the test: Have you tryied to change null by an empty string?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark KleinAuthor Commented:
when? After the initial data entry?
0
gplanaCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Mark KleinAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark KleinAuthor Commented:
that worked.  thanks.
0
Mark KleinAuthor Commented:
simple solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.