Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - ASP.Net Page Using SQL Server
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

ASP.Net Page Using SQL Server
 Post Reply Post Reply Page  <123>
Author
  Topic Search Topic Search  Topic Options Topic Options
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2004 at 12:58pm

I am really new at using SQL Server with an ASP.Net page. I am confused. I think I understand Michael's code better. But I still cannot get it to work. I am getting the following error now:

Compiler Error Message: BC30289: Statement cannot appear within a method body. End of method assumed.

Source Error:

Line 4:  
Line 5:  <%
Line 6:  Function SQLConnection(servername,username,password,database) as SQLConnection

I don't understand about SQLCommand because I noticed that it was not defined in Michael's code.

Here's my code now:

<%
Function SQLConnection(servername,username,password,database) as SQLConnection

    servername = "myservername"
        username = "username"
        password = "password"
        databasename = "dbname"
        Dim conString As String
        conString = conString.Format("server={0};User ID={1};Password={2};Database={3};", servername, username, password, databasename)
        Dim objSQLCon As New System.Data.SQLClient.SqlConnection(conString)
        Return objSQLCon

 

        Dim dtaMusic As New OleDbDataAdapter()
        Dim dtsMusic As New DataSet()
        Dim StrSQL As String

         
       SQLConnection.ConnectionString = conString
       SQLConnection.Open()

        'Start SQL statement
        strSQL = "Select * From Spa2"

        'Add Order By
        strSQL = strSQL & " Order By SpaStoreName"

        Trace.Warn ("strSQL = " & strSQL)

        'Set the Command Object properties
        SQLCommand.Connection = SQLConnect
        SQLCommand.CommandType = CommandType.Text
        SQLCommand.CommandText = strSQL

        'Point the DataAdapter object at a Command object to work with
        dtaMusic.SelectCommand = SQLCommand

        'Get the data from the database and
        'put it into a DataTable object named dttMusic in the DataSet object
        dtaMusic.Fill(dtsMusic, "dttMusic")

        'Bind the DataGrid
        dtgMusic.DataSource = dtsMusic
        dtgMusic.DataBind()

End Function

 

 
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2004 at 3:05pm
Seems you are not doing it in code behind so you have to put it within the server code tags of your form. I'd put it in Codebehind though.
Blog | MPG Tracker
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2004 at 4:27pm

Do you mean for me to put it between the <Form> tags? I know how to use ASP.Net with an Access database. I'm just lost on how to use it with SQL Server.

I have an ASP Page that retrieves data from a SQL Server. I set up a second web page with a function. But I am still lost on ASP.net pages.

Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2004 at 5:00pm
Unless there is a typo, this page should bind a grid the way you want. 


<%@ Page Language="VB" %>

<script runat="server">



    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

              If Not Page.IsPostBack THEN

                  BindDataGrid()

              END IF

    End Sub

    

    

    Private Sub BindDataGrid()

    

              Dim strConnect As String

              Dim objConnect As New System.Data.SqlClient.SQLConnection(ConnectionString())

              Dim objCommand As New System.Data.SqlClient.SQLCommand

              Dim strSQL as String

              Dim dtaMusic As New System.Data.SqlClient.SQLDataAdapter()

              Dim dtsMusic As New DataSet()

    

              objConnect.Open()

    

              'Start SQL statement

              strSQL = "Select * From Spa2"

    

              'Add Order By

              strSQL = strSQL & " Order By SpaStoreName"

    

    

              'Set the Command Object properties

              objCommand.Connection  = objConnect

              objCommand.CommandType  = CommandType.Text

              objCommand.CommandText  = strSQL

    

              'Point the DataAdapter object at a Command object to work with

              dtaMusic.SelectCommand  = objCommand

    

              'Get the data from the database and

              'put it into a DataTable object named dttMusic in the DataSet object

              dtaMusic.Fill(dtsMusic,  "dttMusic")

    

              'Bind the DataGrid

              dtgMusic.DataSource  = dtsMusic

              dtgMusic.DataBind()
 
    

    End Sub

    

    Private Function ConnectionString () as String

    

        Dim servername, username, password, databasename As String

        servername = "myserver"

        username = "sa"

        password = "mypassword"

        databasename = "mydatabase"

        Dim conString As String

        conString = conString.Format("server={0};User ID={1};Password={2};Database={3};", servername, username, password, databasename)

        Return conString

    

    End Function



</script>

<html>

<head>

</head>

<body>

    <form runat="server">

        <asp:DataGrid id="dtgMusic" runat="server"></asp:DataGrid>

    </form>

</body>

</html>
Blog | MPG Tracker
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2004 at 5:07pm
eek. thats a lot of code for such a simple thing
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2004 at 7:23pm

Michael,

The code that you gave me in the last posting worked. Thank you very much for your help! I have a better idea of how an ASP.Net page works with SQL Server.

Back to Top
Amateur View Drop Down
Senior Member
Senior Member
Avatar

Joined: 22 July 2004
Location: Ireland
Status: Offline
Points: 210 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Amateur Quote  Post ReplyReply Direct Link To This Post Posted: 01 October 2004 at 9:00am
Anyone know of a tutorial or website which details how ASP.NET and SQL SERVER interact, code examples etc?
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 01 October 2004 at 9:19am
Originally posted by Mart Mart wrote:

eek. thats a lot of code for such a simple thing

I know that but it makes it easier for him to visualize what is going on.
Blog | MPG Tracker
Back to Top
 Post Reply Post Reply Page  <123>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.