Basic Oracle SQL Query

Hi all,
I am trying to do a basic sql statement:
"Select count(*) from users"
And I want to output this to a Textbox.

Here is my code:
        Dim con As New OleDb.OleDbConnection()
        Dim com As New OleDb.OleDbCommand()
        Dim da As New OleDb.OleDbDataAdapter()
        Dim ds As New DataSet()
        con = New OleDb.OleDbConnection("Provider=MSDAORA;Password=" + DBPassword + ";User ID=" + _
        DBUserName + ";Data Source=" + DBName)
        Dim x As Integer
        con.Open()
        com = New OleDb.OleDbCommand("", con)
        com.CommandText = "select count(*) from users"
        com.CommandType = CommandType.Text
        com.ExecuteNonQuery()
        da = New OleDb.OleDbDataAdapter(com)
        da.Fill(ds, "users")
        Dim dt As DataTable
        dt = ds.Tables("users")
        Dim dr As DataRow
        Dim ts As New DataGridTableStyle()
        ts.MappingName = ds.Tables(0).TableName
        For Each dr In dt.Rows
            frmMain.txtD3.Text = dr(0)
        Next
        con.Close()

I use this same type to fill a listview and it is fine, but here I get an error.
When I debug it, the line  "For each dr in dt.Rows....." is equal to Nothing.
Should I do this differently?
Please help!

Akula
LVL 1
AkulaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
One way to do it is to chage your sql

com.CommandText = "select * from users"


     frmMain.txtD3.Text = DS.Tables("users").Rows.Count      
0
AkulaAuthor Commented:
Ok...now it tells me this:
Object Reference not set to an object (or something of that nature)
When it gets to:
frmMain.txtD3.Text = DS.Tables("users").Rows.Count

To check to see what is happening I put :
Dim Temp as String
Temp = DS.Tables("users").Rows.Count

and then tried:
frmMain.txtD3.Text = Temp
Same error.

This is a module and I have done this:
Private frmMain as New frmMain  (this is a form in my project...obviously)

Any ideas?
Thanks,
Ak
0
planoczCommented:
to see if the dataset is being filled try this

frmMain.txtD3.Text = DS.Tables(0).Rows.Count
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

AkulaAuthor Commented:
That gave me the same error
0
planoczCommented:
put a break just after this line

 da.Fill(ds, "users")

and then highlight the ds and do a quick look and read to see of it is  getting a tables count of 1.

If so then you are making a good connection.
If not then you still have connection problems.
0
AkulaAuthor Commented:
I checked and I am getting a value listed there.
It says "15", but when I try and set the textbox value to it I get this error:

Object reference not set to an instance of an object.

Thanks,
Ak
0
slyckboyCommented:
if all you want to do is execute that count statement try this

com = new oledb.oledbcommand("Select count(*) from users",con)
dim intCount as integer = com.executescalar()
frmMain.txtD3.Text = intcount
0
slyckboyCommented:
let me be more clear.  This would replace everything, your code seems like a lot of overhead, unless there is something you are not showing us.

        Dim con As New OleDb.OleDbConnection()
        Dim com As New OleDb.OleDbCommand()
        con = New OleDb.OleDbConnection("Provider=MSDAORA;Password=" + DBPassword + ";User ID=" + _
        DBUserName + ";Data Source=" + DBName)
        Dim x As Integer
        con.Open()
        com = new oledb.oledbcommand("Select count(*) from users",con)
        dim intCount as integer = com.executescalar()
        frmMain.txtD3.Text = intcount
        con.Close()
0
AkulaAuthor Commented:
All,
This is all working out great except for the error below:

Object reference not set to an instance of an object.

This occurs at the line:
frmMain.txtD3.Text = incount

Both examples above are producing correct results, but I think my question has really moved to why I can't set the frmMain.txtD3.text to anything from inside this module.

Any ideas?
Thanks,
Ak
0
planoczCommented:
If you are not in frmMain you will get a "Object reference not set to an instance of an object."

0
AkulaAuthor Commented:
Hmm...
This is a module I created and frmMain is the "main form"
I declared the follwoing

Private frmMain as New frmMain

Shouldn't that "allow" me to do this?

Ak
0
planoczCommented:
change this to a global var

frmMain.txtD3.Text = incount

to

'MODULE

Public iCount as short

iCount = com.executescalar()
 
then in frmMain

frmMain.txtD3.Text = iCount
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.