Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-10-24
4
Medium Priority
?
2,737 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 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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