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.StoredProcedur e
cmd.CommandText = "procInsertProgrammes"
cmd.Parameters.Add(New SqlParameter("@UserCreated ", SqlDbType.Text, 35))
cmd.Parameters("@UserCreat ed").Value = g_Username
cmd.Parameters.Add(New SqlParameter("@DateCreated ", SqlDbType.DateTime, 40))
cmd.Parameters("@DateCreat ed").Value = New Date(Today.Year, Today.Month, Today.Day)
cmd.Parameters.Add(New SqlParameter("@JobClubNumb er", SqlDbType.Text, 100))
cmd.Parameters("@JobClubNu mber").Val ue = txt_JobClubNumber.Text.Rep lace("'", "''")
cmd.Parameters.Add(New SqlParameter("@JobClubNumb erSeries", SqlDbType.Int))
cmd.Parameters("@JobClubNu mberSeries ").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_Fina ncialYearI DAuto", SqlDbType.Int))
cmd.Parameters("@LookUp_Fi nancialYea rIDAuto"). Value = ComboBox_FinancialYear.Sel ectedIndex ()
cmd.Parameters.Add(New SqlParameter("@BranchID", SqlDbType.Int))
cmd.Parameters("@BranchID" ).Value = g_UserCentre
cmd.Parameters.Add(New SqlParameter("@ProgrammesI DAuto", SqlDbType.Int))
cmd.Parameters("@Programme sIDAuto"). 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.Ena bled = False
txtSearch.Focus()
Catch ex As Exception
MessageBox.Show(Err.Descri ption)
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_FinancialYearIDAut o 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_FinancialYearIDAut o, @BranchID)
END
Else
Update Programmes
Set UserCreated = @UserCreated, JobClubNumber = @JobClubNumber, JobClubNumberSeries = @JobClubNumberSeries, StartDate = @StartDate,
EndDate = @EndDate, Location = @Location, LookUp_FinancialYearIDAuto = @LookUp_FinancialYearIDAut o, BranchID = @BranchID
where ProgrammesIDAuto = @ProgrammesIDAuto
GO
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.StoredProcedur
cmd.CommandText = "procInsertProgrammes"
cmd.Parameters.Add(New SqlParameter("@UserCreated
cmd.Parameters("@UserCreat
cmd.Parameters.Add(New SqlParameter("@DateCreated
cmd.Parameters("@DateCreat
cmd.Parameters.Add(New SqlParameter("@JobClubNumb
cmd.Parameters("@JobClubNu
cmd.Parameters.Add(New SqlParameter("@JobClubNumb
cmd.Parameters("@JobClubNu
cmd.Parameters.Add(New SqlParameter("@StartDate",
cmd.Parameters("@StartDate
cmd.Parameters.Add(New SqlParameter("@EndDate", SqlDbType.DateTime, 40))
cmd.Parameters("@EndDate")
cmd.Parameters.Add(New SqlParameter("@Location", SqlDbType.Text, 100))
cmd.Parameters("@Location"
cmd.Parameters.Add(New SqlParameter("@LookUp_Fina
cmd.Parameters("@LookUp_Fi
cmd.Parameters.Add(New SqlParameter("@BranchID", SqlDbType.Int))
cmd.Parameters("@BranchID"
cmd.Parameters.Add(New SqlParameter("@ProgrammesI
cmd.Parameters("@Programme
cmd.ExecuteNonQuery()
If m_ProgrammesIDAuto = 0 Then
MessageBox.Show("New Programme Successfully Saved!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information
Else
MessageBox.Show("Programme
End If
GroupBox_Details.Enabled = False
GroupBox_ProgrammeData.Ena
txtSearch.Focus()
Catch ex As Exception
MessageBox.Show(Err.Descri
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_FinancialYearIDAut
@BranchID int,
@ProgrammesIDAuto int
AS
If @ProgrammesIDAuto = 0
BEGIN
Insert into Programmes (UserCreated, DateCreated, JobClubNumber, JobClubNumberSeries, StartDate, EndDate, Location, LookUp_FinancialYearIDAuto
Values (@UserCreated, @DateCreated, @JobClubNumber, @JobClubNumberSeries, @StartDate, @EndDate, @Location, @LookUp_FinancialYearIDAut
END
Else
Update Programmes
Set UserCreated = @UserCreated, JobClubNumber = @JobClubNumber, JobClubNumberSeries = @JobClubNumberSeries, StartDate = @StartDate,
EndDate = @EndDate, Location = @Location, LookUp_FinancialYearIDAuto
where ProgrammesIDAuto = @ProgrammesIDAuto
GO
Hi, did you use a shortdate data type in the database
cmd.Parameters.Add(New SqlParameter("@DateCreated ", SqlDbType.DateTime, 40))
cmd.Parameters("@DateCreat ed").Value = New Date(Today.Year, Today.Month, Today.Day)
Can you try cmd.Parameters("@DateCreat ed").Value = Date.now()
cmd.Parameters.Add(New SqlParameter("@DateCreated
cmd.Parameters("@DateCreat
Can you try cmd.Parameters("@DateCreat
I would say that you should replace this
cmd.Parameters("@DateCreat ed").Value = New Date(Today.Year, Today.Month, Today.Day)
with
cmd.Parameters("@DateCreat ed").Value = Now.Date
also...
is this an Integer? cmd.Parameters("@BranchID" ).Value = g_UserCentre <---------???
cmd.Parameters("@DateCreat
with
cmd.Parameters("@DateCreat
also...
is this an Integer? cmd.Parameters("@BranchID"
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
HTH,
Jigit
Change your catch to be more verbose...
Catch ex As Exception
msgbox(ex.toString)
Finally
Catch ex As Exception
msgbox(ex.toString)
Finally
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.SqlC ommand.Exe cuteReader (CommandBe havior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlC ommand.Exe cuteNonQue ry()
at JobClub.frmJTProgrammes.bt nSave_Clic k(Object sender, EventArgs e) in C:\wbcode\Job Club\JobClub\frmJTProgramm es.vb:line 762
System.InvalidCastExceptio n: Object must implement IConvertible.
at System.Data.SqlClient.SqlC ommand.Exe cuteReader (CommandBe havior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlC ommand.Exe cuteNonQue ry()
at JobClub.frmJTProgrammes.bt nSave_Clic k(Object sender, EventArgs e) in C:\wbcode\Job Club\JobClub\frmJTProgramm es.vb:line 762"
Line 762 is:
cmd.ExecuteNonQuery()
This is following error I get when I include the various error catches:
Object must implement IConvertible.
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at JobClub.frmJTProgrammes.bt
System.InvalidCastExceptio
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at JobClub.frmJTProgrammes.bt
Line 762 is:
cmd.ExecuteNonQuery()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Dabas - your comment put me onto the problem which was with this line:
cmd.Parameters("@JobClubNu mberSeries ").Value = ComboBox_FinancialYear
Changing it to
cmd.Parameters("@JobClubNu mberSeries ").Value = ComboBox_FinancialYear.tex t
sorted it.
cmd.Parameters("@JobClubNu
Changing it to
cmd.Parameters("@JobClubNu
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
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
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
...
Run again and post the Message and the StackTrace
Dabas