[maddtechwf]

I read the article that you suggested. I implemented it and now I'm getting a blank result set with the same parameter I was using before.

'**************************************************************
    '
    '                       Staff Functions
    '
    '**************************************************************
    Public Function Staff(param As String) As String Implements CentralService.Staff
        SQLStr = "SELECT Name_First, Name_Last, email_MSU, PhotoFileName FROM tbl_people WHERE Name_First LIKE '% @partial %' OR Name_Last LIKE '% @partial %'"
        Dim jss As JavascriptSerializer
        Dim Employees As New List(Of Employee)
        Dim StaffList As String = ""

        Dim FName As String = ""
        Dim LName As String = ""
        Dim Email As String = ""
        Dim PhotoURL As String = ""

        jss = New JavascriptSerializer()

        Using SQLConn As New SqlConnection(ConnString)
            Using SQLCmd As New SqlCommand(SQLStr, SQLConn)
                SQLCmd.Parameters.AddWithValue("@partial", param)
                SQLConn.Open()
                Dim SQLdr As SqlDataReader = SQLCmd.ExecuteReader()

                Try
                    While SQLdr.Read()
                        If IsDBNull(SQLdr.Item("Name_First")) Then
                            FName = ""
                        Else
                            FName = SQLdr.Item("Name_First")
                        End If
                        If IsDBNull(SQLdr.Item("Name_Last")) Then
                            LName = ""
                        Else
                            LName = SQLdr.Item("Name_Last")
                        End If
                        If IsDBNull(SQLdr.Item("email_MSU")) Then
                            Email = ""
                        Else
                            Email = SQLdr.Item("email_MSU")
                        End If
                        If IsDBNull(SQLdr.Item("PhotoFileName")) Then
                            PhotoURL = ""
                        Else
                            PhotoURL = SQLdr.Item("PhotoFileName")
                        End If
                        Employees.Add(New Employee() With {.firstName = FName, .lastName = LName, .email = Email, .photo = PhotoURL})
                    End While
                Catch ex As Exception
                    Return ex.ToString
                End Try
            End Using
        End Using

        Return jss.Serialize(Employees)

    End Function

[maddtechwf]

rgfirefly24, on 24 October 2012 - 08:25 AM, said:

You are looking at a string object. A string is surrounded by double quotes. Because JSON also uses double quotes to denote an object and it's data, when you view the string the Quotes within the JSON string have to be escaped. I would say that the escape character is being added by the .Serialize method.

In doing some testing, I'm not sure where the \s are coming from actually. When I look at my output of .Serialize it doesn't have the escape character. What I'll do now is try and build your environment as closely as possible and see if i can't reproduce it. I do have one question though. The JSON string you supplied. Where did you get it from?

That came from me running http://localhost:577...ice.svc/Staff/d

[rgfirefly24]

Have you set a breakpoint to follow the data through to see what it is doing? I'm at a loss as to why the backslashes are there. As far as the empty return set, try this to see if it works better:

SQLStr = "SELECT Name_First, Name_Last, email_MSU, PhotoFileName FROM tbl_people WHERE Name_First LIKE @partial OR Name_Last LIKE @partial"
...
SQLCmd.Parameters.AddWithValue("@partial", "'%"+param+"%'")

I suspect because you have a space between the % and @partial it is causing your issue

maddtechwf, on 24 October 2012 - 09:55 AM, said:

That came from me running http://localhost:577...ice.svc/Staff/d

since it is running as localhost I can't look at it, but the issue might be in several places. Because this is a method that returns to another place it can be anywhere. I would suggest setting a breakpoint and following the logical execution of the code and finding at which point the backslashes are added in.

[h4nnib4l]

The spaces were the problem with the parameter, because that causes the query to look for spaces in the specified parameter. I suspect you can just remove the spaces and leave the wildcards in the query, but I always place the wildcards in parameter assignment statement like rgfirefly24 demonstrated.

[maddtechwf]

I was doing some reading and people were saying you would see this when you double serialize data. Is that what I'm doiong?

[rgfirefly24]

Not in the code you've shown your not. If you're doing it elsewhere I have no clue.

[maddtechwf]

The only reason I ask is because my Service1.vb has this

    <WebGet(UriTemplate:="/Staff/{param}", ResponseFormat:=WebMessageFormat.Json)>
    <OperationContract()>
    Function Staff(param As String) As String

