How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server

[Belinda]

Hello All

I need to read a SQL Server table into a Web Page and within the Web
Page to permit my users to make changes to the records, delete or add
new records and then save the entire contents back to the SQL Server
table back.

The functionality I am looking is almost the same as In the SQL
Enterprise Manager whereby I can choose a table open the table and
then return all rows and I can maintain the same and save it back to
the SQL Server table. I want almost a similar web interface to such a
functionality.

Even if not a generic functionality as the SQL Enterprise Manager
table maintenance appreciate if somebody can share the code with a
sample how I can do it in ASP pages + T-SQL if need be.

Thanks
Belinda

[Jeff Cochran]

On 10 Jun 2004 01:20:10 -0700, [email]belindacur@yahoo.com[/email] (Belinda) wrote:

>I need to read a SQL Server table into a Web Page and within the Web
>Page to permit my users to make changes to the records, delete or add
>new records and then save the entire contents back to the SQL Server
>table back.

That's an extremely ineficient design. Send *everything* to the web
page, then send *everything* back, even for just changing a single
phone number?

>The functionality I am looking is almost the same as In the SQL
>Enterprise Manager whereby I can choose a table open the table and
>then return all rows and I can maintain the same and save it back to
>the SQL Server table. I want almost a similar web interface to such a
>functionality.

Then it's *not* the same. You change a row in the Enterprise Manager
and it changes just that data in the table.

>Even if not a generic functionality as the SQL Enterprise Manager
>table maintenance appreciate if somebody can share the code with a
>sample how I can do it in ASP pages + T-SQL if need be.

Pull up a list of records that match a criteria (reduce the amount of
data sent), then when a single record is clicked on to change, pull
the full record into a form. Edit it and do an UPDATE on that record
by clicking a "Save Changes" button. Simpler, faster and infinitely
expandable. Plenty of sample code on the internet for each portion of
this.

Jeff

[Bob Barrows [MVP]]

Belinda wrote:

> Hello All
>
> I need to read a SQL Server table into a Web Page and within the Web
> Page to permit my users to make changes to the records, delete or add
> new records and then save the entire contents back to the SQL Server
> table back.
>
> The functionality I am looking is almost the same as In the SQL
> Enterprise Manager whereby I can choose a table open the table and
> then return all rows and I can maintain the same and save it back to
> the SQL Server table. I want almost a similar web interface to such a
> functionality.
>

You definitely have the wrong idea about what's going on in the Enterprise
Manager interface. Be that as it may, you do NOT want to give your users the
ability to bring ALL the records from a table into their browser. A web
application running in a browser is miles from having the same capabilities
as a client application installed on a user's machine.

I suggest you pick up one of the Wrox books on database programming with ASP
and eductate yourself.

In the meantime, you may want to download and try out one of the generic
database tools for database editing available on te web. One such tool is
Eli Robillard's GenericDB which is available at [url]www.genericdb.com[/url]. This will
give you a starting point and allow you to see what needs to be done.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Belinda]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<OYW0bMuTEHA.556@tk2msftngp13.phx.gbl>...

> Belinda wrote:

> > Hello All
> >
> > I need to read a SQL Server table into a Web Page and within the Web
> > Page to permit my users to make changes to the records, delete or add
> > new records and then save the entire contents back to the SQL Server
> > table back.
> >
> > The functionality I am looking is almost the same as In the SQL
> > Enterprise Manager whereby I can choose a table open the table and
> > then return all rows and I can maintain the same and save it back to
> > the SQL Server table. I want almost a similar web interface to such a
> > functionality.
> >

>
> You definitely have the wrong idea about what's going on in the Enterprise
> Manager interface. Be that as it may, you do NOT want to give your users the
> ability to bring ALL the records from a table into their browser. A web
> application running in a browser is miles from having the same capabilities
> as a client application installed on a user's machine.
>
> I suggest you pick up one of the Wrox books on database programming with ASP
> and eductate yourself.
>
> In the meantime, you may want to download and try out one of the generic
> database tools for database editing available on te web. One such tool is
> Eli Robillard's GenericDB which is available at [url]www.genericdb.com[/url]. This will
> give you a starting point and allow you to see what needs to be done.
>
> Bob Barrows

Hi Bob

Thanks for your tips. Please note the tables I will be permitting to
edit have less than 2000 records so network impact is minimal secondly
I am not planning to let all users maintain these tables.

This is a what if analysis system users change the assumptions in some
these tables which maintain the assumptions they keep iteratively
changing the assumptions unless they meet the goal.

Please highlight to me if there is a better way of maintaining this
kind of asssumptions in a what if analysis situation. We have a number
of users doing what if analysis on a SQL server database and some of
them are remote hence we want to provided some kind of a data grid to
enable them to maintain these assumptions.

Thanks
Belinda

[Bob Barrows [MVP]]

Belinda wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:<OYW0bMuTEHA.556@tk2msftngp13.phx.gbl>...

>> Belinda wrote:

>>> Hello All
>>>
>>> I need to read a SQL Server table into a Web Page and within the Web
>>> Page to permit my users to make changes to the records, delete or
>>> add
>>> new records and then save the entire contents back to the SQL Server
>>> table back.
>>>
>>> The functionality I am looking is almost the same as In the SQL
>>> Enterprise Manager whereby I can choose a table open the table and
>>> then return all rows and I can maintain the same and save it back to
>>> the SQL Server table. I want almost a similar web interface to such
>>> a
>>> functionality.
>>>

>>
>> You definitely have the wrong idea about what's going on in the
>> Enterprise
>> Manager interface. Be that as it may, you do NOT want to give your
>> users the
>> ability to bring ALL the records from a table into their browser. A
>> web
>> application running in a browser is miles from having the same
>> capabilities
>> as a client application installed on a user's machine.
>>
>> I suggest you pick up one of the Wrox books on database programming
>> with ASP
>> and eductate yourself.
>>
>> In the meantime, you may want to download and try out one of the
>> generic
>> database tools for database editing available on te web. One such
>> tool is
>> Eli Robillard's GenericDB which is available at [url]www.genericdb.com[/url].
>> This will
>> give you a starting point and allow you to see what needs to be done.
>>
>> Bob Barrows

>
>
>
> Hi Bob
>
> Thanks for your tips. Please note the tables I will be permitting to
> edit have less than 2000 records so network impact is minimal secondly
> I am not planning to let all users maintain these tables.

This is irrelevant. The network traffic is not the only thing to think
about. Browsers have limited ability to handle large amounts of data. Web
applications do (should) not work by bringing all the records to the client.

>
> This is a what if analysis system users change the assumptions in some
> these tables which maintain the assumptions they keep iteratively
> changing the assumptions unless they meet the goal.
>
> Please highlight to me if there is a better way of maintaining this
> kind of asssumptions in a what if analysis situation. We have a number
> of users doing what if analysis on a SQL server database and some of
> them are remote hence we want to provided some kind of a data grid to
> enable them to maintain these assumptions.
>

I think the best way to do this would be to retrieve the records into a
spreadsheet and allow the users to do what they will with the data. I would
not recommend using an ASP application to give your users this
functionality. Use the tools that are appropriate for the task.

A Google search should find you information about using ASP to retrieve data
from a database and put it into a spreadsheet which the user can download.
As for saving the changes back to the database, you have several options,
including:

1. Give the user the ability to upload (there are third-party upload
applications that can make this easy for you - do a search at [url]www.aspfaq.com[/url]
for some examples) his spreadsheet to a folder on your webserver and use DTS
to import it into the database. See [url]www.sqldts.com[/url] for pointers.

There are other less efficient options, but I'm out of time. Maybe some
other people can chime in here ...

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"