Annoying error with Stored Procedure


Below is the code I'm using but on this line:

 txt_Name.Text = cmd.Parameters("@Name").Value

I'm getting the following error 'Cast from type 'DBNULL' to type 'String' is not valid'.  I know the Name field isn't NULL. What am I doing wrong?

**************************

        Try
            cnn.open

            Dim cmd As SqlCommand = cnn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "procLoadNames"

            'Input Parameter
            cmd.Parameters.Add(New SqlParameter("@NameIDAuto", SqlDbType.Int))
            cmd.Parameters("@ReferralServiceProviderIDAuto").Value = m_NameIDAuto

            'Output Parameters
            cmd.Parameters.Add(New SqlParameter("@Name", SqlDbType.VarChar, 100))
            cmd.Parameters("@Name").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@Address", SqlDbType.VarChar, 35))
            cmd.Parameters("@Address").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@City", SqlDbType.VarChar, 35))
            cmd.Parameters("@City").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@Phone", SqlDbType.VarChar, 30))
            cmd.Parameters("@Phone").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@EMail", SqlDbType.VarChar, 60))
            cmd.Parameters("@EMail").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@ContactPerson", SqlDbType.VarChar, 35))
            cmd.Parameters("@ContactPerson").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.VarChar, 6000))
            cmd.Parameters("@Description").Direction = ParameterDirection.Output

            GroupBox_Details.Enabled = True

            cmd.ExecuteNonQuery()

            txt_Name.Text = cmd.Parameters("@Name").Value
            txt_Address.Text = cmd.Parameters("@Address").Value
            txt_City.Text = cmd.Parameters("@City").Value
            txt_Phone.Text = cmd.Parameters("@Phone").Value
            txt_Email.Text = cmd.Parameters("@EMail").Value
            txt_ContactPerson.Text = cmd.Parameters("@ContactPerson").Value
            txt_OrganisationNotes.Text = cmd.Parameters("@Description").Value

            Grid_SearchResult.Visible = False

        Catch ex As Exception
            MessageBox.Show("ERROR" & ex.ToString)
        Finally
            cnn.Close()
        End Try

**************************
PeterErhardAsked:
Who is Participating?
 
etmendzConnect With a Mentor Commented:
Indeed your stored procedure is the problem!

I recommend that for cases like this, you'd rather use SqlDataReader. That way, you don't have too many return arguments via SqlCommand. So in your case, the code should more or less be:

-----

Private m_ReferralServiceProviderIDAuto As Integer

Private Function LoadReferralServiceProvider()
 Try
  'Check the status of the connection
  CheckConnectionStatus()
  Dim cmd As SqlCommand = cnn.CreateCommand
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "procLoadReferralServiceProvider"
  'Input Parameter
  cmd.Parameters.Add(New SqlParameter("@ReferralServiceProviderIDAuto", SqlDbType.Int))
  cmd.Parameters("@ReferralServiceProviderIDAuto").Value = m_ReferralServiceProviderIDAuto
  GroupBox_Details.Enabled = True
  Dim reader As SqlDataReader = cmd.ExecuteReader()
  If reader.Read()
   With reader
    txt_Name.Text = .GetValue(.GetOrdinal("ProviderName"))
    txt_Address.Text = .GetValue(.GetOrdinal("Address"))
    txt_City.Text = .GetValue(.GetOrdinal("City"))
    txt_Phone.Text = .GetValue(.GetOrdinal("Phone"))
    txt_Email.Text = .GetValue(.GetOrdinal("EMail"))
    txt_ContactPerson.Text = .GetValue(.GetOrdinal("ContactPerson"))
    txt_OrganisationNotes.Text = .GetValue(.GetOrdinal("Description"))
   End With
  Else
   ' do something when result is empty
  End If
  Grid_SearchResult.Visible = False
 Catch ex As Exception
  MessageBox.Show("ERROR" & ex.ToString)
 Finally
  reader.Close()
  cnn.Close()
 End Try
End Function

-----

Your stored procedure should more or less be:

-----

CREATE PROCEDURE procLoadReferralServiceProvider
 @ReferralServiceProviderIDAuto int
AS
 select ProviderName, Address, City, Phone, EMail, ContactPerson, Description
 from ReferralServiceProvider
 where ReferralServiceProviderIDAuto = @ReferralServiceProviderIDAuto
GO

-----

Have fun.
0
 
flavoCommented:
I know that in Access Name is a reserved word, and causes funny problems, not too sure if this is the same in MS SQL.

Try changing the parameter Name to sName

Dave
0
 
