[AWS]

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, _
                                 ByVal dateDate1 As Date, ByVal dateDate2 As Date)

        'called from utilconvertrcleducid
        'saves rcleduc_id in the names and rclhistory tables
        Dim objCommand3 As SqlCommand = New SqlCommand()
        Dim objConnection As New SqlConnection(strConnection)

        strAccount_no = "000000010"
        dateDate1 = SqlDateTime.Null
        dateDate2 = SqlDateTime.Null

        'set the SQLCommand object properties
        objCommand3.Connection = objConnection


        objCommand3.CommandText = "INSERT INTO names2 " & _
                   "(account_no, date1, date2) " & _
                   "VALUES(" & strAccount_no & ", " & dateDate1 & ", " & dateDate2 & ")"

        objCommand3.CommandType = CommandType.Text

        'open Connection
        If objConnection.State = ConnectionState.Closed Then objConnection.Open()

        Try
            objCommand3.ExecuteNonQuery()
        Catch SqlExceptionErr As SqlException
            MessageBox.Show(SqlExceptionErr.Message)
        End Try

        'close connection
        If objConnection.State = ConnectionState.Open Then objConnection.Close()

        'dispose of objects
        If Not IsNothing(objCommand3) Then objCommand3.Dispose()
    End Sub

Any help would be appreciated.

---

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

View the full article