How to specify the data type of a field when reading from an SQL database

Question

I have an SQL table called "Clients". It has a CustomerNumber field with values ​​of type "0001234567", that is, they consist only of numbers, but some of them include leading 0s. So, when I try to run something like

sqlFetch(channel=myconn, sqtable="Customers", stringsAsFactors=FALSE)

it returns a table of my customers in which the CustomerNumber field is numeric (instead of a character), and therefore I lose all these leading 0s.

Is there a way to specify the type of field for a column or an alternative solution that will not truncate all my leading 0s?

Answer 1

as.is, "" ?sqlFetch, ?sqlQuery ?sqlGetResults.

, , , , . .

( , RODBC , type.convert, API- API char varchar . 4-5 , RODBC .)

Answer 2

, , .

Sub Main()
    Dim myConn As String = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnStr")
    Dim sqlConnection As SqlConnection = New SqlConnection(myConn)

    Dim strSQL As String = "select cast(CustomerNumber as varchar(30)) as CustomerNumber_varchar, * from Customers"
    Dim myds As DataSet

    sqlConnection.Open()

    Dim cmd As SqlCommand = New SqlCommand(strSQL, sqlConnection)
    cmd.CommandTimeout = 60

    Dim myReader As SqlDataReader = cmd.ExecuteReader()
    myds = ConvertDataReaderToDataSet(myReader)
    myReader.Close()
End Sub


Public Function ConvertDataReaderToDataSet(ByVal reader As SqlDataReader) As DataSet
    Dim dataSet As DataSet = New DataSet()
    Dim schemaTable As DataTable = reader.GetSchemaTable()
    Dim dataTable As DataTable = New DataTable()
    Dim intCounter As Integer
    For intCounter = 0 To schemaTable.Rows.Count - 1
        Dim dataRow As DataRow = schemaTable.Rows(intCounter)
        Dim columnName As String = CType(dataRow("ColumnName"), String)
        Dim column As DataColumn = New DataColumn(columnName, _
            CType(dataRow("DataType"), Type))
        dataTable.Columns.Add(column)
    Next
    dataSet.Tables.Add(dataTable)
    While reader.Read()
        Dim dataRow As DataRow = dataTable.NewRow()
        For intCounter = 0 To reader.FieldCount - 1
            dataRow(intCounter) = reader.GetValue(intCounter)
        Next
        dataTable.Rows.Add(dataRow)
    End While
    Return dataSet
End Function