Error Invalid cast from 'System.String' to 'System.Byte[]' in C# calling stored procedure

Question

I have a dropdownlist that gets populated with a varbinary(256) column from SQL Server. I send the selected value in this dropdownlist to a stored procedure, but I get the error

Invalid cast from 'System.String' to 'System.Byte[]'.

The C# code is calling a SP that runs this query to return the name and id to the dropdown list.

SELECT staffID, sAMAccountName,  (sn + ', ' + givenName) AS fullName
FROM staff
WHERE deleteEmployee = 'no' AND recordType = 'staff'
ORDER BY sn

I add the parameter as below.

objCmd.Parameters.Add("@staffID", SqlDbType.Binary).Value = ddl_staff.SelectedItem.Value;

If I go into SQL and execute the SP like below, I get the expected results.

DECLARE @staffID varbinary(256)
SELECT @staffID = staffID from staff where samaccountname = 'johndoe'
EXECUTE stp_nho_status @staffID

What is happening to the data when it is run by the asp.net page; is it converted to a string in the dropdownlist value? Do I have to convert it somehow?

If you set a breakpoint and inspect the value of ddl_staff.SelectedItem.Value, what do you get? — Chris
– Chris, Commented Jan 6, 2015 at 20:11
have you checked the value of SelectedItem.Value of the dropdowmlist, the value use to be a string in the parameters you need a Binary and youre adding string value,in your query you should convert the SelectedItem.Value to binary, can you checked — Enrique YC
– Enrique YC, Commented Jan 6, 2015 at 20:12
Also, wonder if the parameter should be SqlDbType.VarBinary since it's declared as a varbinary in your SQL? — littlerufe
– littlerufe, Commented Jan 6, 2015 at 20:57

littlerufe · Accepted Answer · 2015-01-06 20:23:12Z

1

You might consider writing a method that converts a string to a byte[] (which can be converted to a SqlDbType.VarBinary):

public static byte[] StrToByteArray(string value)
{
    System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
    return encoding.GetBytes(value);
}

Then you could use it like:

objCmd.Parameters.Add("@staffID", SqlDbType.VarBinary).Value = 
    StrToByteArray(ddl_staff.SelectedItem.Value);

answered Jan 6, 2015 at 20:23

littlerufe

37.2k5 gold badges33 silver badges48 bronze badges

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

3 Comments

user1633947 Over a year ago

Does ASP.NET convert the varbinary to a string when it is populated in the dropdownlist? I tried as you suggested, I returned no results where I expected them. I inserted the staffID being passed to the SP in a temp table, and it looks different than what would have been coming out of the staff table, some conversion issue?

littlerufe Over a year ago

Look it up: msdn.microsoft.com/en-us/library/…

littlerufe Over a year ago

Sounds like a conversion problem. What does the inserted value look like compared to the pre-conversion string value?

Collectives™ on Stack Overflow

Error Invalid cast from 'System.String' to 'System.Byte[]' in C# calling stored procedure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related