I am trying to retreive some data from the SQLDatabase using the following code.

<WebMethod(Description:="This gets certain information about the chemical searching via the Chemical state")> _
       Public Function getChemicalState(ByVal sChemState As String) As String

        Dim connectionString As String = "server='(local)'; user id='username'; password='password'; database='Chemical'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionStri ng)

        Dim myCommand As SqlCommand
        myCommand = New SqlCommand("usp_ChemicalState", dbConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim dbParam_chemicalstate As SqlParameter = New SqlParameter("@ChemState", SqlDbType.NVarChar, 6)
        dbParam_chemicalstate.Value = sChemState
        myCommand.Parameters.Add(dbParam_chemicalstate)

        Dim myDataSet As New DataSet("GET_CHEMICAL_INFORMATION")
        Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCommand)
        myDataAdapter.Fill(myDataSet)
        myDataSet.Tables(0).TableName = "CHEMICAL_INFORMATION"
        myDataSet.Tables(1).TableName = "TREATMENT_INFORMATION"

        Return myDataSet.GetXml

    End Function

When it is run and the search value is entered and you click invoke it then opens a web page and the error HTTP 500 Internal server error. Does anyone know where am going wrong.

Someday you eat the bar and sometimes the bar eats you

Can you place a break point at the beginning of your function and then run it in debug mode? Then you can step through the code and find the exact statement that errors out.

-Manish Jadhav