Results 1 to 5 of 5

Thread: [RESOLVED] "Object cannot be cast from DBNull to other types" only on Insert

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2010
    Posts
    7

    Resolved [RESOLVED] "Object cannot be cast from DBNull to other types" only on Insert

    I am having problems with my code, where I get the "Object cannot be cast from DBNull to other types" message when Insert(). I get the message on every Insert to this table, even though the data is successfully inserted. If I Update the same data after Insert(), it does so without error. I have checked my values and none of them are DBNull. Even when the same values are passed on Insert that are passed on Update. My code is :

    Code:
        Private Sub Save()
            Dim bo As tbl_GL
            bo = New tbl_GL()
            bo.ConnectionString = AppConfig.ConnectString
    
            With bo
                .ID = Convert.ToInt32(Me.txtID.Text.Trim)
                .CodeID = Me.ddlBldgLoc.SelectedItem.Value.ToString
                .ClassificationID = Convert.ToInt32(Me.ddlClassification.SelectedValue)
                If ddlAggregate.SelectedValue <> "0" Then
                    .Aggregate = Convert.ToDecimal(ddlAggregate.SelectedValue)
                Else
                    .Aggregate = 0
                End If
                .Premium = Me.GetPremium
                .ModifiedBy = mwus.LoginID
                .ModifiedDt = Date.NowFormatted()
                .Insert()
            End With
    My tbl_GL columns are:

    GL_ID(PK,int,NotNull)
    ID (FK,int,NotNull)
    CodeID(nchar(5),Null)
    ClassificationID(int,NotNull)
    Aggregate(money,NotNull)
    Premium(money,NotNull)
    ModifiedBy(nvarchar(50),NotNull)
    ModifiedDT(datetime,NotNull)(default value = (getdate())
    GUID(uniqueidentifier, null)(default value = (newid())

    Any suggestions would be helpful

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,924

    Re: "Object cannot be cast from DBNull to other types" only on Insert

    There's not really too much we can say because we don't know what a 'tbl_GL' is and we don't know exactly what its Insert method does.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2010
    Posts
    7

    Re: "Object cannot be cast from DBNull to other types" only on Insert

    Sorry, here is the code created by a code generator that creates a SQL string to perform the Insert:
    Code:
    Public Overrides Function InsertSQL() As String
    		Dim strSQL As String = String.Empty		
    Select Case mintInsertFilter
            Case InsertFilters.All
                           Dim sb As New System.Text.StringBuilder()
                sb.Append("INSERT INTO tbl_GL(")
                sb.Append("GL_ID")
                    sb.Append(", ID")
                    sb.Append(", CodeID")
                sb.Append(", ClassificationID")
                sb.Append(", Aggregate")
                sb.Append(", Premium")
                sb.Append(", ModifiedBy")
                sb.Append(", ModifiedDt")
                sb.Append(") VALUES (")
                sb.Append(NumberToField(mintGL_ID, "GL_ID"))
                    sb.Append(", " & NumberToField(mintID, "ID"))
                    sb.Append(", " & StringToField(mstrCodeID, "CodeID"))
                sb.Append(", " & NumberToField(mintClassificationID, "ClassificationID"))
                sb.Append(", " & NumberToField(mcurAggregate, "Aggregate"))
                sb.Append(", " & NumberToField(mcurPremium, "Premium"))
                sb.Append(", " & StringToField(mstrModifiedBy, "ModifiedBy"))
                sb.Append(", " & StringToDate(mstrModifiedDt, "ModifiedDt"))
                sb.Append(")")
                strSQL = sb.ToString()         
    End Select

  4. #4
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: "Object cannot be cast from DBNull to other types" only on Insert

    Parameters are you're friend. That's a painful way to make a SQL Statement. And are those homemade conversion routines? You seem to be going through way too many hoops here. TryParse statements would work fine for your conversions. I would simplify all this first and your error will probably become apparent.
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2010
    Posts
    7

    Re: "Object cannot be cast from DBNull to other types" only on Insert

    As I stated earlier, the code to create the SQL string was generated by a code generator. It is replicated throughout all of the data classes.

    Anyway, I found the problem was not the code or sql string, but the proble was with the table itself.

    Column GL_ID is the Primary Key and does not allow nulls. And, (Is Identity) was set to No)

    This means the GL_ID required a value on INSERT. Causing the DBNull error.

    When I set (Is Identity) to Yes, the GL_ID value was auto incremented on Insert and my problem was solved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width