Solved

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

Posted on 2006-10-24
4
2,653 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
Comment Utility
can you post your code?
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
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
Comment Utility
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
Comment Utility
That line should probably say:

    cmd1.Parameters.Add(param)

Rather than:

    cmd.Parameters.Add(param)

Since cmd1 is you SqlCommand.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now