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 inserting into the database table

I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code:
Dim Message As String
Dim connStr As String
Dim myConnection As SqlConnection
Dim mySqlCommand As SqlCommand
connStr = "server=SIMI\VSdotNET;Trusted_Connection=yes;database=AeroSea"
myConnection = New SqlConnection(connStr)
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)

If I execute the above code, then nothing gets updated. When I change the insert staement into the following,
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then,
value 1 for customerID field and myname in the namefield is added.
I know I am doing something stupid, but can't figure it out.
I am confused. please help me.One possibility is this (presuming textbox is name):

mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'" + name.text + "')", myConnection)

Better,use parameters, as the code above is subject to SQL Injection attacks, and as written will fail if the name.Text is "O'Reilly".|||Thanks, I used the parameters and it works fine.

Having Different Versions of Crystal Reports (8,9) in the same web application

See, We have a web application which is developed using Webclass Designer and currently runs on IIS 4.0 and IIS 5.0. It also contains a Reports Menu thorugh which the end user sees the data. These reports are mainly displayed using Crystal Reports 8.0.0.371. There are around 110 reports. The client wants specifically only couple of these reports to be migrated to version 9.0 leaving the rest. The reports are mainly opened, passed with SQL query etc in a single class file. So this is common for all the reports. Just that the object of this class file is created in every instance. The reference of CRAXDRT.dll of version 8 is added as a reference in this class file so that objects can be created.



Now my question is , is it possible to have two different versions of Crystal Reports in the same application. It means that ideally the support files are different. Also , it will be not possible for the report’s class file present in VB to refer both 8 and 9 at the same time. This is what I feel but as usual the client needs some support docs if this is true. Hence I need ur expert opinion on this.


Any help in this issue ...Please share ur expert opinionsHi,
I wish to do the same, did you manage to solve this problem, or does any1 else have any input?

Have two active SQL servers

Right now I am working on a project that requires two active SQL servers with
different local web servers accross geographic locations. I read an article
on Active-Active SQL servers and am seeing some possiblity in SQL 2005 for
this situation to work but in SQL 2000 is there any way to have two servers
with independent RAID arrays sync over the network to have different users
modifying records?
Thanks,
Joseph Ronzio
You are basically asking if you can use Majority Node Set (MNS) clustering.
SQL 2000 does not support it. Some third parties does have excellent
products that work. NSI is one that comes to mind, LeftHand software, etc.
Check my website for more.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:772EC28E-A275-42B5-A610-3925F0C3542B@.microsoft.com...
> Right now I am working on a project that requires two active SQL servers
> with
> different local web servers accross geographic locations. I read an
> article
> on Active-Active SQL servers and am seeing some possiblity in SQL 2005 for
> this situation to work but in SQL 2000 is there any way to have two
> servers
> with independent RAID arrays sync over the network to have different users
> modifying records?
> Thanks,
> Joseph Ronzio
|||And SQL Server Clustering does not support 2 nodes modifying data in the
same physical DB (neither does Oracle or Sybase clustering).
If you need 2 nodes modifying data, look at replication.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> You are basically asking if you can use Majority Node Set (MNS)
clustering.[vbcol=seagreen]
> SQL 2000 does not support it. Some third parties does have excellent
> products that work. NSI is one that comes to mind, LeftHand software, etc.
> Check my website for more.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:772EC28E-A275-42B5-A610-3925F0C3542B@.microsoft.com...
for[vbcol=seagreen]
users
>
|||So MySQL, Lotus Domino and IBM DB2 can do this but MS SQL can't? From my
reading it looks like SQL 2005 might be able to do this will it?
Thanks,
Joseph Ronzio
"Mike Epprecht (SQL MVP)" wrote:

