Database Discussion Boards

Re: C# 2.0 Table Adapter Times Out at 30 Sec

Colin Angus Mackay15-Apr-06 12:59

15-Apr-06 12:59

There are two timeouts. The Connection Timeout is the length of time that you are prepared to wait for it to connect to the database (not do any work). You are looking for the Command Timeout which defines the length of time that the command can take. By default it is set at 30 seconds. You need to change the CommandTimeout on the SqlCommand object you are using.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Re: C# 2.0 Table Adapter Times Out at 30 Sec

GTWebb17-Apr-06 5:44

GTWebb

17-Apr-06 5:44

Thanks for guiding me in the right direction. I still have another problem. When I create a partial class to adjust the command timeout.

The _commandCollection and _adapter are always null.

namespace mdsTableAdapters
{
public partial class MedDiagTableTableAdapter
{

public int SelectCommandTimeout
{

get
{
return (this._commandCollection[0].CommandTimeout);
}

set
{
for (int i = 0; i < this._commandCollection.Length; i++)
{
if ((this._commandCollection[i] != null))
{
((System.Data.SqlClient.SqlCommand)
(this._commandCollection[i])).CommandTimeout = value;
}
}
}

}
}
}

Re: C# 2.0 Table Adapter Times Out at 30 Sec

Colin Angus Mackay17-Apr-06 5:50

Colin Angus Mackay

17-Apr-06 5:50

What benefits are you getting from using a partial class in this instance?

GTWebb wrote:
The _commandCollection and _adapter are always null.

Do you ever initialise them somewhere? You don't show that code so I can't see what could be wrong.

Re: C# 2.0 Table Adapter Times Out at 30 Sec

GTWebb18-Apr-06 3:04

GTWebb

18-Apr-06 3:04

I'm using a partial class because that is the only way to get to the CommandTimeout property. The _commandCollection and _adapter are already incorportated into the TableAdapter Class. From everything that I have read they do not need to be initialized.

Here is the project I was basing this from.

http://www.codeproject.com/csharp/TableAdaptrCommandTimeout.asp

Re: C# 2.0 Table Adapter Times Out at 30 Sec

GTWebb18-Apr-06 8:59

GTWebb

18-Apr-06 8:59

To answer my own question. Although all the examples online use _commandCollection I used CommandCollection and it works mooooe butter.

How to restor database

papa198013-Apr-06 21:41

papa1980

13-Apr-06 21:41

