|
Thanks for the fine example. Except this gives the factory the possibility to have more than one row of pollutiondata. Unfortunately this is not allowed in the datamodel!
I created this at first, but it kept getting the system design into trouble.
Best regards
Soeren
|
|
|
|
|
That's pretty easy to fix:
drop table pollution
drop table factory
CREATE TABLE [Factory] (
[FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Factory] PRIMARY KEY CLUSTERED
(
[FactoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Pollution] (
[PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryID] [int] <font size="5">NOT </font>NULL ,
[PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Pollution] PRIMARY KEY CLUSTERED
(
[PollutionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY
(
[FactoryID]
) REFERENCES [Factory] (
[FactoryID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')
insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')
insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')
delete from factory where FactoryID = @nF1ID
Go
Last modified: 1hr 14mins after originally posted --
WTF was this all about??? (See post below)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
-- modified at 14:31 Thursday 12th October, 2006
|
|
|
|
|
That won't fix that part of the problem. Making FactoryId the primary key or putting a unique constraint on it will.
|
|
|
|
|
Of course! I could do that. But what would be the best way to go? I kind of find the idea of making factoryId the primary key a bad idea. I don't know exactly why, but something tells me... Primary Key in one table, but also primary key in another table - well - I don't know.
UNIQUE constraint could be a way to go.
Please correct me if I'm wrong.
Best Regards
Soeren
|
|
|
|
|
Bad Robot wrote: Primary Key in one table, but also primary key in another table
Is valid for one-to-one or one-to-zero joins.
If you are always going to have one factory row with a single corresponding pollution row then you have a one-to-one relationship.
One-to-One relationships are a bit dodgy (I think) because it really means that the data belongs in the parent table. However, most one-to-one relationships are really one-to-zero relationships. e.g. If there was only ever going to be one pollution row for a corresponding factory row, but the pollution row was optional. So, it exists zero or one times for every factory row.
Bad Robot wrote: UNIQUE constraint could be a way to go
That just creates redundant data because the primary key is always unique anyway. Better to eliminate the redundant data and share the same values for the primary key in both tables.
|
|
|
|
|
Colin Angus Mackay wrote: That won't fix that part of the problem. Making FactoryId the primary key or putting a unique constraint on it will.
Wow, I can really be stupid sometimes. What was I thinking? Thanks for pointing that out.
CREATE TABLE [Pollution] (
[PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryID] [int] NOT NULL ,
[PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Pollution] PRIMARY KEY CLUSTERED
(
[PollutionID]
) ON [PRIMARY] ,
CONSTRAINT [IX_FactoryID] UNIQUE NONCLUSTERED
(
[FactoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY
(
[FactoryID]
) REFERENCES [Factory] (
[FactoryID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thank you very much guys! I think this would actually be the solution to my problem!
Best regards
Soeren
|
|
|
|
|
Create cluster. Now it is upto you whether u want to go for one big table or few small tables.
Hope this helps u...
|
|
|
|
|
i want to make navigation bar in my appliction
i want to know the methods in ADO.NET using C# to navigate records in database
|
|
|
|
|
A DataReader will retrieve rows in a database in a forward only fashion. You can use a DataAdapter to extract all the rows in one go and put them in a DataSet. You can then navigate at will because it uses indexes to retrieve the rows.
myDataSet[0].Tables[0].Row[i]
|
|
|
|
|
Hi,
I want to get the value of "OrderID", which is the first colum in the Gridview2, from the Gridview when a specific row is selected with hard coding.
My code is:
Session("OrderID") = Convert.ToString(CType(GridView2.Rows(GridView2.EditIndex).FindControl("Label4"), Label).Text)
I've also tried to use GridView2.SelectedIndex, which always returns -1, which is an invaliad index value.
or
GridView2.SelectedRow.Cells(1).Text
but it seems the SelectedRow is always nothing.
So I don't know what is wrong with me.
Can anyone help please?
Thank you.
|
|
|
|
|
Hi,
I didnt find any problem with your code. Which Event handler did you used? The below is sample code in C#.For your help[^]
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)<br />
{<br />
Label1.Text = GridView1.SelectedRow.Cells[2].Text;<br />
int index = GridView1.SelectedIndex;<br />
}
But , if you are used this code in SelectedIndexChanging event handler, it won't give correct value.
Regards,
Jay
|
|
|
|
|
I convered the image button in the Gridview into the Templet, and used the ImageButton1_Click event handler.
I think because when you select the row, it will change the row index to "-1", which does not really exist in the GridView,
By using:
Session("OrderID") = Convert.ToString(CType(GridView2.Rows(GridView2.SelectedIndex).FindControl("Label4"), Label).Text)
it keeps on throw me the error that "Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"
Otherwise if I use:
Session("OrderID") = Convert.ToString(CType(GridView2.SelectedRow.FindControl("Label4"), Label).Text)
It will say " Object reference not set to an instance of an object."
So it just doesn't work.
I don't know how to use these 2 properties.
Can you please help?
Thanks,
|
|
|
|
|
sbao004 wrote: when you select the row, it will change the row index to "-1"
Your SelectedRow property of GridView may not give you the exact Row which is selected currently ( in SelectedIndexChanging event handler, ofcourse which fires first when you select the Row). Have a look at
NewSelectedIndex Property[^]
What are the GridView event handlers did you used?
Regards,
Jay
|
|
|
|
|
Using vc#2005, I databinded an Access mdb datatable to a datagrid. I want it when the user adds a new entry, the "Language" column in the table will be automatically filled with the current culture code.
E.g. Say there's a total of 2 columns in the table, "Name" and "Language". After a user enters his name in the "Name" column and hits enter, the "Language" column will be automatically filled with "en-US" if the current culture is US, and "fr-FR" if French.
So two questions really:
1. How do I link a program variable to the "DefaultValue" property in the xsd dataset file?
2. How do I do it specifically for culture? (without hardcoding the values)
If only know answer to first question that is fine. Thanks.
|
|
|
|
|
hi friends
v are using Reporting Services 2005 in our project , we are using parameter in our reports , if we use 'From Non query ' there is no problem but whenever we using 'From Query ' , we got this following Error
'Forward Dependencies are not valid ' , we try it out lot , but we can't so please give me a solution
Regards
Raja-Chennai
|
|
|
|
|
how do we backup a transaction log of a certain database?
if transaction log doesn't exist yet, how to create transaction log?
thanks.
|
|
|
|
|
You back up a transaction log either through the Enterprise Manager (or Management Studio for SQL Server 2005) or using the BACKUP LOG statement.
The second question has no meaning for SQL Server - every database has at least one transaction log file, even in Simple recovery mode. The transaction log contains the information necessary to roll back to a particular point or, in the case of recovering from a failure, to roll forward to a particular point. SQL Server writes what it is going to do to the transaction log directly to the disk, then makes the modifications to the actual data pages in RAM. It can then write the changes to the data pages lazily, knowing that it can always recover the changes if there was a failure.
To support roll back, SQL Server records what was there before and what the new data is. If a transaction rolls back (either explicitly through a ROLLBACK TRANSACTION statement, or implicitly when a statement fails), SQL Server simply reads the 'before' image and applies it to the data pages.
If you're in Full recovery mode, the transaction log contains 'before' and 'after' images of every row that has been modified since you last backed up the log file. An INSERT has no 'before' image and a DELETE no 'after' image. TRUNCATE operations merely log that the truncation occurred, and I'm not sure that it's possible to undo them. If the database file becomes unusable, but the database is still attached to the server, I believe it is still possible to back up the transaction log; then, you can restore your last full database backup and restore all the transaction log backups in sequence to recover to the point of failure.
Bulk recovery mode is the same as full mode, but for BULK INSERT operations, SELECT INTO, CREATE INDEX and operations on text - and image -type columns, the server only records in the transaction logs which pages were affected by the change. When you make a transaction log backup, these pages are copied into the backup file. This does not allow rollback to a point in time, because only the latest versions of the bulk pages were stored. The data file must be available in order to back up the transaction log.
In Simple recovery mode, the log contains all the information that the Full model does, but it is automatically truncated on a checkpoint. Space occupied by transactions that had already committed becomes eligible for reuse. Long-running transactions can still cause the transaction log to grow in size.
Making a full backup of the database does not back up the transaction log (this is different from Exchange, which does). The space in the transaction log is only reused once it has been backed up. You should regularly back up the transaction log to stop it growing out of control.
If you only have an .mdf (data file) but not the corresponding .ldf (log file), you've got problems. There are ways to rebuild your database, and gain a log file, but they are not guaranteed to leave your database in a transactionally-consistent state. See here[^] for more details. You should restore a backup if you have one.
|
|
|
|
|
Can anyone help me with a SQL Script that backups SQL Server 2005 database with the current date i.e the name of the backup file should also include the current date
drajkhowa
|
|
|
|
|
Sql server 2005 seem cant easily backup data to be used in sql server 2000. I'm using sql server 2005 to build my database. But my fren is using sql server 2000. He complain that he cant restore using my backup file. Any body can help me?
|
|
|
|
|
how can I return vaule recordset and @@rowcount from stroed procedure in asp?
Stored Procedure:
CREATE Pro sp_results
(
@FileNumber varchar(100),
@StreetNumber varchar(50)
)
AS
SET NOCOUNT ON
BEGIN
SELECT RecordIndexDB.entity_id,
RecordIndexDB.job_entity_id,
RecordIndexDB.file_number,
RecordIndexDB.file_location,
RecordIndexDB.street_name1,
RecordIndexDB.street_name2,
RecordIndexDB.street_name3,
RecordIndexDB.street_name4,
RecordIndexDB.street_name5,
RecordIndexDB.street_number1,
RecordIndexDB.street_number2,
RecordIndexDB.street_number3,
RecordIndexDB.street_number4,
RecordIndexDB.street_number5
FROM RecordIndexDB
WHERE RecordIndexDB.status = 1
AND
(
(RecordIndexDB.file_number Like '%' + @FileNumber + '%' AND len(@FileNumber)>0 ) OR
(len(@FileNumber)=0)
)
AND
(
((RecordIndexDB.street_number1 Like '%' + @StreetNumber + '%' OR
RecordIndexDB.street_number2 Like '%' + @StreetNumber + '%' OR
RecordIndexDB.street_number3 Like '%' + @StreetNumber + '%' OR
RecordIndexDB.street_number4 Like '%' + @StreetNumber + '%' OR
RecordIndexDB.street_number5 Like '%' + @StreetNumber + '%') AND len(@StreetNumber)>0 ) OR
(len(@StreetNumber)=0)
)
ORDER BY RecordIndexDB.file_number ASC
END
Return @@RowCount
In my asp page, I have a recordset to hold the results, but I don't know how to get the @@RowCount value. have any idea??
Lisa
|
|
|
|
|
Lisa you have a couple of options...
First you could execute your sql command as executereader (which returns a data reader) and you could read from that. You could execute your sql command as executescalar which returns the first row of the SP as a string. For both of these options you will need to SET @Rowcount = @@RowCount then SELECT @RowCount. Or you could have an output parameter in your stored proc. Your asp code would need to be looking for a parameter type of output in order to grab this value.
This article should help you assess what you would like to do http://aspnet.4guysfromrolla.com/articles/062905-1.aspx[^]
I hope this helps.
|
|
|
|
|
What is the difference between cloaning and copy in an array?
|
|
|
|
|
Perhaps you might like to try a forum for the programming language you are using. This does not relate to SQL. It may relate to C++ or C# - you might have better luck in one of those forums.
|
|
|
|
|
Good day everyone,
I'm doing a project with database MS Access in VB.Net 2005.
I'm struggling with the connection and coding with database.
Does anyone know where I can get a tutorial of how to connect to database and manipulate with data?
Thanks for your help in advance.
Eric
eric
eric
|
|
|
|
|