May 22, 2018 at 4:13 pm
I have written a function (UDF) and a stored procedure that will hopefully take in an integer list and insert some data based off of that list. I am having a hard time getting my VB.NET code to properly call the stored procedure. Most of the examples I have seen use a string (VARCHAR) as a parameter list. So this is where the confusion begins I think. I need to be able to call my stored proc with VB.NET. How do I convert my List (Of Integer0 into something my stored proc can use (Varchar(MAX) I guess...) Any direction would be most appreciated. The error I get currently is "{"No mapping exists from object type System.Collections.Generic.List`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type."}" In SQL Profiler Any help would be most appreciated.
--Jason
VB.NET Code
Public Sub SaveList(QuoteNums As List(Of Integer))
Dim Cmd As SqlCommand = CreateSQLCommand()Cmd.Connection = _Cn
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandText = "SP_Insert_WC000311A_PremFormData"
Cmd.Parameters.AddWithValue("@quotelist", QuoteNums.ConvertAll(Of String)(Function(i As Integer) i.ToString()))
Cmd.Parameters.AddWithValue("@quotelist", QuoteNums.ToString.ToArray)
Try
SafeOpen()
Dim Obj As Object = ScalarReturn(Cmd)
Catch Ex As Exception
Throw New ApplicationException("DAL::QuoteFormData::SaveList - Msg: " & Ex.Message & ", Cmd: " & GetParameterString(Cmd))
Finally
FinallyClose()
End Try
End Sub
--Jason
GO
/****** Object: UserDefinedFunction [dbo].[intlist_to_tbl] Script Date: 5/22/2018 4:43:35 PM ******/
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[intlist_to_tbl] (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
]
GO
/****** Object: StoredProcedure [dbo].[SP_Insert_WC000311A_PremFormData] Script Date: 5/22/2018 4:42:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Insert_WC000311A_PremFormData]
@quotelist varchar(MAX) = NULL
AS
DECLARE @PoliciesRenewed TABLE
(
PolicyRenewed VARCHAR(10),
PolicyIDRenewed INT
)
INSERT INTO @PoliciesRenewed
SELECT PolicyRenewed,pd.PolicyID
FROM Quote q INNER JOIN PolicyData pd ON q.PolicyRenewed = pd.Policy
WHERE q.QuoteNum IN (SELECT number FROM intlist_to_tbl(@quotelist))
INSERT INTO [dbo].[QuoteFormData] ([QuoteNum],[EndmtNum],[DataKey],[Num],[Value])
SELECT q.QuoteNum,pfd.EndmtNum,pfd.DataKey,pfd.Num,pfd.Value
FROM PremFormData pfd INNER JOIN @PoliciesRenewed pr ON
pfd.PolicyID = pr.PolicyIDRenewed INNER JOIN Quote q ON
q.PolicyRenewed=pr.PolicyRenewed
WHERE pfd.EndmtNum='WC000311A'
]
May 22, 2018 at 9:27 pm
I've not worked with VB in more than a decade so I'm a bit rusty there. It looks like you're trying to send a quoted CSV and then split it with your function.
Could you provide an example of what the string looks like from your VB?
Also, what's the max length of the string you're trying to pass? I ask because your UDF is an mTVF (Multi-statement Table Value Funtion), which is slow and resource intensive to begin with, and it has a WHILE Loop based "inchworm" splitter. There's a MUCH better alternative but I need to know the max length of the string. Hopefully, it's not really NVARCHAR(MAX), especially if you're only passing integers and delimiters.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2018 at 9:52 pm
you need to change your code to send a string, not a list
something like (untested and not necessarily correct from a syntax point of view)
Cmd.CommandText = "SP_Insert_WC000311A_PremFormData"
dim liststr as String
dim lists As List(Of String) = QuoteNums.ConvertAll(Of String)(Function(i As Integer) i.ToString())
dim liststr as String = String.Join(",", lists)
Cmd.Parameters.AddWithValue("@quotelist", liststr)
Cmd.Parameters.AddWithValue("@quotelist", QuoteNums.ToString.ToArray)
May 23, 2018 at 7:26 am
Jeff Moden - Tuesday, May 22, 2018 9:27 PMI've not worked with VB in more than a decade so I'm a bit rusty there. It looks like you're trying to send a quoted CSV and then split it with your function.Could you provide an example of what the string looks like from your VB?
Also, what's the max length of the string you're trying to pass? I ask because your UDF is an mTVF (Multi-statement Table Value Funtion), which is slow and resource intensive to begin with, and it has a WHILE Loop based "inchworm" splitter. There's a MUCH better alternative but I need to know the max length of the string. Hopefully, it's not really NVARCHAR(MAX), especially if you're only passing integers and delimiters.
Hi Jeff ,
Really haven't thought too much about maxlength . Put it this way , the quote list could be very substantial in size. Below is basically the list . Per my understanding , the QuoteList would just get build up by
Dim QuoteNums As New List(Of Integer)()
QuoteNums.Add(20)
QuoteNums.Add(30)
QuoteNums.Add(500)
May 23, 2018 at 8:59 am
frederico_fonseca - Tuesday, May 22, 2018 9:52 PMyou need to change your code to send a string, not a list
Could they use a table-valued parameter in the proc here instead of building a string then shredding it again in the proc?
https://www.codeproject.com/Articles/42901/Table-Value-Parameters-in-SQL-Server-VB-NET
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply