Accessing one Cell of Data in a Database

[Farmernick]

Hey,
I am new to VB .net but not to programming. But with the book I got it does not go into much about Databases. It only shows how to change stuff through the graphical Data Grid. I need to access a certain cell in the database(taking certain ones that are for a certain person and then adding them up to find out what the full amount is). I need to know how to access a certain cell and then I can start using loops to go through and check to find out if it is the same person and then go and get the amount and add it to the other amount. If you can tell me what to do to access a certain cell in a database or datagrid please help me.

Thanks so mcuh!!

Nicholas

[LeeSalter]

Might be easier with a SQL statement like this:-

"SELECT * FROM TABLENAME WHERE FIELDNAME='PersonName'"

[Farmernick]

Can you use SQL statements inside the VB .net program?

[LeeSalter]

You certainly can me ode China.

Might be worth reading up on DataReaders....
I'll post some code in a sec from one of my DB apps.....

[Farmernick]

What is DataReaders??

Also Do you know a good book that deals with VB .net and Databases where you are able to read data in to VB .net and then use if for all types of purposes.

Thanks for all of you help.
Nicholas

[salvelinus]

Murach's VB.NET Database Programming is a good learning book. Explanations are on the left page, code examples on the right.

[LeeSalter]

OK, this is the function that returns a DataReader:-

    Public Function DBDataReader(ByVal Table As String, Optional ByVal SortOption As String = Nothing, _
    Optional ByVal FilterOption As String = Nothing) As OleDbDataReader

        Dim cmd1 As New OleDbCommand
        cmd1.Connection = cnn1 'cnn1 is a global variable to the     OleDbConnection object
        cmd1.CommandText = _
            "SELECT * FROM " & Table

        If SortOption <> Nothing And FilterOption = Nothing Then
            cmd1.CommandText += " " & SortOption
        End If

        If SortOption <> Nothing And FilterOption <> Nothing Then
            cmd1.CommandText += " " & FilterOption & " " & SortOption
        End If

        If SortOption = Nothing And FilterOption <> Nothing Then
            cmd1.CommandText += " " & FilterOption
        End If

        'Create an instance of a data reader for the result set 
        'returned by cmd1

        Dim drd1 As OleDbDataReader
        Try
            drd1 = cmd1.ExecuteReader
        Catch
            MessageBox.Show("There was a problem opening the database." & _
                vbCrLf & vbCrLf & Err.Description, _
                "Error Encountered", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Return drd1

    End Function

You can call this function with this code:-

dim dr as new OleDbDataReader

dr=DBDataReader(Table,"ORDER BY DESC","WHERE FieldName='PersonName'")

You can then iterate through the DataReader like this:-

Dim Count as integer
Do While dr.Read
     Count+=1
Loop

messagebox.show(Count & " records returned.")

I realise this may be messy and stuff, but that's howI do it and it works for me.

Have fun.

[LeeSalter]

Originally posted by Farmernick
What is DataReaders??

Also Do you know a good book that deals with VB .net and Databases where you are able to read data in to VB .net and then use if for all types of purposes.

Thanks for all of you help.
Nicholas

I use this one. It's done me no harm and taught me all I needed to know.

[Farmernick]

Hey,
Thanks I will work with this.

Is there not a way that you can just call cells by the number that they are like tablename(0,0) or tablename(12,3) or anything like that????

THanks,
Nicholas

[salvelinus]

You might be thinking arrays or spreadsheets, not databases.

[Farmernick]

Hey,
I am trying to access a Database (access or sql). I have to take the entries that are in it check to see who they belong too and then all the entries for a certain person add up all the numbers in the amount column.

Thanks,
Nicholas

[salvelinus]

To retrieve data from a database, use SQL. I'm not aware of any db that treats its row/column intersections as numbered cells. In fact, I don't know of any db that promises that a particular record value will be found at a particular "cell". Each record (i.e. row) should be unique, at least in a decently designed table, but you never know if it will be the first row, second row, nth row, etc. So although you may know the column # of the field you want, you won't know its row.

[brown monkey]

hello salvz. it can be accessed. some sort of a matrix. table.row(row_index)(column_index). sample

Dim cn As New SqlConnection()
   Dim da As New SqlDataAdapter()
   Dim dt As New DataTable("territories")
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      cn.ConnectionString = "user id=sa;password=password;initial catalog=northwind"
      cn.Open()
      da.SelectCommand = New SqlCommand("select * from territories", cn)
      da.Fill(dt)
 
      outMatrix0401()
   End Sub
 
   Sub outMatrix0401()
      MessageBox.Show(dt.Rows(4)(1))
   End Sub

hope i make sense...

[salvelinus]

Well, maybe, never heard of that, but my point is that you don't know what record will be in row 4 (or is it 1, I forget), so it doesn't help much to access it that way. Even if you do an ORDER BY in your SQL, you'd have to know exactly what that ORDER will sort out. It's not like Excel, where you can refer to cell C5, and know that row 5 always contains, say, June net sales.

[brown monkey]

yep. hehe. accessing a i dont know record is such a nonsense. hehe. cheers mate.

[Farmernick]

Hey,
I know that way is kind of a bad way to access the cells. I was going to try to put it in some type of loop and have the cell numbers loop.

But I will use SQL as it will most likely be easier.

Now how do I make it so VB.net will know I am entering in an SQL statement. The table is called "payRate"

Thanks,
Nicholas