This is my code:
1If Session("ctr") = False Then23 Connect()45 SQL = "SELECT * FROM counter"6 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"7 dbRead()89 If dbReader.HasRows = True Then1011 dbReader.Read()12 hits = dbReader("hits")13 hits = hits + 114 dbClose()1516 SQL = "UPDATE counter SET hits=" & hits17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"18 dbExecute()1920 Else2122 SQL = "INSERT INTO counter(ipaddress,hits)"23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"24 dbExecute()2526 End If2728 Session("ctr") = True2930 End If1Public Sub Connect()2 Conn = New SqlConnection("Initial Catalog=NURSETEST;User Id=sa;Password=sa;Data Source=KSNCRUZ")3 If Conn.State = ConnectionState.Open Then4 Conn.Close()5 End If6 Conn.Open()7 End Sub89 Public Sub Close()10 Conn.Close()11 Conn = Nothing12 End Sub1314 Public Sub dbExecute()15 dbCommand = New SqlCommand(SQL, Conn)16 dbCommand.ExecuteNonQuery()17 End Sub1819 Public Sub dbRead()20 dbCommand = New SqlCommand(SQL, Conn)21 dbReader = dbCommand.ExecuteReader22 End Sub2324 Public Sub dbClose()25 SQL = ""26 dbReader.Close()27 End Sub
You should always call the Close method when you have finished using theDataReader object. and also set dbReader Object = nothing and re intialized the object on next use
1 If Session("ctr") = False Then
2
3 Connect()
4
16 SQL = "UPDATE counter SET hits=hits+1"
17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
SQL = SQL & " IF @.@.Rowcount=0"
22 SQL =SQL & " INSERT INTO counter(ipaddress,hits)"
23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"
24 dbExecute()
25 Close()
27
28 Session("ctr") = True
29
30 End If