PeterErhardAuthor Commented:
No such luck - same error :(
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
etmendzCommented:
Breakpoint on that line, debug, step and see if it is indeed not DbNull.

BTW. Don't you think there's something wrong here?

            'Input Parameter
            cmd.Parameters.Add(New SqlParameter("@NameIDAuto", SqlDbType.Int))
            cmd.Parameters("@ReferralServiceProviderIDAuto").Value = m_NameIDAuto

Have fun...
0
 
123654789987Commented:
Can u post the stored proc. Probably there is a mistake in declaring "@Name" in the stored proc. Also do test at runtime whether the stored proc is returning any value.

Apart from that the syntax looks fine.
0
 
mdouganCommented:
Hi PeterErhard,

Yea, look to see how you defined the @name parameter when you created it in VB... make sure you've defined it as Output or InputOutput... then, make sure that in your proc, you've got the parameter defined with the word Output after it (assuming SQL Server here).  Then, make sure that you are SETting the value of the parameter in your proc, and not within a block of code that only executes conditionally... (cover all of your possible conditions to always set it as something).  Finally, if you want to avoid the error, you can always do the assignment by appending an empty string to the end, which will prevent the Null error...

txt_Name.Text = cmd.Parameters("@Name").Value & ""


Cheers!
0
 
PeterErhardAuthor Commented:
Thanks for all your responses! Here's my re-done stored procedure and function but I'm still getting the same error. I know the Stored Procedure should be returning a value

**********************

Private m_ReferralServiceProviderIDAuto As Integer

 Private Function LoadReferralServiceProvider()
        Try

            'Check the status of the connection
            CheckConnectionStatus()

            Dim cmd As SqlCommand = cnn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "procLoadReferralServiceProvider"

            'Input Parameter
            cmd.Parameters.Add(New SqlParameter("@ReferralServiceProviderIDAuto", SqlDbType.Int))
            cmd.Parameters("@ReferralServiceProviderIDAuto").Value = m_ReferralServiceProviderIDAuto

            'Output Parameters
            cmd.Parameters.Add(New SqlParameter("@ProviderName", SqlDbType.VarChar, 100))
            cmd.Parameters("@ProviderName").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@Address", SqlDbType.VarChar, 35))
            cmd.Parameters("@Address").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@City", SqlDbType.VarChar, 35))
            cmd.Parameters("@City").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@Phone", SqlDbType.VarChar, 30))
            cmd.Parameters("@Phone").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@EMail", SqlDbType.VarChar, 60))
            cmd.Parameters("@EMail").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@ContactPerson", SqlDbType.VarChar, 35))
            cmd.Parameters("@ContactPerson").Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.VarChar, 6000))
            cmd.Parameters("@Description").Direction = ParameterDirection.Output

            GroupBox_Details.Enabled = True

            cmd.ExecuteNonQuery()

            txt_Name.Text = cmd.Parameters("@ProviderName").Value
            txt_Address.Text = cmd.Parameters("@Address").Value
            txt_City.Text = cmd.Parameters("@City").Value
            txt_Phone.Text = cmd.Parameters("@Phone").Value
            txt_Email.Text = cmd.Parameters("@EMail").Value
            txt_ContactPerson.Text = cmd.Parameters("@ContactPerson").Value
            txt_OrganisationNotes.Text = cmd.Parameters("@Description").Value

            Grid_SearchResult.Visible = False

        Catch ex As Exception
            MessageBox.Show("ERROR" & ex.ToString)
        Finally
            cnn.Close()
        End Try
    End Function

*****************************************

CREATE PROCEDURE procLoadReferralServiceProvider
      @ReferralServiceProviderIDAuto int,
      @ProviderName varchar(100) OUTPUT,
      @Address varchar(35) OUTPUT,
      @City varchar(35) OUTPUT,
      @Phone varchar(30) OUTPUT,
      @EMail varchar(60) OUTPUT,
      @ContactPerson varchar(35) OUTPUT,
      @Description varchar(6000) OUTPUT
AS
      select @ProviderName, @Address, @City, @Phone, @EMail, @ContactPerson, @Description
      from ReferralServiceProvider
      where ReferralServiceProviderIDAuto = @ReferralServiceProviderIDAuto
GO

***********************************
0
 
mdouganCommented:
Your problem is that you're not really selecting columns from the database... you're only selecting the parameters... you need to change that to this:


 select @ProviderName = ProviderName,
          @Address = ReferralServiceProvider.Address,
          @City = ReferralServiceProvider.City,
          @Phone = ReferralServiceProvider.Phone,
          @EMail = ReferralServiceProvider.EMail,
          @ContactPerson = ReferralServiceProvider.ContactPerson,
         @Description = ReferralServiceProvider.Description
     from ReferralServiceProvider
     where ReferralServiceProviderIDAuto = @ReferralServiceProviderIDAuto

Still, this is really dicey on a couple of scores... what happens if no record matches the @ReferralServiceProviderIDAuto... what will be in the parameters?  Also, what if a record is found, but has a null value in one of the columns?  It's always best to pre-initialize the result values to something not null, but which will indicate an error:

set @ProviderName = '?'
set @Address = '?'
set @Phone = '?'
set @EMail = '?'
set @ContactPerson = '?'
set @Description = '?'

 select @ProviderName = ProviderName,
          @Address = ReferralServiceProvider.Address,
          @City = ReferralServiceProvider.City,
          @Phone = ReferralServiceProvider.Phone,
          @EMail = ReferralServiceProvider.EMail,
          @ContactPerson = ReferralServiceProvider.ContactPerson,
         @Description = ReferralServiceProvider.Description
     from ReferralServiceProvider
     where ReferralServiceProviderIDAuto = @ReferralServiceProviderIDAuto

This ensures that no matter what, y ou will have a non null value in your output parameter
0
 
mdouganCommented:
yep, I'd have to agree with etmendez, that's the way I'd do it.
0
 
PeterErhardAuthor Commented:
Thanks etmendz  - all works except for one little thing:

        Finally
            reader.Close()
            cnn.Close()
        End Try

When I have reader.close() inside the FInally statement it says "name 'reader' is not declared"
0
 
mdouganConnect With a Mentor Commented:
That's is because you've dimmed the Reader variable inside of the Try block... if you move the Dim reader as SQLDataReader up above the Try statement, you won't have any trouble.

Now, I like to close the reader inside of the "If Reader.Read()" section, because I believe that if reader.read() is false... an empty recordset, then the reader may already be closed, and trying to close it again may generate an error.
0
 
PeterErhardAuthor Commented:
Thanks for all your help guys :)
0
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.

All Courses

From novice to tech pro — start learning today.