Having some trouble connecting to a database....

I am doing one of the Microsoft virtual labs "Creating ASP.NET Web Applications with C# - Part 2" using Visual Web Developer Express. I am trying to fill a gridview with database information, but it gives me the error

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)".

This is on the local machine that I am executing the code.

public void BindGrid(string sortfield) {//Create DataAdapter to fetch data from Prodcuts table SqlDataAdapter myCommand =new SqlDataAdapter("select * from Products", myConnection);//Create dataset and fill it with Product data DataSet ds =new DataSet(); myCommand.Fill(ds,"Products");//Bind Product data to Datagrid DataView Source = ds.Tables["Products"].DefaultView; Source.Sort = sortfield; dgProducts.DataSource = Source; dgProducts.DataBind(); }

It stops on the line "myCommand.Fill(ds, "Products"); and points out the error mentioned earlier. I am not sure what to do...any help would be greatly appreciated, thanks in advance, and I apologize as well if I have not pointed out enough information.

What does your connection string look like?|||

check this link for help:

http://support.microsoft.com/kb/914277/en-us

Thanks

|||

First of all, thank you both greatly for responding.

Smalltalk, here is the connection string:

myConnection = new SqlConnection("server=localhost;database=northwind;Trusted_Connection=yes");

Jpazgier, my connections are set at local and remote connections, plus the server is on the same machine, I have no idea why it mentions that error for me.

Thanks again, guys.

|||

Hi GWShane,

Please make sure that the server is the default instance on your machine. If you're using SQL Express, the default name for this instance might be SQLExpress. In this case, you might need to add the instance name to the DataSource like:

myConnection = new SqlConnection("server=localhost\\SQLExpress;database=northwind;Trusted_Connection=yes");

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !

Handling Null fields with three-tier architecture

I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -

Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'"
Dim message As String = ""
Dim Found As Boolean = False
Try
Using connection As New SqlConnection(GDB_AppSettings.Connection)
Dim command As New SqlCommand(queryString, connection)
connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

If reader.Read() Then
Found = True
_USRid = reader("USRid")
_USRPassword = reader("USRPassword")
_USREmail = reader("USREmail")
_USRTitle = reader("USRTitle")
_USRName = reader("USRName")
_USRRole = reader("USRRole")
If IsDBNull(reader("USRAgreedConditions")) = False Then
_USRAgreedConditions = reader("USRAgreedConditions")
End If
End If
reader.Close()
End Using
Catch ex As Exception
If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then
Return "Cannot open database to logon"
Else
Return Err.Description
End If
End Try

the new version is much more elegant: -

Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()

Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid)
If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible
Return "User not found"
End If

Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0)
_USRid = userid
_USRPassword = password
_USREmail = trUser.USREmail
_USRTitle = trUser.USRTitle
_USRName = trUser.USRName
_USRRole = trUser.USRRole
_USRAgreedConditions = trUser.USRAgreedConditions

However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -

System.Data.StrongTypingException was unhandled by user code
Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull."
Source="App_Code.wt2vzoc1"
...

There is no point in writing: -
If Not IsDBNull(trUser.USRAgreedConditions) Then
_USRAgreedConditions = trUser.USRAgreedConditions
End If
because the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.

It seems that my only options are: -
1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture
2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?
3. Code a Try block within the business layer: -
Try
_USRAgreedConditions = trUser.USRAgreedConditions
Catch exAs Exception
_USRAgreedConditions =Nothing
EndTry

This seems to work OK, but seems less elegant than the original code in the old version: -
If IsDBNull(reader("USRAgreedConditions")) = False Then
_USRAgreedConditions = reader("USRAgreedConditions")
End If

Is there a better way?

Hi Robert my friend,

There is a better way. Change your query to list all of the fields within the table that you want and set the USRAgreedConditions field to a specific valid date if it is NULL via the ISNULL() function. The SQL below will return today's date in this field where it is NULL but you can change it to use any valid date value that you wish.

SELECT USRid, USRPassword, URSEmail, USRTitle, USRName, USRRole,

ISNULL(USRAgreedConditions, GetDate()) AS USRAgreedConditions

FROM NZGDB_User

For this reason, it is not good practice to rely solely on date fields for business logic. If this field being null indicates that the conditions have not been agreed yet, it would be better to have an additional BIT field that indicates Yes or No and then the USRAgreedConditions field would simply be a field containing the date information for those that are set to 1 (Yes).

Kind regards

Scotty

|||

Thanks Scotty, that's certainly a neater solution, and I'll progressively use it. Unfortunately my code is now riddled with Try/End try blocks to handle Null fields, so it will take some time to get rid of them all.

Your solution will certainly work in most, perhaps all, of my situations. However I wonder if there isn't a lurking problem. The point of NULL is to distinguish "No value" from "Default value". Sometimes you need to make the distinction in your code, and there is a risk that whatever value you choose for VALUE in
ISNULL(USRAgreedConditions, VALUE) AS USRAgreedConditions
will coincide with a real value. Getdate() won't do, and neither will 0 (which becomes 1/1/1900) as either could be a valid date in this application. In this case I can use Getdate()+20 and test for a future date (which is otherwise logically impossible), but I have to be careful to remember to check that my logic doesn't write these artificial values back to the database. I tried using
DIM USRAgreeConditions as NULLABLE (OF Datetime)
but found myself getting into a lot of trouble with errors popping up elsewhere.

It's a pity that the designers of the new NULLABLE feature didn't make the handling of all potentially-null fields consistent. Thus I find that there is little trouble with String fields (CHAR, VARCHAR, etc in the database), as I can simply set the column's Nullvalue property to Empty. It's just silly that I can't do this for GUID and Date (and any other type) fields. There are also some idiocies in the way that operator usage changes with NULLABLE fields. Enough raving!

Thanks for your help.

Regards, Robert

|||

Hi Robert,

You can also try this within the query. In addition to generating a valid date for your column, have another column that inidcates whether or not it was generated so that you can check this extra column in the code to determine how to process the date field: -

SELECT ISNULL(USRAgreedConditions, GetDate()) AS USRAgreedConditions,
CASE WHEN USRAgreedConditions IS NULL THEN 'Yes' ELSE '' END AS IsNullDate
FROM TableName

Kind regards

Scotty

|||

Thanks Scotty, yes that's exactly what I want. Something like this (returning the default value of Nothing and an indicator of whether it's a real null or not is what Microsoft should be doing automatically: in the meantime I guess this is the best we can do.

Thanks again, Robert