Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Annoying error with Stored Procedure

Posted on 2004-10-13
12
Medium Priority
?
157 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 1800 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 200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

715 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