SQL Connection prob from ASP [Resolved]

[rothjm]

VB.NET 2003 and SQL 2000
I am attempting to create a web form to query a SQL DB to post xray history using Windows authentication.
Currently the test IIS is running on my XP PC and the SQL server is on the network (domain).

The Form works fine when I run it from the VB Designer.
When I run it from just a browser it fails the first time with a General Network Error. Check you Network documentation. If I try it again it works....I think because my code does not disconnect from the sql server even if I close the browser. When I get the initial error, I can look in the Ent Manager and see that I did get connected to the SQL server. I looked up the error on the Microsoft site and it says the SQL side is not configured for SSL...I don't have SSL configured on the IIS server. I do have the domain authentication turned on and the anonymous turned off in the IIS.

So 2 questions.
1). Why does my app get the Network Error and how do I fix it?
2). How do I make the app close the connection to the SQL server?

Here is my code:

Imports System.Data
Imports System.Data.SqlClient
 
Public Class WebForm1
    Inherits System.Web.UI.Page
    'Protected WithEvents grdPatient As System.Web.UI.WebControls.DataGrid
    'Declare a Connection object that is global in scope
    Dim objConnection As SqlConnection
 
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        'Initialize the Connection Object...
        objConnection = New SqlConnection("Server=TESTSQL1; Database=XRAY; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog = XRAY; ") 'Trusted_Connection = True")
    End Sub
 
    Private Sub btnFindPatient_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFindPatient.Click
        'Declare Objects...
        Dim objDataSet As DataSet
        Dim objDataAdapter As SqlDataAdapter
        Dim myCmd As SqlCommand
        Dim myReader As SqlDataReader
        Dim RsltMedRec As String
        Dim RsltRad As String
        Dim RsltPatient As String
        Dim RsltSex As String
        Dim RsltBirthDate As String
        Dim RsltSocSec As String
        Dim RsltLastExam As String
        Dim strMed_REC As String
 
        'Assign the value in the input box to the query variable
        strMed_REC = txtGetMedRec.Text
 
        'Create a SQL Command Object to query Patient by MedRec number
        myCmd = objConnection.CreateCommand
        myCmd.CommandText = "SELECT Med_Rec#, RAD#, First_Name," & _
        "Last_Name, Sex, Birth_Date, Soc_Sec#, Last_Exam_Date " & _
        "From tblPatient " & _
        "WHERE Med_Rec#= '" & strMed_REC & "'"
 
        objConnection.Open()
        myReader = myCmd.ExecuteReader()
 
        'Place the query Results into the result strings
        Do While myReader.Read()
            RsltMedRec = myReader.GetString(0)
            RsltRad = myReader.GetString(1)
            RsltPatient = myReader.GetString(3) & ", " & myReader.GetString(2)
            RsltSex = myReader.GetString(4)
            RsltBirthDate = myReader.GetString(5) & "   "
            RsltSocSec = myReader.GetString(6)
            RsltLastExam = myReader.GetString(7)
        Loop
 
        'Display Results on the web form...
        lblMedRec.Text = RsltMedRec
        lblRad.Text = RsltRad
        lblPatient.Text = RsltPatient
        lblSex.Text = RsltSex
        lblBirthDate.Text = RsltBirthDate
        lblSocSec.Text = RsltSocSec
        lblLastExam.Text = RsltLastExam
        'Close(DataReader)
        objConnection.Close()
 
        'Set the SQL Query String to pull all the procedures...
        objDataAdapter = New SqlDataAdapter("SELECT Proc_Date_Time as 'Proc Date/Time'," & _
        "Dept, PROC#, Proc_Description As 'Proc Desc'," & _
        "Ordering_Phy_Last As 'Order Phy LastName'," & _
        "Ordering_Phy_Firs As 'Order Phy FirstName'" & _
        "FROM tblProcedure WHERE Med_Rec# = '" & strMed_REC & "'" & _
        "Order By Proc_Date_Time DESC", objConnection)
 
        'Initialize the DataSet object and fill it...
        objDataSet = New DataSet
        objDataAdapter.Fill(objDataSet, "tblProcedure")
 
        'Declare a DataView Object, populate it, and sort the data in it...
        Dim objDataView As DataView = objDataSet.Tables("tblProcedure").DefaultView
        grdPatient.DataSource = objDataView
        grdPatient.DataBind()
        'Close Connection
        objConnection.Close()
    End Sub
