Using cmd.ExecuteScalar() and showing 3 Selects

If i use
cmd.CommandText = "Select TOP 1 LastPoster,title,lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()

Open in new window

how would i displayit so it looked like this

LastPoster& "Posted"& title &"On the"& lastime

Open in new window

runnerjp2005Asked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
Then LastPost is null. Is it a label? Make sure it is not null.
0
 
Asim NazirCommented:
Either you can use OutParameters or you can use DataReader.
0
 
runnerjp2005Author Commented:
how would i use them though?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Asim NazirCommented:
0
 
runnerjp2005Author Commented:
my full code is:

Public Class Class1

    Public Structure Results
        Dim Result1 As Integer
        Dim Result2 As Integer
        Dim Result3 As String
    End Structure

    Shared Function noposts(ByVal forumname As String) As Results
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try
            con.Open()
            cmd.CommandText = "Select Count(*) From forum where Forum =@forumname"
            Dim Res As Results
            Res.Result1 = cmd.ExecuteScalar()
            cmd.CommandText = "Select Count(*) From forum where Forum =@forumname and LastPoster <> '' "
            Res.Result2 = cmd.ExecuteScalar()
            cmd.CommandText = "Select TOP 1 LastPoster,title,lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()
            Return (Res)
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function

End Class

Open in new window

0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,

ExecuteScalar will only return you first row first column (single cell), so eith get DataSet or DataReader to get all columns,

alternatively use following

cmd.CommandText = "Select TOP 1 LastPoster & "" Posted "" & title & "" On the "" & lastime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"

Res.Result3 = cmd.ExecuteScalar()

thanks
0
 
DhaestCommented:
You can't use the executeScalar for that
Dim myCMD As SqlCommand = New SqlCommand(""Select TOP 1 LastPoster,title,lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC", nwindConn)

nwindConn.Open()

Dim myReader As SqlDataReader = myCMD.ExecuteReader()

Dim fNextResult As Boolean = True
  Do While myReader.Read()
      Console.WriteLine(vbTab & myReader.GetName(0) & " posted " & myReader.GetName(1) & " on the " & myReader.GetName(2));
  Loop

myReader.Close()
nwindConn.Close()

Open in new window

0
 
Pratima PharandeCommented:
      cmd.CommandText = "Select TOP 1 LastPoster + " Posted " + title + " On the ",lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()
0
 
runnerjp2005Author Commented:
I tried

  cmd.CommandText = "Select TOP 1 LastPoster & title & lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = Res.Result2 = cmd.ExecuteScalar()

Open in new window


But i get the error

Conversion from string "tedtest 2 forum03/12/2011 07:50:" to type 'Double' is not valid.
0
 
Pratima PharandeCommented:
cmd.CommandText = "Select TOP 1 LastPoster + ' Posted ' + title + ' On the ',lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()
0
 
CodeCruiserCommented:
Make sure that Result3 is of type string then

cmd.CommandText = "Select TOP 1 LastPoster + '|' + title + '|' + lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()



Res.Result3.Split("|")(0) & " Posted " & Res.Result3.Split("|")(1) & " On the " & Res.Result3.Split("|")(2)
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Please use following

cmd.CommandText = "Select TOP 1 LastPoster & "" Posted "" & title & "" On the "" & lastime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"


or

cmd.CommandText = "Select TOP 1 LastPoster + "" Posted "" + title + "" On the "" & lastime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"

0
 
runnerjp2005Author Commented:
with

cmd.CommandText = "Select TOP 1 LastPoster + '|' + title + '|' + lasttime from forum where Forum =@forumname AND lasttime <> NULL order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()

i get Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Data type mismatch in criteria expression.

Result 3 is string
0
 
runnerjp2005Author Commented:
Is it because lasttime is within a date/time field??

0
 
CodeCruiserCommented:
>Is it because lasttime is within a date/time field??
Yes. Try this

'|' + Format(lasttime, 'dd/mm/yyyy')

0
 
runnerjp2005Author Commented:
And when i try to split them i get

Object reference not set to an instance of an object.

Im using: Lastpost.Text = Res.Result3.Split("|")(0) & " Posted " & Res.Result3.Split("|")(1) & " On the " & Res.Result3.Split("|")(2)
0
 
CodeCruiserCommented:
Is any of the fields empty in database?
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
Please varify what you get in Res.Result3 if it is null or empty?
0
 
runnerjp2005Author Commented:
Yes but thats why i use lasttime <> NULL
0
 
CodeCruiserCommented:
Step through the code to see what is coming back in Result3
0
 
runnerjp2005Author Commented:
And yes IJZ it gets the result      Res.Result3      "test 2 forum|ted|03/12/2011 07:50:13"      String
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
then try following and check what you get in s in debugger

Dim s As String() = Res.Result3.Split("|")
Lastpost.Text = s(0) & " Posted " & s(1) & " On the " & s(2)
0
 
runnerjp2005Author Commented:
top stuff once again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.