> And SQL Server Clustering does not support 2 nodes modifying data in the
> same physical DB (neither does Oracle or Sybase clustering).
> If you need 2 nodes modifying data, look at replication.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> clustering.
> for
> users
>
>
|||Hi
No, SQL Server 2005 will not. There will still be one primary node that
handles all the work.
Clustering by definition, is for high availability, not load balancing.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:E2BF74E7-ADEE-4C9F-B183-44D1D6C87122@.microsoft.com...[vbcol=seagreen]
> So MySQL, Lotus Domino and IBM DB2 can do this but MS SQL can't? From my
> reading it looks like SQL 2005 might be able to do this will it?
> Thanks,
> Joseph Ronzio
> "Mike Epprecht (SQL MVP)" wrote:
etc.[vbcol=seagreen]
servers[vbcol=seagreen]
2005[vbcol=seagreen]
|||That's only true about SQL Server!
Oracle RAC (and 10g grid) does support modifying the same shared physical DB
from any node. Basically, the same physical database is exposed to multiple
nodes with consistency controlled via distributed lock manager. I believe
Sybase has also just started to offer such a feature.
In theory at least, this approach offers both failover (HA) and scalability
(dynamic load balancing over more nodes since a query can be routed to any
node for processing).
Linchi
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:O6S7HdG5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> And SQL Server Clustering does not support 2 nodes modifying data in the
> same physical DB (neither does Oracle or Sybase clustering).
> If you need 2 nodes modifying data, look at replication.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> clustering.
> for
> users
>
|||Ok apparently there was some confusion when I posted this I'm not talking the
same physcial database I am talking about replication where two database
servers have the same database but different copies. Can both database
servers have modification made to the database and replicate changes?
"Mike Epprecht (SQL MVP)" wrote:

> And SQL Server Clustering does not support 2 nodes modifying data in the
> same physical DB (neither does Oracle or Sybase clustering).
> If you need 2 nodes modifying data, look at replication.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> clustering.
> for
> users
>
>
|||You can do two-way replication, but what do you gain? Both servers not only
have to handle all the updates but they have to handle synching them with
their partner. The nodes won't split the work unless the database uses
partitioned views, something that only fits a very few scenarios.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:41FDC5C9-93E1-4639-BDAC-BE3648F322A2@.microsoft.com...
> Ok apparently there was some confusion when I posted this I'm not talking
the[vbcol=seagreen]
> same physcial database I am talking about replication where two database
> servers have the same database but different copies. Can both database
> servers have modification made to the database and replicate changes?
> "Mike Epprecht (SQL MVP)" wrote:
etc.[vbcol=seagreen]
servers[vbcol=seagreen]
2005[vbcol=seagreen]
|||I would get the capability to have two servers in different geographic
regions with two different web farms for referencing and modifying data.
Granted there could be conflicts generated but in Lotus Domino today I don't
have that problem with our typical business records and I'd be migrating the
same application to SQL. I would just need the resources that would instruct
me how to do this for SQL.
"Geoff N. Hiten" wrote:

> You can do two-way replication, but what do you gain? Both servers not only
> have to handle all the updates but they have to handle synching them with
> their partner. The nodes won't split the work unless the database uses
> partitioned views, something that only fits a very few scenarios.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:41FDC5C9-93E1-4639-BDAC-BE3648F322A2@.microsoft.com...
> the
> etc.
> servers
> 2005
>
>
|||Have you considered Merge Replication?
Or if you want to use Transactional Replication then you can use Transactional Replication with Updating Subscribers.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Hardware Requirements

I'm a code monkey ... I don't know these things ...
Is anyone aware of any guidelines as far as what kind of hardware (boxes in
the web farm, sql server machines, etc) is required for a given amount of
throughput?
You may want to start from:
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
Anith
|||There is no guideline for deciding your server configuration in abstract for
'a given amount of throughput'. If possible, collect some performance stats
with your real app in whatever environment it may be in, and use that as the
starting point.
Linchi
"Dave T" wrote:

> I'm a code monkey ... I don't know these things ...
> Is anyone aware of any guidelines as far as what kind of hardware (boxes in
> the web farm, sql server machines, etc) is required for a given amount of
> throughput?

Hardware Requirements

I'm a code monkey ... I don't know these things ...
Is anyone aware of any guidelines as far as what kind of hardware (boxes in
the web farm, sql server machines, etc) is required for a given amount of
throughput?You may want to start from:
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
--
Anith|||There is no guideline for deciding your server configuration in abstract for
'a given amount of throughput'. If possible, collect some performance stats
with your real app in whatever environment it may be in, and use that as the
starting point.
Linchi
"Dave T" wrote:
> I'm a code monkey ... I don't know these things ...
> Is anyone aware of any guidelines as far as what kind of hardware (boxes in
> the web farm, sql server machines, etc) is required for a given amount of
> throughput?

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware that
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application design
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.
Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware that
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application design
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
--
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware tha
t
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application desig
n
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

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