and my Service1.svc.vb has this

    '**************************************************************
    '
    '                       Staff Functions
    '
    '**************************************************************
    Public Function Staff(param As String) As String Implements CentralService.Staff
        SQLStr = "SELECT Name_First, Name_Last, email, PhotoFileName FROM tbl_people WHERE Name_First LIKE @partial OR Name_Last LIKE @partial"
        Dim jss As JavascriptSerializer
        Dim Employees As New List(Of Employee)

        Dim FName As String = ""
        Dim LName As String = ""
        Dim Email As String = ""
        Dim PhotoURL As String = ""

        jss = New JavascriptSerializer()

        Using SQLConn As New SqlConnection(ConnString)
            Using SQLCmd As New SqlCommand(SQLStr, SQLConn)
                SQLCmd.Parameters.AddWithValue("@partial", "%" & param & "%")
                SQLConn.Open()
                Dim SQLdr As SqlDataReader = SQLCmd.ExecuteReader()

                Try
                    While SQLdr.Read()
                        If IsDBNull(SQLdr.Item("Name_First")) Then
                            FName = ""
                        Else
                            FName = SQLdr.Item("Name_First")
                        End If
                        If IsDBNull(SQLdr.Item("Name_Last")) Then
                            LName = ""
                        Else
                            LName = SQLdr.Item("Name_Last")
                        End If
                        If IsDBNull(SQLdr.Item("email")) Then
                            Email = ""
                        Else
                            Email = SQLdr.Item("email")
                        End If
                        If IsDBNull(SQLdr.Item("PhotoFileName")) Then
                            PhotoURL = ""
                        Else
                            PhotoURL = SQLdr.Item("PhotoFileName")
                        End If
                        Employees.Add(New Employee() With {.firstName = FName, .lastName = LName, .email = Email, .photo = PhotoURL})
                    End While
                Catch ex As Exception
                    Return ex.ToString
                End Try
            End Using
        End Using

        Return jss.Serialize(Employees)

    End Function

I was just reading and I didn't know if since I declared my Response format to be JSON and then I was also serializing the data before sending it out, if that was doing it.

This post has been edited by maddtechwf: 29 October 2012 - 10:54 AM

[maddtechwf]

I have another question. I have two classes, one has a public property that is over the other class. I'm trying to tell it how to handle the data but I'm a little loss. Below is my code.

Employee_RSS.vb

Public Class Employee_RSS
    Public Property ID As Integer
    Public Property Name As New List(Of FullNameTitle)
    Public Property Email As String
    Public Property Dept As String
    Public Property Building As String
    Public Property Room As String
    Public Property Title As String
    Public Property Photo As String
    Public Property Phone As String
    Public Property Fax As String
End Class

FullNameTitle.vb

Public Class FullNameTitle
    Public Property Title As String
    Public Property First As String
    Public Property Middle As String
    Public Property Last As String
    Public Property Suffix As String
End Class

Service1.svc.vb

    Public Function StaffRSS() As String Implements CentralService.StaffRSS
        SQLStr = "SELECT tbl_people.ID, tbl_people.Name_Title, tbl_people.Name_First, tbl_people.Name_Last, tbl_people.Name_Middle, tbl_people.Name_Suffix, tbl_people.PhotoFileName, tbl_people.email_ FROM tbl_people"

        'Declare Variables
        Dim jss As JavascriptSerializer = New JavascriptSerializer
        Dim RSSEmployee As New List(Of Employee_RSS)
        Dim id As String = ""
        Dim TName As String = ""
        Dim FName As String = ""
        Dim MName As String = ""
        Dim LName As String = ""
        Dim SName As String = ""
        Dim email As String = ""
        Dim dept As String = ""
        Dim building As String = ""
        Dim room_numb As String = ""
        Dim title As String = ""
        Dim photo As String = ""
        Dim phone As String = ""
        Dim fax As String = ""

        Using SQLConn As New SqlConnection(ConnString)
            Using SQLCmd As New SqlCommand(SQLStr, SQLConn)
                SQLConn.Open()
                Dim SQLdr As SqlDataReader = SQLCmd.ExecuteReader()

                Try
                    While SQLdr.Read()
                        If IsDBNull(SQLdr.Item("ID")) Then
                            id = "0"
                        Else
                            id = SQLdr.Item("ID").ToString
                        End If
                        If IsDBNull(SQLdr.Item("Name_Title")) Then
                            TName = ""
                        Else
                            TName = SQLdr.Item("Name_Title")
                        End If
                        If IsDBNull(SQLdr.Item("Name_First")) Then
                            FName = ""
                        Else
                            FName = SQLdr.Item("Name_First")
                        End If
                        If IsDBNull(SQLdr.Item("Name_Middle")) Then
                            MName = ""
                        Else
                            MName = SQLdr.Item("Name_Middle")
                        End If
                        If IsDBNull(SQLdr.Item("Name_Last")) Then
                            LName = ""
                        Else
                            LName = SQLdr.Item("Name_Last")
                        End If
                        If IsDBNull(SQLdr.Item("Name_Suffix")) Then
                            SName = ""
                        Else
                            SName = SQLdr.Item("Name_Suffix")
                        End If
                        If IsDBNull(SQLdr.Item("email_")) Then
                            email = ""
                        Else
                            email = SQLdr.Item("email_")
                        End If
                        If IsDBNull(SQLdr.Item("PhotoFileName")) Then
                            photo = ""
                        Else
                            photo = SQLdr.Item("PhotoFileName")
                        End If
                        RSSEmployee.Add(New Employee_RSS() With {.Email = email, .Name = New List(Of FullNameTitle) With {.Title = TName, .First = FName, .Middle = MName, .Last = LName, .Suffix = SName}, .ID = id})
                    End While
                Catch ex As Exception
                    Return ex.ToString
                End Try
            End Using
        End Using

        Return jss.Serialize(RSSEmployee)
    End Function

