How do you pass NULLs to SQL via an Insert Statment

[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

 

[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.
__________________________________

 

[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[email protected]]
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

 

[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)

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

 

[Bart Mermuys]

Hi,

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