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()

LocEngineer · Accepted Answer · 2015-07-01 08:21:10Z

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 1, 2015 at 8:21

LocEngineer

3,0071 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Chloe Allison Over a year ago

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

LocEngineer Over a year ago

Please post sample values for your Session and UOM. Is POTableCounter an int? Get rid of "" &.

LocEngineer Over a year ago

Ummm... Do the insertCommands have anything to do with the above code? What values do the Session variables hold? Also, according to stackoverflow.com/questions/4670247/… please always use the & operator to concatenate strings in VB.Net.

Chloe Allison Over a year ago

"EA" and "LOT" for most of them. They are text values @LocEngineer

LocEngineer Over a year ago

In that case try this:

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

|

Collectives™ on Stack Overflow

How do I display Data from Database in VBA Excel?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related