We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not SqlParameter objects.

niger007
niger007 asked
on
Medium Priority
2,974 Views
Last Modified: 2008-09-12
Hi,

The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not SqlParameter objects.

I have encountered the above error message while executing my sql parameter. What could be the root cause for the above error.

I am connecting to a Ms access table using oledb connection string and copying some info based on user selection into sql server tables.. i have check whether the same info already exist in my sql server table also .. but when i run my code.. i have experienced above issue..

If needed i can provide my code also...

Niger
Comment
Watch Question

CERTIFIED EXPERT

Commented:
can you post your code?
Carl TawnSenior Systems and Integration Developer
CERTIFIED EXPERT

Commented:
Well, the error would suggest that you are trying to add an SqlParameter object to the parameters collection of an OleDbCommand object.

Author

Commented:
Dim tempSKU As String
        Dim tempInternalWO As String
        Dim tempMediaSet As String
        Dim tempMediaWO As String
        Dim tempDiscPartNo As String
        Dim tempIPCPartNo As String
        Dim tempQuantity As Int64
        Dim tempStart As String
        Dim tempEndCode As String
        Dim tempPdate As DateTime
        Dim tempReceivedDate As DateTime
        Dim tempFileName As String
        Dim tempUserName As String
        Dim tempBusinessStream As String
        Dim tempPO As Int64
        Dim tempSignalQty As Int64
        Dim tempSampleQty As Int64
        Dim tempActualUsage As Int64
        Dim tempGroupID As Int16
        Dim tempRemark As String

        If IsValidOK() = True Then
            btnImport.Enabled = False

            Dim conn As New OleDb.OleDbConnection
            Dim cmd As New OleDb.OleDbCommand

            Dim dataadapter As New OleDb.OleDbDataAdapter
            Dim data As New DataSet
            Try

                conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Master.mdb"
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = " Select * from PID3 where SKU = '" & txtSKU.Text.Trim & "'and InternalWO = '" & txtWorkOrder.Text.Trim & "' and MediaSet = '" & txtCDSET.Text.Trim & "' and MediaWO = '" & txtSkuWO.Text.Trim & "'"
                dataadapter.SelectCommand = cmd
                dataadapter.Fill(data, "Master")
                'ddlImport.DataSource = data
                'ddlImport.DataTextField = "FileName"
                'ddlImport.DataBind()

            Catch ex As Exception

            End Try

            Dim dRow As DataRow
            Dim intcount As Integer
            Dim i As Integer = 0
            intcount = data.Tables(0).Rows.Count


            For Each dRow In data.Tables("Master").Rows

                'Data.Tables(0).Rows(0)(6)
                If Convert.ToString(data.Tables(0).Columns.Item(0)).Trim = "SKU" Then
                    tempSKU = data.Tables(0).Rows(i)(0)
                End If
                If Convert.ToString(data.Tables(0).Columns.Item(1)).Trim = "InternalWO" Then
                    tempInternalWO = data.Tables(0).Rows(i)(1)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(2)).Trim = "MediaSet" Then
                    tempMediaSet = data.Tables(0).Rows(i)(2)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(3)).Trim = "MediaWO" Then
                    tempMediaWO = data.Tables(0).Rows(i)(3)
                End If
                If Convert.ToString(data.Tables(0).Columns.Item(4)).Trim = "DiskPartNo" Then
                    tempDiscPartNo = data.Tables(0).Rows(i)(4)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(5)).Trim = "IPCPartNo" Then
                    tempIPCPartNo = data.Tables(0).Rows(i)(5)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(6)).Trim = "Quantity" Then
                    tempQuantity = data.Tables(0).Rows(i)(6)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(7)).Trim = "Start" Then
                    tempStart = data.Tables(0).Rows(i)(7)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(8)).Trim = "End" Then
                    tempEndCode = data.Tables(0).Rows(i)(8)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(9)).Trim = "Date" Then
                    tempPdate = data.Tables(0).Rows(i)(9)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(10)).Trim = "ReceiveDate" Then
                    tempReceivedDate = data.Tables(0).Rows(i)(10)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(11)).Trim = "FileName" Then
                    tempFileName = data.Tables(0).Rows(i)(11)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(12)).Trim = "User" Then
                    tempUserName = data.Tables(0).Rows(i)(12)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(13)).Trim = "BusinessStream" Then
                    tempBusinessStream = data.Tables(0).Rows(i)(13)
                End If


                If Convert.ToString(data.Tables(0).Columns.Item(14)).Trim = "PO" Then
                    tempPO = data.Tables(0).Rows(i)(14)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(15)).Trim = "SignalQty" Then
                    tempSignalQty = data.Tables(0).Rows(i)(15)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(16)).Trim = "SampleQty" Then
                    tempSampleQty = data.Tables(0).Rows(i)(16)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(17)).Trim = "ActualUsage" Then
                    tempActualUsage = data.Tables(0).Rows(i)(17)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(18)).Trim = "Groupid" Then
                    tempGroupID = data.Tables(0).Rows(i)(18)
                End If

                If Convert.ToString(data.Tables(0).Columns.Item(19)).Trim = "Remark" Then
                    tempRemark = data.Tables(0).Rows(i)(19)
                End If
                If i <= intcount Then
                    i = i + 1
                Else
                    Exit For
                End If

                Dim conn1 As New SqlClient.SqlConnection
                Dim cmd1 As New SqlClient.SqlCommand
                Dim dataAdapter1 As New SqlClient.SqlDataAdapter
                Dim param As SqlClient.SqlParameter

                conn1 = (New DBConnection).GetConnection()
                cmd1.Connection = conn1
                cmd1.CommandType = CommandType.StoredProcedure
                cmd1.CommandText = "uspImportData"

                param = New SqlClient.SqlParameter("@SKU", SqlDbType.VarChar)
                param.Value = tempSKU
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@InternalWO", SqlDbType.VarChar)
                param.Value = tempInternalWO
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@MediaSet", SqlDbType.VarChar)
                param.Value = tempMediaSet
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@MediaWO", SqlDbType.VarChar)
                param.Value = tempMediaWO
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@DiscPartNo", SqlDbType.VarChar)
                param.Value = tempDiscPartNo
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@IPCPartNo", SqlDbType.VarChar)
                param.Value = tempIPCPartNo
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@Quantity", SqlDbType.BigInt)
                param.Value = tempQuantity
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@Start", SqlDbType.VarChar)
                param.Value = tempStart
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@EndCode ", SqlDbType.VarChar)
                param.Value = tempEndCode
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@PDate", SqlDbType.DateTime)
                param.Value = tempPdate
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@ReceivedDate", SqlDbType.DateTime)
                param.Value = tempReceivedDate
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@FileName", SqlDbType.VarChar)
                param.Value = tempFileName
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@UserName", SqlDbType.Char)
                param.Value = tempUserName
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@BusinessStream", SqlDbType.VarChar)
                param.Value = tempBusinessStream
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@PO", SqlDbType.BigInt)
                param.Value = tempPO
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@SignalQty", SqlDbType.BigInt)
                param.Value = tempSignalQty
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@SampleQty", SqlDbType.BigInt)
                param.Value = tempSampleQty
                cmd1.Parameters.Add(param)


                param = New SqlClient.SqlParameter("@ActualUsage", SqlDbType.BigInt)
                param.Value = tempActualUsage
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@GroupID", SqlDbType.Int)
                param.Value = tempGroupID
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@Remarks", SqlDbType.VarChar)
                param.Value = tempRemark
                cmd1.Parameters.Add(param)

                param = New SqlClient.SqlParameter("@Output", SqlDbType.Bit)
                param.Direction = ParameterDirection.Output
                cmd.Parameters.Add(param)-------------------------------->>>>>>>>>>>>>>Output paramter giving the problem... my access does nt have field named out put.. it it is just to check the whether records already been imported or not.
               

                cmd1.ExecuteNonQuery()

                If param.Value = 1 Then
                    lblError.Text = "File already imported"


