Accessing SQL Server 2000 with ASP.NET

Hi Everyone,

I just started using ASP.NET and I cannot figure out how to use a connection string to connect to a SQL Server database using ASP.NET. I want to use Windows Authentication to bring up a database called upsizedCandidate on a server called SQLSERVER and display a table within that database through internet explorer. I'm not sure if I should be using OLE DB or ODBC. Can anyone help me with this? Thanks in advance!

There is an easy way to set this up.

1. Create a new webForm.

2. Go to view -> Database Explorer.

3.In the new window, make a new connection to your database. Fill in the relevant parts.

4. In the Database explorer, find a table/view that you wish to display.

5. Drag and drop the view/table onto the page. This will setup everything for you. Then you can edit the sqlDataSource and Gridview/Datagrid accordingly.

HTH

Jag

|||

toddw607:

I cannot figure out how to use a connectionstring to connect to a SQL Server database using ASP.NET. I want to useWindows Authentication to bring up a database calledupsizedCandidate on a server called SQLSERVER

Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=SSPI;

remember whenever you stuck with connection string go to http://www.connectionstrings.com/ really helpfull stuff.

toddw607:

I'm not sure if I should be using OLE DB or ODBC. Can anyone help me with this? Thanks in advance!

look,It depends on what database are you using, if you are using Sql ServerI would recommend you to use SQLClient namespace of .netframework, if you find specific driver for database always usethat at prior level, other wise go for oledb or odbc.

cheers

|||

Hi Jag,

Thanks for the quick response! I did what you instructed to do but when I try to bring it up in the browser it gives me the following error:

The connection name 'upsizedCandidateConnectionString1' was not found in the applications configuration or the connection string is empty.

Here is my code:

<%@.PageLanguage="VB" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><tableborder="1"class="ex"width="100%"><tr><td><pre>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataSourceID="SqlDataSource1"EmptyDataText="There are no data records to display."><Columns><asp:BoundFieldDataField="myNewCol1"HeaderText="myNewCol1"SortExpression="myNewCol1"/><asp:BoundFieldDataField="myNewCol2"HeaderText="myNewCol2"SortExpression="myNewCol2"/></Columns>

</

asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:upsizedCandidateConnectionString1 %>"ProviderName="<%$ ConnectionStrings:upsizedCandidateConnectionString1.ProviderName %>"SelectCommand="SELECT [myNewCol1], [myNewCol2] FROM [ToddsTable1]"></asp:SqlDataSource></pre></td></tr></table>

</div></form>

</

body>

</

html>

Any thoughts? Thanks again!

|||

When I drag the table over another form is created called <web.config> which has my connection string in it but is not referenced from my code. Any thoughts?

|||I just put the provider name

ProviderName

="<%System.Data.SqlClient%>"and connection string

ConnectionString

="<%Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=True%>"

into my code and now I'm getting a different error:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

Any thoughts?

|||

When you drag and drop the table, visual studio automaticallycreates a connection string in a file called web.config. This is thesafest location for a connection string. It then creates ansqldatasource that is used to connect to the database, and a gridviewto display the data.

The sqldatasource should be referencing the connection string. Have a look in the web.config file and get the name of the connection string. Then have a look at the properties of the sqldatasource to see where you are meant to put this. I don't have Visual Studio 2005 installed on the computer I am using at the moment, so I cannot tell you the exact property to look for.

Here is a page that should have everything you are looking for if you get stuck. It should not be hard though - just try deleting everything and dragging and dropping again.

Let us know how you get on.

Jag

|||Thanks for all the info! I dled all the ASP.NET gridview examples and saw how everything worked. I set it up like the examples and the main page works fine now when I access it through the server but I have one more problem: I have tables coming out through SQL Server 2000 and when I try to bring them up through the main page it gives me the 404 error. I have the web.Config file stored properly so that the main page can access it but I think I need another one stored else where so when I call the tables from the main page they will automatically retrieve from SQL Server. Any thoughts on the placement or if it could be another problem? Thanks.|||I forgot one more thing which may help...I have the pages saved which the tables are stored in from SQL Server 2000. All the main page does is reference the saved pages and is supposed to bring them up in Internet Explorer like the main pages does. These pages work fine if I go into my folder on the server and bring them up in VB and right click to "View in Browser", they come up in IE but but I attempt to bring them up by reference it gives me the 404 error in IE. I hate to be repetitive with some of the info but I wanted my problem to be as clear as possible before you read the whoel thing. Thanks again!