SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


No Value Given for One or More Required Parameters oledb to Excel.


No Value Given for One or More Required Parameters oledb to Excel.

Author
Message
chcampbelljr 84439
chcampbelljr 84439
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 66
In order to avoid the 255 character limitation in Excel and SSIS I have written a VB.Net SSIS script to export the results of a query to an Excel template. The query runs fine, the columns are all spelled correctly, the columns align to the Excel template however when the script attempts to update the DataTable I receive an error that says "No Value Given For One or More Required Parameters"


COMM.CommandText = "WITH NO_SLR_CTE AS( SELECT " _
& " [HIC NUMBER], [SSN], [FIRST NAME], [MI], [LAST NAME], [BIRTH DATE], [GEN], [EFF_DATE], " _
& " CAST(A.[FULL_DESCRIPTION] + REPLICATE(SPACE(1), 1000-LEN(A.[FULL_DESCRIPTION])) AS VARCHAR(1000)) AS [DESCRIPTION], A.[CATEGORY]" _
& " ,CASE WHEN LEN(ISNULL([PRI_HEIAR_LEVEL1], '')) > 0 THEN [PRI_HEIAR_LEVEL1] ELSE ISNULL([SEC_HEIAR_LEVEL1], '') END AS [CARRIER]" _
& " ,CASE WHEN LEN(ISNULL([PRI_HEIAR_LEVEL2], '')) > 0 THEN [PRI_HEIAR_LEVEL2] ELSE ISNULL([SEC_HEIAR_LEVEL2], '') END AS [ACCOUNT]" _
& " ,CASE WHEN LEN(ISNULL([PRI_HEIAR_LEVEL3], '')) > 0 THEN [PRI_HEIAR_LEVEL3] ELSE ISNULL([SEC_HEIAR_LEVEL3], '') END AS [GROUP]" _
& " ,ISNULL([SECONDARY_MEMBER_ID], '') AS [SECONDARY_MEMBER_ID]" _
& " ,ISNULL(A.REASON_CODE,'') AS [REASON_CODE]" _
& " ,ISNULL(A.RECORD_STAGE,'') AS [RECORD_STAGE]" _
& " ,ISNULL(A.CLASSIFICATION,'') AS [CLASSIFICATION]" _
& " ,ISNULL(A.APPL_STATUS,'') AS [APPL_STATUS]" _
& " ,ISNULL(A.INCP_COMMENTS,'') AS [SYS_COMMNENTS]" _
& " ,ISNULL(SLR_REASON_CODE,'') AS [SLR_REASON_CODE]" _
& " FROM " _
& " TableA A WITH(NOLOCK)" _
& " LEFT OUTER JOIN TableB B WITH(NOLOCK)" _
& " ON A.REASON_CODE = B.REASON_CODE " _
& " AND ISNULL(A.APPL_STATUS, '') = ISNULL(B.APPL_STATUS,'') " _
& " AND ISNULL(A.RECORD_STAGE,'') = ISNULL(B.RECORD_STAGE,'') " _
& " AND ISNULL(A.CLASSIFICATION,'') = ISNULL(B.CLASSIFICATION,'')" _
& " AND RTRIM(LTRIM(ISNULL(A.INCP_COMMENTS,''))) LIKE '%'+ RTRIM(LTRIM(ISNULL(B.SYS_COMMENT,''))) +'%'" _
& " )" _
& " SELECT * FROM NO_SLR_CTE cte" _
& " WHERE Len(cte.SLR_REASON_CODE) = 0" _
& " ORDER BY " _
& " [CATEGORY] DESC, [DESCRIPTION] DESC"


COMM.Connection = CONN
ADAP.SelectCommand = COMM
ADAP.Fill(RecordSetDataTable)

If Not RecordSetDataTable Is Nothing AndAlso RecordSetDataTable.Rows.Count > 0 Then
ExcelConnection = New OleDb.OleDbConnection(Dts.Connections("LOAD_REPORT_EXCEL_CONNECTION").ConnectionString)
ExcelConnection.Open()

ExcelInsertCommand = New OleDb.OleDbCommand
With ExcelInsertCommand
.Connection = ExcelConnection
.CommandText = String.Format("INSERT INTO [NO_SLR_CODE$]([HIC NUMBER],[SSN],[FIRST NAME],[MI],[LAST NAME],[BIRTH DATE],[GEN],[EFF_DATE],[DESCRIPTION],[CATEGORY],[CARRIER],[ACCOUNT],[GROUP],[SECONDARY_MEMBER_ID],[REASON_CODE],[RECORD_STAGE],[CLASSIFICATION], [APPL_STATUS],[SYS_COMMENTS],[SLR_REASON_CODE]) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
.Parameters.Add(New OleDb.OleDbParameter("@HICNUMBER", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(0).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SSN", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(1).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@FIRSTNAME", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(2).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@MI", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(3).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@LASTNAME", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(4).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@BIRTHDATE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(5).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@GEN", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(6).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@EFF_DATE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(7).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@DESCRIPTION", OleDb.OleDbType.VarWChar, 1000, RecordSetDataTable.Columns(8).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@CATEGORY", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(9).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@CARRIER", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(10).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@ACCOUNT", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(11).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@GROUP", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(12).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SECONDARY_MEMBER_ID", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(13).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@REASON_CODE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(14).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@RECORD_STAGE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(15).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@CLASSIFICATION", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(16).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@APPL_STATUS", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(17).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SYS_COMMENTS", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(18).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SLR_REASON_CODE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(19).ColumnName))
End With


ExcelUpdateCommand = New OleDb.OleDbCommand
With ExcelUpdateCommand
.Connection = ExcelConnection
.CommandText = String.Format("UPDATE [NO_SLR_CODE$] SET [HIC NUMBER] = ?, [SSN] = ?, [FIRST NAME] = ?, [MI] = ?, [LAST NAME] = ?, [BIRTH DATE] = ?, [GEN] = ?, [EFF_DATE] = ?, [DESCRIPTION] = ?, [CATEGORY] = ?, [CARRIER] = ?, [ACCOUNT] = ?, [GROUP] = ?, [SECONDARY_MEMBER_ID] = ?, [REASON_CODE] = ?, [RECORD_STAGE] = ?,[CLASSIFICATION] = ?, [APPL_STATUS] = ? ,[SYS_COMMENTS] = ?,[SLR_REASON_CODE] = ?")
.Parameters.Add(New OleDb.OleDbParameter("@HICNUMBER", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(0).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SSN", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(1).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@FIRSTNAME", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(2).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@MI", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(3).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@LASTNAME", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(4).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@BIRTHDATE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(5).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@GEN", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(6).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@EFF_DATE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(7).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@DESCRIPTION", OleDb.OleDbType.VarWChar, 1000, RecordSetDataTable.Columns(8).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@CATEGORY", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(9).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@CARRIER", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(10).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@ACCOUNT", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(11).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@GROUP", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(12).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SECONDARY_MEMBER_ID", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(13).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@REASON_CODE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(14).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@RECORD_STAGE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(15).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@CLASSIFICATION", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(16).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@APPL_STATUS", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(17).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SYS_COMMENTS", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(18).ColumnName))
.Parameters.Add(New OleDb.OleDbParameter("@SLR_REASON_CODE", OleDb.OleDbType.VarWChar, 255, RecordSetDataTable.Columns(19).ColumnName))
End With

'First row will always be set to modified.
RecordSetDataTable.Rows(0).SetModified()

'Rest all rows should be set to added
For i As Int32 = 1 To RecordSetDataTable.Rows.Count - 1
RecordSetDataTable.Rows(i).SetAdded()

Next


ExcelDataAdapter = New OleDb.OleDbDataAdapter
ExcelDataAdapter.InsertCommand = ExcelInsertCommand
ExcelDataAdapter.UpdateCommand = ExcelUpdateCommand
ExcelDataAdapter.Update(RecordSetDataTable) THE ERROR OCCURS HERE....

I have been fighting with this for days!!! Help! Please!!
bcaruso 14201
bcaruso 14201
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 141
I haven't tried any VB-related code in a quite a while, so ignore me if you already tried this Smile But two pieces I would investigate are whether your columns can insert nulls and whether you insert the data before you update.

In your main select command, you've got ISNULL transformations on most columns (not all returning an alternate field if null), so some like the COMMENTS column probably are null if these data elements are not required. If you're inserting these into your Excel, that's probably not the issue.

Other than that, I noticed your INSERT statement up top but I didn't see where you called that. You reference that insert statement near the end of your code and assign it to a command but the recordset is called with the update function.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62560 Visits: 13298
Or you could set the datatype in SSIS to DT_NTEXT and save you all the trouble in VB.NET.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search