a webpage that directly inputs data to a specific table in an sql database ?

im new to sql and ive created a test data base with a test table having 3 input fields i was wondering if it was possible to use a webpage to input data into the database in a specific table
ive seen some net articles discussing this but as i said im new they talked about using an asp page,so i was wondering if someone could help me out with a simple code to enable a webpage to input data on an sql table
so how do i go about it ?
test_database
dbo.test_table
the 3 input fields are name age gender

ive also set an odbc for the data base (test_database) set at native

tnx

Quote:

Originally Posted by arawsg

im new to sql and ive created a test data base with a test table having 3 input fields i was wondering if it was possible to use a webpage to input data into the database in a specific table
ive seen some net articles discussing this but as i said im new they talked about using an asp page,so i was wondering if someone could help me out with a simple code to enable a webpage to input data on an sql table
so how do i go about it ?
test_database
dbo.test_table
the 3 input fields are name age gender

ive also set an odbc for the data base (test_database) set at native

tnx

Hi ,

I don't know which platform you are using to develop your webpage.I give you sample code here to insert data into a sql server database table from an asp.net web page. I think this will help you.

---------
Using System.Data.SqlClient
void Page_Load()
{
SqlConnection con=new SqlConnection ("server=labserver;database=testdb;username=sa;passw ord=s");
SqlCommand cmd=new SqlCommand("insert into emptable(empid,empname) values(2312,'Adam')",con);
con.open();
cmd.ExecuteNonQuery();
con.close();
}
----------|||

Quote:

Originally Posted by balajiu

Hi ,

I don't know which platform you are using to develop your webpage.I give you sample code here to insert data into a sql server database table from an asp.net web page. I think this will help you.

---------
Using System.Data.SqlClient
void Page_Load()
{
SqlConnection con=new SqlConnection ("server=labserver;database=testdb;username=sa;passw ord=s");
SqlCommand cmd=new SqlCommand("insert into emptable(empid,empname) values(2312,'Adam')",con);
con.open();
cmd.ExecuteNonQuery();
con.close();
}
----------

tnx ill try your code out

a TSQL question

i want to write a query that will return last created 200 records + record
with the minimum ID.
is there a way to do that in one single Select statement?
Following statement should return the record that has the minimum ID too.
SELECT TOP 200 ID , CREATEDATE
FROM TBL_RECORDS
ORDER BY CREATEDATE DESC
ID column has IDENTITY property and There could be records with negative IDs
which come through synchronization.
Thanks in advance..Well, what do you mean by single select? Union would probably be the best
solution here, why dont you use that?
MC
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uJjpdS$KGHA.2300@.TK2MSFTNGP15.phx.gbl...
>i want to write a query that will return last created 200 records + record
>with the minimum ID.
> is there a way to do that in one single Select statement?
> Following statement should return the record that has the minimum ID too.
> SELECT TOP 200 ID , CREATEDATE
> FROM TBL_RECORDS
> ORDER BY CREATEDATE DESC
> ID column has IDENTITY property and There could be records with negative
> IDs which come through synchronization.
> Thanks in advance..
>|||Thanks for the answers ,

> Well, what do you mean by single select? Union would probably be the best
> solution here, why dont you use that?
that is what i mean with single select! i plan to use this query in a RDA
application. I am
not sure that RDA's Pull method with "tracking on" option supports the
UNION operator.
Anyway , i will give a try with UNION method,i hope it works :)

A simple trigger that doesnt work

Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...

I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.

If anyone could help me, I would appreciate.

NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?

Francois

This is the trigger:
------------------

ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS

DECLARE @.noPerson int

SET NOCOUNT OFF

IF UPDATE(LastMove)
BEGIN
SELECT @.noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @.noPerson;
END

SET NOCOUNT ONyou are making the assumption that you are only updating 1 record at a time. triggers need to handle sets. think in joins.|||You would need to set up a linked server connection to do this. And DON'T DO IT! This is one of the worst things you can do with a trigger.|||--Use the following code. You update should handle multiple rows simultaneously. However, in case you DB exist on a different server, set up linked server first:

ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS

SET NOCOUNT OFF

IF UPDATE(LastMove)
UPDATE A
SET A.status = 1
from Intranet.dbo._Users A, Inserted I
WHERE A.personNo = I.Person

SET NOCOUNT ON

--Scalability Experts.|||Hi all, first of all, thanks for your helpful advices, I greatly appreciate it. The trigger is now working properly.

