Link to home
Start Free TrialLog in
Avatar of PeterErhard
PeterErhard

asked on

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
Avatar of Dabas
Dabas
Flag of Australia image

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
Avatar of vinhthuy_nguyen
vinhthuy_nguyen

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()
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 <---------???
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
Change your catch to be more verbose...

 Catch ex As Exception
            msgbox(ex.toString)
 Finally
Avatar of PeterErhard

ASKER

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()
ASKER CERTIFIED SOLUTION
Avatar of Dabas
Dabas
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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