Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

No Value Given for One or More Required Parameters oledb to Excel. Expand / Collapse
Author
Message
Posted Saturday, April 19, 2014 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 8, 2014 12:15 PM
Points: 6, Visits: 57
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!!
Post #1563288
Posted Thursday, June 5, 2014 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:41 AM
Points: 14, Visits: 120
I haven't tried any VB-related code in a quite a while, so ignore me if you already tried this :) 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.
Post #1577737
Posted Thursday, June 5, 2014 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577746
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse