How to Get Primary Key From the Table Return From Database - Database Discussion Boards

Yaron Ben-Ami14-Aug-07 5:48

14-Aug-07 5:48

Hi there!

I have a table of users, that has a city column.
I would like to find the cities from which I have only one registered user.

This is the code I use:

SELECT city<br />
FROM users<br />
GROUP BY city<br />
HAVING (COUNT(city) = 1)

It works fine, but I also want to get the id of the user, and that's where the problem is.

If I use this code:

SELECT city, id<br />
FROM users<br />
GROUP BY city, id<br />
HAVING (COUNT(city) = 1)

I get wrong results because the id column is unique, and the GROUP BY makes a mess.

I hope you'll understand... Smile | :)

Thanks in advance!

Re: GROUP BY problem (MSSQL)

Colin Angus Mackay14-Aug-07 5:51

Colin Angus Mackay

14-Aug-07 5:51

Have a subquery:

SELECT u.id, c.city
FROM users AS u
INNER JOIN (SELECT city
            FROM users
            GROUP BY city
            HAVING (COUNT(city) = 1)
           ) AS c ON c.city = u.city

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: SQL Bits

My website

Re: GROUP BY problem (MSSQL)

Colin Angus Mackay15-Aug-07 13:18

Colin Angus Mackay

15-Aug-07 13:18

And my previous post was worth a one for what reason?

If the answer didn't help or I didn't fully understand the question then I'd prefer an opportunity to resolve the problem rather than be randomly voted down.

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

MSgt~Nesher14-Aug-07 3:56

MSgt~Nesher

14-Aug-07 3:56

I'm connecting to an old DBF file (dBase 5.0) and after sevral commands are executed (about 570-590 times)

it shows me thw following error

ERROR [HY001] [Microsoft][ODBC dBase Driver] System resource exceeded.

and reading the DB failes after that for good.

<br />
        private void CollectDBData()<br />
        {<br />
            try<br />
            {<br />
                // Defining OdbcConnection to connect to DB.<br />
                OdbcConnection conn = new OdbcConnection();<br />
                OdbcDataReader reader = null;<br />
<br />
                while ((m_areThreadActive) && <br />
                    (Thread.CurrentThread.ThreadState ==<br />
                    System.Threading.ThreadState.Running))<br />
                {<br />
                    try<br />
                    {<br />
                            // Setting connection string.<br />
                            conn.ConnectionString = @"Driver={Microsoft dBASE Driver(*.dbf)};DriverID=533;Dbq=c:\";<br />
<br />
<br />
                            // Opening connection to DB.<br />
                            conn.Open();<br />
<br />
<br />
                            // Creating sql select command string.<br />
                            string commandStr = "SELECT * FROM TABLE1";<br />
<br />
 <br />
<br />
                            // Creating sql select command.<br />
                            OdbcCommand comm =  new OdbcCommand(commandStr, conn);<br />
<br />
 <br />
<br />
                            // Executing command!<br />
                            reader = comm.ExecuteReader();<br />
<br />
                            while ((reader!=null) && (reader.Read()))<br />
                            {<br />
                                string data = GetColumnValStr(reader,DBColumnsEnum.DATA1));<br />
<br />
                            // This data is inserted to queue for another thread to handle.<br />
                            }<br />
<br />
                            reader.Close();<br />
                            reader = null;<br />
                            comm.Dispose();<br />
                            conn.Close();<br />
                    }<br />
                    catch (Exception ex)<br />
                    {<br />
                        Console.WriteLine(ex.Message);<br />
                    }<br />
                    finally<br />
                    {<br />
                        // Closing connection to DB.<br />
                        if (reader != null)<br />
                        {<br />
                            reader.Close();<br />
                        }<br />
                        if (conn != null)<br />
                        {<br />
                            conn.Close();<br />
                        }<br />
                    }<br />
<br />
                    Thread.Sleep(1000);<br />
                }<br />
            }<br />
            catch (Exception exc)<br />
            {<br />
                Console.WriteLine(exc.Message);<br />
            }<br />
<br />
        }<br />
<br />
        private string GetColumnValStr(OdbcDataReader reader, DBColumnsEnum column)<br />
        {<br />
            try<br />
            {<br />
                string col = column.ToString().ToUpper();<br />
                return Encoding.GetEncoding(1255).GetString(<br />
                    Encoding.GetEncoding(862).GetBytes(reader[col].ToString()));<br />
            }<br />
            catch (Exception ex)<br />
            {<br />
                Console.WriteLine(ex.Message);<br />
            }<br />
            return string.Empty;<br />
        }<br />

the thread sleeps for 1 second (in the real code it is changable by app.config)

but for frequent updated sometime it will be as low as 1 sec...

i've tried to play with connection string and other stuff
i also tried connecting through Access file and got same errors...

i get another message from time to time

ERROR [HY000] [Microsoft][ODBC dBase Driver] Unexpected error from external database driver (15877).

this something i've searched the web and came out empty

except shuting down the application and opening it again

not a good thing for me

and replcaing the DB ain't possible cause it's not my system and i can't touch\don't have the code

(this application works over another application's DB without changing the data in it, only reading it)

btw i'm using the latest MDAC (2.8 SP1) and the computer is running win 2000

in the mean while running the same application on win 2003 over 10 times the time it runs on the destination computer

anyway, every help will be much appreciated to solve this mind crazy problems

Thanks Smile | :)

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

Michael Potter14-Aug-07 9:23

Michael Potter

14-Aug-07 9:23

I will take a shot at this:

Start Quote: DbConnection.Close Method Documentation

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

An application can call Close more than one time. No exception is generated.

If the DbConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose, which are functionally equivalent. If the connection pooling value Pooling is set to true or yes, this also releases the physical connection.

End Quote

Close() and Dispose() are functionally equivalent. This means that you are lucky that your application functions at all. Most likely you have lots of memory and it takes time for the garbage collector to catch up and completely deallocate your connection object. The driver is eventually getting a pointer to a jumble of crap. To fix this, you need to reallocate (conn = new OdbcConnection();) just before you assign the connection string in the second try block.

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

MSgt~Nesher14-Aug-07 20:52

MSgt~Nesher

14-Aug-07 20:52

i'll try it now and get back to you... thanks!

hope it works for the destination computer...

in a different computer (stronger one as i said with the win 2003 server) the app is working almost 24hours
with no problem.. :\

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')");

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