How did I solve the problem:
As a lot of people suggested me to do, I changed some lines in the trigger to make it compatible with multiple insert or update statements and change the security contex. I added specifics users from the source DB on the target source.

Thanks all for your help ! Francois|||Perhaps the reason they are called "triggers" is because they make it so easy to shoot yourself in the foot...as you seem bound and determined to do after SQLDBA_2005 so kindly loaded the gun and showed you where to point it.

Just my opinion. :rolleyes:

A Question of Views

Hi,
A question

regarding Views. If a views is created which is made up of several

unions from several tables; and when a select is placed on the view

with a filter on a particular field, will the view first run the entire

underlying query and then perform a filter, or otherwise. Please

explain. Thanks a lot
Gogula
Annoyingly, the answer seems to be 'it depends'.

The optimiser can do a lot, and if you have a 'union all', then the optimiser can tell that this can be expanded out. But it will use statistics and so on to check to see whether this is ideal or not.

The best thing to advise you is to try it. Get some sample data similar to your real-world situation, and try out some cases. If you find it's not doing what you want, try tweaking the view and query a little and see what you can do. Also try making sure that good indexes are used.

But also bear in mind that you may find that you can get results that are just as good using a user-defined function to pass in filter parameters.

Rob|||

as rob said..try it out..and while doin so , do go thru this article.. http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

spcially look fro the ..NOEXPAND view hint , may give u some more to try...

A question about varchar parameters

I've created a stored procedure that takes a varchar(10) as a parameter. However calling this stored procedure from an ASP page, with a string of greater length, generates an error. However this does not happen in Query Analyzer (it simply truncates the string to 10 characters). I was under the previous impression that this truncation was implicit, but now it seems that it is not. Can someone please give me a quick overview of how to work around this issue (is there an SQL setting I can flip on). I know I could pre-truncate every value in my page, but that seems like a design nightmare (seeing as how I would need to know the size of every varchar parameter in every stored procedure old and new, also I'd like to be able to simply increase the size of the data field in the table, at a later point, without having to match it up in every stored procedure and ASP page ).

P.S. I am using SQL Sever 2000

How did you call the stored procedure from your code? I use SqlConnection and SqlCommand to call the sp with a Parameter, it succeeded even I input a string with length greater then the length defined for the stored procedure parameter, as what happened in Query Analyzer.

So I guess your exception came from ASP .NET, not SQL. Did you call the stored procedure using OleDbCommand and specify the length for the parameter on the application side?

A question about using stored procedure to update database?

A stored procedure was created to update a database.

But, I don't know how to trigger it in code:

This is the example to create a dataset for Dropdownlist,

Dim da As New SqlDataAdapter(myCommand)

Dim ds AS DataSet = new DataSet() da.Fill(ds)

This way doesn't work for triggering the stored procedure for updating database

How to trigger the stored Procedure for updating the database in the code?

Thx.

You need to create a SqlCommand object - in the properties for this you can set it to be a stored procedure and set the text to be something like [dbo].sprocname - then need to give it a SqlConnection object for it to be able to access the database

eg:

using

(SqlConnection conSQL =newSqlConnection("YourConString"))

{

using (SqlCommand cmd =newSqlCommand())

{

cmd.CommandText =

"[dbo].sprocname";

cmd.CommandType = System.Data.

CommandType.StoredProcedure;

cmd.Parameters =

newSqlParameterCollection(<this should match your sproc>);

cmd.ExecuteNonQuery();

}

}

a problem with transactions

