Using cmd.ExecuteScalar() and showing 3 Selects

Either you can use OutParameters or you can use DataReader.

how would i use them though?

For details on using OutParameters see http://msdn.microsoft.com/en-us/library/59x02y99(v=VS.100).aspx

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

Select allOpen in new window

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

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()

Select allOpen in new window

      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()

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()

Select allOpen in new window

But i get the error

Conversion from string "tedtest 2 forum03/12/2011 07:50:" to type 'Double' is not valid.

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()

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)

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"

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

Is it because lasttime is within a date/time field??

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

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

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)

Is any of the fields empty in database?

Hi,
Please varify what you get in Res.Result3 if it is null or empty?

Yes but thats why i use lasttime <> NULL

Step through the code to see what is coming back in Result3

And yes IJZ it gets the result      Res.Result3      "test 2 forum|ted|03/12/2011 07:50:13"      String

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)

top stuff once again