How do I display Data from Database in VBA Excel?

Question

I have a column in the database called "UOM" and I want to display it beside a value called "Qty" via opening an SQL query. But it showed an error saying "No data exists for the row/column". This is my codes.

Dim Oledbconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:/inetpub/wwwroot/ProjectIntegrated/Inven.mdb;Persist Security Info=True")
        Dim cmd As New OleDbCommand
        Dim reader As OleDbDataReader

        cmd.CommandText = "Select UOM FROM Master WHERE IPN = '" & Session("PO IPN")(SummaryCounter) & "'"
        cmd.CommandType = CommandType.Text
        cmd.Connection = Oledbconn

        Oledbconn.Open()
        reader = cmd.ExecuteReader()

        oSheet.Range("F" & ExcelCounter).Value = "" & Session("PO Qty") + reader.Item("UOM") + (POTableCounter)
        oSheet.Range("F" & ExcelCounter).HorizontalAlignment = -4108


        Oledbconn.Close()

score 0 · Answer 1 · answered Jul 01 '15 at 08:21

0

Executing the reader alone will not do it. You also have to actually read. Try this:

Oledbconn.Open()
reader = cmd.ExecuteReader()

if (dreader.HasRows)
{
    dreader.Read();
    oSheet.Range("F" & ExcelCounter).Value = "" & Session("PO Qty") + reader.Item("UOM") + (POTableCounter)
    oSheet.Range("F" & ExcelCounter).HorizontalAlignment = -4108
}

Oledbconn.Close()

answered Jul 01 '15 at 08:21

LocEngineer

2,847
1
16
28

I've tried it but it shows this error "Operator '+' is not defined for type 'String()' and string "EA" ". EA is a text value data in the column @LocEngineer – Chloe Allison Jul 01 '15 at 09:12
Please post sample values for your Session and UOM. Is POTableCounter an int? Get rid of `"" &`. – LocEngineer Jul 01 '15 at 09:20
Ummm... Do the `insertCommands` have anything to do with the above code? What **values** do the Session variables hold? Also, according to http://stackoverflow.com/questions/4670247/concat-strings-by-and-in-vb-net please always use the `&` operator to concatenate strings in VB.Net. – LocEngineer Jul 01 '15 at 10:14
"EA" and "LOT" for most of them. They are text values @LocEngineer – Chloe Allison Jul 02 '15 at 05:09
In that case try this: `oSheet.Range("F" & ExcelCounter).Value = Session("PO Qty").ToString() & reader.Item("UOM").ToString() + (POTableCounter).ToString()` – LocEngineer Jul 02 '15 at 08:46
Seems like the cell in Excel displays "System.String[]E" instead of the values @LocEngineer – Chloe Allison Jul 03 '15 at 01:36
Aha. Getting closer. What does this one yield: `oSheet.Range("F" & ExcelCounter).Value = Session("PO Qty").ToString() & dreader.GetString(0) & (POTableCounter).ToString()` – LocEngineer Jul 03 '15 at 09:13
`System.String[]EA0` but what does it mean~ @LocEngineer – Chloe Allison Jul 03 '15 at 09:48
It means that there is your Session("PO Qty") is the culprit. Another amendment then: `oSheet.Range("F" & ExcelCounter).Value = CType(Session("PO Qty"), String) & dreader.GetString(0) & (POTableCounter).ToString()` – LocEngineer Jul 03 '15 at 10:10

How do I display Data from Database in VBA Excel?

1 Answers1