ODBC Connection unable to write to MSSQL 7 DB

S

starwiz

Member
Joined
Jul 2, 2003
Messages
21
I have a VB class that reads and writes from a database via ODBC. The connection and command objects are private members of the class (youll see all this in my code below). Everything worked for a period of time, but now, a few things are happening:

1) Whenever I try to write out to the database, the command is not executed. I get no error, but no update is written out to my DB (MSSQL 7).

2) In my code (below), I test to see if my connection is open. When debugging my code, there are cases when, according to the watch window, the connection is open, but my if statement testing to see if the connections state doesnt equal open says that the connections state is, in fact, something other than open. Weird...

Like I said, everything was working fine until yesterday at 8:07 PM...no idea what the problem is.

Heres my code...if anyone can assist me with this, Id be greatly appreciative.
Code: 
Public Class Referrals

...

  Private myConn As Odbc.OdbcConnection
  Private myCmd As Odbc.OdbcCommand

...

  Private Sub InitializeConnection()
    If myConn Is Nothing Then
      myConn = New Odbc.OdbcConnection(ConStr)
      myConn.Open()
      myCmd = New Odbc.OdbcCommand
      myCmd.Connection = myConn
    End If
    ConnectedToDB = True
  End Sub

  Public Property ConnectedToDB() As Boolean
    Get
      If myConn.State = System.Data.ConnectionState.Open Then
        Return True
      Else
        Return False
      End If
    End Get
    Set(ByVal Value As Boolean)
      If Value = True Then
        heres where it says its not open, even though the debug watch says it is
        If myConn.State <> System.Data.ConnectionState.Open Then myConn.Open()
      Else
        If myConn.State <> System.Data.ConnectionState.Closed Then myConn.Close()
      End If
    End Set
  End Property

  heres one of the functions that tries to write to the DB but does nothing

  Private Sub AdvanceNextHitID()
    write out the new Hit
    InitializeConnection()

    myCmd.CommandText = "Update internalnums set tophitid = " & CStr(NextHitID)
    myCmd.ExecuteNonQuery()
  End Sub

  heres another one that doesnt work

  Public Sub WriteToDB(ByRef Info As SessionInfo)
    InitializeConnection()

    Dim CmdStr As New System.Text.StringBuilder

    Well write out the TimeLeft in a different function
    With CmdStr
      .Append("Insert into hits (")
      .Append(DBKeywordName)
      .Append(", ")
      .Append(DBSearchEngineName)
      .Append(", ")
      .Append(DBReferringURLName)
      .Append(", ")
      .Append(DBPageVisitedName)
      .Append(", ")
      .Append(DBReferringWebsiteName)
      .Append(", ")
      .Append(DBTimeArrivedName)
      .Append(", ")
      .Append(DBHitIdName)
      .Append(") values(")
      .Append(Info.Keyword)
      .Append(", ")
      .Append(Info.SearchEngine)
      .Append(", ")
      .Append(Info.ReferringURL)
      .Append(", ")
      .Append(Info.PageVisited)
      .Append(", ")
      .Append(Info.ReferringWebsite)
      .Append(", ")
      .Append(Info.TimeArrived.ToShortDateString)
      .Append(" ")
      .Append(Info.TimeArrived.ToShortTimeString)
      .Append(", ")
      .Append(Info.Id)
      .Append(")")
    End With

    myCmd.CommandText = CmdStr.ToString

    myCmd.ExecuteNonQuery()

  End Sub
End Class

Whats wrong? Is there anything wrong? Im really stuck here...

Thanks a lot for your help,
-Starwiz
 
Back
Top