CODEWITHSUNDEEP
vb.netvisual-studio-2010visual-studioms-access
user1532468
user1532468

Reputation: 1753

Sql statement returning incorrect data

I am confused as to why my SQL select statement is returning incorrect data. In my database the value is 009698 and it is returning 9698. Could someone shed some light as to why this would be happening.

It is an MS Access 2010 database and the column is text and the size is 6. 

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    Try
        DBConnection.connect()

        sql = "SELECT MAX([Request no]) from Requests WHERE Customer = '" & cmbCustomer.Text & "' "
        Dim cmd As New OleDb.OleDbCommand
        Dim id As Integer

        cmd.CommandText = sql
        cmd.Connection = oledbCnn
        dr = cmd.ExecuteReader

        While dr.Read()
                id = CInt(dr.Item(0))
                id = (id) + 1
        End While

        'MessageBox.Show("00" & id)
        'sql = "INSERT INTO Requests ([Request no], Customer) VALUES ('" & id & "', '" & cmbCustomer.Text & "')"

        cmd.Dispose()
        'dr.Close()
        oledbCnn.Close()

    Catch ex As Exception

        MessageBox.Show(ex.Message)

    End Try
End Sub

Upvotes: 0

Views: 130

Answers (2)

Szymon
Szymon

Reputation: 43023

You are treating the returned value as integer so '009698' and '9698' are the same values in this context.

If you want to later convert it to a six-digit string, you can do:

Dim stringId as String
While dr.Read()
    id = CInt(dr.Item(0))
    id = (id) + 1
    stringId = id.ToString().PadLeft(6, "0"c)
End While

Upvotes: 3

Jeff
Jeff

Reputation: 918

Since the field is text, why not use the GetString function on the DataReader.

Dim id As String

While dr.Read
  ''The string value returned from the database
  Dim stringID = dr.GetString(0)

  ''convert the string to an int and add 1
  Dim numericID = CInt(stringID) + 1

  ''convert the number back to a string with leading 0
  id = numericID.ToString.PadLeft(stringID.Length, "0")

End While

I'm assuming you're trying to get the string value from a db, convert it to a number, add one, and then convert it back to the string format with leading zeros.

Upvotes: 1

Related Questions