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


No Value Given For One or More Required Parameter oledb to Excel


No Value Given For One or More Required Parameter oledb to Excel

Author
Message
chcampbelljr 84439
chcampbelljr 84439
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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!!
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14882 Visits: 18591
Quick question, why are you using the nolock hint?
briankwartler
briankwartler
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 662
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?
briankwartler
briankwartler
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 662
Okay, I think I see what you're doing. Could you provide the schemas for TableA, TableB, and for NO_SLR_CODE$
briankwartler
briankwartler
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 662
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
chcampbelljr 84439
chcampbelljr 84439
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 66
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.
briankwartler
briankwartler
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 662
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.
chcampbelljr 84439
chcampbelljr 84439
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 66
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.
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