Re: Generate Reference Number - Database Discussion Boards

Re: select upcoming birthday?

Meax11-Mar-09 23:27

Meax

11-Mar-09 23:27

i am just trying to learn

Re: select upcoming birthday?

Jay Royall11-Mar-09 23:41

Jay Royall

11-Mar-09 23:41

Sure I realise that, I am too, but the best way to learn isn't to get other people to do it for you. Like I said, Luc has offered a great deal of help here, more than enough for you to work out the rest.

If you still can't do it then post your code, I'm sure someone will be able to point you in the right direction.

Re: select upcoming birthday?

je_gonzalez15-Mar-09 16:07

je_gonzalez

15-Mar-09 16:07

SELECT *
FROM UsersProfile
WHERE ((403 + (31 * month(DOB) + day(DOB)) - (31 * month(getdate()) + day(getdate()))) % 403) =
(SELECT MIN(((403 + (31 * month(DOB) + day(DOB)) - (31 * month(getdate()) + day(getdate()))) % 403)) FROM UsersProfile)

Create Table in Oracle 10G

E_Gold11-Mar-09 2:47

E_Gold

11-Mar-09 2:47

Hi How I can make my Table in Oracle 10G ?
in graphically - like in Sql server

thank's in advance

Re: Create Table in Oracle 10G

Blue_Boy11-Mar-09 2:57

Blue_Boy

11-Mar-09 2:57

Did you try to search on google?[^]!

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Create Table in Oracle 10G

E_Gold11-Mar-09 5:36

E_Gold

11-Mar-09 5:36

Thanks for the help
but I need graphics explanation (better with picture) for How to
make Tables and Database.
I know to work with sql server, but today i install the Oracle...and...so much complicated... why Oracle ?? why ???......

Generate Reference Number

musefan11-Mar-09 0:06

musefan

11-Mar-09 0:06

Hi All,

Im creating an application in C# that needs to generate a partially-sequential value for a reference number which is stored in an SQL server database

the reference number will consist of three parts - a year value, a sequential number, and a system definded value

for example, 090001REF

then the next one generated would need to be 090002REF... 090003REF and so on

Ideally the sequence number would be reset at the start of a new year. 093245REF... 100001REF etc.

Can anyone offer a posible solution to how i would need to incorperate this functionality?

Can this be done within the database? or is this something i will need to handle throu my application? also i will need to keep in mind users creating a reference number at the same time, even if it is highly unlikely

Thanks for any suggestions Smile | :)

Life goes very fast. Tomorrow, today is already yesterday.

Re: Generate Reference Number

Blue_Boy11-Mar-09 2:18

Blue_Boy

11-Mar-09 2:18

Hope this will help you

string refnum = "091222REF";//here you get last reference number from database<br />
            string newrefNum = "";<br />
            if (DateTime.Now.ToString("yy") != refnum.Substring(0, 2))<br />
            {<br />
                newrefNum = DateTime.Now.ToString("yy") + "0001REF";<br />
            }<br />
            else<br />
            {<br />
                int incNum = int.Parse(refnum.Substring(2, 4)) + 1;<br />
<br />
                newrefNum = DateTime.Now.ToString("yy") +  incNum.ToString("0000") + "REF";<br />
            }

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Generate Reference Number

musefan11-Mar-09 2:29

musefan

11-Mar-09 2:29

Thanks for the reply, got me thinking in the right direction Smile | :)

i suppose some sort of TOP specification of 1 while ordering by created date and time would get the row i want.

Do you have any suggesion on how to avoid multiple users trying to add an entry at the same time thou?

What about just having the column as 'unique' then cathing the error and then trying the process again with an incremented reference?
Im not asking if it would be possible, as im sure it is, i just want to know if you can see any holes in the logic that i may have missed.

thanks again

Life goes very fast. Tomorrow, today is already yesterday.

Re: Generate Reference Number

Blue_Boy11-Mar-09 2:55

Blue_Boy

11-Mar-09 2:55

1. You can make unique column of reference number and then in exception you insert new reference number.
example:

try<br />
{<br />
//insret refnumber<br />
}<br />
catch<br />
{<br />
//get last inserted number in table and then genereate new refnumber<br />
}<br />
finally<br />
{<br />
//close connection or do some else<br />
}

2. You can select last inserted refnumber and then generate new refnumber.
Example:

select top 1 refnumber from mytable order by id desc //ID column must be inc autonumber

