SQL Connection prob from ASP

[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:

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

[Aspnot]

Here's what I see at first look. You are setting myCmd equal to the connection object's command and it needs to be the other way around.

 

Replace

       '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

       '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

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

   With objCommand
     .CommandText = "GetPatientByMedRec"
     .CommandType = CommandType.StoredProcedure
     .Connection = objConnection
     .Parameters.Add("@MedRec", SqlDbType.Int).Value = txtGetMedRec.Text
   End With

[rothjm]

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:

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.

 

 
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:

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!!!

[kahlua001]

From looking at your recent code, you should do

 

myCmd = new Sqlcommand

[rothjm]

Thanks kahlua001, that got me going!

[rothjm]

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 don't kill the SQL connection it will work the first try.

[rothjm]

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!!!

[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!!!

 

 

 

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:

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()