Finding the last row number in a data table

[Papa_Don]

Hi group,

I'm still learning Visual Basic and SQL. Please overlook my inabilities.

I'm writing a module that is meant to write a small amount of data to a database. I've named these fields 'PrinterID', 'PrinterName', 'PrinterNumber', 'PrinterLocation' and 'PrinterAddress'. The user will eventually have 30 or 40 records to add to this file.

My questions are: when writing to the file, how do I number the rows (this will be the PrinterID)? As the user adds to this, how do I find the last row number so that I can assign the next row value? And lastly, do these rows begin with '0' or '1'? I ask, because I assume '0'. But I'd rather it be 1!

In advance, thanks for the help.

Don

[pixelsoul]

You can just use COUNT() to get the total number of rows (which of course would be the last row number). My MSSQL server is at work so I can't look at it, and I am having a "duh" moment, but I believe the rows start at 0. You can't open it up and look? ;-)

[IIM]

@doncwilson_1: use autoincrement if that field is unique and increment by 1 everytime.take a look http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
if you still need to get last printerId then you this query

SELECT * FROM Printer ORDER BY PrinterID DESC LIMIT 1

order by will first give data in descending order of PrinterId and then LIMIT 1 will restrict to the last data

[IIM]

@pixelsoul-If we use count it will return number of records not the last Id.So if some record is deleted ,then it will return the wrong Id.

[pixelsoul]

@IIM - I totally misread his question (I'm tired) and didn't realize he was talking about inserting rows to a table. And like you said auto increment :)

[Papa_Don]

IIM,

I've been reading through the article you've suggested. One of my (many) issues (from my great lack of experience and knowledge) is that I don't yet understand the query language. Consequently, trying to "translate" this into Visual Basic is somewhat difficult. In this instance, the programmer is "Creat"ing a new table. I'm not doing this. I just want to write to the table this first time. And, for future row creations, I need to find the last row number and add '1' to that.

I do want to try to figure this out. So let me "think" outside here:

The example reads

id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

First, I have no idea what "ENGINE=MyISAM" is. Maybe you can explain.

I'm guessing that "id MEDIUMINT NOT NULL AUTO_INCREMENT" is creating the column "id" and that it is a "int" data type (albeit, I've not seen 'mediumint' as a data type). A "NULL" value is not allowed. This field is set to "auto-increment". Finally, "id" has the primary key. How am I doing so far?

Assuming I'm correct here, I still don't know how to use this.

I've purchased a book called "Transact-SQL Programming", by Kevin Klein, Lee Gould and Andrew Zanevsky. I've looked to see if "AUTO_INCREMENT" is covered. It's not. So I'm still at square one.

Now to use your suggestion to find the last row, I gather I should write my commands as this:

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        con.ConnectionString = "Data Source=DON-PC;Initial Catalog=DATADESIGNSOLUTIONS;Integrated Security = True;User ID=DON-PC;Password=be67011;"                    
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM Printer ORDER BY PrinterID DESC LIMIT 1"
        con.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader
    Do While rdr.Read()
        rownumber = rdr(0)
      Loop
    rdr.Close()
    con.Close()

What do you think?

Thanks for your help.

Don

[Papa_Don]

I've written the following line:

cmd.CommandText = "SELECT * FROM PRINTERMAINT ORDER BY PrinterID DESC LIMIT 1"

I'm getting a syntax error that says "Incorrect syntax near 'LIMIT'". It doesn't say what it's looking for. any idea what the correct syntax should be? I'm using Visual Studio (Visual Basic) 2010 Express.

Thanks.

Don

[pixelsoul]

If you're using MSSQL rather than MySQL, MSSQL does not support LIMIT.

You would probably need to change it to:

SELECT TOP 1 * FROM PRINTERMAINT ORDER BY PrinterID DESC

If for some reason that doesn't work, it may need a () around the number like SELECT TOP(1)

[pixelsoul]

Also just wanted to put this here http://msdn.microsoft.com/en-us/library/ms189463.aspx
I use this resource all the time at work.

My questions are: when writing to the file, how do I number the rows (this will be the PrinterID)? As the user adds to this, how do I find the last row number so that I can assign the next row value? And lastly, do these rows begin with '0' or '1'? I ask, because I assume '0'. But I'd rather it be 1!

I not very familiar with VB.net I know ASP.net & C# a little.

This is how your DataTable in your database will look like:

 ID |  PrinterID | PrinterName | PrinterNumber | PrinterLocation | PrinterAddress | Date
 ---------------------------------------------------------------------------------------
  1 |      12    |     HP      |      123      |     Home        | 123.123.132    | 
  2 |      13    |    Acer     |      345      |    Office       | 124.128.132    |
  3 |      14    |   Brother   |      567      |     Room        | 125.122.132    |

This is your DataTable code:

Module Module1

