Annoying error with Stored Procedure

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

No such luck - same error :(

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

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.

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!

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

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

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

yep, I'd have to agree with etmendez, that's the way I'd do it.

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"

Thanks for all your help guys :)