problem with data type

[sweatha]

Hi

I have to insert the commision value and date into the database. For that I have given the coding as

 
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Partial Class commision1
Inherits System.Web.UI.Page
Public strConnection As String
 
Public Conn As SqlConnection
Public Sql As String
Public AdditionalSQL As String
 
Protected Sub btncommission_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btncommission.Click
If Page.IsValid Then
Dim cmdItem As New SqlCommand
Dim commision As String = txtCommission.Text
Dim EffectiveDate As String = txtDate.Text
 
cmdItem.Connection = GetConnection()
 
cmdItem.CommandType = CommandType.StoredProcedure
cmdItem.CommandText = "sp_CommisionAdd"
 
Dim paramcommision As New SqlParameter("@commision", SqlDbType.Decimal)
paramcommision.Value = commision
cmdItem.Parameters.Add(commision)
 
Dim paramEffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.SmallDateTime)
paramEffectiveDate.Value = EffectiveDate
cmdItem.Parameters.Add(paramEffectiveDate)
cmdItem.ExecuteNonQuery()
 
End If
End Sub
Public Function GetConnection() As SqlConnection
strConnection = ConfigurationManager.AppSettings.Get("ConnectString")
Conn = New SqlConnection(strConnection)
Conn.Open()
Return Conn
End Function
End Class
 

In sql server 2005 I have a table named commision with feilds as

Commision decimal(2,2)
EffectiveDate smalldatetime


but while running I am getting the error as

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

[sweatha]

Hi

Then I replaced the lines

Dim commision As String = txtCommission.Text
Dim EffectiveDate As String = txtDate.Text

as

Dim commision As Decimal = txtCommission.Text
Dim EffectiveDate As Date = txtDate.Text

but while running the error is

The SqlParameterCollection only accepts non-null SqlParameter type objects, not Decimal objects.

[DrBunchman]

Hi sweatha,

I think there's a problem on line 25. You define your SQLParameter but then instead of adding that you add the variable commission to your SQLCommand.

Hope this helps,

Dr B

[sweatha]

Hi

I have changed the line no 25 as

cmdItem.Parameters.Add(paramcomm)

But still I got the error as

Error converting data type numeric to decimal.

[DrBunchman]

I'd have thought this one was self explanatory!

It's telling you what the error is - you are trying to pass a variable of type Numeric to one of type Decimal. Check that the data type of your sql parameter, your sql parameter object and the variable you are passing in to it all match.

Dr B