[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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

0
runnerjp2005
Asked:
runnerjp2005
  • 9
  • 5
  • 4
  • +3
1 Solution
 
Asim NazirCommented:
Either you can use OutParameters or you can use DataReader.
0
 
runnerjp2005Author Commented:
how would i use them though?
0
 
Asim NazirCommented:
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 ZiaCommented:
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 ZiaCommented:
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 ZiaCommented:
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
 
CodeCruiserCommented:
Then LastPost is null. Is it a label? Make sure it is not null.
0
 
Imran Javed ZiaCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now