Vb.net 2005 convert integer to Null

db , vb.net United States
  • 13 years ago

    Connection:

    Public Shared Sub SQLExecute(ByVal strSQL As String)
            Dim Conn As New SqlConnection(getConnectionString)
            Dim cmd As New SqlCommand(strSQL, Conn)
            With cmd
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()
                .Dispose()
            End With
        End Sub

     

    Public Sub add()
            DBHelper.SQLExecute("insert into emp(empno,ename,sal,deptno) values (" & Empno & ",'" & Ename & "'," & Sal & "," & Deptno & ")")
        End Sub

    I am using the above code to insert the row in sqlserver db.

    When i am trying to insert with empno as NULL which is  of integer type it storing as 0.

    Please help me to store the NULL in the database.

  • 13 years ago


    Integers are value types and can not be null. Running the code below will result in i being 0, not null.
    Dim i As Integer
    i= Nothing

    In the example that you have above, I would not insert into the empno column if empno should be null: 

    Public Sub add()
            DBHelper.SQLExecute("insert into emp(ename,sal,deptno) values ('" & Ename & "'," & Sal & "," & Deptno & ")")
        End Sub

    If a parameterised query or stored procedure, you can use System.DBNull.Value to denote a null.

     

  • 13 years ago

    Thanks for the reply. Iam not using parameterised , before insering  i am holding value  as a  some object .

     

  • 13 years ago

    Public Sub add()

    dim szEmp as string

    'assume you set empno somewhere and have some way of knowing it is null?

    if  empno_should_be_null then 

         szEmp = "NULL"

    else

         szEmp =  format(empno,"0")

    endif
            DBHelper.SQLExecute("insert into emp(empno,ename,sal,deptno) values (" & Empno & ",'" & Ename & "'," & Sal & "," & Deptno & ")")
        End Sub

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is anticipated with distaste, performed with reluctance, and bragged about forever.” - Dan Kaminsky