How to get generated Primary Key when adding a record - Database Discussion Boards

select year/month syntax (for contracts up for renewal in X month)

tellytub18-Dec-07 16:09

18-Dec-07 16:09

Hi all,

I'm currently doing a contract and supplier system, using VB.net and MSSQL.

I have this table (Contract) containing the following fields:

Contract_ID INT NOT NULL,<br />
Vendor_ID INT NOT NULL,<br />
ContractDate datetime,<br />
DateStart datetime,<br />
DateDue datetime,<br />
PRIMARY KEY(Contract_ID),<br />
FOREIGN KEY (Vendor_ID) REFERENCES Vendor(Vendor_ID)

1) I am going to do something like select contracts up for renewal in X months, eg in 3 months. (the syntax is one thing i'm not sure)

2) But the datetime format for both VB.net and MSSQL are different as shown below. (the format differs would matter too much? cos in the end, we are still proceeding with sql datetime format code.)
http://i5.tinypic.com/7xaeo95.jpg
http://i4.tinypic.com/739s66g.jpg

3) Do I put the codes in vb form or in vb's stored procedures?

I think it is something like this: http://www.outlookcode.com/d/code/remindbday.htm
But I have no idea how to start.

I hope the info I gave are not too vague. Any help is appreciated. Thanks.

Re: select year/month syntax (for contracts up for renewal in X month)

Giri K18-Dec-07 17:11

Giri K

18-Dec-07 17:11

you can do it in Stored Procedure, use the convert function, then where clause to serve the purpose. something like

set @Date = CAST(DATEPART(YEAR, @Date) as varchar) + '-' + CAST(DATEPART(MONTH, @Date) as varchar)+ '-' + CAST(DATEPART(DAY, @Date) as varchar) + ' 00:00:00'

Re: select year/month syntax (for contracts up for renewal in X month)

tellytub18-Dec-07 20:15

tellytub

18-Dec-07 20:15

Hi,

thanks for the reply. Regarding stored procedure, what would I need to do to show the contracts up for renewal?

But if you don't mind can you explain what the code does? What would be the @Date ?? Why is it plus-ing? Sorry, I quite a beginner for database.

telly

Re: select year/month syntax (for contracts up for renewal in X month)

Giri K19-Dec-07 11:55

Giri K

19-Dec-07 11:55

@Date is your parameter @DateStart, convert the Dateformat, so that you can strip it out and select which ever datepart you want, like year, month, days. the concatination separates the date part, to be able to easily select

like select contractID from XXX
where @DateStart between 01-10-2007 to 03-12-2007

hope you get this time..

joins of two tables +search

regin18-Dec-07 1:42

regin

18-Dec-07 1:42

hi...
plz somone help me write these queries..
i want to search the data's from employee select the employee name,designation_name
where
1)employee code that i can pass or else designation name i can pass
2)only one i can pass from theses 2
3)the employee can contain null value for desination name
4)constraint is :employee.designation_id=desig.designation_id
plz somone help me out to solve this pblm

Re: joins of two tables +search

pmarfleet18-Dec-07 1:59

pmarfleet

18-Dec-07 1:59

What part of your code are you specifically having difficulties with? At the moment, it sounds like you want someone to do your work for you.

You haven't stated what database you are you using. However if it is SQL Server, I suggest you do some research on writing stored procedures to encapsulate the logic for your search.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: joins of two tables +search

andyharman18-Dec-07 3:47

andyharman

18-Dec-07 3:47

For SQL-Server:

select employee.name, designation.name
from employee
left outer join designation
  on designation.designation_id = employee.designation_id
where employee.code = IsNull(@EmployeeCode, employee.code)
and IsNull(designation.name, '') = IsNull(@DesignationName, designation.name)
order by employee.name

The outer join links the two tables together - even if there is no designation.

The IsNull bit allows you to optionally pass in a NULL or a proper value for the employee code and the designation.

Hope that helps.
Andy

How to get generated Primary Key when adding a record

gunner_uk200018-Dec-07 1:35

gunner_uk2000

18-Dec-07 1:35

I have these (amongst others) tables in my database:

Tasks and Successors

Tasks hold details about task, and Successors has two taskIDs as forgien keys (jointly primary key) to hold data on the relationship between task.

Ie Task A has Tasks B and C as successors.

Task Table has three records for the task. The Successor table has TaskA/B and TaskA/C primary key feilds.

I'm using this on a C# Clone of MS Project. So I'm reading this data in and converting them into classes, in a tree structure so each Task Object has a List<task> of successors.

I can read this data in fine by just parsing the data in the tables. The problem comes when I want to put newly created data into the database, which is stored in this tree of instances of Task.

[b]If I put add a Task record into the database, how do I get the primary key of the one I've just put in so that I can create the records in the successor table to link them?[/b]

In my example I will probably have to added the tasks 1st so that when I add the successor records, the tasks actually exists. The algorithim for this will be quite simple if I was able to get the primary keys when I adding the tasks.

Anyone have any idea on how to do this?

Thanks for any help,

Ian.

Re: How to get generated Primary Key when adding a record

Pete O'Hanlon18-Dec-07 1:38

Pete O'Hanlon

18-Dec-07 1:38

If you are using SQL Server, use SELECT ... = SCOPE_IDENTITY() after your insert statement.

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

My blog | My articles

Re: How to get generated Primary Key when adding a record

Giorgi Dalakishvili18-Dec-07 1:42

Giorgi Dalakishvili

18-Dec-07 1:42

