SQL Query - rarely issue

[Frehley]

Hi! What I am trying to do is to send and recive messages throuh users.
The sending part works oks, but I am having troubles reciving em.

I want a label to display "Hi, you Have X new messages". The way I have to know that it is a new messaje is the field M_State, when its > 0 then its new.

Here is my code

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("User") = "" Then
            Server.Transfer("login2.aspx")
        Else
            Label1.Text = "Bienvenido, " & Session("User") & "!"
            Dim conns As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn"))
            Dim consnew As String = "SELECT COUNT (M_State) as M_Aux FROM tblMessa WHERE " & _
            "M_State > 0 and M_To = " & "'" & Session("User") & "'"
            Dim consnew2 As String = "SELECT tblMessa.M_Aux FROM tblMessa"
            Dim cmd As New SqlCommand(consnew, conns)
            Dim cmd2 As New SqlCommand(consnew2, conns)
            'OPEN CONNECTION
            If conns.State = ConnectionState.Closed Then
                ' IF DB IS NOT OPEN, THEN OPEN IT
                conns.Open()
                cmd.ExecuteNonQuery()
                cmd2.ExecuteNonQuery()

                HyperLink1.Text = "You have " & cmd2.ExecuteNonQuery & " new messages"
                'CLOSE THE CONNECTION
                conns.Close()

            End If
        End If
    End Sub

Its a SQL Server 2000 connection, thats working fine, the only problem is that when I execute it sayd that the M_Aux field is not valid.

Thanks!

[Paladine]

The answer is this; You have created the column (M_Aux) in the first select statement as your AS parament, but you then ask to to Select into it in the second select statement ( consnew2 variable).

Not possible, as the second statment can not see the first statement.

The M_Aux is not a valid field, unless it exists in the table already, and if it does, the SELECT COUNT statment doesn't write to the Database. Which you don't want to do in this case anyway...as it would dynamically change over time.

I would have recommended a

SELECT Count(M_State) as NewMessages
FROM tblMessa
WHERE M_State > 0 and M_To = " & "'" & Session("User") & "'"

Which would cause far less confusion, and only require one command variable.

Hope this helps

:cool: Remember, walk through the logic, line by line of code and use actual values and you will quickly see if your logic is sound or not.

Breakpoints are our friend! :cool:

[Frehley]

Thanks for the answer, really. But now I have one more thing... Why it keeps saying that I have -1 new messages?

[Frehley]

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("User") = "" Then
            Server.Transfer("login2.aspx")
        Else
            Label1.Text = "Bienvenido, " & Session("User") & "!"
            Dim conns As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn"))

            Dim consnew As String = _
            "SELECT Count (M_State) as M_Aux " & _
            "FROM tblMessa " & _
            "WHERE M_State > 0 And M_To = " & "'" & Session("User") & "'"

            Dim cmd As New SqlCommand(consnew, conns)

            '  OPEN CONNECTION
            If conns.State = ConnectionState.Closed Then
                ' OPEN DATABASE
                conns.Open()
                cmd.ExecuteNonQuery()
                '''''''''
                Dim DataAdapter As SqlDataAdapter
                Dim DataSet1 As DataSet
                DataAdapter = New SqlDataAdapter(consnew, conns)
                DataSet1 = New DataSet()
                DataAdapter.Fill(DataSet1, "tblMessa")
                GridView1.DataSource = DataSet1
                GridView1.DataBind()
                '''''''''
                HyperLink1.Text = "You have " & WHAT HERE?? & " new messages"
                'CLOSE CONNECTION
                conns.Close()
            End If
        End If
    End Sub

The Gridview1 shows what I want (M_Aux value), but I cant make the label to show the M_Aux value. I hope some1 can help me, I am new in ASP.

Thanks!

[Paladine]

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("User") = "" Then
            Server.Transfer("login2.aspx")
        Else
            Label1.Text = "Bienvenido, " & Session("User") & "!"
            Dim conns As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn"))

            Dim consnew As String = _
            "SELECT Count (M_State) as M_Aux " & _
            "FROM tblMessa " & _
            "WHERE M_State > 0 And M_To = " & "'" & Session("User") & "'"

            Dim cmd As New SqlCommand(consnew, conns)

What needs to be added:

'   |||||||||||||||||||||||||||||
            '    |||||    PALADINE"S CODE |||||
            '   |||||||||||||||||||||||||||||
            Dim iCount As Integer
            ' OPEN CONNECTION
            If conns.State = ConnectionState.Closed Then
                ' OPEN DATABASE
                conns.Open()
                iCount = CInt(cmd.ExecuteScalar())
                If iCount > 0 Then
                    HyperLink1.Text = "You have " & iCount & " new messages"
                Else
                    HyperLink1.txt = "You have no new messages"
                End If
            End If

What needs to be removed is commented out:

'   |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
            'cmd.ExecuteNonQuery()  '    ******** INCORRECCT THIS IS A QUERY YOU ARE RUNNING|||||||||||||||
            '   ||||    *** Hence the SELECT statement you have in your command text  ***   |||||||||||||||
            '   ||||    ExecuteNonQuery is for a Non-query action; i.e. DDL, not DML (SQL language types)||
            '   |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
            'Dim DataAdapter As SqlDataAdapter
            'Dim DataSet1 As DataSet
            'DataAdapter = New SqlDataAdapter(consnew, conns)
            'DataSet1 = New DataSet
            'DataAdapter.Fill(DataSet1, "tblMessa")
            'GridView1.DataSource = DataSet1
            'GridView1.DataBind()
            '''''''''
            '     HyperLink1.Text = "You have " & WHAT HERE?? & " new messages"
            'CLOSE CONNECTION

            conns.Close()
        End If

Hope this helps.

Please, goto http://msdn.microsoft.com/library/ look up each command in the above code and learn what it is doing.

The best advice I can give yourself, and all others who are out there hvaing problems with coding is this. Stop what you are doing, and go line by line, yes ....line by line of your code that is causing you problems and just make comments (like I did above) and state what each line is doing or what you think it is suppose to do. TRUST ME.... you will very very quickly learn what the problem is and what you may have missed.

Hope this helps.... Happy Coding

:cool:

[Frehley]

[IMG]http://img.photobucket.com/albums/v610/sod123/thanks.jpg[/IMG]