VB SQL INSERT Command trims leading zeros.

Discussion in 'Visual Basic .Net' started by DN Admin, Apr 25, 2012.

  1. DN Admin

    DN Admin
    Expand Collapse
    Staff Member

    United States
    Aug 7, 2010
    Likes Received:
    Trophy Points:
    Joliet, IL U.S.A.
    VB 2010
    SQL Server Express 2008 R2
    When I try to insert a new row in my data table, the account_no field, PK, nvarchar(9), leading zeros are trimmed.
    String variable "000000010" is saved as "10".
    If I use SQL management to INSERT the row, then it is save correctly.
    Here is the code I am using.
    Private Sub SaveUserDefined(ByVal strDataTable As String, ByVal strAccount_no As String, _<br/>
    ByVal dateDate1 As Date, ByVal dateDate2 As Date)<br/>
    'called from utilconvertrcleducid<br/>
    'saves rcleduc_id in the names and rclhistory tables<br/>
    Dim objCommand3 As SqlCommand = New SqlCommand()<br/>
    Dim objConnection As New SqlConnection(strConnection)<br/>
    strAccount_no = "000000010"<br/>
    dateDate1 = SqlDateTime.Null<br/>
    dateDate2 = SqlDateTime.Null<br/>
    'set the SQLCommand object properties<br/>
    objCommand3.Connection = objConnection<br/>
    objCommand3.CommandText = "INSERT INTO names2 " & _<br/>
    "(account_no, date1, date2) " & _<br/>
    "VALUES(" & strAccount_no & ", " & dateDate1 & ", " & dateDate2 & ")"<br/>
    objCommand3.CommandType = CommandType.Text<br/>
    'open Connection<br/>
    If objConnection.State = ConnectionState.Closed Then objConnection.Open()<br/>
    Catch SqlExceptionErr As SqlException<br/>
    End Try<br/>
    'close connection<br/>
    If objConnection.State = ConnectionState.Open Then objConnection.Close()<br/>
    'dispose of objects<br/>
    If Not IsNothing(objCommand3) Then objCommand3.Dispose()<br/>
    End Sub
    Any help would be appreciated.

    VB6 -> VB.Net learner FoxPro -> SQL Server learner

    View the full article

Share This Page