|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do you pass NULLs to SQL via an Insert StatmentI 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 Let's see the code you're using to execute the INSERT
-- Show quote____________________________________ 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 > 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 Hope this gives you some idea as what I am trying to do.>> (SurveyID, Inactive, Description, Datatype, >> Question, ParentQuestionID, QuestionOrderNum) >> VALUES >> (@SurveyID,@Inactive,@Description,@Datatype,@Question,@ParentQuestionID,@QuestionOrderNum) >> SELECT @QuestionID = @@IDENTITY If I am doing this all wrong any pointers would be great! Thanks again. Phil Show 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 >> > > Hi,
Show quote "Phil Williams" <Phillandsa***@hotmail.com> wrote in message First thing to check, do the columns in the table allow null and do the 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)) 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 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 >>> >> >> > > 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  |
|||||||||||||||||||||||
Other interesting topics