hi
i created an explicite transaction which contains 2 update statements
both of these two update statements act on a single table with the name of
"TABLE1".
these table has two column "name varchar(20)" & "account (int)".
this table also has a check constraint on the "account" column which resric
ts
the value of this column to be greater than 100.
here is the table before execting the transaction:
<<
NAME ACCOUNT
' ali ' 140
' nima ' 200
here is the code of my transaction:
<<
begin transaction
update table1 set account=account-50 where name='ali'
update table1 set account=account+50 where name='esi'
commit transaction
and this is table1 after executing the transaction:
<<
NAME ACCOUNT
' ali ' 140
' nima ' 250
if the value of the "account" column be for example " 140" before executing
the
transaction , with executing the transaction a check constraint violation
happens
and the first update statement does not affect on the table but the second
update statement affect permanently on the table.
alltransactions are "ATOMIC" .Either all the satements inside them should
perform or none of them. with executing this transaction the "CONSISTANCY"
feature off transactions is ignored too.(140+200 before execution) &
(140+250 after execution).
wwwwhhhhhhhhhhhhhhyyyyyyyyyyyyyyyy'''The problem is that you don't have any error handling. A constraint
violation does not terminate a batch or a transaction, it only terminates
the offending statement and sets @.@.ERROR. You must always, Always, ALWAYS
check @.@.ERROR after every DML statement (INSERT, UPDATE, DELETE) and every
stored procedure call. It is also possible for a transaction to be rolled
back when an error occurs, but for the batch to continue executing. That
means that statements following an error are no longer protected by a
transaction! Failing to include error handling defeats the purpose of
transactions.
There is a statement, SET XACT_ABORT, which is supposed to cause every
transaction to be rolled back and terminated whenever an error occurs, but I
don't rely on it. I use @.@.ERROR and @.@.ROWCOUNT because that gives me more
control over the error recovery process.
"pooyan_pdm" <pooyan_pdm@.discussions.microsoft.com> wrote in message
news:3C02A013-830C-42C7-AB75-C53348528FDA@.microsoft.com...
> hi
> i created an explicite transaction which contains 2 update statements
> both of these two update statements act on a single table with the name of
> "TABLE1".
> these table has two column "name varchar(20)" & "account (int)".
> this table also has a check constraint on the "account" column which
resricts
> the value of this column to be greater than 100.
> here is the table before execting the transaction:
> <<
> NAME ACCOUNT
>
> ' ali ' 140
> ' nima ' 200
>
>
>
> here is the code of my transaction:
> <<
> begin transaction
> update table1 set account=account-50 where name='ali'
> update table1 set account=account+50 where name='esi'
> commit transaction
>
>
> and this is table1 after executing the transaction:
> <<
> NAME ACCOUNT
>
> ' ali ' 140
> ' nima ' 250
>
>
>
> if the value of the "account" column be for example " 140" before
executing
> the
> transaction , with executing the transaction a check constraint
violation
> happens
> and the first update statement does not affect on the table but the
second
> update statement affect permanently on the table.
> alltransactions are "ATOMIC" .Either all the satements inside them should
> perform or none of them. with executing this transaction the
"CONSISTANCY"
> feature off transactions is ignored too.(140+200 before execution) &
> (140+250 after execution).
>
> wwwwhhhhhhhhhhhhhhyyyyyyyyyyyyyyyy'''
>|||Brian
But there are some kind of error that terminate batches and @.@.ERROR does not
catch them
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23wF7tWdkFHA.2916@.TK2MSFTNGP14.phx.gbl...
> The problem is that you don't have any error handling. A constraint
> violation does not terminate a batch or a transaction, it only terminates
> the offending statement and sets @.@.ERROR. You must always, Always, ALWAYS
> check @.@.ERROR after every DML statement (INSERT, UPDATE, DELETE) and every
> stored procedure call. It is also possible for a transaction to be rolled
> back when an error occurs, but for the batch to continue executing. That
> means that statements following an error are no longer protected by a
> transaction! Failing to include error handling defeats the purpose of
> transactions.
> There is a statement, SET XACT_ABORT, which is supposed to cause every
> transaction to be rolled back and terminated whenever an error occurs, but
> I
> don't rely on it. I use @.@.ERROR and @.@.ROWCOUNT because that gives me more
> control over the error recovery process.
> "pooyan_pdm" <pooyan_pdm@.discussions.microsoft.com> wrote in message
> news:3C02A013-830C-42C7-AB75-C53348528FDA@.microsoft.com...
> resricts
> executing
> violation
> second
> "CONSISTANCY"
>|||You're absolutely right. It's important to keep that in mind. It's also
important to understand that the nature of error and transaction management
change within triggers. However, neither of these supplant the requirement
to manage errors that do not terminate a batch.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uylJrjekFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Brian
> But there are some kind of error that terminate batches and @.@.ERROR does
not
> catch them
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23wF7tWdkFHA.2916@.TK2MSFTNGP14.phx.gbl...
terminates
ALWAYS
every
rolled
That
but
more
should
>

A previous prgram installation created a pending file operations on the installation machine

I know there is a help file on how to do this...its something i need to do in the regedit but i cant find the site that had anyone have the link? thanks

http://support.microsoft.com/kb/312995/

