null varbinary(max) question

I'm doing inserts into a table where I pass a byte[] array to a
varbinary(max) column. The colums is NULLS OK.
when I have a jpg it works great. but if the byte[] is null I get an er
ror
saying
argument @.Photo expects a value.
Why won't it accept null?
Regards,
Gary BlakelyTry passing DbNull.Value instead of a .Net null.
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> I'm doing inserts into a table where I pass a byte[] array to a
> varbinary(max) column. The colums is NULLS OK.
> when I have a jpg it works great. but if the byte[] is null I get an
> error saying
> argument @.Photo expects a value.
> Why won't it accept null?
> --
> Regards,
> Gary Blakely
>|||Yes, when sending a null value as a Parameter value in a command to the
database, you cannot use null (Nothing in Visual Basic.NET). Instead you
need to use DBNull.Value.
You may refer to the section "Passing Null as Parameter Value" in this
article:
Best Practices for Using ADO.NET
http://msdn2.microsoft.com/en-us/library/ms971481.aspx
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
> Try passing DbNull.Value instead of a .Net null.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
>|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:qR1tCUb2HHA.4100@.TK2MSFTNGHUB02.phx.gbl...
> Yes, when sending a null value as a Parameter value in a command to the
> database, you cannot use null (Nothing in Visual Basic.NET). Instead you
> need to use DBNull.Value.
> You may refer to the section "Passing Null as Parameter Value" in this
> article:
> Best Practices for Using ADO.NET
> http://msdn2.microsoft.com/en-us/library/ms971481.aspx
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||Hi Tina,
Thanks for your detailed response.
For the error yielded by DBNull.Value, it should be caused by not
explicitly specifying the column type. I performed a test with the
following code and it worked fine:
#define _BYTES
....
#if _BYTES
byte[] bytes = { 0x01, 0x11, 0x10, 0x21, 0xFF };
#else
byte[] bytes = null;
#endif
SqlConnection cn = new
SqlConnection("server=sha-chlwang-2k3\\wow;database=broker;integrated
security=SSPI");
cn.Open();
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO BinTest
VALUES(@.P1,@.P2)", cn);
cmd.Parameters.Add(new SqlParameter("@.P1",
SqlDbType.VarBinary, -1, "BImage"));
cmd.Parameters.Add(new SqlParameter("@.P2",
SqlDbType.NVarChar, 50, "DESC"));
if(bytes==null)
cmd.Parameters["@.P1"].Value = DBNull.Value;
else
cmd.Parameters["@.P1"].Value = bytes;
cmd.Parameters["@.P2"].Value = "Test";
int n = cmd.ExecuteNonQuery();
MessageBox.Show(n.ToString());
}
finally
{
cn.Close();
}
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
Charles already pointed out that you can specify the parameter data type in
the SqlParameter constructor to avoid implicit conversion. I want to add
that an important clue about the cause of the error is the reference to
nvarchar in the error message. IMHO, the Best Practice is to always specify
the parameter data type that matches the data type on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:%23vGjc9t2HHA.6128@.TK2MSFTNGP02.phx.gbl...
> Sorry but I didn't do a very good job of stating my problem. Let me try
> again...
> This is my code where Photo is a byte[]...
> if (photo == null)
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
> }
> else
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", photo));
> }
> I have successfully used this technique on DateTime types using
> Convert.DBNull and that worked fine. But Convert.DBNull won't work for
> the Varbinary. Your suggestion to use DBNull.Value yields the following
> error...
>
> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
> ?
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
>|||Hi Gary,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
(sorry for the confusion caused by my replying from another developer's
computer (tina) earlier in this thread)
the code pasted below, that I developed based on your suggestion, works.
Like I said I have done this before with datetime types and I have never had
to explicitly state the datatype before. But with varbinary it apparently
needs to be done. Thanks much for your help!
cmd.Parameters.Add(new SqlParameter("@.photo",
SqlDbType.VarBinary, -1, "Photo"));
if (photo == null)
{
cmd.Parameters["@.photo"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@.photo"].Value = photo;
}
--
Regards,
Gary Blakely
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YiiQ2ey3HHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hi Gary,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>