how to insert DBNnull into binary column via parameter list?

Question

how to insert DBNnull into binary column via parameter list?

j2bmw

SSC Enthusiast

Points: 158
More actions
October 14, 2010 at 8:10 pm

#388010

The table was created using the following script:
CREATE TABLE [USERS] (
[USER_PASSWORD] binary(100) NULL
);
The SqlCeCommand command text:
INSERT INTO [USERS]
([PASSWORD])
VALUES
(@password);
Add the parameter:
command.Parameters.Add("@password", SqlDbType.Binary).Value = DBNull.Value;
Execute the command and get the error "Value was either too large or too small for an unsigned byte."
Can you help please?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Thank you for posting create table scripts and making this pretty easy. I am not sure what you are having problems with though.

I ran the following code and it worked perfectly.

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))

{

da.InsertCommand = new SqlCommand();

da.InsertCommand.Connection = conn;

da.InsertCommand.CommandText = "INSERT INTO [USERS] ([USER_PASSWORD]) VALUES (@password)";

da.InsertCommand.Parameters.Clear();

da.InsertCommand.Parameters.Add("@password", SqlDbType.Binary).Value = DBNull.Value;

da.InsertCommand.ExecuteNonQuery();

}

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

j2bmw SSC Enthusiast Points: 158 More actions · Answer 2

Hi SeanLange,

Thanks for looking at my code.

You are right. The code works fine. I thought it was this column even though I couldn't figure out anything wrong. But later I found the error was caused by another column. So the problem has been resolved.

🙂