string refnum = "091222REF";//here you have last inserted reference number in database<br />
string newrefNum = "";<br />
if (DateTime.Now.ToString("yy") != refnum.Substring(0, 2))<br />
{<br />
newrefNum = DateTime.Now.ToString("yy") + "0001REF";<br />
}<br />
else<br />
{<br />
int incNum = int.Parse(refnum.Substring(2, 4)) + 1;<br />
<br />
newrefNum = DateTime.Now.ToString("yy") + incNum.ToString("0000") + "REF";<br />
}

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

T-SQL and XML question.

Jamie Nordmeyer10-Mar-09 17:11

Jamie Nordmeyer

10-Mar-09 17:11

A website I'm writing uses jQuery and a plugin called Flexigrid, that expects an XML document in this format:

<rows>
   <page>1</page>
   <total>7</total>
   <row id="1">
      <cell>1</cell>
      <cell>SomeName</cell>
      <cell>SomeDescription</cell>
   </row>
   <row id="2">
      <cell>2</cell>
      <cell>SomeName</cell>
      <cell>SomeDescription</cell>
   </row>
</rows>

I managed to make it work with the following SQL:

Select
	1 As 'page'
	,7 As 'total'
	,(
		Select
			ID As '@id'
			,(Select ID As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type)
			,(Select [Name] As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type)
			,(Select [Description] As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type, Elements XsiNil)
		From
			Table dc
		For Xml Path('row'), Type
	)
For Xml Path('rows'), Type

Is there a better way to do this? I've scoured Google, and wasn't able to find anything. This works, but it seems like there must be a better way that I'm just not finding.

Thanks.

Jamie Nordmeyer
Portland, Oregon, USA
http://www.feralcodemonkies.com

Random or sequential allocation!

Learner52010-Mar-09 14:06

Learner520

10-Mar-09 14:06

hi everybody,
i have two tables student and supervisor

1-Student
studentId studentname
1 john
2 paul
3 Martin
4 tony
.
.
.
14 Mike

2-Supervisor
SupervisorId supervisorName
1 Mark
2 Rodney
3 Wilson
4 Sarah
5 Raj
6 Neil
7 David

i hav 14 students and 7 supervisor i have to allocate each supervisor to 2 students. please could anyone tell me how i can allocate 1 supervisor to 2 sutdents or how can i display output as Allocation Table

like this
Allocation Table
AllocationId Student Supervisor
1 1 1
2 2 1
3 3 2
4 4 2
5 5 3
6 6 3
7 7 4
. . .
. . .
. . .
13 13 7
14 14 7

thanks for any kind help in adv.
regards
learner

Re: Random or sequential allocation!

Ashfield10-Mar-09 22:04

Ashfield

10-Mar-09 22:04

So didn't you understand the answer the first time you asked? If not, try responding to the original post, not starting a new one.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Random or sequential allocation

Learner52010-Mar-09 6:52

Learner520

10-Mar-09 6:52

hello everybody,
i have two tables Student and Supervisor

StudentId StudentName
1 john
2 paul
3 britney
4 Mike
.
.
.
.
13 beckham

SupervisorId SupervisorName
1 Martin
2 Tony
3 Janet
4 Brad
5 johnson
6 David
7 Raj

how can i allocate 7 supervisor to 13 students equally.
any help would be appricaited.
regards learner

Re: Random or sequential allocation

Blue_Boy10-Mar-09 13:32

Blue_Boy

10-Mar-09 13:32

I guess your table Student should contain column which have any value from Supervisor.
Your Student table have to looks like this :

StudentId StudentName  SupervisorId <br />
1         john          1<br />
2         paul          1<br />
3         britney       3<br />
4         Mike          5<br />
.<br />
.<br />
.<br />
.<br />
13       beckham        7

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

@@Identity and triggers

Distind10-Mar-09 3:51

Distind

10-Mar-09 3:51

I've got an issue that I'd like to find a better solution to,

I'm inserting a new row into a table, which fires of a trigger that does a set of inserts to replace something that used to be done manually. Previously I had been using @@Identity to retrieve the ID of the new row I inserted, however since I added the trigger I was getting the ID of the last item the trigger inserted.

I've found SELECT IDENT_CURRENT('tbl'), which works, but leaves me concerned with potential concurrency issues.

Any better methods out there or should I consider killing off the trigger to ensure the correct ID will always be returned?

Re: @@Identity and triggers

Mycroft Holmes10-Mar-09 16:35

Mycroft Holmes

10-Mar-09 16:35

Read up on Scope_Identity() in BOL. This is a classic error when using triggers.

