SQL Connection prob from ASP

R

rothjm

Member
Joined
May 25, 2005
Messages
8
Location
KS, USA
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 dont 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:
Code: 
visual basic 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()

Thanks for any assistance! Jeff
 
Heres what I see at first look. You are setting myCmd equal to the connection objects command and it needs to be the other way around.

Replace
Code: 
        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()

With
Code: 
        Create a SQL Command Object to query Patient by MedRec number
        With 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 & ""
            .CommandType = CommandType.Text
            .Connection = objConnection
        End With

        objConnection.Open
        myReader = myCmd.ExecuteReader()

That should do it for you.

Now... You mention that you are accessing a SQL Server. I would recommend taking your CommandText and creating a Stored Procedure out of it. Then change .CommandType = CommandType.Text to .CommandType = CommandType.StoredProcedure.

For Example:
Stored Procedure
Code: 
Create Procedure dbo.GetPatientByMedRec
@MedRec int

AS

Select Med_Rec#, RAD#, First_Name, Last_Name, Sex, Birth_Date, Soc_Sec#, Last_Exam_Date From tblPatient WHERE Med_Rec#= @MedRec

Command Object
Code: 
    With objCommand
      .CommandText = "GetPatientByMedRec"
      .CommandType = CommandType.StoredProcedure
      .Connection = objConnection
      .Parameters.Add("@MedRec", SqlDbType.Int).Value = txtGetMedRec.Text
    End With
 
Thank you very much for your response!
I tried your advice (with the sp and the Select).
Now I am getting this error on the .CommandText line:
Code: 
Server Error in /XrayHistory Application.
--------------------------------------------------------------------------------

[COLOR=Red]Object reference not set to an instance of an object[/COLOR]. 
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.NullReferenceException: Object reference not set to an instance of an object.

Source Error: 
 
Line 75:         Create a SQL Command Object to query Patient by MedRec number
Line 76:         With myCmd
Line 77:             .CommandText = "sp_GetPatientByMedRec"
Line 78:             .CommandType = CommandType.StoredProcedure  GETTING THE ERROR HERE
Line 79:             .Connection = objConnection


I created the stored procedure and I can run it in query analyzer with the parameter in single quotes.

Code: 
 Dim myCmd As SqlCommand

        Create a SQL Command Object to query Patient by MedRec number
        With myCmd
            .CommandText = "sp_GetPatientByMedRec"
            .CommandType = CommandType.StoredProcedure
            .Connection = objConnection
            .Parameters.Add("MedRec", SqlDbType.Char).Value = txtGetMedRec.Text
        End With

Here is the stored procedure:
Code: 
CREATE PROCEDURE sp_GetPatientByMedRec 
@MedRec as VARCHAR(10)

AS 

Select Med_Rec#, RAD#, First_Name, Last_Name, Sex, Birth_Date, Soc_Sec#, Last_Exam_Date 
From tblPatient 
WHERE Med_Rec#= @MedRec
GO

Thanks again for the assistance!!!
 
Actually, I am still getting the "General Network Error. Check you Network documentation" outside of my developer.
It runs fine in the developer...but when I try it via the web browser it gets the error....I can click on back and try it again and it will work ok....because after the error, I am logged into SQL (I believe is why it works the second time). If I dont kill the SQL connection it will work the first try.
 
A little more info.
I commented out the bottom query (and fill dataAdaper) and the top part works great.
When I comment out the top query (and fill the text boxes) I get the problem.
So I think something is wrong with the Procedure query/connection/fill DataAdaper.

Thanks for any insight!!!
 
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 dont 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!!!



Code:
Code: 
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:
Code: 
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: [COLOR=Red]Line 97[/COLOR]


Line 95: 
Line 96:         objDataSet = New DataSet
[COLOR=Red]Line 97:         objDataAdapter.Fill(objDataSet, "Med_Rec#")[/COLOR]
Line 98:         grdPatient.DataSource = objDataSet.Tables("Med_Rec#").DefaultView
Line 99:         grdPatient.DataBind()
 
Back
Top