webservice vb.net read data sql server 2008 R2

问题:

i have 2 problem with my program when i wanna read data from sql server. my problem

1. when i wanna read data type varchar from sql server 2008 r2 got error like this http://prntscr.com/apni8g and when i read data type integer from sql no error.

my code

<WebMethod()> _
Public Function TopKill() As Integer
    Dim con As New SqlConnection
    con.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=RF_World;Integrated Security=True"
    Dim killing As String
    con.Open()
    Dim cmd As New SqlCommand(("SELECT TOP 20 Name FROM tbl_pvporderview  Join tbl_base ON tbl_pvporderview.serial = tbl_base.Serial ORDER BY [Kill] DESC"), con)
    Dim killreader As SqlDataReader
    killreader = cmd.ExecuteReader()
    killreader.Read()
    If killreader.HasRows Then
        killing = killreader.Item("Name").ToString
    End If
    con.Close()
    Return killing
End Function ' TOP 20 Killer

2. when i read 2 data integer why output only one like this http://prntscr.com/apnjnk

my code

<WebMethod()> _
Public Function TopKill() As Integer
    Dim con As New SqlConnection
    con.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=RF_World;Integrated Security=True"
    Dim killing As String
    con.Open()
    Dim cmd As New SqlCommand(("SELECT TOP 20 [Kill], Death FROM tbl_pvporderview  Join tbl_base ON tbl_pvporderview.serial = tbl_base.Serial ORDER BY [Kill] DESC"), con)
    Dim killreader As SqlDataReader
    killreader = cmd.ExecuteReader()
    killreader.Read()
    If killreader.HasRows Then
        killing = killreader.Item("Kill").ToString
        killing = killreader.Item("Death").ToString
    End If
    con.Close()
    Return killing
End Function ' TOP 20 Killer

i don't know how to fix it. i realy need help to fix my code. maybe anyone can help me to fix my code

thanks before

回答1:

I would first try to remove the TOP 20 portion of the SQL to see if that is throwing the SqlDataReader off into thinking the first field is an integer column. If so, then try forcing SQL to interpret the field as a numeric: SELECT TOP 20 CAST([Kill] AS NVARCHAR(20)), Death FROM tbl_pvporderview Join tbl_base ON tbl_pvporderview.serial = tbl_base.Serial ORDER BY [Kill] DESC

回答2:

Look at your Function

Public Function TopKill() As Integer

Now look at what you are returning

Dim killing As String
....
Return killing

Your function is expecting to return an Integer and you are passing a String.. well actually a String array

Change to

Public Function TopKill() As String

As for your second issue

You are over writing the Kill value with Death... the function gives an answer as it is Cast'able' as an Integer so VB does this for you.

UPDATE

Looking at your code you need to output a datatable not a string (array) So change function to

    <WebMethod> _
Public Function Function TopKill() As DataTable

    Dim con As New SqlConnection
    con.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=RF_World;Integrated Security=True"
    Dim SelectQry As String = "SELECT TOP 20 Name FROM tbl_pvporderview  Join tbl_base ON tbl_pvporderview.serial = tbl_base.Serial ORDER BY [Kill] DESC"

    Dim da As New Sqldataadapter()
    Dim Command As New SqlCommand(SelectQry, con)
    da.selectedCommand = Command
    Dim dtresults As DataTable = newDataTable()

    Try
        con.Open()
        da.Fill(dtresults)
    Catch ex As Exception
        Throw ex
    Finally
        con.Close()
    End Try

    Return dtresults

End Function

The returned results will be a datatable with the Top 20 Name, Kill and Death records.