Database

Re: Timeout Expired The timeout period elapsed prior to completion

9-Sep-05 7:56

Thanks for the link.

Jaffer Mumtaz11-Sep-05 19:41

Jaffer Mumtaz

11-Sep-05 19:41

Thanks guys for ur immediate response but i have tried the things u have pointed out. I ran the query through query analyzer and it seems to give me timeout their as well. I think this seems to be an issue with SQL server not with connection. My database only have 120000 records but i am storing PDF its size is over 10 GB. When i ran select query on a particular table it runs fine but when i try to update a row or record or delete a record it gives timeout using query analyzer. Any one who can point out this issue why is this happening.

Once again thnx for ur immediate response.

Jaffer

Re: Timeout Expired The timeout period elapsed prior to completion

OMalleyW12-Sep-05 6:32

OMalleyW

12-Sep-05 6:32

Then this is totally a data problem. You could try to partition your table...
Here is some light reading on the how and why.

Clcky[^]

hope this helps

Will

-- modified at 12:33 Monday 12th September, 2005

Re: Timeout Expired The timeout period elapsed prior to completion

Andy Brummer12-Sep-05 8:27

Andy Brummer

12-Sep-05 8:27

First of all the image data type can only hold 2GB of data so even if your operation didn't time out, it would fail. If you have to store documents which are that large, you are going to have to increase your command timeout. The connection timeout should only increase if the server is getting overloaded.
120,000 records is a relatively small amount for sql server to handle if indexes are setup properly. Make sure the indexes are on the table to support all your queries. If that doesn't fix the problem, then start looking to see if SQL server is bound up with disk or memory access from the large files, or if it is just load from the tables in the database.

I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon

DataRow Deletion Error.Please Help Me !

User 20930738-Sep-05 21:10

User 2093073

8-Sep-05 21:10

