Datasource question

2 May 2007 3:48 AM

slinky

I have a practice website (I'm a newbie) that I want to use the
Northwind SQL Server sample database in. First, do I upload the
database into my App_Data folder of my website? Also I have a
textbook .aspx I'm doing a lab on with the following code (what I'm
wondering is do I change the "local" to the complete URL of my
website? Also should I drag the Northwind object over my .aspx form to
create the Data Adapter or is that handled in the code below? Also in
HTML code following, where should I place A and where should I place
B?):

A) Imports System.Data
    Imports.System.Data.SqlClient

B) Private Sub Page_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
       If IsPostBack Then
          Dim cnn As SqlConnection("Data Source=(local);" & _
           New SqlConnection("Data Source=(local);" & _
           "Initial Catalog=Northwind;" & _
           "Integrated Security=SSPI")
          Dim ds As DataSet = New DataSet()
          Dim da As SqlAdapter = _
           New SqlDataAdapter()
          Dim cmdSelect As SqlCommand = _
            cnn.CreateCommand()
            cmdSelect.CommandType = CommandType.Text
            cmdSelect.CommandText = _
           "SELECT CustomerID, CompanyName, " & _
           "ContactName FROM Customers"
          Dim cmdInsert As SqlCommand = _
           cnn.CreateCommand()
            cmdInsert.CommandType = CommandType.Text
            cmdInsert.CommandText = _
              "INSERT INTO Customers " & _
               "(CustomerID, CompanyName, ContactName) " & _
                "VALUES(@CustomerID, @CompanyName, _
                 "@ContactName)"
           cmdInsert.Parameters.Add("@CustomerID", _
             SqlDbType.NChar, _
                  5, "CustomerID")
           cmdInsert.Parameters.Add("@CompanyName", _
             SqlDbType.NVarChar, _
                  40, "CompanyName")
           cmdInsert.Parameters.Add("@ContactName", _
             SqlDbType.NVChar, _
                  30, "ContactName")
       cmdInsert.Parameters("@CustomerID"), _
SourceVersion = _
DataRowVersion.Original
Da.SelectCommand = cmdSelect
Da.InsertCommand = cmdInsert
Da.Fill(ds, "Customers")
Dim dr As DataRow = ds.Tables( _
"Customers").NewRow()
Dr(0) = txtCustomerID.Text
Dr(1) = txtCompanyName.Text
Dr(2) = txtContactName.Text
Ds.Tables("Customers").Rows.Add(dr)
Da.Update(ds, "Customers")
lblResults.Text = "Row added!"
End If
End Sub

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body bgcolor="#00ffcc">
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="txtCustomerID" runat="server" Style="z-index:
100; left: 256px; position: absolute;
            top: 240px" BorderStyle="Ridge"></asp:TextBox>
        <asp:TextBox ID="txtCompanyName" runat="server" Style="z-
index: 101; left: 256px; position: absolute;
            top: 272px"></asp:TextBox>
        <asp:Button ID="btnAdd" runat="server" Style="z-index: 102;
left: 344px; position: absolute;
            top: 360px" Text="Add" Width="128px"
OnClick="btnAdd_Click" />
        <asp:TextBox ID="txtContactName" runat="server" Style="z-
index: 103; left: 256px;
            position: absolute; top: 304px"></asp:TextBox>
        <asp:Label ID="Label1" runat="server" BackColor="#004040" Font-
Bold="True" ForeColor="White"
            Style="z-index: 104; left: 104px; position: absolute; top:
240px" Text="Customer ID"
            Width="120px"></asp:Label>
        <asp:Label ID="Label2" runat="server" BackColor="#004040" Font-
Bold="True" ForeColor="White"
            Style="z-index: 105; left: 104px; position: absolute; top:
304px" Text="Contact Name"
            Width="120px"></asp:Label>
        <asp:Label ID="Label3" runat="server" BackColor="#004040" Font-
Bold="True" ForeColor="White"
            Style="z-index: 106; left: 104px; position: absolute; top:
272px" Text="Company Name"
            Width="120px"></asp:Label>
        <asp:Label ID="Label4" runat="server" BackColor="White"
ForeColor="Black" Style="z-index: 108;
            left: 88px; position: absolute; top: 432px"
Text="[lblResults]" Width="440px"></asp:Label>

    </div>
    </form>
</body>
</html>

4 May 2007 4:58 AM

Michel Bechelani

> First, do I upload the database into my App_Data folder of my website?

I would do so.

> do I change the "local" to the complete URL of my
> website?

If your SQL Server is in the same box as your web server you can leave
local. Otherwise write the name of the server and instance name. Example:
MYSERVER\SQLEXPRESS

>where should I place A and where should I place
> B?):

Are you using Visual Studio to build your web site? If the Page_Load method
should be there as default when you create a new ASPX page with code-behind
enable. If youd using a text editor then the proper way to put A is above
the class and namespace declaration and B goes inside the class, since it's
a method.

Hope this helps,

Regards,

Michel Bechelani

--
This post is provided "AS IS" with no explicit or implied warranties.

Show quoteHide quote

