Error in Stored Procedure

I'm getting the following error in the stored procedure below "Object must implement IConvertible". Can anyone see why?

   Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If Not ValidateProgrammeFields() Then Exit Sub

        'Check the status of the connection
        CheckConnectionStatus()

        Dim cmd As SqlCommand = cnn.CreateCommand

        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "procInsertProgrammes"

            cmd.Parameters.Add(New SqlParameter("@UserCreated", SqlDbType.Text, 35))
            cmd.Parameters("@UserCreated").Value = g_Username
            cmd.Parameters.Add(New SqlParameter("@DateCreated", SqlDbType.DateTime, 40))
            cmd.Parameters("@DateCreated").Value = New Date(Today.Year, Today.Month, Today.Day)
            cmd.Parameters.Add(New SqlParameter("@JobClubNumber", SqlDbType.Text, 100))
            cmd.Parameters("@JobClubNumber").Value = txt_JobClubNumber.Text.Replace("'", "''")
            cmd.Parameters.Add(New SqlParameter("@JobClubNumberSeries", SqlDbType.Int))
            cmd.Parameters("@JobClubNumberSeries").Value = ComboBox_FinancialYear
            cmd.Parameters.Add(New SqlParameter("@StartDate", SqlDbType.DateTime, 40))
            cmd.Parameters("@StartDate").Value = DateTime_StartDate.Value
            cmd.Parameters.Add(New SqlParameter("@EndDate", SqlDbType.DateTime, 40))
            cmd.Parameters("@EndDate").Value = DateTime_EndDate.Value
            cmd.Parameters.Add(New SqlParameter("@Location", SqlDbType.Text, 100))
            cmd.Parameters("@Location").Value = txt_Location.Text.Replace("'", "''")
            cmd.Parameters.Add(New SqlParameter("@LookUp_FinancialYearIDAuto", SqlDbType.Int))
            cmd.Parameters("@LookUp_FinancialYearIDAuto").Value = ComboBox_FinancialYear.SelectedIndex()
            cmd.Parameters.Add(New SqlParameter("@BranchID", SqlDbType.Int))
            cmd.Parameters("@BranchID").Value = g_UserCentre
            cmd.Parameters.Add(New SqlParameter("@ProgrammesIDAuto", SqlDbType.Int))
            cmd.Parameters("@ProgrammesIDAuto").Value = m_ProgrammesIDAuto

            cmd.ExecuteNonQuery()

            If m_ProgrammesIDAuto = 0 Then
                MessageBox.Show("New Programme Successfully Saved!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("Programme Successfully Updated!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

            GroupBox_Details.Enabled = False
            GroupBox_ProgrammeData.Enabled = False
            txtSearch.Focus()

        Catch ex As Exception
            MessageBox.Show(Err.Description)
        Finally
            cnn.Close()
        End Try

    End Sub

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

CREATE PROCEDURE procInsertProgrammes
      @UserCreated varchar(35),
      @DateCreated datetime,
      @JobClubNumber varchar(100),
      @JobClubNumberSeries int,
      @StartDate datetime,
      @EndDate datetime,
      @Location varchar(100),
      @LookUp_FinancialYearIDAuto int,
      @BranchID int,
      @ProgrammesIDAuto int

AS
      If @ProgrammesIDAuto = 0
      BEGIN
            Insert into Programmes (UserCreated,  DateCreated, JobClubNumber, JobClubNumberSeries, StartDate, EndDate, Location, LookUp_FinancialYearIDAuto,BranchID)
            Values (@UserCreated, @DateCreated,  @JobClubNumber, @JobClubNumberSeries, @StartDate, @EndDate, @Location, @LookUp_FinancialYearIDAuto, @BranchID)
      END
      Else
            Update Programmes
            Set UserCreated = @UserCreated,  JobClubNumber = @JobClubNumber, JobClubNumberSeries = @JobClubNumberSeries, StartDate = @StartDate,
            EndDate = @EndDate, Location = @Location, LookUp_FinancialYearIDAuto = @LookUp_FinancialYearIDAuto, BranchID = @BranchID
            where ProgrammesIDAuto = @ProgrammesIDAuto
GO
PeterErhardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DabasCommented:
Hi PeterErhard,
The error looks more like a .NET error rather than a stored procedure error.

Also, in your Catch section, you are showing Err.Description.
I suggest you change it to

    Catch ex as Exception
        MessageBox.Show(ex.Message & vbcrlf & ex.StackTrace)
        ...

Run again and post the Message and the StackTrace

Dabas
0
vinhthuy_nguyenCommented:
Hi, did you use a shortdate data type in the database

cmd.Parameters.Add(New SqlParameter("@DateCreated", SqlDbType.DateTime, 40))
cmd.Parameters("@DateCreated").Value = New Date(Today.Year, Today.Month, Today.Day)

Can you try cmd.Parameters("@DateCreated").Value = Date.now()
0
natlozCommented:
I would say that you should replace this

cmd.Parameters("@DateCreated").Value = New Date(Today.Year, Today.Month, Today.Day)

with

cmd.Parameters("@DateCreated").Value = Now.Date

also...
is this an Integer? cmd.Parameters("@BranchID").Value = g_UserCentre <---------???
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

JigitCommented:
PeterErhard, it seems that Dabas is right. It seems like .NET error. Take a look what are base classes of your class and make sure you implement all their interfaces. You may publish more source code to help us helping you.

HTH,
Jigit
0
natlozCommented:
Change your catch to be more verbose...

 Catch ex As Exception
            msgbox(ex.toString)
 Finally
0
PeterErhardAuthor Commented:
Changing the date doesn't work  and yes g_UserCentre is an Integer.

This is following error I get when I include the various error catches:

Object must implement IConvertible.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at JobClub.frmJTProgrammes.btnSave_Click(Object sender, EventArgs e) in C:\wbcode\Job Club\JobClub\frmJTProgrammes.vb:line 762
System.InvalidCastException: Object must implement IConvertible.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at JobClub.frmJTProgrammes.btnSave_Click(Object sender, EventArgs e) in C:\wbcode\Job Club\JobClub\frmJTProgrammes.vb:line 762"

Line 762 is:

cmd.ExecuteNonQuery()
0
DabasCommented:
PeterErhard:
>    cmd.Parameters("@JobClubNumberSeries").Value = ComboBox_FinancialYear
Try converting this to a CINT

   cmd.Parameters("@JobClubNumberSeries").Value = Cint(ComboBox_FinancialYear)

Same with FinancialYearIDAuto, BranchID and ProgrammesIDAuto

Since you are using parameters, I do not think you need the Replace("'", "''") . Just try without Replace on the relevant two lines, to determine if they are the cause of the problem.

Dabas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterErhardAuthor Commented:
Thanks Dabas - your comment put me onto the problem which was with this line:

cmd.Parameters("@JobClubNumberSeries").Value = ComboBox_FinancialYear

Changing it to

cmd.Parameters("@JobClubNumberSeries").Value = ComboBox_FinancialYear.text

sorted it.
0
DabasCommented:
Peter,
    I would still suggest you CInt it.

Also placing an Option Strict = ON at the beginning of all of your code (or setting the appropriate setting so its done automatically) is highly recommended to avoid this type of error

Dabas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.