This post has been edited by maddtechwf: 30 October 2012 - 02:19 PM

[h4nnib4l]

Why did you make the Employee's Name a List of FullNameTitle? Each employee only has one name, right? So it should be Public Property Name As FullNameTitle.

Since you're assigning a class to the Name value, you'll have to create that FullNameTitle class and assign the values inside the loop. Just dim an instance of it in your loop (or do it from outside the loop, and then redim it in the loop - I'm no expert on VB.NET), and then after all of your assignments in the If/Else block, try

myFullNameTitle.Title = Tname
myFullNameTitle.First = Fname
myFullNameTitle.Middle = Mname
myFullNameTitle.Last = Lname
myFullNameTitle.Suffix = Sname

I'll assume that you've changed the Employee's Name field from a List of FullNameTitle to a single instance of FullNameTitle now, so then adding it to the list would look like this:

RSSEmployee.Add(New Employee_RSS() With {.Email = email, .Name = myFullNameTitle, .ID = id})

I didn't actually check any of this in VS, because I don't have any active VB.NET projects to check against, but the basic idea should be right even if you have to tweak the syntax.

[maddtechwf]

Thank you h4nnib4l, that worked like a charm. You wouldn't know any good tutorials on outputting data in XML format instead of JSON would you? I have a specific structure that I need the XML to follow.

[h4nnib4l]

I don't man, I'd be googling just like you would. I'm sure someone can chime in who does though. You might look into LINQ-to-XML.

[rgfirefly24]

If you are writing a truly ReST service it won't matter what they want as long as they specify application/json or application/xml they will get the output they want. If you are not already I would highly suggest you look into using web API or using MVC as a lot of what you are needing is taken care of already.

[maddtechwf]

Well the XML is for an RSS feed to be used in an iphone app.

[maddtechwf]

Okay, so I spent most of yesterday looking at a new approach to my API. I watched a good video and it was in C#. I decided that I wanted to use what they were doing so I downloaded the code from GitHub. I'm trying to learn C# since it seems that more people use C# than VB.NET.

With that said, I have a .dbml that has four tables in it and their relationship set. I want to travers from on table down to another one to find what I need. I just don't know how to write that in my EmployeesController.cs.

EmployeeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Http.WebHost;
using System.Web.Http.WebHost.Routing;

namespace hello_services.Controllers {
    
    // Inheriting from the APIController class will designate this as a WebAPI endpoint
    public class EmployeesController : ApiController {


        // the linq to sql context that provides the data access layer
        private Data.ContactManagerContextDataContext _context = new Data.ContactManagerContextDataContext();
     
        // WebAPI will respond to an HTTP GET with this method
        public List<Models.Employee> Get() {

            // get all of the records from the employees table in the
            // northwind database.  return them in a collection of user
            // defined model objects for easy serialization.
            var employees = from e in _context.tbl_peoples
                            select new Models.Employee
                            {
                                Id = e.ID,
                                 First = e.Name_First,
                                 Middle = e.Name_Middle,
                                 Last = e.Name_Last,
                                 Title = e.Name_Title,
                                 Suffix = e.Name_Suffix,
                                 email = e.email_1,
                                 photo = e.PhotoFileName
                            };

            // returns the employees as a list, which is converted
            // to an array during serialization to JSON
            return employees.ToList();

        }
    }
}

My source that I'm basing this off of is here.

This post has been edited by maddtechwf: 01 November 2012 - 07:11 AM

[maddtechwf]

Okay so I didn't realize that I was using a LINQ database query. I just found that out yesterday. So with that said, I'm needing some serious help in writing a LINQ statement that will return an individual info along with all their positions that they currently hold. This data is scattered over multiple tables. I have my basic info showing just fine, just don't know how to also query all the positions they have also.