RestFul service & SQL Query

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

52 Replies - 3865 Views - Last Post: 10 November 2012 - 08:10 AM Rate Topic: -----

#1 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

RestFul service & SQL Query

Posted 22 October 2012 - 02:01 PM

I'm working on a ReST service for a website. I'm wanting it to the following call http://www.somewebsi...c/Staff/{param} and show the results. I keep getting this error with the following code. I've worked with VB in ASP.NET before but I have never worked with ReST services. I would appreciate any help.

Error:
System.NullReferenceException: Object reference not set to an instance of an object. at Rest.Service1.Staff(String param) in c:\Rest\Service1.svc.vb:line 46

Code:
Public Function Staff(param As String) As String Implements Service.Staff
        SQLStr = "SELECT Name_First, Name_Last, email_1 FROM tbl_people WHERE Name_First LIKE '%" & param & "%' OR Name_Last LIKE '%" & param & "%'"
        Dim SQLConn As New SqlConnection
        Dim SQLCmd As New SqlCommand
        Dim SQLdr As SqlDataReader
        Dim jss As JavascriptSerializer

        jss = Nothing


        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr

        SQLConn.ConnectionString = ConnString 'Set the Connection String
        SQLConn.Open() 'Open the connection

        SQLdr = SQLCmd.ExecuteReader

        Try

            While SQLdr.Read()
                Return jss.Serialize(SQLdr.Item("Name_First") & " , " & SQLdr.Item("Name_Last") & " , " & SQLdr.Item("email_1"))
            End While
        Catch ex As Exception
            Return ex.ToString
        End Try

        SQLdr.Close()
        SQLConn.Close()

    End Function



Is This A Good Question/Topic? 1
  • +

Replies To: RestFul service & SQL Query

#2 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,470
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 22 October 2012 - 02:17 PM

You set jss equal to nothing, but then attempt to make a call to Serialize on line 22. Why are you setting jss to nothing?

you need to dim a new JavascriptSerializer before you can use it

Dim jss As JavascriptSerializer
jss = new JavascriptSerializer()
jss.Serialize()


This post has been edited by rgfirefly24: 22 October 2012 - 02:20 PM

Was This Post Helpful? 1
  • +
  • -

#3 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 23 October 2012 - 06:10 AM

That worked perfectly. Thank you soooo much. Can I ask you one more question. I have a Try/Catch with a While inside it. When I run this code, it only returns one result instead of multiple. What did I do wrong?

Try

            While SQLdr.Read()
                Return jss.Serialize(SQLdr.Item("Name_First") & " , " & SQLdr.Item("Name_Last") & " , " & SQLdr.Item("email_1"))
            End While
        Catch ex As Exception
            Return ex.ToString
        End Try


Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,470
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 23 October 2012 - 07:10 AM

you are using a return statement.

Nothing after a return statement gets executed no matter how you have it setup. Change line 4 to insert the return of the jss.Serialize into a List and then return the list after the while ends
Was This Post Helpful? 1
  • +
  • -

#5 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 23 October 2012 - 07:31 AM

Okay, so I declared a StaffList as List(of Integer) = New List(Of Integer). Then on line 4 I changed it to StaffList.Add(jss.Serialize(SQLdr.Item("Name_First") & " , " & SQLdr.Item("Name_Last") & " , " & SQLdr.Item("email_1"))) and then after the Try/Catch I returned the list: Return StaffList.ToString. Is that correct? I've never used a List before.
Was This Post Helpful? 0
  • +
  • -

#6 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,673
  • Joined: 24-August 11

Re: RestFul service & SQL Query

Posted 23 October 2012 - 07:50 AM

Once a method hits return, it returns the result and exits. What you'll want to do there is add those results to a collection, and then return the collection after the While statement exits.

EDIT: Should've refreshed the thread before I replied, looks like rgfirefly24 beat me to it...

This post has been edited by h4nnib4l: 23 October 2012 - 07:51 AM

Was This Post Helpful? 1
  • +
  • -

#7 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,470
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 23 October 2012 - 07:50 AM

your list must be of type string because the JavascriptSerializer.Serialize method returns type string

if you want a single JSon string then you don't need a list, although more context into what your doing might help in determining what you need to use:

So for a List(Of String) you would do something like this:

While Read
stringList.Add(JavascriptSerializer.Serialize())
End

return stringList



if you can't change the method signature to return a List then you can do something like the following although it will take more effort, and introduce more failure points:

Dim returnString as String

While Read
returnString &= JavascriptSerializer.Serialize()
End

return returnString


Was This Post Helpful? 2
  • +
  • -

#8 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,673
  • Joined: 24-August 11

Re: RestFul service & SQL Query

Posted 23 October 2012 - 07:54 AM

To add to what rgfirefly24 said, if you're looking for an integer from all of this, you'll either have to use a TryParse on the result of the Serialize() command before you plug it into that list (which means it would need to be an integer list instead of a string list), or loop through the list items after creation and pass them to a new list.
Was This Post Helpful? 1
  • +
  • -

#9 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 23 October 2012 - 07:56 AM

rgfirefly24 - I'm querying a database that has all of our staff information in it. We are moving to a new CMS solution and the trainer suggested that I use a ReST service to pull information in from my Employee Profile db and populate pages with it.

With that said, the above code, I want to to query a database based on the parameter passed to it and then push out everything that partially matches it in the Name_First and Name_Last.

I'm then going to return that data from the search to a page for people to look at.
Was This Post Helpful? 0
  • +
  • -

#10 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 23 October 2012 - 08:33 AM

I stored it in a string and returned it and it works now.

Is there a way to make it a little more nicer looking than just "First_Name, Last_Name, Email_1""First_Name, Last_Name, Email_1".....
Was This Post Helpful? 0
  • +
  • -

#11 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,470
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 23 October 2012 - 11:44 AM

yes absolutely and it deals with making it into a collection.

Instead of doing the JSON serialization on each of the returned items from the database, create an object that will store the information called Customer or what ever and fill that. Then call the Serialize() on that object:

public class Customer
//put all your properties in here like First Name ect
End Class


Dim customers as new List(of Customer) 

while read()
customers.add(new Customer(){ FirstName = ......})
End While

return jss.Serialize(customers)


This post has been edited by rgfirefly24: 23 October 2012 - 11:45 AM

Was This Post Helpful? 1
  • +
  • -

#12 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 23 October 2012 - 11:57 AM

I'm lost on the public class customer part. What do I need to put inside it?
Was This Post Helpful? 0
  • +
  • -

#13 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,673
  • Joined: 24-August 11

Re: RestFul service & SQL Query

Posted 23 October 2012 - 12:08 PM

Do you not know what a class is? MSDN tutorial on creating classes. If you're working in .NET and need that, though, you really should step back and read up on Object Oriented Programming. You can only get so far programming in a language without understanding the paradigm it's built on.

This post has been edited by h4nnib4l: 23 October 2012 - 12:08 PM

Was This Post Helpful? 1
  • +
  • -

#14 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 23 October 2012 - 12:41 PM

I know what a class is. I didn't clearly state what I was missing. Inside the class do I just need to declare a bunch of variables?
Was This Post Helpful? 0
  • +
  • -

#15 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,673
  • Joined: 24-August 11

Re: RestFul service & SQL Query

Posted 23 October 2012 - 12:47 PM

You would be declaring properties, not "variables". The class would just be modeling the DB table. You would create a property for each column in the table (string firstName;, etc). Then you would do as rgfirefly24 demonstrated: create a list of customers, and then each pass of your reader would use an object initializer to create an instance with the data from that pass, and then add the new customer to your list of customers.
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »