Help with connecting to SQL database and binding

I am new to .net and I am using Visual Web Developer 2005 Express with SQL Server 2005 Express. What I would like to do is connect to my SQL database (which resides in the app_data folder) and open a table and pull out a field and place it in either a textbox or label on the page. No editing or deleting. Just simple one field binding. By the way, I can do this with all the cool built-in tools of VWD, but I want to know how to do it all by hand. I would really appreciate it if someone could help me out.

nate200@.hotmail.com:

I am new to .net and I am using Visual Web Developer 2005 Express with SQL Server 2005 Express. What I would like to do is connect to my SQL database (which resides in the app_data folder) and open a table and pull out a field and place it in either a textbox or label on the page. No editing or deleting. Just simple one field binding. By the way, I can do this with all the cool built-in tools of VWD, but I want to know how to do it all by hand. I would really appreciate it if someone could help me out.

This should give you an idea, it's a very manual example, no flashy tools:

http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Roger|||

Hey thanks that helped a lot! I got the code in place but when I view the page I don't get anything in my label. Here's my code.

<

formid="form1"runat="server"><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:PollConn %>"SelectCommand="SELECT TOP 1 MemberNum FROM Members"></asp:SqlDataSource><asp:LabelID="Label1"runat="server"Text='<%# Bind("MemberNum") %>'></asp:Label></form>

What am I missing??

|||

nate200@.hotmail.com:

Hey thanks that helped a lot! I got the code in place but when I view the page I don't get anything in my label. Here's my code.

<formid="form1"runat="server">

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:PollConn %>"

SelectCommand="SELECT TOP 1 MemberNum FROM Members"></asp:SqlDataSource>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("MemberNum") %>'></asp:Label>

</form>

What am I missing??

When using a data source you need to wrap a formview tag around the label, and bind the formview to the datasource.

Here's some samples of the formview tag:

http://www.asp.net/QuickStart/aspnet/doc/ctrlref/data/formview.aspx

Roger

Help with "Problem generating manifest" error

Hi!

I'm using VS 2005 SP 1 on my first project with CE. I've recently run into the problem that if I do anything with my SDF file in Visual Studio I get the following error when I run the project (in debug mode from VS):

The process cannot access the file 'C:\xxx\MySDF.sdf' because it is being used by another process.

The solution is to close the project and reopen it however this is getting painful. Anyone else seen this problem?

Thanks!

This problem hasn't gone away....was hoping someone else has seen it and has a solution.

Thanks!

|||

Do you have the connection open in Server Explorer or any other place. Basically as long as there is some connection open to that database, you can not delete it. However, you can open multiple connection to the same database. We support multiple connecitons.

Thanks,

Laxmi

Help with "Problem generating manifest" error

Hi!

I'm using VS 2005 SP 1 on my first project with CE. I've recently run into the problem that if I do anything with my SDF file in Visual Studio I get the following error when I run the project (in debug mode from VS):

The process cannot access the file 'C:\xxx\MySDF.sdf' because it is being used by another process.

The solution is to close the project and reopen it however this is getting painful. Anyone else seen this problem?

Thanks!

This problem hasn't gone away....was hoping someone else has seen it and has a solution.

Thanks!

|||

Do you have the connection open in Server Explorer or any other place. Basically as long as there is some connection open to that database, you can not delete it. However, you can open multiple connection to the same database. We support multiple connecitons.

Thanks,

Laxmi

HELP using SQL Server 2005

I am using SQLServer2005 Express Edition in Visual Studio 2005. I uploaded the database to the server with the rest of the web files. When I try to insert data into a table, I receive the "error: 26 - Error Locating Server/Instance Specified" error message.

It is my belief that it is the connection string that is causing the problem. Currently the connection string looks like this:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDATABASE.mdf;Integrated Security=True;User Instance=True

Is there a way using VS2005 to create a username and pass for the database? Then how do I set up a typical connection string that will work on a web server?

Desired connection string format would be something like:

Data Source=??serverIP??;UID=MYusername;PASSWORD=MYpassword;SERVER=?serverIP/NAME?

The question marks are in there because I am not 100% sure what would go in those properties. Any help is appreciated.

Thanks,
KJAK

Are you sure that the sqlserver is installed in the server ? When you use .mdf files is necessary that the server have the sql server installed.

A connection string that will work on a web server is something like this:

connectionString="Data Source=SERVERIP;Initial Catalog=DATABASENAE;Persist Security Info=True;User ID=USER;Password=PASSWORD"providerName="System.Data.SqlClient"

|||

SQLServer is installed on the server. The current connection string is provided from my development machine in the web.config file. I will try to adjust it to be similar to what you have posted.

I don't have full access to the server so I can't make any direct adjustments to SQLServer if that is needed. How can I create a username and password for the database using Visual Studio 2005?

|||

SQLServer is installed on the server

which version of sql server is installed? express? or sql 2005?

But whatever,based on my understanding, in neither case you can create sql user name and passwork through visual studio. The sql database is managed by database management tool thus you can only create/modify user name and psw through management studio.

I don't have full access to the server so I can't make any direct adjustments to SQLServer if that is needed.

and based on my understanding, i think you must have admin previlage if you want to create new sql user accounts.

Hope my suggestion helps

Help required!

Hi all

I am creating a web services application in Visual Studio Dot net
which links to a MS SQL database.

I need to create a form which lets the user pick his field of
choice(doesnt matter what it is..) using drop down boxes,radio buttons
etc and query the database according to what he has picked and
retrieve only those columns and rows.

The thing is, I do not want to hardcode the queries using
OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
on the fly, once the user picks his fields from the form.

I'd greatly appreciate any help.

Thanks,
mSeems you are looking to provide ad-hoc reporting capabilities. To avoid
hard-coding queries you would have to access the SQL Server meta-data to
retrieve information about tables and to provide options for users to pick
tables and columns. You can start by looking at the information schema views
in the SQL Server documentation. Here is one example of a query that returns
tables and columns:

SELECT c.table_catalog AS 'Database Name',
c.table_schema AS 'Owner',
c.table_name AS 'Table Name',
c.column_name AS 'Column Name',
c.data_type AS 'Column Data Type'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.table_catalog = c.table_catalog
AND t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_type = 'BASE TABLE'

Since the table and column names may not mean much to users, you may want to
build a mapping table with descriptive names. That involves some hard-coding
and maintenance (best stored in a table that you can join easily) to update
when a new table/column is added or changed, but greatly improves the user
experience.

Next you would allow selection of columns and placing filters (the WHERE
clause of the query). A good idea is to enforce a requirement to have at
least one condition. Users tend to forget about adding filters and could
possibly dump the whole table out. At this point it is very important to
check the column type and perform verification on any parameters entered.

Last is to build a dynamic query and return the results to the user. A good
reading for dynamic queries is Erland Sommarskog's article here:
http://www.sommarskog.se/dynamic_sql.html. Pay attention to the SQL
injection section as this is very common for ad-hoc query systems.

This is a very simplified approach to what you are looking for. It can get a
lot more complex if you need to provide special operations like summary
queries, or if you decide to provide capabilities to join multiple tables.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

In addition to Plamen's suggestions, you might look into the report
builder part of Sql Server Reporting Services. The programmer sets up
the appropriate data model, and the user configures the report. There
is something of a learning curve for the user but designing a report
can be fairly trivial.|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

And you'll likely get more replies if you're specific in your post,
rather than just Help Required!.