VB SQL INSERT Command trims leading zeros.

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

  1. DN Admin

    Staff Member

    Joined:
    Aug 7, 2010
    Location:
    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/>
    <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/>
    <br/>
    strAccount_no = "000000010"<br/>
    dateDate1 = SqlDateTime.Null<br/>
    dateDate2 = SqlDateTime.Null<br/>
    <br/>
    'set the SQLCommand object properties<br/>
    objCommand3.Connection = objConnection<br/>
    <br/>
    <br/>
    objCommand3.CommandText = "INSERT INTO names2 " & _<br/>
    "(account_no, date1, date2) " & _<br/>
    "VALUES(" & strAccount_no & ", " & dateDate1 & ", " & dateDate2 & ")"<br/>
    <br/>
    objCommand3.CommandType = CommandType.Text<br/>
    <br/>
    'open Connection<br/>
    If objConnection.State = ConnectionState.Closed Then objConnection.Open()<br/>
    <br/>
    Try<br/>
    objCommand3.ExecuteNonQuery()<br/>
    Catch SqlExceptionErr As SqlException<br/>
    MessageBox.Show(SqlExceptionErr.Message)<br/>
    End Try<br/>
    <br/>
    'close connection<br/>
    If objConnection.State = ConnectionState.Open Then objConnection.Close()<br/>
    <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
    <br/>

    View the full article
     
    #1

Share This Page