A point in time backup with SQL Server 2000 Enterprise Manager

I'm having a problem testing a point in time backup. Here
is what I was trying:
1) I created a complete database backup to a file
D:\TEMP.DAT (of a database named
PHCS) (Time - 10:45)
2) I went and made some changes to the PHCS database at
10:49:10
3) I went and made some more changes to the PHCS database
at 10:50:50
4) I backed up the transaction log database (also into
D:\TEMP.DAT, appending).
(Time - 10:52)
5) I went to restore the database backed up at 10:45 and
left it unoperational
6) I went to restore the transaction log created at 10:52
and tried to specify a time
of 10:50:00. I wanted to restore the changes made in step
2, but not step 3.
He restore me the hole last transaction log i selected
an don't stop between step 2 and 3 !!
Many thanks for your help
DanielDid you use STOPAT..?
Here's the example from SQL BOL
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
HTH
Ryan Waight, MCDBA, MCSE
"Ischi" <daniel_ischi@.hotmail.com> wrote in message
news:0def01c38d87$06a78890$a101280a@.phx.gbl...
> I'm having a problem testing a point in time backup. Here
> is what I was trying:
> 1) I created a complete database backup to a file
> D:\TEMP.DAT (of a database named
> PHCS) (Time - 10:45)
> 2) I went and made some changes to the PHCS database at
> 10:49:10
> 3) I went and made some more changes to the PHCS database
> at 10:50:50
> 4) I backed up the transaction log database (also into
> D:\TEMP.DAT, appending).
> (Time - 10:52)
> 5) I went to restore the database backed up at 10:45 and
> left it unoperational
> 6) I went to restore the transaction log created at 10:52
> and tried to specify a time
> of 10:50:00. I wanted to restore the changes made in step
> 2, but not step 3.
> He restore me the hole last transaction log i selected
> an don't stop between step 2 and 3 !!
> Many thanks for your help
> Daniel
>|||Hello
No i use the SQL Enterprise Manager not the Query
Analycer. I know the statement in Qury Analycer.
Thaks and best regards.
Daniel
>--Original Message--
>Did you use STOPAT..?
>Here's the example from SQL BOL
>-- Restore the database backup.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1, MyNwind_2
> WITH NORECOVERY
>GO
>RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
>GO
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Ischi" <daniel_ischi@.hotmail.com> wrote in message
>news:0def01c38d87$06a78890$a101280a@.phx.gbl...
>> I'm having a problem testing a point in time backup.
Here
>> is what I was trying:
>> 1) I created a complete database backup to a file
>> D:\TEMP.DAT (of a database named
>> PHCS) (Time - 10:45)
>> 2) I went and made some changes to the PHCS database at
>> 10:49:10
>> 3) I went and made some more changes to the PHCS
database
>> at 10:50:50
>> 4) I backed up the transaction log database (also into
>> D:\TEMP.DAT, appending).
>> (Time - 10:52)
>> 5) I went to restore the database backed up at 10:45
and
>> left it unoperational
>> 6) I went to restore the transaction log created at
10:52
>> and tried to specify a time
>> of 10:50:00. I wanted to restore the changes made in
step
>> 2, but not step 3.
>> He restore me the hole last transaction log i selected
>> an don't stop between step 2 and 3 !!
>> Many thanks for your help
>> Daniel
>
>.
>|||To do it within Enterprise Manager :-
Right Click DB
All Tasks
Restore DataBase
Tick the Box "Point in Time Restore"
Specify the time you wish to stop at.
--
HTH
Ryan Waight, MCDBA, MCSE
"Ischi" <daniel_ischi@.hotmail.com> wrote in message
news:0cde01c38d90$dcd9bc90$a301280a@.phx.gbl...
> Hello
> No i use the SQL Enterprise Manager not the Query
> Analycer. I know the statement in Qury Analycer.
> Thaks and best regards.
> Daniel
> >--Original Message--
> >Did you use STOPAT..?
> >
> >Here's the example from SQL BOL
> >
> >-- Restore the database backup.
> >RESTORE DATABASE MyNwind
> > FROM MyNwind_1, MyNwind_2
> > WITH NORECOVERY
> >GO
> >RESTORE LOG MyNwind
> > FROM MyNwind_log1
> > WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> >GO
> >
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Ischi" <daniel_ischi@.hotmail.com> wrote in message
> >news:0def01c38d87$06a78890$a101280a@.phx.gbl...
> >> I'm having a problem testing a point in time backup.
> Here
> >> is what I was trying:
> >>
> >> 1) I created a complete database backup to a file
> >> D:\TEMP.DAT (of a database named
> >> PHCS) (Time - 10:45)
> >> 2) I went and made some changes to the PHCS database at
> >> 10:49:10
> >> 3) I went and made some more changes to the PHCS
> database
> >> at 10:50:50
> >> 4) I backed up the transaction log database (also into
> >> D:\TEMP.DAT, appending).
> >> (Time - 10:52)
> >> 5) I went to restore the database backed up at 10:45
> and
> >> left it unoperational
> >> 6) I went to restore the transaction log created at
> 10:52
> >> and tried to specify a time
> >> of 10:50:00. I wanted to restore the changes made in
> step
> >> 2, but not step 3.
> >>
> >> He restore me the hole last transaction log i selected
> >> an don't stop between step 2 and 3 !!
> >>
> >> Many thanks for your help
> >>
> >> Daniel
> >>
> >
> >
> >.
> >

