none
Web service help RRS feed

  • Question

  • I am trying to design a webservice that will access a database. I can display the info but I can't do a search or allow addition to the database. Below is the Web methods WS_GetData that works and GetEmployeeByCity that doesn't. Is there source code on MSDN that has what I need?

     

    <WebMethod()> _

    Public Function WS_GetData() As DataSet

    Dim CS As String = "Provider=Microsoft.JET.OLEDb.4.0;" + "Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Northwind.mdb")

    Dim objCN As New OleDbConnection(CS)

    Dim OleDbConnection1 As New OleDbConnection(CS)

    'objCN.Open()

    Dim objDS1 As New DataSet("Northwind")

    Dim MySQL As String = "Select * from employees"

    Dim ComObj = New OleDbCommand(MySQL, objCN)

    Dim myDataAdapter As New OleDbDataAdapter(MySQL, OleDbConnection1)

    myDataAdapter.SelectCommand = ComObj

    myDataAdapter.Fill(objDS1, "EmployeeData")

    Return objDS1

    End Function

     

    <WebMethod()> _

    Public Function GetEmployeeByCity(ByVal city As String) As DataSet

    Dim CS As String = "Provider=Microsoft.JET.OLEDb.4.0;" + "Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Northwind.mdb")

    Dim objCN As New OleDbConnection(CS)

    Dim OleDbConnection1 As New OleDbConnection(CS)

    objCN.Open()

    Dim objDS2 As New DataSet("Northwind")

    Dim MySQL As String = "SELECT LastName, FirstName, Title, City FROM employees WHERE City= " & city

    Dim ComObj = New OleDbCommand(MySQL, objCN)

    Dim myDataAdapter As New OleDbDataAdapter(MySQL, OleDbConnection1)

    'also add the parameters

    myDataAdapter.SelectCommand.Parameters.AddWithValue("@City", city)

    'set up the insert/update/delete queries

    'Dim myCmdBuilder As New OleDbCommandBuilder(myDataAdapter)

    myDataAdapter.SelectCommand = ComObj

    myDataAdapter.Fill(objDS2, "EmployeeData")

    objCN.Close()

    Return objDS2

    End Function

    Sunday, March 9, 2008 3:59 AM

All replies

  • The reason it's not working is because you're trying to use a parameterized query and not doing it correctly. The correct code is below.

    Try something like this (I refactored it a bit):

    Code Snippet

    Private Function GetConnection() As OleDb.OleDbConnection
            Dim connectionString As String = "Provider=Microsoft.JET.OLEDb.4.0;" + "Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Northwind.mdb")

            Dim objCn As New OleDb.OleDbConnection(connectionString)


        End Function

       
        <WebMethod()> _
    Public Function WS_GetData() As DataSet

            Dim employeesDataSet As New DataSet("Northwind")
            Dim sql As String = "Select * from employees"
            Dim employeesCommand = New OleDb.OleDbCommand(sql, GetConnection)
            Dim myDataAdapter As New OleDb.OleDbDataAdapter(employeesCommand)
            myDataAdapter.Fill(employeesDataSet, "EmployeeData")

            Return employeesDataSet
        End Function

     

    <WebMethod()> _

        Public Function GetEmployeeByCity(ByVal city As String) As DataSet

            Dim employeesDataSet As New DataSet("Northwind")
            Dim sql As String = "SELECT LastName, FirstName, Title, City FROM employees WHERE City= @city"
            Dim employeesCommand = New OleDb.OleDbCommand(sql, GetConnection)
            Dim myDataAdapter As New OleDb.OleDbDataAdapter(employeesCommand)
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@City", city)
            myDataAdapter.Fill(employeesDataSet, "EmployeeData")

            Return employeesDataSet

        End Function

     

     

     

    Sunday, March 9, 2008 4:48 AM
  • No that didn't work. The connection function did not return anything so I gave it a return. I was able to get a return of all the data again but the search by city did not work.

     

    Mike

     

    Sunday, March 9, 2008 3:52 PM