Database Discussion Boards

Re: Problems connecting to old DBF with OleDB and .NET (C#)

MSgt~Nesher14-Aug-07 22:19

MSgt~Nesher

14-Aug-07 22:19

as expected... not working Dead | X|

thanks for the help anyway.. if you have any other ideas
i'd appreciate them

How to Get Primary Key From the Table Return From Database

N a r e s h P a t e l14-Aug-07 3:44

N a r e s h P a t e l

14-Aug-07 3:44

Hi Friends,
I need the Primary Key of the Table return from DataBase.
Eg: I am useing the following code:

DataTable dt=new DataTable();
dt=GetDataTable();
DataColumn[] columns;
columns =dt.PrimaryKey;
for (int col = 0; col < columns.Length; col++)
{
///Process the Primary Key
}

But the above code-dt.PrimaryKey always returns dimansion[0] means nothing.

Is there any idea regarding this.
Please help.
Thanks in advance.
Rose | [Rose]

Naresh Patel

Re: How to Get Primary Key From the Table Return From Database

Blue_Boy14-Aug-07 4:45

Blue_Boy

14-Aug-07 4:45

declare	@TableName varchar(128)<br />
	select @TableName = 'tablename'<br />
	<br />
	select 	c.COLUMN_NAME <br />
	from 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,<br />
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c<br />
	where 	pk.TABLE_NAME = @TableName<br />
	and	CONSTRAINT_TYPE = 'PRIMARY KEY'<br />
	and	c.TABLE_NAME = pk.TABLE_NAME<br />
	and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

I Love SQL

Connecting to SQL 2005 Express remotely

GermanDM14-Aug-07 3:20

GermanDM

14-Aug-07 3:20

Hi,

I know that you cannot connect remotely from SQL server 2005 Express to any other sql server.
But is it maybe possible to connect remotely from SQL Server 2005 (full edition) to SQL server 2005 express?

Thanks

fatch specified no

MHASSANF14-Aug-07 3:09

MHASSANF

14-Aug-07 3:09

hi

how can i fatch specified no of rows starting from some row no. for eg. if i want to fatch records from 5th row to 10th row how can i do it.

With Thanks

MHF

Re: fatch specified no

Krish - KP14-Aug-07 3:17

Krish - KP

14-Aug-07 3:17

include an autoincrement column. using which this can be done.

Regards
KP

Re: fatch specified no

MHASSANF14-Aug-07 3:26

MHASSANF

14-Aug-07 3:26

i want to know this is only way ?

is there any sql syntax ( like TOP ) that return specified no of rows ?

MHF

Re: fatch specified no

N a v a n e e t h14-Aug-07 3:30

N a v a n e e t h

14-Aug-07 3:30

SQL Server 2005 having ROW_NUMBER() function.

My Website | Ask smart questions

Re: fatch specified no

Krish - KP14-Aug-07 3:55

Krish - KP

14-Aug-07 3:55

in SQL Server 2000 is not possible.,
you may try as navaneeth suggesting.

Regards
KP

SQL Query reverse LIKE [modified]

Ronni Marker14-Aug-07 0:11

Ronni Marker

14-Aug-07 0:11

Gents,

I have a string "abcd1;abcd2;abcd3;abcd4"

in my database on an MS SQL there are stored "abcd3" in a column. In the reverse situation I would be able to do a select statement like this

Select * from MyTable where MyColumn LIKE '%abcd3%'

Is there any easy solution to it in the reverse way so i fx could do something like

Select * from MyTable where 'abcd1;abcd2;abcd3;abcd4' LIKE MyColumn

Tried this by the way, didnt return any result or error so guess there are some solution to it.

Cheers,

-- modified at 6:28 Tuesday 14th August, 2007

Re: SQL Query reverse LIKE

Rami Said Abd Alhalim14-Aug-07 0:24

Rami Said Abd Alhalim

14-Aug-07 0:24

this not correct because '%adbcd3%' not equal to '%abcd3%'
Select * from MyTable where MyColumn LIKE '%adbcd3%'
if you need show resault you must write select statment

Select * from MyTable where MyColumn LIKE '%abcd3%'

Re: SQL Query reverse LIKE

Ronni Marker14-Aug-07 0:28

Ronni Marker

14-Aug-07 0:28

Hi Rami,
This was an typo in this topic on the example for the normal way to use like statement.

Thanks for the notification of the spelling error.

/Ronni

Re: SQL Query reverse LIKE

Rami Said Abd Alhalim14-Aug-07 0:32

Rami Said Abd Alhalim

14-Aug-07 0:32

hi ronii
you solve the problem
if you not solving problem i need more explane about your problem
with regards

Rami Abd alhalim

Re: SQL Query reverse LIKE

Ronni Marker14-Aug-07 0:47

Ronni Marker

14-Aug-07 0:47

nope didnt solve the problem. please read the whole text.

I have string "abcd1;abcd2;abcd3;abcd4" for example posted from a website or an application

In my database i have a string "abcd1".

I now need to find out if any part of the string ("abcd1;abcd2;abcd3;abcd4") is present in the column who hold fx "abcd1". This have to be done in a single select statement.

If done reverse "Find abcd1 in database with abcd1;abcd2;abcd3;abcd4 then a standard Select * from TableName where ColumnName LIKE '%abcd1%'" but is there an fast and easy to do that in a simliar way? just reverse in finding part of a string in the database?

/Ronni

Re: SQL Query reverse LIKE

Rami Said Abd Alhalim14-Aug-07 2:28

Rami Said Abd Alhalim

14-Aug-07 2:28

add this function after that you can split string to compare between your string

=======================
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER FUNCTION [dbo].[fn_Split]
(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
--Name2 Varchar(8000)

)
AS
BEGIN
/*Declare @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)

)
*/

DECLARE @index int
SET @index = -1

Declare @Text2 varchar(8000)
SET @Text2=@text
Declare @index2 int

WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index >1)
BEGIN
SET @index2=len(Right(@text, @index - 1))
SET @text2=SUBSTRING(@text,LEN(LEFT(@text, @index+1 )),CHARINDEX(@delimiter , @text)-1 )
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text , (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

=======================
after add this function
select * from tablename where 'abcd1'in select value from fn_Split('abcd1;abcd2;abcd3;abcd4',';')

Rami Abd alhalim

Re: SQL Query reverse LIKE

andyharman14-Aug-07 1:04

andyharman

14-Aug-07 1:04

How about:

select * from MyTable
  where ';abcd1;abcd2;abcd3;abcd4;' like '%;' + MyColumn + ';%'

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: SQL Query reverse LIKE

Ronni Marker14-Aug-07 2:45

Ronni Marker

14-Aug-07 2:45

Great thanks Andy. Worked like a charm.

Cheers,
Ronni

Problem in inserting date for datetime field

potlakayala14-Aug-07 0:09

potlakayala

14-Aug-07 0:09

I am inserting a row into database from asp.net. Below is the command i had given:

insert_cmd = string.Format("Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD',14/08/2007,30,'Medium')");

The date i had given in the command is 14/08/2007, but when I check the table data in SQL server the date is displayed as ---- 1/1/1900 12:00:00 AM

Please help me out... Frown | :(

Sailaja

Re: Problem in inserting date for datetime field

Rami Said Abd Alhalim14-Aug-07 0:29

Rami Said Abd Alhalim

14-Aug-07 0:29

this problem maybe cause format date
if you need sure that set in command this date
solve:
insert_cmd = string.Format("Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD',08/14/2007,30,'Medium')");

Re: Problem in inserting date for datetime field

potlakayala14-Aug-07 0:37

potlakayala

14-Aug-07 0:37

Said could you plz tell me how to do that as i dnt know.
i'm new to sql Frown | :(

Sailaja

Re: Change the date format as shown below.

okdeshpande14-Aug-07 1:57

okdeshpande

14-Aug-07 1:57

Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD','2007-8-11',30,'Medium'

In VB u have format function. U can use this function to format the date.
i.e. YYYY-MM-DD format.

Re: Change the date format as shown below.

potlakayala14-Aug-07 2:28

potlakayala

14-Aug-07 2:28

thx pande... the code worked correctly if I give the date in single quotes but am not getting the current system time in it.. Frown | :(

its displayed as:
8/14/2007 12:00:00 AM

Sailaja

Re: Change the date format as shown below.

Pete O'Hanlon14-Aug-07 2:50

Pete O'Hanlon

14-Aug-07 2:50

If you want to insert the current date and time, use the Getdate() function. It's a Sql Server function that returns the current date and time. For instance:

insert into myTable(CurrentDate) values (GetDate());

Deja View - the feeling that you've seen this post before.

Re: Change the date format as shown below.

potlakayala14-Aug-07 3:14

potlakayala

14-Aug-07 3:14

my problem has been solved.
thank u so much... Rose | [Rose]

Sailaja

How to put values in rows to columns in sql server 2005

Bhaskar Ravindranath14-Aug-07 0:05

Bhaskar Ravindranath

14-Aug-07 0:05

i have a table, i want to display the values in rows into columns in sql server 2005..
Ex:-

 a 1 x ----> a b c b 2 y ----> 1 2 3 c 3 z ----> x y z 

Can anyone help me on this??

Dream it to achieve it

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