locked
Creating a Webservice that returns JSON data RRS feed

  • Question

  • User-2118372426 posted

    I was wondering if anyone can help me with my web service.  I've tried to follow multiple example but I'm getting an error on line 29.

    Imports System.Web
    Imports System.Collections
    Imports System.Web.Services
    Imports System.Web.Services.Protocols
    Imports System.Data
    Imports System.Data.Odbc
    Imports System.Web.Script.Serialization
    Imports System.Web.Script.Services
    Imports System.ComponentModel
    
    ' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    ' <System.Web.Script.Services.ScriptService()> _
    <System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
    <System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
    <ToolboxItem(False)> _
    Public Class Calendar
        Inherits System.Web.Services.WebService
    
        Public Sub New()
        End Sub
    
        <WebMethod(Description:="Gets the books matching part of a title.")> _
        <ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
        Public Function GetCalendarEvents() As String
            Dim objConnection As New OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DTS").ConnectionString)
            Dim objCommand As New OdbcCommand("select linktext, pagepath, (select dateValue from PERC_PAGE_METADATA_PROPERTIES where ENTRY_ID=p.ENTRY_ID and name='perc:start_date') as StartDate, (select dateValue from PERC_PAGE_METADATA_PROPERTIES where ENTRY_ID=p.ENTRY_ID and name='perc:end_date') as EndDate from PERC_PAGE_METADATA d,PERC_PAGE_METADATA_PROPERTIES p where d.pagepathHash=p.ENTRY_ID and stringvalue='university-calendar';", objConnection)
            Dim objDataSet As New DataSet()
            Dim objDataAdapter As New OdbcDataAdapter(objCommand)
            objDataAdapter.Fill(objDataSet, "reading")
            objConnection.Close()
    
            ' Create a multidimensional jagged array
            Dim JaggedArray As String()() = New String(objDataSet.Tables(0).Rows.Count - 1)() {}
            Dim i As Integer = 0
            For Each rs As DataRow In objDataSet.Tables(0).Rows
                JaggedArray(i) = New String() {rs("linktext").ToString(), rs("pagepath").ToString(), rs("StartDate").ToString(), rs("EndDate").ToString()}
                i = i + 1
            Next
            ' Return JSON data
            Dim js As New JavaScriptSerializer()
            Dim strJSON As String = js.Serialize(JaggedArray)
            Return strJSON
        End Function
    End Class



    Friday, October 18, 2013 10:46 AM

Answers

  • User-760709272 posted

    If your data is in SQL Server then use the Sql data namespace (SqlConnection, SqlCommand etc), forget everything to do with OleDb.  You can test that your connection string is valid by just trying to open it in some sample code;

    SqlConnection c = new SqlConnection(" your string here");

    c.Open();

    And see if it throws any errors.  Look at www.connectionstrings.com for various examples of how you connect to different servers.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 18, 2013 11:49 AM
  • User-760709272 posted

    Use SqlDataAdapter instead of OdbcDataAdapter

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx

    However you need to ensure your connection etc is valid and correct or else your code after is somewhat irrelevant.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 18, 2013 11:55 AM

All replies

  • User-760709272 posted

    What is line 29 and what is the error?

    Friday, October 18, 2013 10:53 AM
  • User-2118372426 posted
    objDataAdapter.Fill(objDataSet, "reading")

    ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    Friday, October 18, 2013 10:55 AM
  • User-760709272 posted

    Check what this is returning;

    System.Configuration.ConfigurationManager.ConnectionStrings("DTS").ConnectionString

    It is either an invalid connection string, or an empty string.

    Friday, October 18, 2013 11:02 AM
  • User-2118372426 posted

    It's because I had importeded System.Data.Odbc.  I changed it to System.Data.SqlClient but now my whole function doesn't work.  The code was written to work with MySQL and not MS SQL.  Any chance you have any experience with it?

    Friday, October 18, 2013 11:19 AM
  • User-760709272 posted

    Is the data still in MySQL or is it in SQL Server?

    Friday, October 18, 2013 11:31 AM
  • User-2118372426 posted

    My data is stored in a SQL Server.  The article I followed was MySQL.

    Friday, October 18, 2013 11:34 AM
  • User-760709272 posted

    If your data is in SQL Server then use the Sql data namespace (SqlConnection, SqlCommand etc), forget everything to do with OleDb.  You can test that your connection string is valid by just trying to open it in some sample code;

    SqlConnection c = new SqlConnection(" your string here");

    c.Open();

    And see if it throws any errors.  Look at www.connectionstrings.com for various examples of how you connect to different servers.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 18, 2013 11:49 AM
  • User-2118372426 posted

    Well I knew that.  My thing is when I get down to a specific part of the code, I don't know how to convert it over for SQL.

    Dim objDataSet As New DataSet()
            Dim objDataAdapter As New OdbcDataAdapter(objCommand)
            objDataAdapter.Fill(objDataSet, "reading")
            objConnection.Close()
    
            ' Create a multidimensional jagged array
            Dim JaggedArray As String()() = New String(objDataSet.Tables(0).Rows.Count - 1)() {}
            Dim i As Integer = 0
            For Each rs As DataRow In objDataSet.Tables(0).Rows
                JaggedArray(i) = New String() {rs("linktext").ToString(), rs("pagepath").ToString(), rs("StartDate").ToString(), rs("EndDate").ToString()}
                i = i + 1
            Next
            ' Return JSON data
            Dim js As New JavaScriptSerializer()
            Dim strJSON As String = js.Serialize(JaggedArray)
            Return strJSON



    Friday, October 18, 2013 11:51 AM
  • User-760709272 posted

    Use SqlDataAdapter instead of OdbcDataAdapter

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx

    However you need to ensure your connection etc is valid and correct or else your code after is somewhat irrelevant.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 18, 2013 11:55 AM