Click here to monitor SSC
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
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