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 Parameter oledb to Excel Expand / Collapse
Author
Message
Posted Sunday, April 20, 2014 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:08 PM
Points: 6, Visits: 56
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 #1563358
Posted Sunday, April 20, 2014 6:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 2,000, Visits: 5,446
Quick question, why are you using the nolock hint?
Post #1563369
Posted Monday, April 21, 2014 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 132, Visits: 162
Maybe I'm missing something but I see a common table expression NO_SLR_CTE but the CommandText of your ExcelUpdateCommand (and that of your ExcelInsertCommand) refers to NO_SLR_CODE$. Is NO_SLR_CODE$ supposed to be NO_SLR_CTE?
Post #1563478
Posted Monday, April 21, 2014 8:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 132, Visits: 162
Okay, I think I see what you're doing. Could you provide the schemas for TableA, TableB, and for NO_SLR_CODE$
Post #1563501
Posted Monday, April 21, 2014 10:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 132, Visits: 162
Why are you using String.Format()? I don't think it's needed, but it's not what's causing your problem - you get the same INSERT or UPDATE statement whether you surround it with String.Format() or you don't.

I would need to spend some time looking at this problem. I'm not sure if it's even possible to do what you're trying to do. It seems you want to use ADO.NET to update an Excel worksheet as if it were a SQL database. You might need a completely new approach.

Perhaps you or someone else on this forum has had successful experience doing something along these lines. If so, I would like to know how it can be done.

It's been a while since I've programmed using ADO.NET but from what I remember, you would normally provide your SqlDataAdapter (or OleDbDataAdapter) with a SelectCommand with a WHERE clause that filtered the rows by the primary key such as

SELECT OrderID, ProductID, Quantity, UnitPrice
FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID

Then you could either use the Table Adapter / Data Adapter Configuration Wizard in Visual Studio or SqlCommandBuilder to generate the InsertCommand, UpdateCommand, and DeleteCommand, or you could provide your own. The InsertCommand would look something like the following:

INSERT INTO [Order Details]
( OrderID, ProductID, Quantity, UnitPrice )
VALUES ( @p1, @p2, @p3, @p4 )

The UpdateCommand might look like the following:

UPDATE [Order Details]
SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New, UnitPrice = @UnitPrice_New
WHERE OrderID = @OrderID_Old AND ProductID = @ProductID_Old

The DeleteCommand might be

DELETE [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID

Again, this worked well for tables in SQL Server databases but I don't know if you are trying to apply this to Excel, where it probably would not be appropriate.

Regards,
Brian
Post #1563540
Posted Monday, April 21, 2014 4:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:08 PM
Points: 6, Visits: 56
After going through everything line by line I found that there was a space in the header of my Template which may have caused the Parameter issue. However, that has now been replaced with a Concurrency Violation: the UpdateCommand affected 0 of the the expected 1 records error. If I didn't need to export the description column with 1,000 characters this wouldn't even be an issue.
Post #1563644
Posted Tuesday, April 22, 2014 7:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 132, Visits: 162
You might want to take a look at http://support.microsoft.com/kb/316934 to see if it addresses the problem you are trying to solve.
Post #1563836
Posted Tuesday, April 22, 2014 5:10 PM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:08 PM
Points: 6, Visits: 56
I finally figured it out. In the end, all of my problems were caused by the Excel template that I was trying to write to. I had to add enough blank spaces in the column that needed to accept the 1,000 character Description field.
Post #1564059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse