A simple VB.net SQL data query that is not returning any records

Question

I am trying to debug my code, which involves a simple webservice that passes parameters to a data class, which calls a stored proc, and returns one row of record. I am not getting any error messages, but I am not getting any records back either.

My webservice code is:

<WebMethod()> _
Public Function GetDataValues(ByVal AutoVIN As String, ByVal OrderID As String, ByVal StatusCode As String, ByVal DivisionID As String) As String
    Dim oGetHeaderValue As New clsGetHeaderValue

    Dim GetHeaderValues As String = ""

    Dim strAutoVIN = Trim(AutoVIN)
    Dim strOrderID = Trim(OrderID)
    Dim strStatusCode = Trim(StatusCode)
    Dim strDivisionID = Trim(DivisionID)

    Try
        'Validate user entries if they are the correct length;
        If Not IsNumeric(strAutoVIN) Or Len(Trim(strAutoVIN)) <> 3 Then
            Throw New SoapException("Invalid Auto VI Number", SoapException.ClientFaultCode)
        End If

        'OrderID could be 12 digits in length
        If Not IsNumeric(strOrderID) Then
            Throw New SoapException("Invalid Order ID", SoapException.ClientFaultCode)
        End If

        '************************Verify if the order exists "Order Number not in requested status" in table Auto.orderHeader
        '************************Validate entries against db table;

        'Call Stored Proc and pass parameters;
        Dim retDataSet As Data.DataSet
        retDataSet = oGetHeaderValue.GetHeaderValue(strAutoVIN, strOrderID, strStatusCode, strDivisionID)

        GetHeaderValues = retDataSet.ToString()
        Return GetHeaderValues

    Catch ex As Exception
        Return "<ERR>" & ex.Message.ToString & "</ERR>"
    End Try
End Function

My database class code is:

Public Function GetGetHeaderValue(ByVal sAutoVIN As String, ByVal sOrderID As String, ByVal sStatusCode As String, ByVal sDivisionID As String) As DataSet
Dim sqlDA As New SqlDataAdapter
Dim ds As New DataSet
Dim sqlcmd As New SqlCommand

Dim conn As New SqlConnection(strCN_Auto)

sqlDA = New SqlDataAdapter("uspExportOrders", conn)
sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure

sqlDA.SelectCommand.Parameters.Add(New SqlParameter("@AutoVIN", SqlDbType.VarChar, 3))
sqlDA.SelectCommand.Parameters("@AutoVIN").Value = sAutoVIN

sqlDA.SelectCommand.Parameters.Add(New SqlParameter("@OrderID", SqlDbType.VarChar, 12))
sqlDA.SelectCommand.Parameters("@OrderID").Value = sOrderID

sqlDA.SelectCommand.Parameters.Add(New SqlParameter("@StatusCode", SqlDbType.VarChar, 10))
sqlDA.SelectCommand.Parameters("@StatusCode").Value = sStatusCode

sqlDA.SelectCommand.Parameters.Add(New SqlParameter("@DivisionID", SqlDbType.VarChar, 3))
sqlDA.SelectCommand.Parameters("@DivisionID").Value = sDivisionID

sqlDA.Fill(ds) 'Fill the DataSet with the rows returned.

Return ds

sqlDA.Dispose()
    conn.Close()
End Function  

Answer 1

I'm basing this on the assumption that your stored procedure only returns a single string value. I don't think your problem is in your procedure not returning any data.

Change the following:

GetHeaderValues = retDataSet.ToString()

to

Dim dr As DataRow
dr = retDataSet.Tables(0).Rows(0)
GetHeaderValues = dr(0).ToString()

Here's why. If all you want out of this procedure in your database is a single string, you don't have to return and entire dataset, but that's OK. The dataset is a little more complicated than just getting the string value out of it. It can contain many tables (which is why it is normally used) and could allow you to change the data (which you are not doing). You may want to research using the command's .ExecuteScalar. I've assumed yours has only one, so you can refer to it as .Table(0) or you could replace the zero with the name of the table (Which you don't have.). In each table, there are rows and columns. Again only one row and one column so we can reference them as row(0) and column(0).