Loop through rows, or build an array?

[uberamd]

Alright, I have stated in a previous post that my Visual Basic .NET book does not cover databases in depth at all. So I have been using google to get my answers, however cant find a very simple answer: How do I loop through a database, grab a value, and use it?

For example I want to grab a value from a row, and use a equation to manipulate it.

In PHP, I would do it similar to this:

            <?php 
            $result = mysql_query("SELECT x, y FROM locations") or die(mysql_error());
            $row_count = mysql_num_rows( $result );
            $i == 0;
            while($i < $row_count)
            {
                $row = mysql_fetch_array( $result );
                $newx = (800/16400)* $row['x'] + 400;
                $newy = (815/16400)* $row[y'] + 407.5;
                // METHOD HERE TO DRAW CIRCLE
                $i++;
            } 
            ?>

Thats not exactly how it would be modeled in PHP, but a general idea. Basicly I need my query which is SELECT x, y FROM account to loop through each row, take x from the database and manipulate it like so: (800/16400)*x + 400 and then set that as x1, then take the y that is in the same row, and manipulate it like so: (815/16400)*y + 407.5 and then set that as y1, then run this to plot the point:

 Protected Overrides Sub OnPaint(ByVal pe As PaintEventArgs)
        Dim g As Graphics = pe.Graphics
        Dim fnt As New Font("Verdana", 10)
        g.DrawString("First Last", fnt, New SolidBrush(Color.Red), x+6, x-5)
        Dim pn As New Pen(Color.Red, 5)
        Dim rect As New Rectangle(x, y, 5, 5)
        g.DrawEllipse(pn, rect)
    End Sub 'OnPaint

Im sorry if this is hard to understand I will summarize:

A) How do I loop through rows in a mysql database query
B) I need to take the value of each row and run the equation's above on the x and y value
C) I need to run the above graphics sub to draw the point
D) Loop and do the same with the next point

This probably is VERY easy, I just need some help getting on track.

 

[cjard]

A) How do I loop through rows in a mysql database query

You use a DataReader. Every time you call .Next() on the reader, it advances it by one row

B) I need to take the value of each row and run the equation's above on the x and y value

Rows dont really have a value, in kinda the same way that cars dont have a wheel.. BUt i know what you mean. In vb its not so stunningly different to your PHP:

newx = reader.GetInt32(0) 'column zero holds the x value

C) I need to run the above graphics sub to draw the point

So, call it with the relevant parameters..

D) Loop and do the same with the next point

The syntax is:

While Not reader.Next()
...
End While

This probably is VERY easy, I just need some help getting on track.

The db reader is the hardest bit, definitely!

 

[uberamd]

Thank you very much for the answer, it was quite helpful!

Say my query were like:

SELECT account_id FROM account WHERE username='" & Convert.ToString(txtUname.Text) & "' AND password='" & Convert.ToString(txtPass.Text) & "'"

and I wanted to check to see if a value was returned (to see if an account exists, if no account_id is returned, account doesnt exist and error out), would it look like:

If myReader.GetString(0) = "" Then
            'Access denied code goes here'
        Else
            'Accepted code goes here
        End If

Is that proper syntax?

And for my paint sub, how would I pass in the x and y values?

 

[cjard]

Convert.ToString(txtUname.Text)

Use of convert here is 100% pointless because .Text is already a string. Also, the preferred way to convert something to a string is to call .ToString() on it



We dont really use string concatentation to build queries any more, because its not really that safe or type secure.. I could break into your system by typing the following into both fields:
a' OR 'a'='a

For a much easier, safer and better engineered way to do data access, take a look at the DW2 link in my signature...