Below is my stored procedue i am using to import data from access to sql server.

REATE PROCEDURE uspImportData

@SKU varchar(50),
@InternalWO varchar(50),
@MediaSet varchar(50),
@MediaWO varchar(50),
@DiscPartNo varchar(50),
@IPCPartNo varchar(50),
@Quantity bigint,
@Start varchar(29),
@EndCode varchar(29),
@PDate datetime,
@ReceivedDate datetime,
@FileName varchar(20),
@UserName varchar(20),
@BusinessStream varchar(10),
@PO bigint,
@SignalQty bigint,
@SampleQty bigint,
@ActualUsage bigint,
@GroupID bigint,
@Remarks varchar(10),
@Output bit OUTPUT




 AS
Begin
if exists(select * from  [BatchTracking].[dbo].[Master] where FileName = @FileName)
begin


insert into [BatchTracking].[dbo].[Master]
(
      [SKU],
      [InternalWO],
      [MediaSet],
      [MediaWO],
      [DiscPartNo],
      [IPCPartNo],
      [Quantity],
      [Start],
      [EndCode],
      [PDate],
      [ReceivedDate],
      [FileName],
      [UserName],
      [BusinessStream],
      [PO],
      [SignalQty],
      [SampleQty],
      [ActualUsage],
      [GroupID],
      [Remark]
)
values
(
   @SKU,
   @InternalWO,
   @MediaSet,
   @MediaWO,
   @DiscPartNo,
   @IPCPartNo,
   @Quantity,
   @Start,
   @EndCode,
   @PDate,
   @ReceivedDate,
   @FileName,
   @UserName,
   @BusinessStream,
   @PO,
   @SignalQty,
   @sampleQty,
   @ActualUsage,
   @GroupID,
   @Remarks

)

end
else
begin
Set @Output=1
end
end
GO
............................Pls help




                End If
Senior Systems and Integration Developer
CERTIFIED EXPERT
Commented:
That line should probably say:

    cmd1.Parameters.Add(param)

Rather than:

    cmd.Parameters.Add(param)

Since cmd1 is you SqlCommand.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.