End Class

The error is on line 79: MyReader = myCmd.ExecuteReader()
TIA, I am a wooky, oops I mean rookie! - Jeff

[indydavid32]

I think you are getting the error because you are trying to use windows integrated security. I use the sql user name and password. That way, it won't matter who is logged into the machine, the program will work.

Here is an example of the sql connection that I use.

Dim cn As New SqlClient.SqlConnection()
 
cn.ConnectionString = "Integrated Security=True;" & _
"Data Source=Server1;Initial Catalog=Pubs;" & _
"user id=UserName;password=Password;"
cn.Open()

On your second question, cn.close will close the connetion. cn.dispose will completly remove it from memory. I do both.

[rothjm]

The bossman would really like to use the Windows Authentification...then if it fails use the SQL login.
Anyone have any ideas for me to try?

The error is on line 79: MyReader = myCmd.ExecuteReader()
I also tried:
myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

I get the same error:

General network error. Check your network documentation. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: General network error. Check your network documentation.

Source Error: 


Line 79: 
Line 80:         objConnection.Open()
Line 81:         myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection)
Line 82: 
Line 83:         'Place the query Results into the result strings
 

Source File: c:\inetpub\wwwroot\XrayHistory\XrayHistory.aspx.vb    Line: 81 

Stack Trace: 


[SqlException: General network error.  Check your network documentation.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   XrayHistory.WebForm1.btnFindPatient_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\XrayHistory\XrayHistory.aspx.vb:81
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1292

TIA - Jeff

[rothjm]

A little more info:
I commented out the bottom query (Procedure) and the top query (Patient) runs just fine. When I comment out the top part I still get the error.
Once it fails once...it runs fine because I am still logged into the SQL server.
The problem has to be with the second query/connection/DataAdapter.

Thanks for any insight!!!

[rothjm]

I am still getting the network error the first time I try to run the program...after it fails the first time it leaves my login connection on the sql server and then it works everytime (as long as I don't kill my connection).
I condensed my code down to try to find the problem. I also changed to run a stored procedure. Also tried hard coding the username and password (same result). I think the problem is where I fill the dataset and bind to the grid.
Can someone look at that section and see if there is anything wrong.
Thanks for any advice!!!

Imports System.Data
Imports System.Data.SqlClient
'Imports System.Security

Public Class WebForm1
    Inherits System.Web.UI.Page
    'Protected WithEvents grdPatient As System.Web.UI.WebControls.DataGrid
    'Declare a Connection object that is global in scope
    Dim objConnection As SqlConnection

#Region " Web Form Designer Generated Code "

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        'Initialize the Connection Object...
        objConnection = New SqlConnection("Server=TESTSQL1\MISC; Database=XRAY; Integrated Security=SSPI; Persist Security Info=FALSE;")
        'objConnection = New SqlConnection("Server=TESTSQL1\MISC; Database=XRAY; uid = XX; pwd = XXXXXX;")
    End Sub

    Private Sub btnFindPatient_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFindPatient.Click
        'Declare Objects...
        Dim objDataSet As DataSet
        Dim objDataAdapter As SqlDataAdapter
        Dim myCmd As SqlCommand
        Dim myReader As SqlDataReader

        'Assign the value in the input box to the query parameter
        strMed_REC = txtGetMedRec.Text

        objDataAdapter = New SqlDataAdapter("sp_GetProcByMedRec", objConnection)
        objDataSet = New DataSet
        objConnection.Open()
        objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
        objDataAdapter.SelectCommand.Parameters.Add("@MedRec", SqlDbType.Char).Value = strMed_REC
      
        objDataSet = New DataSet
        objDataAdapter.Fill(objDataSet, "Med_Rec#")
        grdPatient.DataSource = objDataSet.Tables("Med_Rec#").DefaultView
        grdPatient.DataBind()

        objConnection.Close() 'Close Connection
        objConnection.Dispose() 'Clears Memory        
    End Sub
End Class

Error:

General network error. Check your network documentation. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: General network error. Check your network documentation.

Source Error: Line 97


Line 95: 
Line 96:         objDataSet = New DataSet
Line 97:         objDataAdapter.Fill(objDataSet, "Med_Rec#")
Line 98:         grdPatient.DataSource = objDataSet.Tables("Med_Rec#").DefaultView
Line 99:         grdPatient.DataBind()