I would like to create an ASP.Net Page that uses a SQL Server database. I already know how to connect to a SQL Server database from an ASP page.

How would you convert the following code to work with ASP.Net:

     dim connString

      dim serverName, userName, password

      'Set connection values

      serverName = "servername;"

      databaseName = databasename & ";"

      userName = "username;"

      password = "password;"

      'Build connection string

  connString = "PROVIDER=SQLOLEDB; " & _

                            "SERVER=" & serverName & _

                            "UID=" & userName & _

                            "PWD=" & password & _

                            "DATABASE=" & databaseName & ";"

      'Return connection string

      BuildSQLServerConnectionString = connString

Michael,

Thank you for the information! I have a problem because I am not sure how to put this on the following ASP.Net page. I have tried using the function you have provided me with, but I have been unsuccessful due to OleDbConnection() and OleDbCommand()

. I am new at using SQL Server on an ASP.net page. Please look at the code below:

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

'---------------------------------------------
' name: BindDataGrid()
'---------------------------------------------
Sub BindDataGrid()

        Dim strConnect As String
        Dim objConnect As New OleDbConnection()
        Dim objCommand As New OleDbCommand()
        Dim strSQL as String
        Dim dtaMusic As New OleDbDataAdapter()
        Dim dtsMusic As New DataSet()

        'Build connection object
        strConnect    (I am not sure what I should do).
  
        objConnect.ConnectionString = strConnect
        objConnect.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
        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

Can someone please help me figure out how to use this with SQL Server?

You missed a "

>> strSQL = "Select * From Spa2

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

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.

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.