Solved

Sql Server Nulls and nothings

Posted on 2013-01-20
10
303 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

688 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