Simple problem with regards using integers in SQL Databases

[Smurfsdabomb]

What I want to do is retrieve data from my database using an SQL query (which I can do perfectly) and then manipulate it. Specifically I've got a unique identifier field and I want to get the highest previous entry and increment it by one with this SQL statement

        NextID = "Select ID from League where ID = (select max(ID) from League);"
        NextID = NextID + 1


        aQuery = "INSERT INTO League"
        aQuery = aQuery & " VALUES(" & NextID & ",'" & LeagueOwner & "','" & LeagueName & "','" & LeagueDescription & "')"
        aCommand = New Data.OleDb.OleDbCommand(aQuery, aConnection1)
        aCommand.ExecuteNonQuery()
        aConnection1.Close()

And no problems with the SQL as it is, the problem is with the first line in the code as NextID is an integer being assigned to a string, and it needs to be an integer so I can increment it.

Is there any way to convert a string to an integer or alternatively to return an integer from an SQL statement?

Thanks!

 

[Robert_Zenz]

Hello.

I have a little question for you...what does this do?

NextID = "Select ID from League where ID = (select max(ID) from League);"

Exactly, it assignes a String to the variable NextID.

NextID = NextID + 1

And yet you're trying to increment a String by one...

The long short, you're missing to execute the SQL-Statement at all!

Bobby

 

[Smurfsdabomb]

I knew that but how could I return the result of that SQL statement as an integer?

Or should I be executing it first then assigning the variable to it?

 

[Smurfsdabomb]

idQuery = "Select ID from League where ID = (select max(ID) from League);"
        idCommand = New Data.OleDb.OleDbCommand(idQuery, aConnection1)
        idCommand.ExecuteNonQuery()
        NextID = ????

Now it executes the query but whats the syntax after that. I'm used to binding it to a datagrid at this point but I'm sure how to assign it to a variable.

 

[Robert_Zenz]

You'll have to use ExecuteScalar(). See the MSDN for further Informations on the differences of ExecuteScalar(), ExecuteReader() and ExecuteNonQuery().

NextID = CInt(idCommand.ExecuteScalar()) + 1

 

[Smurfsdabomb]

Thanks a lot, that worked perfectly. =)

 

[cjard]

if youre using sqlserver you should be using IDENTITY column, not performing a SELECT MAX(id) + 1 to establish an autoincrementing number