A newbie need help for SQL XML

I am trying to set up my SQL server so that I can get recordset in XML format
over HTTP.
In a seperate IIS server (with SQL tools):
1) I created a directory (C:\nwind) and underneath created C:\nwind\template
and C:\nwind\schema.
2) Opened the IIS virtual dir. for SQL (IISsql) and created a virtual
directory nwind and pointed to C:\nwind.
3) Set the security to either sql or windows.
4)Selected the SQL server and appropriate database.
5)Checked allow POST and allow URL query.
6) Created virtual names
template->type-template->location-C:\nwind\template, dbobject->type-dbobject,
schema->type-schema->location-C:\nwind\schema.
I have both a windows account and sql account "test" that has access to
northwind database. When I type the following in a workstation:
http://IISserver/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
I get the:
HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)
But when I ran the IIS in XP machine I didn't have any problem. Any help is
greatly appreciated. Thanks
What OS is this machine running?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"SN" <SN@.discussions.microsoft.com> wrote in message
news:4FD030C3-B878-4B2E-B8FA-5B3505756371@.microsoft.com...
>I am trying to set up my SQL server so that I can get recordset in XML
>format
> over HTTP.
> In a seperate IIS server (with SQL tools):
> 1) I created a directory (C:\nwind) and underneath created
> C:\nwind\template
> and C:\nwind\schema.
> 2) Opened the IIS virtual dir. for SQL (IISsql) and created a virtual
> directory nwind and pointed to C:\nwind.
> 3) Set the security to either sql or windows.
> 4)Selected the SQL server and appropriate database.
> 5)Checked allow POST and allow URL query.
> 6) Created virtual names
> template->type-template->location-C:\nwind\template,
> dbobject->type-dbobject,
> schema->type-schema->location-C:\nwind\schema.
> I have both a windows account and sql account "test" that has access to
> northwind database. When I type the following in a workstation:
> http://IISserver/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
> I get the:
> HTTP Error 404 - File or directory not found.
> Internet Information Services (IIS)
> But when I ran the IIS in XP machine I didn't have any problem. Any help
> is
> greatly appreciated. Thanks
|||It's IIS6.0/Windows 2003.
"Irwin Dolobowsky [MSFT]" wrote:

> What OS is this machine running?
> --
> Thanks,
> Irwin
> Irwin Dolobowsky
> Program Manager, SqlXml
> http://blogs.msdn.com/irwando
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SN" <SN@.discussions.microsoft.com> wrote in message
> news:4FD030C3-B878-4B2E-B8FA-5B3505756371@.microsoft.com...
>
>
|||Check the docs, there are some steps for getting it running on Win2k3. The
"Guidelines and Limitations" section.
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"SN" <SN@.discussions.microsoft.com> wrote in message
news:23043A59-00DB-464F-A58B-504E3EAE751E@.microsoft.com...[vbcol=seagreen]
> It's IIS6.0/Windows 2003.
> "Irwin Dolobowsky [MSFT]" wrote:

A generic error occurred in gdi+

We have created an application that shows reports with the ms
reportviewer. But on some machines (like our Terminal Server) we see
the reportviewer on processing and than we recive this error: A generic
error occurred in gdi+.
Any ideas?
thxAdditions: We use VS 2005 and .Net Framework 2.0 and der Version of the
Report dlls is 8.00|||solved ... wrong report path *argh*
thx