ExecuteNonQuery Wierdness

9 Jun 2006 12:18 AM

bug0926

The following code runs and gets no errors...the select returns no
rows, what am I doing wrong? I instantiate the class, run the write a
few teams, then the read, and in vb.net I still see no data in the log
table.

Public Class cLog

    Enum eventType As Integer
        Ph = 1
        Temperature = 2
        ORP = 3
        Timer = 4
        Misc = 5
    End Enum

    Private dbConn As SqlClient.SqlConnection
    Private dbCommand As SqlClient.SqlCommand

    Public Sub New()

        dbConn = New
SqlClient.SqlConnection(My.Settings.DBConnectionString)
        dbConn.Open()

    End Sub

    Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer,
ByVal eventValue As String)
        Dim sql As String

        sql = "insert into Log (sensor, eventID, eventValue) values ("
& sensor & "," & eventId & ",'" & eventValue & "')"

        dbCommand = dbConn.CreateCommand()
        dbCommand.CommandText = sql
        dbCommand.ExecuteNonQuery()

    End Sub

    Public Sub read()
        Dim sql As String
        Dim dbReader As SqlClient.SqlDataReader

        sql = "select timestamp, sensor, eventId, eventValue from Log"
        dbCommand = dbConn.CreateCommand()
        dbCommand.CommandText = sql

        dbReader = dbCommand.ExecuteReader(sql)

        While dbReader.HasRows
            Debug.Print(dbReader(0))
            Debug.Print(dbReader(1))

        End While
        dbReader.Close()
    End Sub

End Class

9 Jun 2006 12:31 AM

Kerry Moorman

bug0926,

While dbReader.Read( )

Kerry Moorman

Show quoteHide quote

"bug0***@yahoo.com" wrote:

> The following code runs and gets no errors...the select returns no
> rows, what am I doing wrong? I instantiate the class, run the write a
> few teams, then the read, and in vb.net I still see no data in the log
> table.
>
> Public Class cLog
>
>     Enum eventType As Integer
>         Ph = 1
>         Temperature = 2
>         ORP = 3
>         Timer = 4
>         Misc = 5
>     End Enum
>
>     Private dbConn As SqlClient.SqlConnection
>     Private dbCommand As SqlClient.SqlCommand
>
>     Public Sub New()
>
>         dbConn = New
> SqlClient.SqlConnection(My.Settings.DBConnectionString)
>         dbConn.Open()
>
>     End Sub
>
>     Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer,
> ByVal eventValue As String)
>         Dim sql As String
>
>         sql = "insert into Log (sensor, eventID, eventValue) values ("
> & sensor & "," & eventId & ",'" & eventValue & "')"
>
>         dbCommand = dbConn.CreateCommand()
>         dbCommand.CommandText = sql
>         dbCommand.ExecuteNonQuery()
>
>     End Sub
>
>     Public Sub read()
>         Dim sql As String
>         Dim dbReader As SqlClient.SqlDataReader
>
>         sql = "select timestamp, sensor, eventId, eventValue from Log"
>         dbCommand = dbConn.CreateCommand()
>         dbCommand.CommandText = sql
>
>         dbReader = dbCommand.ExecuteReader(sql)
>
>         While dbReader.HasRows
>             Debug.Print(dbReader(0))
>             Debug.Print(dbReader(1))
>
>         End While
>         dbReader.Close()
>     End Sub
>
> End Class
>
>

9 Jun 2006 12:38 AM

GhostInAK

Hello bug0***@yahoo.com,

The last part of your code is a lil screwy. Try this:

Public Sub Read()

    Dim tSQL As String = "SELECT timestamp, sensor, eventId, eventValue FROM
Log"
    Dim tConnection As SqlConnection = New SqlConnection
    Dim tCommand As SqlCommand = New SqlCommand
    Dim tReader As SqlDataReader = Nothing

    With tCommand
        .Connection = tConnection
        .COmmandType = CommandType.Text
        .CommandText = tSQL
    End With
    tConnection.Open
    tReader = tCommand.ExecuteReader
    If tReader.HasRows Then
        While tReader.Read
            Debug.Print(String.Format("{0}, {1}, {2}, {3}", tReader("timestamp"),
tReader("sensor"), tReader("eventId"), tReader("eventValue")))
        End While
    End If
    tConnection.Close

End Sub

Show quoteHide quote

> Public Sub read()
> Dim sql As String
> Dim dbReader As SqlClient.SqlDataReader
> sql = "select timestamp, sensor, eventId, eventValue from Log"
> dbCommand = dbConn.CreateCommand()
> dbCommand.CommandText = sql
> dbReader = dbCommand.ExecuteReader(sql)
>
> While dbReader.HasRows
> Debug.Print(dbReader(0))
> Debug.Print(dbReader(1))
> End While
> dbReader.Close()
> End Sub