Solved

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

Posted on 2006-10-24
4
2,704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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