Hello everybody could tell me how to restore my db after windows reinstallation?When i try it give me errore "RESTORE DATA BASE MUST BE USED IN SINGLE USER MODE WHEN tring to restor to master database.Restore datebase is terminating abnormally" how to solve it ??????????????
pleas help me Frown | :(

(

-- modified at 3:42 Friday 14th April, 2006

Re: How to restor database

albCode13-Apr-06 21:50

albCode

13-Apr-06 21:50

check this

Create Table from Trigger

Guinness4Strength13-Apr-06 12:20

Guinness4Strength

13-Apr-06 12:20

I'm attempting to create a new table from and INSERT trigger on MSSQL Server.
I've tried the following with no luck...any help out there /

ALTER   TRIGGER [TRG_INS_CLIENTS] ON [dbo].[Clients] 
FOR INSERT
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName+'_ParseTable' FROM inserted

CREATE TABLE @ClientName (
	[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
	[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

Re: Create Table from Trigger

Eric Dahlvang14-Apr-06 10:53

Eric Dahlvang

14-Apr-06 10:53

One possible solution:

CREATE TRIGGER TRG_INS_CLIENTS ON [dbo].[Clients] 
FOR INSERT 
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName FROM inserted

EXEC('CREATE TABLE ' + @ClientName + '(
	[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
	[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL )')

I'm assuming that your field name is ClientName. I'm not sure what +'_ParseTable' is. If it is the field name, then just put ClientName_ParseTable in the select.

Be careful with EXEC() - it opens a door to malicious code.

----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin

Re: Create Table from Trigger

Guinness4Strength16-Apr-06 13:55

Guinness4Strength

16-Apr-06 13:55

Thanks ! I found another solution using sp_sqlexecute() but this one is more elligant.

Re: Create Table from Trigger

Eric Dahlvang17-Apr-06 3:26

Eric Dahlvang

17-Apr-06 3:26

Glad it helped. Here is some info on sp_sqlexec.

SQL Books Online

SQL Server 6.x
sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application.

SQL Server 2000
Removed; no longer available. Remove all references to sp_sqlexec.

----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin

SQL Newbie question

martin_hughes13-Apr-06 7:32

martin_hughes

13-Apr-06 7:32

Hi all,

In a test database I have two tables, which are joined on a primary key. In the first table, this key is an auto-incrementing field.

Can anyone tell me a SQL (ie generic) way of being able to insert records into the primary table, retrieve the Primary Key value and be able to use that in the secondary table to create a related record?

I have thought about using a second query to return the last record entered, hwoever this seems a bit ugly, and would be prone to error in a multi-user environment.

Thanks,

Martin.

Re: SQL Newbie question

mastjabs13-Apr-06 9:38

mastjabs

13-Apr-06 9:38

Is the database SQL server 2000..?U can use the "@@identity" to retrieve the primary key of the last record entered..

Re: SQL Newbie question

martin_hughes13-Apr-06 11:25

martin_hughes

13-Apr-06 11:25

At the moment it's Access - but it could be SQL Server, Oracle OR DB2 in the future

Re: SQL Newbie question

Colin Angus Mackay13-Apr-06 11:33

Colin Angus Mackay

13-Apr-06 11:33

I don't think there is a way that is so generic that it could handle all that. Each database vendor uses a slightly different flavour of SQL. For example SQL Server 2000 is based roughly on ANSI SQL-92, whereas SQL Server 2005 is roughly based on ANSI SQL-99. Oracle will have its on proprietary features and ways of doing things, as will DB2 and so on.

You could try looking at an open source product called NHibernate[^] which has its own SQL varient which is then translated in to the specific flavour of SQL required by the back end database.

Re: SQL Newbie question

Frank Kerrigan13-Apr-06 22:19

Frank Kerrigan

13-Apr-06 22:19

MS Enterprise Libraries offer a Db access layer support across different databases manufactures; but it's not for the newbies or the faint hearted.

http://msdn.microsoft.com/library/?url=/library/en-us/dnpag2/html/EntLib2.asp[^]

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: SQL Newbie question

Colin Angus Mackay17-Apr-06 6:01

Colin Angus Mackay

17-Apr-06 6:01

Frank Kerrigan wrote:
MS Enterprise Libraries offer a Db access layer support across different databases manufactures;

True, but not to the same extent as Hibernate, unless I missed something in the Enterprise Library documentation. Hibernate (and NHibernate) offers the ability to translate SQL into the various flavours. For example. In SQL Server you might have

SELECT TOP 10 * FROM MyTable

, while in Oracle the equivalent (if I remember) is something like

SELECT * FROM MyTable LIMIT 10,0

NHibernate permits the developer to develop in their varient which is then translated to any of the supported vendors SQL Language varient with not much more effort that a change in a config file.

As far as I can see the Enterprise Library still requires that the developer write SQL against the varient used by the back end database system - so there will still be a lot of work to do to get it to work nicely with various database systems.

Frank Kerrigan wrote:
Look where you want to go not where you don't want to crash.

Excellent advice. Big Grin | :-D

I remember from a skid driving course I did a few years ago something similar.

list all tables names in combobox

valiovalio13-Apr-06 1:24

valiovalio

13-Apr-06 1:24

Hi, I want to list all tables names in combobox. Can somebody write a code example. Thank you Smile | :)

Re: list all tables names in combobox

Dinuj Nath13-Apr-06 1:27

Dinuj Nath

13-Apr-06 1:27

select * from information_schema.tables where table_type='Base table'

-
बुरा जो देखण मै चला, बुरा न िमलया कोय,
जो मन खोजा आपणा तो मुझसे बुरा न कोय।

Translation

Re: list all tables names in combobox

valiovalio13-Apr-06 3:11

valiovalio

13-Apr-06 3:11

thank You

Can we pass an argument for the top function in sql server 2005

varmag13-Apr-06 1:05

varmag

13-Apr-06 1:05

I want to write genric select statement in which i want to get the top 10 people fom the tabel based on their total marks column.
I am getting the result when i give

"select top 10 percent fom studentmarks"

But it is not working when i making the statement generic.

"select top @p persent from stiudentmarks"

I am using SQL server 2000
I want to run it on the same (i have already tried it on server 2005 it is working fine)

Please any body help me how to make it work on SQlserver 2000

Varmag

Re: Can we pass an argument for the top function in sql server 2005

Colin Angus Mackay13-Apr-06 5:15

Colin Angus Mackay

13-Apr-06 5:15

varmag wrote:
I am using SQL server 2000

This feature is not supported on SQL Server 2000. It is a new feature introduced in SQL Server 2005.

varmag wrote:
Please any body help me how to make it work on SQlserver 2000

You could use dynamic SQL and construct a string like this:

DECLARE @sql VARCHAR(1000);
SET @sql = 'select top '+CAST(@p AS VARCHAR(10))+' percent fom studentmarks';
EXEC(@sql)

However, be careful as dynamic SQL can be a source of SQL Injection Attacks. If @p is already an integer type then the code should be fine.

Re: Can we pass an argument for the top function in sql server 2005

HimaBindu Vejella14-Apr-06 0:06

HimaBindu Vejella

14-Apr-06 0:06

Colin Angus Mackay wrote:
However, be careful as dynamic SQL can be a source of SQL Injection Attacks

What is SqlInjection attack?

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

Re: Can we pass an argument for the top function in sql server 2005

Colin Angus Mackay14-Apr-06 0:31

Colin Angus Mackay

14-Apr-06 0:31

HimaBindu Vejella wrote:
What is Sql Injection attack?

See here[^] or here[^]

DataAdapter.FillError

Jochen Jonckheere12-Apr-06 22:35

Jochen Jonckheere

12-Apr-06 22:35

Hi,

In a project I'm working on, we are using the DataAdapter.Fill(table) to get the data from the database. The table we are filling is created before we call the fill. When the DataTable contains a column with a System.Char type I get a FillError.

Here is some code to make it clear.
The field status is a CHAR in the database to.

<br />
' Create a table<br />
Dim table As New System.Data.DataTable("Customer")<br />
<br />
With table .Columns()<br />
    .Add("id", GetType(System.Int32));<br />
    .Add("name", GetType(System.String));<br />
    .Add("status", GetType(System.Char));<br />
End With<br />
<br />
' Other code to create and open the connection / dataadapter<br />
...<br />
<br />
' Create connection / command / dataadapter<br />
Dim cn As New SqlConnection("connectionstring")<br />
cn.Open()<br />
<br />
Dim cm As New SqlCommand("SELECT id, name, status FROM Customer", cn)<br />
cm.CommandType = CommandType.Text<br />
<br />
Dim da As New SqlDataAdapter(cm)<br />
<br />
' I add a handler to catch my exception<br />
AddHandler da.FillError, New FillErrorEventHandler(AddressOf Helper.FillError)<br />
<br />
table.BeginLoadData()<br />
da.Fill(table)<br />
table.EndLoadData()<br />

Because the predefined DataTable contains a column with a Char type I always get the FillError. I catch the error and try to solve it, see the next code snippit.

<br />
Public Shared Sub FillError(ByVal sender As Object, ByVal args As FillErrorEventArgs)<br />
	Dim table As System.Data.DataTable<br />
	Dim row As System.Data.DataRow<br />
	Dim values As Object()<br />
<br />
	Dim valueType As System.Type<br />
	Dim tableType As System.Type<br />
<br />
	' Try to solve exception<br />
	If args.Errors.GetType() Is Type.GetType("System.ArgumentException") Then<br />
		' There was an error during the fill<br />
		table = args.DataTable<br />
		values = args.Values<br />
<br />
		If args.Values.Length = table.Columns.Count Then<br />
			row = table.NewRow<br />
<br />
			' Try not to get here, not good for the performance<br />
			For i As Integer = 0 To table.Columns.Count - 1<br />
				valueType = values(i).GetType<br />
				tableType = table.Columns(i).DataType<br />
<br />
				If valueType.Equals(tableType) OrElse values(i) Is System.DBNull.Value Then<br />
					row(i) = values(i)<br />
				Else<br />
					row(i) = Convert.ChangeType(values(i), tableType)<br />
				End If<br />
			Next<br />
<br />
			table.Rows.Add(row)<br />
<br />
			' Continue without error<br />
			args.[Continue] = True<br />
		Else<br />
			' Throw error<br />
			args.[Continue] = False<br />
		End If<br />
	End If<br />
End Sub<br />

Now you wonder why I post this code. Because it seems I have a solution. But the solution is slow and not correct all the time. That's why I have the following questions.

Is there a better solution? One without catching the FillError event.
Is there a reason why the Fill doesn't work with a Char?

I hope somebody can help or someone else is helped with the code I provided.

Jochen

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.