• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
PeterErhard
Asked:
PeterErhard
  • 3
  • 2
  • 2
  • +2
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now