"slinky" <campbellbrian2***@yahoo.com> wrote in message
news:1178077713.031162.262560@l77g2000hsb.googlegroups.com...
>I have a practice website (I'm a newbie) that I want to use the
> Northwind SQL Server sample database in. First, do I upload the
> database into my App_Data folder of my website? Also I have a
> textbook .aspx I'm doing a lab on with the following code (what I'm
> wondering is do I change the "local" to the complete URL of my
> website? Also should I drag the Northwind object over my .aspx form to
> create the Data Adapter or is that handled in the code below? Also in
> HTML code following, where should I place A and where should I place
> B?):
>
> A) Imports System.Data
>    Imports.System.Data.SqlClient
>
> B) Private Sub Page_Load(ByVal sender As System.Object, _
>     ByVal e As System.EventArgs) Handles MyBase.Load
>       If IsPostBack Then
>          Dim cnn As SqlConnection("Data Source=(local);" & _
>           New SqlConnection("Data Source=(local);" & _
>           "Initial Catalog=Northwind;" & _
>           "Integrated Security=SSPI")
>          Dim ds As DataSet = New DataSet()
>          Dim da As SqlAdapter = _
>           New SqlDataAdapter()
>          Dim cmdSelect As SqlCommand = _
>            cnn.CreateCommand()
>            cmdSelect.CommandType = CommandType.Text
>            cmdSelect.CommandText = _
>           "SELECT CustomerID, CompanyName, " & _
>           "ContactName FROM Customers"
>          Dim cmdInsert As SqlCommand = _
>           cnn.CreateCommand()
>            cmdInsert.CommandType = CommandType.Text
>            cmdInsert.CommandText = _
>              "INSERT INTO Customers " & _
>               "(CustomerID, CompanyName, ContactName) " & _
>                "VALUES(@CustomerID, @CompanyName, _
>                 "@ContactName)"
>           cmdInsert.Parameters.Add("@CustomerID", _
>             SqlDbType.NChar, _
>                  5, "CustomerID")
>           cmdInsert.Parameters.Add("@CompanyName", _
>             SqlDbType.NVarChar, _
>                  40, "CompanyName")
>           cmdInsert.Parameters.Add("@ContactName", _
>             SqlDbType.NVChar, _
>                  30, "ContactName")
>       cmdInsert.Parameters("@CustomerID"), _
> SourceVersion = _
> DataRowVersion.Original
> Da.SelectCommand = cmdSelect
> Da.InsertCommand = cmdInsert
> Da.Fill(ds, "Customers")
> Dim dr As DataRow = ds.Tables( _
> "Customers").NewRow()
> Dr(0) = txtCustomerID.Text
> Dr(1) = txtCompanyName.Text
> Dr(2) = txtContactName.Text
> Ds.Tables("Customers").Rows.Add(dr)
> Da.Update(ds, "Customers")
> lblResults.Text = "Row added!"
> End If
> End Sub
>
>
>
> <%@ Page Language="VB" %>
>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
> www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>
> <script runat="server">
>
> </script>
>
> <html xmlns="http://www.w3.org/1999/xhtml" >
> <head runat="server">
>    <title>Untitled Page</title>
> </head>
> <body bgcolor="#00ffcc">
>    <form id="form1" runat="server">
>    <div>
>        <asp:TextBox ID="txtCustomerID" runat="server" Style="z-index:
> 100; left: 256px; position: absolute;
>            top: 240px" BorderStyle="Ridge"></asp:TextBox>
>        <asp:TextBox ID="txtCompanyName" runat="server" Style="z-
> index: 101; left: 256px; position: absolute;
>            top: 272px"></asp:TextBox>
>        <asp:Button ID="btnAdd" runat="server" Style="z-index: 102;
> left: 344px; position: absolute;
>            top: 360px" Text="Add" Width="128px"
> OnClick="btnAdd_Click" />
>        <asp:TextBox ID="txtContactName" runat="server" Style="z-
> index: 103; left: 256px;
>            position: absolute; top: 304px"></asp:TextBox>
>        <asp:Label ID="Label1" runat="server" BackColor="#004040" Font-
> Bold="True" ForeColor="White"
>            Style="z-index: 104; left: 104px; position: absolute; top:
> 240px" Text="Customer ID"
>            Width="120px"></asp:Label>
>        <asp:Label ID="Label2" runat="server" BackColor="#004040" Font-
> Bold="True" ForeColor="White"
>            Style="z-index: 105; left: 104px; position: absolute; top:
> 304px" Text="Contact Name"
>            Width="120px"></asp:Label>
>        <asp:Label ID="Label3" runat="server" BackColor="#004040" Font-
> Bold="True" ForeColor="White"
>            Style="z-index: 106; left: 104px; position: absolute; top:
> 272px" Text="Company Name"
>            Width="120px"></asp:Label>
>        <asp:Label ID="Label4" runat="server" BackColor="White"
> ForeColor="Black" Style="z-index: 108;
>            left: 88px; position: absolute; top: 432px"
> Text="[lblResults]" Width="440px"></asp:Label>
>
>    </div>
>    </form>
> </body>
> </html>
>