How do you pass NULLs to SQL via an Insert Statment

25 Jan 2006 2:53 AM

Phil Williams

Hello all,
I have a SQL Insert statement like the following:
INSERT INTO tblQuestions
(SurveyID, Inactive, Description, Datatype, Question,
ParentQuestionID, QuestionOrderNum)
VALUES
(@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum)
SELECT @QuestionID = @@IDENTITY

My problem is that the parameters are sometimes NULL.
If I pass in "DataType" as NULL I get the following error

System.InvalidCastException was unhandled
Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
Source="Microsoft.VisualBasic"

Does anyone else get these errors and how do you get around them??

Thanks in advance,
Phil

25 Jan 2006 5:33 AM

William (Bill) Vaughn

Let's see the code you're using to execute the INSERT

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Show quoteHide quote

"Phil Williams" <Phillandsa***@hotmail.com> wrote in message
news:u$eVHqVIGHA.3348@tk2msftngp13.phx.gbl...
> Hello all,
> I have a SQL Insert statement like the following:
> INSERT INTO tblQuestions
> (SurveyID, Inactive, Description, Datatype, Question,
> ParentQuestionID, QuestionOrderNum)
> VALUES
> (@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum)
> SELECT @QuestionID = @@IDENTITY
>
> My problem is that the parameters are sometimes NULL.
> If I pass in "DataType" as NULL I get the following error
>
> System.InvalidCastException was unhandled
> Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
> Source="Microsoft.VisualBasic"
>
> Does anyone else get these errors and how do you get around them??
>
> Thanks in advance,
> Phil
>

25 Jan 2006 8:59 PM

Phil Williams

Hello and thanks for your reply,

The VB code I call to Insert the record uses the TableAdapters Insert method
and is as follows: (Note that I had to send each parameter as the current
cell in the datagridview). This was my way of returning the QuestionID which
is an autonumber
TblQuestionsTableAdapter.Insert(QuestionID, SurveyID, False,
TblQuestionsBindingSource.Current(3), TblQuestionsBindingSource.Current(4),
TblQuestionsBindingSource.Current(5), TblQuestionsBindingSource.Current(6),
TblQuestionsBindingSource.Current(7))

The SQL Insert Statement is as follows: (note that I want the Insert to
return the new QuestionID which is an autonumber)

>> INSERT INTO tblQuestions
>> (SurveyID, Inactive, Description, Datatype,
>> Question, ParentQuestionID, QuestionOrderNum)
>> VALUES
>> (@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum)
>> SELECT @QuestionID = @@IDENTITY

Hope this gives you some idea as what I am trying to do.
If I am doing this all wrong any pointers would be great!

Thanks again.
Phil

Show quoteHide quote

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:ebjFUDXIGHA.3896@TK2MSFTNGP15.phx.gbl...
> Let's see the code you're using to execute the INSERT
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
>
> "Phil Williams" <Phillandsa***@hotmail.com> wrote in message
> news:u$eVHqVIGHA.3348@tk2msftngp13.phx.gbl...
>> Hello all,
>> I have a SQL Insert statement like the following:
>> INSERT INTO tblQuestions
>> (SurveyID, Inactive, Description, Datatype,
>> Question, ParentQuestionID, QuestionOrderNum)
>> VALUES
>> (@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum)
>> SELECT @QuestionID = @@IDENTITY
>>
>> My problem is that the parameters are sometimes NULL.
>> If I pass in "DataType" as NULL I get the following error
>>
>> System.InvalidCastException was unhandled
>> Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
>> Source="Microsoft.VisualBasic"
>>
>> Does anyone else get these errors and how do you get around them??
>>
>> Thanks in advance,
>> Phil
>>
>
>

26 Jan 2006 11:53 AM

Bart Mermuys

Hi,

Show quoteHide quote

"Phil Williams" <Phillandsa***@hotmail.com> wrote in message
news:uwE%23tIfIGHA.208@tk2msftngp13.phx.gbl...
> Hello and thanks for your reply,
>
> The VB code I call to Insert the record uses the TableAdapters Insert
> method and is as follows: (Note that I had to send each parameter as the
> current cell in the datagridview). This was my way of returning the
> QuestionID which is an autonumber
> TblQuestionsTableAdapter.Insert(QuestionID, SurveyID, False,
> TblQuestionsBindingSource.Current(3),
> TblQuestionsBindingSource.Current(4),
> TblQuestionsBindingSource.Current(5),
> TblQuestionsBindingSource.Current(6),
> TblQuestionsBindingSource.Current(7))