Hi everybody !
I have a marvelous problem in deletion of a DataRow from a dataset table.I want my application to delete rows virtually.To aim that,I handeled the 'RowDeleted' event of the DataTable and in that event handler set the "Deleted" column of row (A bit column in SQLServer) to 1 and reject the changes of row to avoidance of row deletion after Updating the dataset to database.(I don't want the rows be deleted REALLY for undo capability of deletions).

See the code :

<code>
this.dataSet.Tables["tablename"].RowDeleted += new DataRowChangeEventHandle(RowDeleted);

//Event Handler
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{			
	//To change the RowState from Deleted to Unchanged
	e.Row.RejectChanges();
	
	//e.Row.RowState is Deleted yet.This means
	//e.Row.RejectChanges(); is not worked truly			
			
	e.Row["Deleted"] = 1;
}

But when the 'e.Row.RejectChanges();' Executes I receive the following Exception :

System.Data.DeletedRowInaccessibleException : Deleted row information cannot be accessed through the row.
Wonderful thing is that when I replace the above code with the following code, everything is ok and the RowState is changed to UnChanged after Catch block !!!!

<code>

private void RowDeleted(object sender, DataRowChangeEventArgs e)
{			
	try
	{
	  e.Row.RejectChanges();			
	}
	catch{}
		
	//e.Row.RowState is Unchanged Here.This means
	//e.Row.RejectChanges(); is worked truly
	
	e.Row["Deleted"] = 1;
}

How can I do with this problem ?

Sorry for bad english.Thanx alot !

[ _ Always there is another way _ ]

Re: DataRow Deletion Error.Please Help Me !

miah alom9-Sep-05 6:00

Re: DataRow Deletion Error.Please Help Me !

9-Sep-05 6:00

Assuming your DataTable is bounded to a DataGrid, I think this is not the best way to do it.
To do virtual deletes, you can use the default DataView and add a RowFilter with the condition "Deleted != 1".

It will display only the Rows with "Deleted != 1".

I hope this helps.

Thanks

-- modified at 13:56 Friday 9th September, 2005

User 20930739-Sep-05 18:41

User 2093073

9-Sep-05 18:41

Thanx for your help.That is true.I used the dataview with

<code>RowFilter = "Deleted != 1"

But this is just a Mask for VIEWING. I have problem when I want to set the Dleted column to 1 !! After setting this column everything will masked with a dataview RowFilter.

<code>
this.dataSet.Tables["tablename"].RowDeleted += new DataRowChangeEventHandle(RowDeleted);

//Delete Event Handler
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{			
	<font color=red>My problem is Here</font>
        <font color=green>
        //To change the RowState from Deleted to Unchanged
        //to avoidance of row deletion after Updating the dataset
	</font>
        e.Row.RejectChanges();
	
	e.Row["Deleted"] = 1;
}

Thanx for your attention

[ _ Always there is another way _ ]

-- modified at 0:44 Saturday 10th September, 2005

Re: DataRow Deletion Error.Please Help Me !

miah alom12-Sep-05 8:57

Re: Sql Enterprise Manager

12-Sep-05 8:57

I think I understand your problem correctly now.
My bet will be to subclass DataGrid that you are using and add the following method

public override bool PreProcessMessage( ref Message msg )
{

Keys keyCode = (Keys)(int)msg.WParam & Keys.KeyCode;

if(msg.Msg == WM_KEYDOWN
&& keyCode == Keys.Delete
&& ((DataView) this.DataSource).AllowDelete)
{

//Do something here(like row["Deleted"] = 1);
// return true will make sure the Row is not deleted.
return true;

}

return base.PreProcessMessage(ref msg);

}

Sql Enterprise Manager

smita_roy8-Sep-05 19:49

smita_roy

8-Sep-05 19:49

How can i hide a database to be listed in sql enterprise manager else how can i prevent data from being deleted in a table through enterprise manager

Andy Brummer8-Sep-05 20:03

Andy Brummer

8-Sep-05 20:03

Make sure only authorized users have rights to make changes to the database. If users have access to their own SQL user names and passwords for an application then you can't prevent them from using other access tools.

I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon

Re: Sql Enterprise Manager

enjoycrack8-Sep-05 23:28

enjoycrack

8-Sep-05 23:28

smita_roy wrote:
How can i hide a database to be listed in sql enterprise manager

=> No u cannot hide ur DB in SQL Enterprise Manager, I think. Because this GUI used to manage Databases

smita_roy wrote:
how can i prevent data from being deleted in a table through enterprise manager
=> u need to ensure that there's no one knows ur DB password except u

<< >>

Re: Sql Enterprise Manager

miah alom9-Sep-05 7:54

9-Sep-05 7:54

(1) How can i hide a database to be listed in sql enterprise manager ?
Just Detach the Database in the Enterprise Manager.

(2) How can i prevent data from being deleted in a table through enterprise manager ?
Remove the permission from the user

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

Error with INSERT INTO

°[Halo]°8-Sep-05 11:57

°[Halo]°

8-Sep-05 11:57

I have a simple insertinto statement with the correct syntax:

INSERT INTO BookingBook ('Date') Values ('01/01/2006');

Why is it throwing up the error that there is no such field as date when there most definitely is... Does the fact that it is a reserved word have anything to do with it...

Thanks.

°[Halo]°
Project Basilisk

Colin Angus Mackay8-Sep-05 12:22

Colin Angus Mackay

8-Sep-05 12:22

INSERT INTO BookingBook ([Date]) Values ('01/01/2006');

The apostrophes indicate a string literal. Use square brackets or quotes.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

enjoycrack8-Sep-05 21:14

enjoycrack

8-Sep-05 21:14

°[Halo]° wrote:
INSERT INTO BookingBook ('Date') Values ('01/01/2006');

the field name is not qouted by brackets, the correct one should be
INSERT INTO BookingBook (Date) Values ('01/01/2006')

u can refer in SQL book online
<< >>

Frank Kerrigan9-Sep-05 0:43

Frank Kerrigan

9-Sep-05 0:43

You could convert the string date on the fly ( if your usinf TSQL )

<br />
INSERT INTO BookingBook ('Date') <br />
Values ((Convert( datetime, '01/01/2005', 103 ))<br />

103 is DD/MM/YYYY

I prefer stating the exact date to SQL as it stops date problems with mixing up DD and MM.

miah alom9-Sep-05 5:41

9-Sep-05 5:41

INSERT INTO BookingBook ([Date])
Values ((Convert( datetime, '01/01/2005', 103 ))

°[Halo]°11-Sep-05 22:43

°[Halo]°

11-Sep-05 22:43

Using sq brackets did not solve this. This provided me also with the error "INSERT INTO statement contains an unknown field 'Date'" or something to that effect. In the end I just changed the field name... I believe that access is reading "Date" as an identifier rather than a field name where it expects a field name...?

°[Halo]°
Project Basilisk

QuietKnight13-Sep-05 11:16

QuietKnight

13-Sep-05 11:16

Quite possibly. If this is on MS SQL, try using brackets [] around the field.

QuietKnight13-Sep-05 11:18

QuietKnight

13-Sep-05 11:18

Doh.. replies didnt show up at first. Ignore redundant post.

about SQL ...

mostafa_h8-Sep-05 9:25

mostafa_h

8-Sep-05 9:25

Hi !
I want to search the field of "telephone":

table:MyTable ,
id_name[int],name[nvarchar],family[nvarchar],telephone[decimal].

id_name is a primary key .
when I want to search a telephone , for example:
telephone=3339964
I write in c#:

string mysearch;
mysearch="SELECT * From MyTable WHERE telephone=3339964";

Now , If the telephone is NULL (one don't enter his telephone) and I want to search this name and family,How can I write this SQL ? Confused | :confused:

mostafa hosseiny b.

Colin Angus Mackay8-Sep-05 10:57

Colin Angus Mackay

8-Sep-05 10:57

So, you want a query that will search by telephone. If telephone is null you want to seach by name and family instead.

This is the query you want:

SELECT * 
FROM MyTable
WHERE (telephone=@telephone AND @telephone IS NOT NULL)
OR ((family = @family AND name = @name) AND @telephone IS NULL)

And the C# code to go with it:

string mySearch = "SELECT * "+
"FROM MyTable "+
"WHERE (telephone=@telephone AND @telephone IS NOT NULL) "+
"OR ((family = @family AND name = @name) AND @telephone IS NULL)";
SqlCommand cmd = SqlCommand(mySearch, myConnection);
cmd.Parameters.Add("@telephone", telephoneNumber);
cmd.Parameters.Add("@name", name);
cmd.Parameters.Add("@family", family);
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
    // Do what you need to do with each row from the table.
}

From the code above telephoneNumber needs to be an object as it needs to be able to supply a System.DBNull.Value in place of the number. You could set this up like this:

object telephoneNumber;
if (telephoneNumberTextBox.Text == "")
    telephoneNumber = System.DBNull.Value;
else
    telephoneNumber = telephoneNumberTextBox.Text;

By the way, I'm not sure storing a telephone number as a decimal is the best approach. Certainly I've always stored them as strings and I've never seen them stored in any numeric format. How do you deal with phone number that start with a zero, or international numbers that are stored with a country independent prefix of +, or special service phone numbers that include the * or #.

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

mostafa_h8-Sep-05 15:24

mostafa_h

8-Sep-05 15:24

Thanks in advance !!;)

mostafa hosseiny b.

Colin Angus Mackay8-Sep-05 20:08

Colin Angus Mackay

8-Sep-05 20:08

manije wrote:
Thanks in advance

Confused | :confused:

Are you a time traveller?? Poke tongue | ;-P

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious