Solved

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

Posted on 2006-10-24
4
2,679 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
0
Comment
Question by:niger007
  • 2
4 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 17795077
can you post your code?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17796968
Well, the error would suggest that you are trying to add an SqlParameter object to the parameters collection of an OleDbCommand object.
0
 

Author Comment

by:niger007
ID: 17797313
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
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 17797646
That line should probably say:

    cmd1.Parameters.Add(param)

Rather than:

    cmd.Parameters.Add(param)

Since cmd1 is you SqlCommand.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question