Why don't you google? Here are some results the search returned:Retrieving Identity or Autonumber Values [^]

#region signature
my articles
#endregion

select the max value from the row and its other values

Deques18-Dec-07 1:19

Deques

18-Dec-07 1:19

Here is the table

Project        	col1	col2	col3	col4
A        	151,46	80	0,29	2
A        	529,14	83	0,29	1
B        	3391,3	80	0	2
B        	3706,2	75	0,06	1
C        	8685,3	78	2,41	1
C        	9005,2	71	0	2

How do I get the max value for col1 for each project and its other values?

ie. project a's max value is 529,14. its others value is 80, 0.29 and 2

Project        	col1	col2	col3	col4
A        	529,14	83	0,29	1
B        	3706,2	75	0,06	1
C        	9005,2	71	0	2

I have this query, but it results everything, not only the max value for each project

select project, max(col1), col2, col3, col4 from out_pumptable group by project, col2, col3, col4

Re: select the max value from the row and its other values

Tobias Schoenig18-Dec-07 1:41

Tobias Schoenig

18-Dec-07 1:41

Try

SELECT g.Maximum, * from Projects, (SELECT MAX(col1) as Maximum FROM projects GROUP BY project)g WHERE MAX(col1) = g.Maximum

Re: select the max value from the row and its other values

Deques18-Dec-07 1:51

Deques

18-Dec-07 1:51

it looks good, but the column project you have after group by isnt used anywhere. is it something missing before group by?

never mind about that.

I have tried the query and it results a little too much. it seems it loops for each max value and result for all row

Re: select the max value from the row and its other values

Tobias Schoenig18-Dec-07 1:59

Tobias Schoenig

18-Dec-07 1:59

You're right - it has to be like that:

SELECT g.Maximum, * from Projects, (SELECT MAX(col1) as Maximum, project FROM projects GROUP BY project)g WHERE MAX(col1) = g.Maximum

Re: select the max value from the row and its other values

Deques18-Dec-07 2:05

Deques

18-Dec-07 2:05

It still doesnt work.
It says an aggregate cant be in WHERE clause

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Tried with Having and it gives a lot other errors

Re: select the max value from the row and its other values

Tobias Schoenig18-Dec-07 2:10

Tobias Schoenig

18-Dec-07 2:10

Sorry my fault, i copied the wrong string:
This should be the right thing:

SELECT g.Maximum, * from Projects, (SELECT MAX(col1) as Maximum, project FROM projects GROUP BY project)g WHERE col1 = g.Maximum <br />

Re: select the max value from the row and its other values

Deques18-Dec-07 2:17

Deques

18-Dec-07 2:17

its working Big Grin | :-D

, just one more thing >.<<br mode="hold" />
some rows in col1 has same value, is it possible to just have one of the values, just take from one row?

Re: select the max value from the row and its other values

Tobias Schoenig18-Dec-07 2:22

Tobias Schoenig

18-Dec-07 2:22

Add a top 1 for example to the first select-statement.

Re: select the max value from the row and its other values

Deques18-Dec-07 2:31

Deques

18-Dec-07 2:31

top 1 seems not be working good for this

I get this result, (partially), from the query you gave me

3320,9	P110101        
3320,9	P110101        
3220	P110102        
3220	P110102        
529,14	P110301        
3706,2	P111501

P110102 got two rows, how do i get one of it only?

Re: select the max value from the row and its other values

Tobias Schoenig18-Dec-07 2:32

Tobias Schoenig

18-Dec-07 2:32

Add a Group by-statement to the very end of the whole query.

Re: select the max value from the row and its other values

Deques18-Dec-07 2:43

Deques

18-Dec-07 2:43

it feels like im getting annoying now

group by statement might work if i have an aggregate statement somewhere, now there isnt i cant do the group by

i am trying distinct now, but i get incorrect syntax

SELECT  g.Maximum, distinct posnumber from out_pumptable, (SELECT  MAX(flow_lpm) as Maximum, posnumber FROM out_pumptable GROUP BY posnumber)g WHERE flow_lpm = g.Maximum

this is actual code, posnumber is project

Re: select the max value from the row and its other values

Tobias Schoenig18-Dec-07 2:49

Tobias Schoenig

18-Dec-07 2:49

This works for me:

SELECT g.Maximum, out_pumptable.posnumber from out_pumptable, (SELECT MAX(flow_lpm) as Maximum, posnumber FROM out_pumptable GROUP BY posnumber)g WHERE flow_lpm = g.Maximum group by g.Maximum, out_pumptable.posnumber

Re: select the max value from the row and its other values

Deques18-Dec-07 2:53

Deques

18-Dec-07 2:53

Thanks a lot and thanks for your time. I appreciate this

Re: select the max value from the row and its other values

Rupert Todd19-Dec-07 9:36

Rupert Todd

19-Dec-07 9:36

I believe you need to join the table to a subset of itself.

You create a subquery that gives you the values of project and the highest col1 and attribute that to a temptable. You then join you orignal table to your temp table on the values of col1. This should do the trick!

SELECT PT.* FROM out_pumptable PT join
(select project, max(col1) as MaxVal from out_pumptable group by project) TEMPTABLE1 on TEMPTABLE1.MaxVal = PT.col1

calling a storedprocedure from a function in mysql

sepel18-Dec-07 0:35

sepel

18-Dec-07 0:35

hi all.
i have a stored procedure that get 2 parameter.
i want to select a special filed from my store procedure.
can i call my procedure from a function and how i should do it.
(i need some code).

sepel

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