• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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
0
Akula
Asked:
Akula
  • 5
  • 5
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now