asked on

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

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

YZlat

can you post your code?

Carl Tawn

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

niger007

ASKER

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

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial