HELP! - Cast from DBNull when there is Data

Hi,

I have built a few pages and a stored procedure and a class on the back of a SQL2000 dbase. and I get the following error:

Cast from type 'DBNull' to type 'String' is not valid.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.

Source Error:

Line 111: Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATALine 112:Line 113: myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)Line 114: myWorkJobs.WorkID = CStr(parameterWorkID.Value)Line 115: myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()

Source File:C:\Inetpub\wwwroot\Commerce\Components\WorkJobs.vb Line:113

My Database has 1 line of data (for testing) and all fields are populated. I am Querying a column called IndividualID which has a value of 3425243 at the moment. This is hardcoded in the aspx.vb at the moment.

ASPX VB:

Public Class WorkRequest
Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

'for now, send this value (stored in dbase under individualID) to the querystring

Dim IndividualID As String = "3425243"

Response.Redirect("WorkRequestMain.aspx?IndividualID=" & IndividualID)
End Sub
End Class

COMPONENTS\WorkJobs.vb (This is the class file)

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Namespace ASPNET.StarterKit.Commerce

Public Class WorkJobsDATA

Public CustomerID As String
Public WorkID As String
Public DateOfQuote As String
Public QuoteAmount As String
Public Title As Decimal
Public FirstName As String
Public Surname As String
Public FirstLine As String
Public District As String
Public Town As String
Public Postcode As String
Public Telephone As String
Public Requirements As String
Public WorkRequired As String
Public EmailAddress As String

End Class

Public Class WorkJobs

Public Function GetWorkDetails(ByVal IndividualID As String) As WorkJobsDATA

Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("SP_PendingQuotes", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterIndividualID As SqlParameter = New SqlParameter("@.IndividualID", SqlDbType.NVarChar, 50)
parameterIndividualID.Value = IndividualID
myCommand.Parameters.Add(parameterIndividualID)

Dim parameterCustomerID As SqlParameter = New SqlParameter("@.CustomerID", SqlDbType.BigInt, 8)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCustomerID)

Dim parameterWorkID As SqlParameter = New SqlParameter("@.WorkID", SqlDbType.NVarChar, 50)
parameterWorkID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkID)

Dim parameterDateOfQuote As SqlParameter = New SqlParameter("@.DateOfQuote", SqlDbType.DateTime, 8)
parameterDateOfQuote.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDateOfQuote)

Dim parameterQuoteAmount As SqlParameter = New SqlParameter("@.QuoteAmount", SqlDbType.Money, 8)
parameterQuoteAmount.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterQuoteAmount)

Dim parameterTitle As SqlParameter = New SqlParameter("@.Title", SqlDbType.NVarChar, 50)
parameterTitle.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTitle)

Dim parameterFirstName As SqlParameter = New SqlParameter("@.FirstName", SqlDbType.NVarChar, 50)
parameterFirstName.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstName)

Dim parameterSurname As SqlParameter = New SqlParameter("@.Surname", SqlDbType.NVarChar, 50)
parameterSurname.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterSurname)

Dim parameterFirstLine As SqlParameter = New SqlParameter("@.FirstLine ", SqlDbType.NVarChar, 50)
parameterFirstLine.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstLine)

Dim parameterDistrict As SqlParameter = New SqlParameter("@.District", SqlDbType.NVarChar, 50)
parameterDistrict.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDistrict)

Dim parameterTown As SqlParameter = New SqlParameter("@.Town", SqlDbType.NVarChar, 50)
parameterTown.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTown)

Dim parameterPostcode As SqlParameter = New SqlParameter("@.Postcode", SqlDbType.NVarChar, 50)
parameterPostcode.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterPostcode)

Dim parameterTelephone As SqlParameter = New SqlParameter("@.Telephone", SqlDbType.NVarChar, 50)
parameterTelephone.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTelephone)

Dim parameterRequirements As SqlParameter = New SqlParameter("@.Requirements", SqlDbType.NVarChar, 3500)
parameterRequirements.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRequirements)

Dim parameterWorkRequired As SqlParameter = New SqlParameter("@.WorkRequired", SqlDbType.NVarChar, 3500)
parameterWorkRequired.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkRequired)

Dim parameterEmailAddress As SqlParameter = New SqlParameter("@.EmailAddress", SqlDbType.NVarChar, 100)
parameterEmailAddress.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterEmailAddress)

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

parameterEmailAddress.Value.GetType()

Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATA

myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)
myWorkJobs.WorkID = CStr(parameterWorkID.Value)
myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()
myWorkJobs.Title = CStr(parameterTitle.Value).Trim()
myWorkJobs.FirstName = CStr(parameterFirstName.Value).Trim()
myWorkJobs.Surname = CStr(parameterSurname.Value).Trim()
myWorkJobs.FirstLine = CStr(parameterFirstLine.Value).Trim()
myWorkJobs.District = CStr(parameterDistrict.Value).Trim()
myWorkJobs.Town = CStr(parameterTown.Value).Trim()
myWorkJobs.Postcode = CStr(parameterPostcode.Value).Trim()
myWorkJobs.Telephone = CStr(parameterTelephone.Value).Trim()
myWorkJobs.Requirements = CStr(parameterRequirements.Value).Trim()
myWorkJobs.WorkRequired = CStr(parameterWorkRequired.Value).Trim()
myWorkJobs.EmailAddress = CStr(parameterEmailAddress.Value).Trim()

Return myWorkJobs

End Function

End Class
End Namespace

And finally my stored procedure:

CREATE Procedure SP_PendingQuotes
(
@.IndividualID nvarchar,
@.CustomerID bigint OUTPUT,
@.WorkID nvarchar(50) OUTPUT,
@.DateOfQuote datetime OUTPUT,
@.QuoteAmount money OUTPUT,
@.Title nvarchar(50) OUTPUT,
@.FirstName nvarchar(50) OUTPUT,
@.Surname nvarchar(50) OUTPUT,
@.FirstLine nvarchar(50) OUTPUT,
@.District nvarchar(50) OUTPUT,
@.Town nvarchar(50) OUTPUT,
@.Postcode nvarchar(50) OUTPUT,
@.Telephone nvarchar(50) OUTPUT,
@.Requirements nvarchar(3500) OUTPUT,
@.WorkRequired nvarchar(3500) OUTPUT,
@.EmailAddress nvarchar(100) OUTPUT
)
AS

SELECT
@.IndividualID = IndividualID,
@.CustomerID = CustomerID,
@.WorkID = WorkID,
@.DateOfQuote = DateOfQuote,
@.QuoteAmount = QuoteAmount,
@.Title = Title,
@.FirstName = FirstName,
@.Surname = Surname,
@.FirstLine = FirstLine,
@.District = District,
@.Town = Town,
@.Postcode = Postcode,
@.Telephone = Telephone,
@.Requirements = Requirements,
@.WorkRequired = WorkRequired,
@.EmailAddress = EmailAddress

FROM
PendingQuotes

WHERE
IndividualID = @.IndividualID
GO

Any ideas anyone?

I appreciate this is a big amount of data, but if anyone wants to chat to me i'm available on MSN Messenger underwolvokid@.msn.com

The problem is in the stored procedure, you've defined @.IndividualID as nvarchar, specify the length, as it is defaulting to 1. Your query then returns no values because there is no record where IndividualID='3'|||

Call me muppett! It all becomes a blur after you go over and over everything a million times trying to find out whats wrong.

Thanks very much. It works now!