Hi all,

I followed the Visual Web Developer 2005 Express Guided Tour (http://www.asp.net/guidedtour2/s14.aspx) to program the "Authors" web site to do "Object Data Binding, Adding Update Functionality". I opened  the ObjectDataSource Tasks menu. When I clicked Configure Data Source,  I got the following error message: The type 'DataAccess' could not be loaded. If the type is located in the App_Code folder, please check that it compiles. If the type is located in a complied assembly, please check that the assembly is referenced by the project.    Could not load type 'DataAccess' from assembly 'Microsoft.VisualStudio.Web, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f11d50a3a'.

I tried to Build "App_Code/DataAccess.vb" and I got the following error message: Error 1 Statement is not valid in a namespace. C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2005\WebSites\Authors\App_Code\DataAccess.vb 12 1 C:\...\Authors\.

Question #1:  I have a downloaded VWD 2005 Express, version 9.0  in my PC.   Is the version 9 of VWD 2005 Express the cause of the 1st error message "Could not load type 'DataAccess' from assembly 'Microsoft.VisualStudio.Web, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f11d50a3a'." ?

Question #2: How do I compile the "App_Code/DataAccess.vb" to solve the problem?

Please help and advise.

Thanks in advance,

Scott  Chang 

Hi Mikhail,  Thanks for your response.

1) Yes, you are right - VWD Express as Visual Studio is version 8, not 9.  I was confused with the SQL Server 2005 Management Studio Express  that is version 9.0. 

2) I have the VWD Express and SQL Server 2005 Management Studio Express  installed in my   Microsoft Windows XP Pro PC that is on Microsoft NT 4 LAN System. Our Computer Administratoer grants me the Administrator Privilege to use the VWD Express.  What is the cause of the problem I stated in the Quetion #1 (i. e. "Could not load type 'DataAccess' from assembly 'Microsoft Visual Studio 2005 WebSites\Authors\App_Code\DataAccess.vb 12 1 C\...\Authors\)? 

Server Error in '/Authors' Application.

---

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30001: Statement is not valid in a namespace.

Source Error:

Line 10: End Class
Line 11: 
Line 12: Public Sub UpdateAuthor(ByVal au_lname As String, ByVal au_fname As String, _
Line 13:     ByVal phone As String, ByVal address As String, ByVal city As String, ByVal state As String, _
Line 14:     ByVal zip As String, ByVal contract As Boolean, ByVal Original_au_id As String)

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE> "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe" /t:library /utf8output /R:"C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll" /R:"C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll" /R:"C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll" /R:"C:\WINDOWS\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll" /R:"C:\WINDOWS\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll" /R:"C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll" /R:"C:\WINDOWS\assembly\GAC_MSIL\System.Web.Mobile\2.0.0.0__b03f5f7f11d50a3a\System.Web.Mobile.dll" /R:"C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll" /R:"C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll" /out:"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\authors\8d6e3bb4\88dba598\App_Code.lb26sr34.dll" /D:DEBUG=1 /debug+ /define:_MYTYPE=\"Web\" /imports:Microsoft.VisualBasic,System,System.Collections,System.Collections.Specialized,System.Configuration,System.Text,System.Text.RegularExpressions,System.Web,System.Web.Caching,System.Web.SessionState,System.Web.Security,System.Web.Profile,System.Web.UI,System.Web.UI.WebControls,System.Web.UI.WebControls.WebParts,System.Web.UI.HtmlControls  "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\authors\8d6e3bb4\88dba598\App_Code.lb26sr34.0.vb" "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\authors\8d6e3bb4\88dba598\App_Code.lb26sr34.1.vb" "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\authors\8d6e3bb4\88dba598\App_Code.lb26sr34.2.vb"


Microsoft (R) Visual Basic Compiler version 8.0.50727.42
for Microsoft (R) .NET Framework version 2.0.50727.42
Copyright (c) Microsoft Corporation.  All rights reserved.

C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2005\WebSites\Authors\App_Code\DataAccess.vb(12) : error BC30001: Statement is not valid in a namespace.

Public Sub UpdateAuthor(ByVal au_lname As String, ByVal au_fname As String, _
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ByVal phone As String, ByVal address As String, ByVal city As String, ByVal state As String, _
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ByVal zip As String, ByVal contract As Boolean, ByVal Original_au_id As String)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Line 1:    Imports Microsoft.VisualBasic
Line 2:    
Line 3:    Public Class DataAccess
Line 4:        Public Function GetAuthors(ByVal state As String) As AuthorsDataSet
Line 5:            Dim authorsTableAdapter As New AuthorsDataSetTableAdapters.authorsTableAdapter
Line 6:            Dim authorsData As New AuthorsDataSet()
Line 7:            authorsTableAdapter.Fill(authorsData.authors, state)
Line 8:            Return authorsData
Line 9:        End Function
Line 10:   End Class
Line 11:   
Line 12:   Public Sub UpdateAuthor(ByVal au_lname As String, ByVal au_fname As String, _
Line 13:       ByVal phone As String, ByVal address As String, ByVal city As String, ByVal state As String, _
Line 14:       ByVal zip As String, ByVal contract As Boolean, ByVal Original_au_id As String)
Line 15:       Dim authorsTableAdapter As New AuthorsDataSetTableAdapters.authorsTableAdapter
Line 16:       authorsTableAdapter.Update(Original_au_id, au_lname, au_fname, phone, address, city, state, _
Line 17:          zip, contract, Original_au_id)
Line 18:   End Sub
Line 19:   
Line 20:   

