4

I have been beating my head against a rock on this. I have written a scalar function that takes a table type I created as a parameter and it returns a simple varchar here is the sql code if it helps

ALTER FUNCTION [dbo].[pe_Get_Manufacturer]
(
-- Add the parameters for the function here
@Row [dbo].[pe_StringList] READONLY
)

RETURNS VARCHAR(103)
AS
BEGIN

DECLARE @OUT VARCHAR(50)
DECLARE @tempTable TABLE
(
    Position INT,
    ManuName CHAR(100),
    ManuCat CHAR(3)
)

INSERT INTO @tempTable
    SELECT DISTINCT r.Position, c.ima_mfg, c.ima_cat
    FROM dbo.[Admin_ MFR Aliases] as c
        INNER JOIN @Row r
            ON c.orig_mfg = r.Candidate 
            OR c.ima_mfg = r.Candidate
            OR c.orgmfgstrp = r.Candidate
    ORDER BY r.Position

SELECT TOP 1 @OUT = LTRIM(RTRIM(ManuName))  + '^' + COALESCE(ManuCat,'')
FROM @tempTable 
ORDER BY Position DESC

-- Return the result of the function
RETURN @OUT
END

on my C# side I have a funcation that takes is a list of strings that is to be put into a Datatable to to be used as the parameter for the function. I believe I have written this is all correctly how ever my project throws when it goes to run ExecuteScalar on the SQLCommand

public string getManufacturer(IList<string> list)
    {

        int counter = 1;
        DataTable dataTable = new DataTable();
        dataTable.Columns.Add("Position", typeof (int));
        dataTable.Columns.Add("Candidate", typeof (string));

        foreach (var candidate in list)
        {
            DataRow dataRow = dataTable.NewRow();
            dataRow["Position"] = counter++;
            dataRow["Candidate"] = candidate;
            dataTable.Rows.Add(dataRow);
        }

        SqlParameter tableType = new SqlParameter("@Row" , SqlDbType.Structured)
        {
            TypeName = "dbo.pe_StringList",
            Value = dataTable
        };

        string query = " SELECT * FROM dbo.pe_Get_Manufacturer(@Row)";

        SqlCommand sqlCommand = new SqlCommand(query, conn);
        sqlCommand.Parameters.AddWithValue("@Row", tableType);

        return sqlCommand.ExecuteScalar().ToString();

    }

This is the information I get from the exception:

"An exception of type 'System.ArgumentException' occurred in System.Data.dll but was not handled in user code

Additional information: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type."

EDIT---- this is the table type I created

CREATE TYPE pe_StringList
AS TABLE 
(
    Position INT,
    Candidate VARCHAR(50)
)

I changed the query from

string query = " SELECT * FROM dbo.pe_Get_Manufacturer(@Row)";

To

string query = " SELECT dbo.pe_Get_Manufacturer(@Row)";
4
  • What is the definition of the type pe_StringList? And does SELECT * FROM dbo.pe_Get_Manufacturer(@Row) work if called from withing SSMS? Commented Jan 26, 2016 at 15:53
  • What is [dbo].[pe_StringList]? Commented Jan 26, 2016 at 15:53
  • that is is the Table type I made Commented Jan 26, 2016 at 15:55
  • @MattC I added the definition of pe_stringList to the bottom of my question for you and I did update the query tot he correct one and yes it works in SSMS Commented Jan 26, 2016 at 16:00

2 Answers 2

4

Simply change:

sqlCommand.Parameters.AddWithValue("@Row", tableType);

to be:

sqlCommand.Parameters.Add(tableType);

since it is already a SqlParameter type. You would use AddWithValue to create the SqlParameter on-the-fly (though please see my note at the bottom about not using that deprecated method), but you have already created the SqlParameter and just need to add it to the collection.

And yes, removing the * FROM from the query was also necessary from a purely T-SQL syntax stand-point.


Side note: it is best to not use AddWithValue in any case, ever ;-):

Sign up to request clarification or add additional context in comments.

3 Comments

Would be helpful if you explained this comment: Side note: it is best to not use AddWithValue in any case, ever ;-).
@MetroSmurf Was hoping to be lazy and not look up the reference :-p. But good call, so I added two links.
Hey it worked. Thanks a lot! It's comforting to know I wasn't way off.
0

Try changing

sqlCommand.Parameters.AddWithValue("@Row", tableType);

to

sqlCommand.Parameters.AddWithValue("@Row", dataTable);

You're setting the value of the Parameter you're adding with value to a SqlParameter which is not needed.

SqlParameterCollection.AddWithValue Method

1 Comment

Tried out what you suggested it still throws but the exception messsage has change to "Additional information: The table type parameter '@Row' must have a valid type name."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.