[kristina1]

hi, i wrote a webmethod which returns a datatable in order to fill a gridview in my windows form in vb.net, here is my web method:

 <WebMethod()> _
    Public Function TrackYourDelivery(ByVal barcode As String) As DataTable
        Dim sqlconn As SqlConnection = New SqlConnection(strconn)
        sqlconn.Open()
        Dim strcommand As String = "select * from v_tran_tracking_operations where MailItemCode ='" & barcode & "'"
        Dim sqlcommand As SqlCommand = New SqlCommand(strcommand, sqlconn)

        Dim dt As DataTable = New DataTable("Tracking_operations")
        Dim da As SqlDataAdapter = New SqlDataAdapter(sqlcommand)
        Dim dataSet As DataSet = New DataSet

        da.Fill(dataSet, "Tracking")
        dt = dataSet.Tables(0)
        Return dt
        sqlconn.Close()
    End Function

and here is where i call the webmethod

    Private Sub btn_tracking_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_tracking.Click
        Dim obj As New Service1SoapClient
        DataGridView1.DataSource = obj.TrackYourDelivery(txt_tracking.Text)
    End Sub

It doesnt happen anything after i press the button and i dont understand why. I try calling the default webmethod which returns "Hello World" and it works properly, it means that i have added the right references of the webservice to the project. I copyed the same function of the web method into another button of the form and it works ok.

Can anyone give me some help about this? i really dont get what is wrong with my code. Thanks a lot for your time

[CharlieMay]

Check your strcommand. Make sure it's building something that should return rows.

You can actually do a Debug.Print strcommand and run the code. This will show you the actual statement being processed.

Then hilight that string and submit it in the query designer inside the sql gui to see what it returns.

Other than some redundancy and unnecessary code, you should still produce a result on a valid query.

Here is how I would write the function

   Public Function TrackYourDelivery(ByVal barcode As String) As DataTable
        Dim sqlconn As SqlConnection = New SqlConnection(strconn)
        'sqlconn.Open()
        Dim strcommand As String = "select * from v_tran_tracking_operations where MailItemCode ='" & barcode & "'"
        'Dim sqlcommand As SqlCommand = New SqlCommand(strcommand, sqlconn)
        Dim dt As DataTable = New DataTable("Tracking_operations")
        Dim da As New SqlDataAdapter(strcommand, sqlconn)
        ' Dim dataSet As DataSet = New DataSet
        da.Fill(dt)
        'da.Fill(dataSet, "Tracking")
        'dt = DataSet.Tables(0)
        Return dt
        'sqlconn.Close()
    End Function

Since you're only returning a datatable from the function, you don't need to create a dataset too. Reserve sqlcommand for executing statements directly with the database and not through the dataadapter, it will do that for you and handle closing the database when complete.

You could just as easily return a dataset and not use a datatable here too. Depending on your needs.

So if you take out all the stuff I commented out, it would look like:

    Public Function TrackYourDelivery(ByVal barcode As String) As DataTable
        Dim sqlconn As SqlConnection = New SqlConnection(strconn)
        Dim strcommand As String = "select * from v_tran_tracking_operations where MailItemCode ='" & barcode & "'"
        Dim dt As DataTable = New DataTable("Tracking_operations")
        Dim da As New SqlDataAdapter(strcommand, sqlconn)
        da.Fill(dt)
        Return dt
    End Function

The only other thing I would change Other than some error handling is to parametrize the query instead of concatenating strings that can be injected by devious users.

This post has been edited by CharlieMay: 13 March 2013 - 11:26 AM

[kristina1]

thanks a lot for your help,but either with the unnecessary lines of my code, it works when i declare and call it inside my winform, the problem is when i call it as a webmethod, anyway i will try out your code and hope it will solve my problem

[kristina1]

hi,
Still no result, the gridview doesn't populate

[CharlieMay]

I'm going to be honest and state right upfront that I've not done anything like this.

Just browsing around, it seems that every time I see <WebMethod()> in an example, the function is Public Shared Function

But then all of what I've found in a quick browse has been called using JQuery