function OnToggleTOCLevel1(level2ID) { var elemLevel2 = document.getElementById(level2ID); if (elemLevel2.style.display == 'none') { elemLevel2.style.display = ''; } else { elemLevel2.style.display = 'none'; } }

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

3) In the last few day, I did some searches on the DataAccess and Data Access Layer (DAL) from Google and Microsoft MSDN and I found 2 diffirent sets of source codes for the same "Authors" DataAccess scripts:

(i)  In the http://www.asp.net/guidedtour2/s14.aspx, I found the following:

Adding Update Functionality

You've seen how your GridView control lost some functionality when using an object data source that only provided a method for data retrieval. You will now see how to add methods to update the database encapsulated by the object data source. This will, in turn, enable you to add this functionality to your GridView control, all with only a small amount of code.

1. Select the App_Code/DataAccess.vb (or .cs) tab. To the DataAccess class add methods for updating records in the database, removing the line breaks required for Web presentation.

public void UpdateAuthor(string au_lname, string au_fname, string phone, string address, string city, 
    string state, string zip, bool contract, string Original_au_id)
{
    AuthorsDataSetTableAdapters.authorsTableAdapter authorsTableAdapter = 
      new AuthorsDataSetTableAdapters.authorsTableAdapter();
    authorsTableAdapter.Update(Original_au_id, au_lname, au_fname, phone, address, 
      city, state, zip, contract, Original_au_id);
}

Public Sub UpdateAuthor(ByVal au_lname As String, ByVal au_fname As String, _
    ByVal phone As String, ByVal address As String, ByVal city As String, ByVal state As String, _
    ByVal zip As String, ByVal contract As Boolean, ByVal Original_au_id As String)
    Dim authorsTableAdapter As New AuthorsDataSetTableAdapters.authorsTableAdapter
    authorsTableAdapter.Update(Original_au_id, au_lname, au_fname, phone, address, city, state, _
        zip, contract, Original_au_id)
End Sub

Notice that there are only two lines of code. You are taking advantage of the new TableAdapter class's inherent support for updating a database. It was able to generate an SQL UPDATE statement automatically based on your SQL SELECT statement. The data access class merely wraps the existing TableAdapter logic, passing in the required arguments.

When you make changes to the data access layer you must update the ObjectDataSource configuration.

2.  Click the Authors.aspx tab. In the Properties window, for the ObjectDataSource, set OldValuesParameterFormatString=Original_{0}. Next, open the ObjectDataSource Tasks menu. Click Configure Data Source and then click Next. In the Define Data Methods step, click the Update tab. For Choose a method select UpdateAuthor. Click Next and then click Finish.

that was what I used in my "Authors" App-Code.  But, in the http://www.guancosonline.org/MSNETSV/downloads/beta.aspnet.GuidedTour.doc, I saw the following:

Object data binding follows the well known design pattern of placing a data access layer into your application.  In it's simplest form, a data access layer encapsulates all the database interactions so database calls are not present in your presentation layer.  Placing SQL statements within your presentation is well recognized as a poor practice for production web applications.  We just witnessed in the last chapter how easy it is to use data bound controls, but the SQL data source control placed the SQL statements directly into the presentation layer.

VWD encapsulates the design pattern of data access layers by allowing you to bind an object as a data source.  The object (your data access layer) will need to still provide the services the SQL data source performed, such as executing a SELECT or UPDATE statement.

In this chapter you will build a simple data access layer and use an object data source.  You will bind it to a GridView control, and it will then execute your code to obtain SQL information rather than direct SQL statements within the presentation layer.

All code not directly supporting your web pages (meaning NOT the code behinds) should be stored in a folder called Code.  This is where all your middle tier code will be stored, such as your data access layer.  VWD will look for your code in the Code folder.

The data access layer will encapsulate the SQL statements that we saw in the earlier section that was embedded in your HTML page.

1.    Create a new folder called Code in your project.  Right click on the root of your project and select New Folder.  Call the folder Code.

2.    Right click on the Code folder and select Add New Item....  Select Class and call it DataAccess.vb.

The source code for the DataAccess.vb class (what you see below) can be downloaded.

2.   Download this and replace your DataAccess.vb class with the downloaded file.

The C# version can also be downloaded.

Imports System.Data

Imports System.Data.SqlClient

Public Class DataAccess

The GetAuthors shared function accepts a state that will be used in the WHERE clause of the select statement.  We need to be able to accept the value from the DropDownList control

    Public Shared Function GetAuthors(ByVal state As String) As DataSet

The connection string stored in the web.config is obtained in order to be able to connect to the authors table.

        Dim connstr As String = 

                    ConfigurationSettings.ConnectionStrings("AppConnectionString1").ConnectionString                            

The following code is standard ADO.NET code to invoke dynamic SQL queries and obtain a DataSet of information.  In practice you should use stored procedures in your code and avoid using dynamic SQL.

        Dim connection As New SqlConnection(connstr)

        Dim command As New SqlCommand()

        command.Connection = connection

        command.CommandType = CommandType.Text

        command.Parameters.Add(New SqlParameter("@state", state))

        command.CommandText = "SELECT * FROM Authors WHERE state=@state"

        Dim adapter As New SqlDataAdapter(command)

        Dim ds As New DataSet

        adapter.Fill(ds)

A DataSet is returned to the calling class.  In this chapter we will be binding this returned DataSet to the GridView control.

        Return ds

    End Function

Because we have allowed editing in the GridView, the UPDATE SQL statement was being used.  Therefore, we need to implement code that will support updating of records.  The parameter names in the function below exactly match the column names in the GridView, which were derived directly from the authors table.

    Public Shared Sub UpdateAuthor(ByVal au_id As String, ByVal au_lname As String, 

                    ByVal au_fname As String, ByVal phone As String, ByVal address As String, 

                    ByVal city As String, ByVal state As String, ByVal zip As String, 

                    ByVal contract As String)

Just as in the previous method, the connection string is obtained from your web.config file.

        Dim connstr As String = 

                    ConfigurationSettings.ConnectionStrings("AppConnectionString1").ConnectionString

The remaining code is simple, standard ADO.NET code.  Again, embedding SQL directly into your code should not be a standard practice and a stored procedure should be used.

        Dim connection As New SqlConnection(connstr)

        Dim command As New SqlCommand()

        command.Connection = connection

        command.CommandType = CommandType.Text

        command.Parameters.Add(New SqlParameter("@LastName", au_lname))

        command.Parameters.Add(New SqlParameter("@FirstName", au_fname))

        command.Parameters.Add(New SqlParameter("@Phone", phone))

        command.Parameters.Add(New SqlParameter("@Address", address))

        command.Parameters.Add(New SqlParameter("@City", city))

        command.Parameters.Add(New SqlParameter("@Contract", contract))

        command.Parameters.Add(New SqlParameter("@Zip", zip))

        command.Parameters.Add(New SqlParameter("@State", state))

        command.Parameters.Add(New SqlParameter("@original_id", au_id))

        command.CommandText = "UPDATE AUTHORS SET au_lname=@LastName, au_fname=@FirstName, 

                    phone=@Phone, address=@Address, city=@City, state=@State, zip=@Zip, 

                    contract=@Contract WHERE au_id=@original_id"

        command.ExecuteNonQuery()

    End Sub

End Class

An ObjectDataSource needs to be created and bound to the data access layer.  In this section you will start by replacing the SqlDataSource with the ObjectDataSource.

But first, you are going to create a clone of the Authors.aspx page, except the GridView on the new Web Form will be accessing the class you just created via the ObjectDataSource.

1.    In the Solution Explorer, right click on the project folder and select Add New Item....  Call the new Web Form ObjectDataBinding.aspx.  Make sure the Place code in separate file is checked.  Click on the Add button when complete.

To create a clone of a page, and to be certain you are copying all the controls correctly, we will be replacing the FORM in the ObjectDataBinding.aspx class with the contents of the FORM tag in Authors.aspx.

2.    Open the HTML source of Authors.aspx.  Find the FORM tag and place the cursor on it.  In the HTML tag navigation bar, right click on the FORM tag and select Select Tag.  Copy the selection (Ctrl-C).

3.    Open the ObjectDataBinding.aspx page and select the FORM tag within the page just as you did in the Authors.aspx page.  Paste the Authors.aspx page into ObjectDataBinding.aspx page (Ctrl-V).  Change the header on the page from Authors to Authors - Object Data Binding.

4.    Remove the SqlDataSource control.  Right click on the control and select Delete.

that is quite diffirent from what http://www.asp.net/guidedtour2/s14.aspx  has!!!???  I am completely lost in this "DataAccess" matter.   Please help me - clarify  the above-mentioned "DataAccess" and advise which version of the script I should follow.     

Thanks in advance,

Scott  Chang

End Class

Line 12: Public Sub UpdateAuthor(ByVal au_lname As String, ByVal au_fname As String, _S

Sub must always appear within a class. In the example above the Sub is in a namespace scope so compiler complains. Please move the function into the class scope and recompile.

Thanks Mikhail.

Please tell me why http://www.guanacosonline.org/MSNETSV/downloads/beta.asp.net.GuidedTour.doc has a set of diffirent "DataAccess" code source for the same "Authors" task. I desparately need your answer.

Thanks again,

Scott  Chang  

Not sure. Where did the document come from? The name of the file suggests it was created for a beta version of ASP.NET 2.0. Quite a few things changed since then.