Solved

Annoying error with Stored Procedure

Posted on 2004-10-13
12
151 Views
Last Modified: 2010-04-23

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

**************************
0
Comment
Question by:PeterErhard
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12304852
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
 

Author Comment

by:PeterErhard
ID: 12304889
No such luck - same error :(
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12305427
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
 
LVL 10

Expert Comment

by:123654789987
ID: 12305633
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
 
LVL 18

Expert Comment

by:mdougan
ID: 12308367
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
 

Author Comment

by:PeterErhard
ID: 12311555
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 18

Expert Comment

by:mdougan
ID: 12312416
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
 
LVL 6

Accepted Solution

by:
etmendz earned 450 total points
ID: 12315788
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
 
LVL 18

Expert Comment

by:mdougan
ID: 12319054
yep, I'd have to agree with etmendez, that's the way I'd do it.
0
 

Author Comment

by:PeterErhard
ID: 12334300
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
 
LVL 18

Assisted Solution

by:mdougan
mdougan earned 50 total points
ID: 12334368
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
 

Author Comment

by:PeterErhard
ID: 12334461
Thanks for all your help guys :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

22 Experts available now in Live!

Get 1:1 Help Now