First thing to check, do the columns in the table allow null and do the
parameters allow nulls, by default they should if the columns in the table
allow nulls.

Then there is another problem, the insert methods, can take a null (Nothing
in VB.Net) but when bound to a DataSet, BindingSource.Current returns a
DataRowView and a DataRowView uses DBNull.Value instead of Nothing, so
you'll need to convert that....

Dim drv As DataRowView = _
DirectCast(TblQuestionsBindingSource.Current, DataRowView)

SomeAdapter.InsertAuto( QuestionID, SurveyID, False, CNull(drv(3)),
CNull(drv(4)), CNull(drv(5)) ...... )

Private Function CNull( Value As Object ) As Object
If ( Value Is DBNull.Value ) Then
Return Nothing
Else
Return Value
End If
End Function

You know i would try to figure out what's going wrong with
TableAdapter.Update, so you don't have to use a workaround like this.

HTH,
Greetings

Show quoteHide quote

>
> The SQL Insert Statement is as follows: (note that I want the Insert to
> return the new QuestionID which is an autonumber)
>>> INSERT INTO tblQuestions
>>>                      (SurveyID, Inactive, Description, Datatype,
>>> Question, ParentQuestionID, QuestionOrderNum)
>>> VALUES
>>> (@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum)
>>>     SELECT     @QuestionID = @@IDENTITY
>
>
> Hope this gives you some idea as what I am trying to do.
> If I am doing this all wrong any pointers would be great!
>
> Thanks again.
> Phil
>
>
> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
> news:ebjFUDXIGHA.3896@TK2MSFTNGP15.phx.gbl...
>> Let's see the code you're using to execute the INSERT
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>> "Phil Williams" <Phillandsa***@hotmail.com> wrote in message
>> news:u$eVHqVIGHA.3348@tk2msftngp13.phx.gbl...
>>> Hello all,
>>> I have a SQL Insert statement like the following:
>>> INSERT INTO tblQuestions
>>>                      (SurveyID, Inactive, Description, Datatype,
>>> Question, ParentQuestionID, QuestionOrderNum)
>>> VALUES
>>> (@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum)
>>>     SELECT     @QuestionID = @@IDENTITY
>>>
>>> My problem is that the parameters are sometimes NULL.
>>> If I pass in "DataType" as NULL I get the following error
>>>
>>> System.InvalidCastException was unhandled
>>> Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
>>> Source="Microsoft.VisualBasic"
>>>
>>> Does anyone else get these errors and how do you get around them??
>>>
>>> Thanks in advance,
>>> Phil
>>>
>>
>>
>
>

25 Jan 2006 9:18 AM

Tiago Teixeira

Hi,

Try to use 0 when the parameter is type integer and you want it with any
value, or make sure that you don't have this field as 'not allowing
nulls' in the database.
If you have it as 'not allowing nulls' you have to assign always a
value, try it.
If you take off 'not allowing nulls' properties in this field, maybe you
won't get that error.

Just and idea.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto:Phillandsa***@hotmail.com]
Posted At: quarta-feira, 25 de Janeiro de 2006 2:54
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: How do you pass NULLs to SQL via an Insert Statment
Subject: How do you pass NULLs to SQL via an Insert Statment

Hello all,
I have a SQL Insert statement like the following:
INSERT INTO tblQuestions
(SurveyID, Inactive, Description, Datatype,
Question,
ParentQuestionID, QuestionOrderNum)
VALUES
(@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,
@QuestionOrderNum)
SELECT @QuestionID = @@IDENTITY

My problem is that the parameters are sometimes NULL.
If I pass in "DataType" as NULL I get the following error

System.InvalidCastException was unhandled
Message="Conversion from type 'DBNull' to type 'Integer' is not
valid."
Source="Microsoft.VisualBasic"

Does anyone else get these errors and how do you get around them??

Thanks in advance,
Phil