Sub Main()
' Get a DataTable instance from helper function.
Dim table As DataTable = GetTable()
End Sub
''' <summary>
''' Helper function that creates new DataTable.
''' </summary>
Function GetTable() As DataTable
' Create new DataTable instance.
Dim table As New DataTable
' Create 7 typed columns in the DataTable.
table.Columns.Add("ID", GetType(Integer))
table.Columns.Add("PrinterID", GetType(String))
table.Columns.Add("PrinterName", GetType(String))
table.Columns.Add("PrinterNumber", GetType(String))
table.Columns.Add("PrinterLocation ", GetType(String))
table.Columns.Add("PrinterAddress", GetType(String))
table.Columns.Add("Date", GetType(DateTime))
' Add 3 rows with those columns filled in the DataTable.
table.Rows.Add(12, "HP", 123, "Home", 123.123.132, DateTime.Now)
table.Rows.Add(13, "Acer", 345, "Office", 124.128.132, DateTime.Now)
table.Rows.Add(14, "Brother", 567, "Room", 125.122.132, DateTime.Now)
Return table
End Function
End Module

I assume your database is connected and your query should look like this:

cmd.CommandText = "SELECT TOP 1 * from Printer Order By PrinterID DESC"

That will get the last row of PrinterID which is 14

I hope this will help you get a image how this works.

[Papa_Don]

For what it's worth, I'm trying to write this now in SQL Server Management 2010 Express. In using the suggestions, I've written it this way"

SELECT TOP 1 *
FROM PRINTERMAINT
ORDER BY 1 DESC

It doesn't like "PRINTERMAINT" (the table name). I am connected to the database, DATADESIGNSOLUTIONS. What in the world is going on here?

[Papa_Don]

Group, it appears that I'm writing to the database. However I'm unable to read the data in the listview I've created. Maybe you can see where my commands are failing. Here's what I'm writing:

con.ConnectionString = ("Data Source=DON-PC;Initial Catalog=DataDesignSolutions;Integrated Security=True;User ID=DON-PC;Password=be67011;")
        Dim command As New SqlCommand("", con)
        command.CommandText = "SELECT * FROM PRINTERMAINT"
        lvPrinterSetup.Items.Clear()
        con.Open()
        rdr = cmd.ExecuteReader
        Do While rdr.Read()
            lvPrinterSetup.Items.Add(New ListViewItem({rdr(0), rdr(1), rdr(2), rdr(3), rdr(4)}))
        Loop
        rdr.Close()
        con.Close()

Can anyone see why my code doesn't seem to be writing to the Listview box?

If you need me, I'll be at the bar shaking and drinking heavily...... (lol!)

Again, thanks greatly for the help. This has been an awesome learning experience.

Don

[glennt]

I thought I would jump in here. If I have missed something and one of my questions has already been addressed, let me know.

Why do you need to know a row number? If the main reason is to have a unique identifier for each row, I would change the table so that the PrinterID column is an auto-incrementing column. If setup this way, SQL will maintain this value for you. As was already stated, using count is not a good way to go because records could be deleted and the count value + 1 could already exist in the PrinterID column if you are maintaining this value on your own. An auto-incrementing column can start at any number you want (including a negative number), and can increment by any value you want. Also, when you do an insert into the table, you would exclude the PrinterID.

Will there ever be a chance that more then one person will be adding data at the same time? If so, you have another issue to deal with and using the auto-incrementing column will solve this.

If you need to know what ID value was created when adding a record to the table, the best way to do this is via a stored procedure, which can return this value back to your program.

With some more information, I can provide you with some specifics.

[Reverend Jim]

Without getting into all the other stuff, if you want to know the highest number in a column you can use the MAX function as in

SELECT MAX(PrinterID) FROM ...

[Papa_Don]

GlennT,

I would prefer the auto increment setup within the data table. To do this, is this where I need to open the table in SQL Management and change that specific field to "(Is Identify)" to "Yes"?

Reverend Jim,

At the end of the day, I'd prefer do what is the best way to do it. If "SELECT MAX" is it, I'm all for it. What are the pros and cons?

Don

[glennt]

Yes, changing the column value 'Is Identity' to yes is correct. Below that is where you can change the starting number (Identity Seed) and the increment number. Remember that you will no longer insert into this column when you make this change. Also, if you ever remove records, any identity number that was created will not be reused, so the numbers will not always be sequential.

I asked a couple of other questions that you did not respond to, so take a look at them and answer them if you think that they might pertain to what you are doing.

[Papa_Don]

GlennT,

I thought that the column number would change. I had forgotten that changing the "Is Identity" would effect that. I did not know to change the "Identity Seed" to effect the starting number.

I doubt that more than one "administrator" will make alterations to this file. But I do not like to take chances.

The only time I'm going to need the Identity ID value is when any changes are to be made. But I've written in for this to happen.

Now that I know all of this, I'm going to dump the idea of creating a ID number and allow the system to take care of this.

Thanks to all for the assistance!

Don