Never underestimate the power of human stupidity
RAH

Cannot connect to SQL express database on XP Home edition under a new admin login

Shrikant Gujar9-Mar-09 23:11

Shrikant Gujar

9-Mar-09 23:11

Hello,

I have 2 admin users - say Admin1 and Admin2 - on a XP home edition. I installed my application on a machine using Admin1 login. The application runs smoothly.

Now, i logged off and logged in to the same machine using Admin2 credentials and try to launch the application. But the application could not be launched as the database connection could not be established. The application uses SQL express as the client side database. I have verified that the SQL express service is running on the client.

I tried to reproduce the same case on XP professional (SP2 and SP3 both) by creating 2 admin users. But could not reproduce the issue. The application works wonderfully with both the logins.

Is there anything with SQL Express that is clashing with XP Home???
Do I need to do some special settings on XP Home for SQL Express to work for all admin users ???

Any help would be highly appreciated.

TIA
~Shrikant

Composite Primary Key

Jay Royall9-Mar-09 22:02

Jay Royall

9-Mar-09 22:02

Hi,

Just a little advice needed here. During university I studied databases extensively which included the use of composite primary keys. Now, since leaving university a year or so ago and working in the industry, I haven't come across the use of a composite primary key anywhere. And what's more, I have come across databases where I believe that a composite primary key, using existing data, is the right choice for the table, a single unique field has been added to be used as the primary key.

My question is this, are composite primary keys a good or bad idea? I always thought that they were a good idea but noticing the lack of them in real world applications I beginning to think twice!

Re: Composite Primary Key

Rob Philpott9-Mar-09 23:21

Rob Philpott

9-Mar-09 23:21

Well, it's debated. The down side of a composite key is when it comes to joins and the like, there more fiddly and take longer. By joining on just a single field your queries are quicker. Some people always have a single field primary key, and the the composite key which you're thinking of as a unique index constraint on the table.

Personally, I don't see anything wrong with a composite primary key. It makes a lot of sense logically and saves a column worths of data.

Perhaps just a question of choice or style.

Regards,
Rob Philpott.

Re: Composite Primary Key

Jay Royall9-Mar-09 23:36

Jay Royall

9-Mar-09 23:36

Ah OK, thanks for the reply.

Having not worked in the industry for too long I haven't really found 'my own style' yet but I do like the idea of using natural data to use as the key rather than adding an extra field, even if it means using a composite key and dealing with it's drawbacks. But then, maybe I'll change my mind once I put it into practice!

Thanks again.

Re: Composite Primary Key

Curtis Schlak.10-Mar-09 13:29

Curtis Schlak.

10-Mar-09 13:29

Theoretically writing, I think composite keys are the way to go because they suggest to the consumer of the database that these are the pieces of data that define a unique row in the table.

Practically writing, if you or programmers that use the database want to use one of the many Object-Relational Mappers out there, quite quickly you'll/they'll start complaining because composite keys make them really hard to use. Quite often you end up with the integral primary key and the unique constraint that Rob Philpot describes above.

"we must lose precision to make significant statements about complex systems."
-deKorvin on uncertainty

Re: Composite Primary Key

Mycroft Holmes10-Mar-09 16:43

Mycroft Holmes

10-Mar-09 16:43

As someone who has been designing data structures for many years there are some things that I avoid, composite primary keys are one of them. Triggers are another, both make the system more difficult to support. However that is my "style" as you noted, you will fall into your own style with experience.

Curtis point about using ORM tolls is the driving decision maker behind my preference. I use a my own custom written ORM and composite keys are a bitch.

Never underestimate the power of human stupidity
RAH

how to generate ID field with auto numbering

sharp_k9-Mar-09 12:44

sharp_k

9-Mar-09 12:44

Example:

Create table myTable(studentID int, 
firstName nvarchar(25), 
lastName nvharchar(23)
)

I want the studentID field to be auto generated automatically, like 1,2,3 or 0,1,2 etc each time i insert a record. How do I do that inside a query (not in design mode).

Re: how to generate ID field with auto numbering

Luc Pattyn9-Mar-09 13:15

Luc Pattyn

9-Mar-09 13:15

Hi,

this is the SQL statement PHPMyAdmin comes up with when creating a table with two fields, one of them auto-incrementing:

 CREATE TABLE `db1`.`test3` (
`name` VARCHAR( 12 ) NOT NULL ,
`ID` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY ( `ID` )
) ENGINE = MYISAM

Hope this helps.

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

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