Increasing a numeric value within SQL database from VB.NET

[InsomniaCalls]

Hi there,

I have a numeric column in my SQL Express 2005 database intended to log the number of times someone has logged in to the database.

I'm trying to work out how to increase this value by one when someone logs into my (VB.NET) application.

I've got the code below set to run when they click login but it's blatantly rubbish so if anyone could help that'd be great!

Dim connString As String = "Server=" + ServerName + "\SQLEXPRESS;Database=login;UID=user;PWD=password"
Dim myConn As New SqlConnection(connString)
Dim commstr As String = "SELECT logins FROM usernames WHERE user_id = '" + RemUser + "'"
Dim mycommand As New SqlCommand(commstr, myConn)
myConn.Open()
Dim myReader As SqlDataReader = mycommand.ExecuteReader()
Dim value As Integer = CInt(myReader("logins"))
Dim value2 As Integer
value2 = (value + 1)
Dim mycommand2 As New SqlCommand("UPDATE usernames SET logins = '" + value2 + "' WHERE user_id = '" + RemUser + "'", myConn)
mycommand2.ExecuteNonQuery()
myconn.close()

TIA

Si

 

[~J~]

I don't see what's wrong with it!

Why make things over complicated?

The only thing I'd personally make different is to move the SELECT statement AND the UPDATE into a Stored Procedure.

You pass the UserID, and if the SELECT statement returns a record, then run the UPDATE statement.

All in one nice little SP that is called once by your program. Simple, effective, not complicated, does the job perfect.

 

[InsomniaCalls]

Thanks for that.

I'm not massively experienced with stored procedures - I know how to add one (and have done so) but not sure how to change my code above into a stored procedure.

Do you have any suggestions or maybe some recommended reading?

Thanks again,

Si

Edit: I'm pretty stumped as to why this code isn't making any changes in the database. Even if it isn't a SP it should still work right?

 

[~J~]

Here's how I'd personally do it.

Create a new stored procedure with a userID as a parameter.

IF exists (select logins FROM usernames where user_id = @userID)
BEGIN
UPDATE usernames
Set logins=logins+1
Where user_id=@userID
END