Msql foreign keys - Database Discussion Boards

Re: Sp in Ms-Sql 2000

pmarfleet1-Feb-08 23:04

1-Feb-08 23:04

I'm not sure what your problem is. Are you having difficulty accessing the multiple resultsets returned by the query?

If you are filling a DataSet, each resultset will form a separate DataTable in your DataSet.

If you are using a SqlDataReader, call NextResult to move between resultsets.

Paul Marfleet

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

Re: Sp in Ms-Sql 2000

r_mohd3-Feb-08 22:24

r_mohd

3-Feb-08 22:24

thanks
sorry for delay actualy my system was out of order, further i am sending exact code which i am using

Private Sub sel_rec()
Dim LstviewRcord As ListViewItem
Try
Actgrpcmd = New SqlCommand("FinAct_SplrMstr_Select_ByName", Conn)
Actgrpcmd.CommandType = CommandType.StoredProcedure
Actgrpcmd.Parameters.AddWithValue("@sid", LstviewName.Text)
Actrdr = Actgrpcmd.ExecuteReader
Actrdr.Read()
LstviewRcord = LstvewAct.Items.Add(Actrdr("SupplierId"))
LstviewRcord.SubItems.Add(Actrdr("Suppliername"))
LstviewRcord.SubItems.Add(Actrdr("Supplierundrgrup"))
LstviewRcord.SubItems.Add(Actrdr("csccityname"))
LstviewRcord.SubItems.Add(Actrdr("cscstatename"))
LstviewRcord.SubItems.Add(Actrdr("csccontry"))
Actrdr.NextResult()
LstviewRcord.SubItems.Add(Actrdr("adrsemail"))

Catch ex As Exception
MsgBox(ex.Message)
Finally

Actgrpcmd = Nothing
Actrdr.Close()
End Try
End sub

but i not able to fect all record, only first statement's result i can get.
help me

rmshah
Developer

Re: Sp in Ms-Sql 2000

pmarfleet3-Feb-08 23:44

pmarfleet

3-Feb-08 23:44

r_mohd wrote:
but i not able to fect all record, only first statement's result i can get.

Does your stored procedure return multiple records? If so, you need to set up a loop to traverse them.

For instance:

While Actrdr.Read()
  ' Do stuff
End While

Paul Marfleet

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

Re: Sp in Ms-Sql 2000

r_mohd4-Feb-08 0:28

r_mohd

4-Feb-08 0:28

still not able to get the record.

here i am giving u more details

i have two tables say tbl_Supplier and tbl_City.

in table city i have stored info about Supplier with city id which comes from tbl_City.

In tbl_city i have stored City name, State name and Country name.

Now I want to display a record in a Listvew, which has following columns.

Supplier Name,Under Group, Email Address, City Name,State Name,Contry Name

i trying to get this using a SP in mssql

Coding of sp and Sql Command already sent u
pl help

rmshah
Developer

Re: Sp in Ms-Sql 2000

pmarfleet4-Feb-08 0:40

pmarfleet

4-Feb-08 0:40

Your stored procedure looks overcomplicated. You could achieve the same result with 1 SELECT statement if you employed JOINs between the tables.

Also, you should consider giving your tables/fields more meaningful names. Your current naming conventions are quite cryptic. It would be difficult for someone else to understand what they mean without having to consult your systems specification.

Paul Marfleet

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

Re: Sp in Ms-Sql 2000

r_mohd4-Feb-08 18:38

r_mohd

4-Feb-08 18:38

Thanks a lot for sugessting me Joins

yes i have done this with multi joins.

Thanks again Laugh | :laugh:

rmshah
Developer

Re: Sp in Ms-Sql 2000

Tim Carmichael4-Feb-08 5:44

Tim Carmichael

4-Feb-08 5:44

Assuming all you want is the last set of values, place 'SET NOCOUNT ON' at the top of your stored procedure.

Tim

Msql foreign keys

Veldrain1-Feb-08 19:29

Veldrain

1-Feb-08 19:29

I am using an mSQL database for a small VB project. When it comes to makeing foreign key, what types match? The normal int datatype claims it is not compatiable with UniqueIdentifier. Am I missing something or does this differ from access and regular SQL?

Re: Msql foreign keys

pmarfleet1-Feb-08 22:58

pmarfleet

1-Feb-08 22:58

uniqueidentifier in SQL Server is a GUID. If you want an integer field that automatically increments, create an int field and set its IDENTITY property to true.

Paul Marfleet

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

Sending mail..

Dio221-Feb-08 8:54

Dio22

1-Feb-08 8:54

One of my many bosses came to me today and asked me to write a stored procedure that will email him if a table doesnt get updated every 15mins. The server is Microsoft Server 2003 with MSSQL Server 2000. I've read around 2000 ways to do this. Each as confusing as the next. I'm a college student with little experiance in the matter. Any help would be appreciated. Thanks Smile | :)

Re: Sending mail..

Krish - KP1-Feb-08 19:25

Krish - KP

1-Feb-08 19:25

Useing sp_OACreate procedure, create a CDO message object
This requires NO extra configurations at SQL Server

Regards
KP

Re: Sending mail..

Marek Grzenkowicz4-Feb-08 0:04

Marek Grzenkowicz

4-Feb-08 0:04

http://sql-server-performance.com/Community/forums/p/20506/114757.aspx[^]

Trigger for Replication database table...

Talsaniya Jayesh1-Feb-08 5:48

Talsaniya Jayesh

1-Feb-08 5:48

Hi

I have permission to a databae that is replicated.. i want to have a trigger on that database table.. is it possible? will the update/insert trigger will work.? my database is updated every 5 to 10 mins..
Please help me out..

Jes

How to Pass Parameter to a Scheduled Job

Neeraj Kr1-Feb-08 1:39

Neeraj Kr

1-Feb-08 1:39

Is there any method of passing parameter to a scheduled job of SQL Server?

-----Have A Nice Day-----

Re: How to Pass Parameter to a Scheduled Job

Michael Potter1-Feb-08 8:45

Michael Potter

1-Feb-08 8:45

Have the job reference a table. Set the column(s) in the table to whatever parameters you want.

What is Wrong with the Following Query

Vimalsoft(Pty) Ltd1-Feb-08 0:36

Vimalsoft(Pty) Ltd

1-Feb-08 0:36

hi all i have the Following Query

<br />
if Exists(select * from sde.Property_Backup p1<br />
 where substring(p1.func_key,1,5)='GEOSS' <br />
 aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key,<br />
								p2.Attrib_code<br />
								from sde.Property_Backup p2<br />
								where p2.func_key = 'PV000000'<br />
								And p1.lis_key = p2.lis_key<br />
								And P2.aCTIVE =1))<br />
begin<br />
<br />
Update sde.Property_Backup p3<br />
		set p3.Attrib_code = p12.Attrib_code<br />
		where p2.lis_key = p1.lis_key<br />
		and substring(p2.func_key,1,5)='GEOSS' <br />
       And P1.aCTIVE =1<br />
end<br />
<br />

And i get the Following Error when run it,

Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'p3'.

what is wrong

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: What is Wrong with the Following Query

Tobias Schoenig1-Feb-08 1:34

Tobias Schoenig

1-Feb-08 1:34

Vuyiswa wrote:
Update sde.Property_Backup p3
set p3.Attrib_code = p12.Attrib_code

Why are you including sde.Property_Backup? If p3 is a table, it would be right to write:
Update p3 set p3.Attrib_code = p12.Attrib_code.

Re: What is Wrong with the Following Query

Vimalsoft(Pty) Ltd1-Feb-08 1:45

Vimalsoft(Pty) Ltd

1-Feb-08 1:45

hi Thanks for your reply

i wrote it like this

<br />
if Exists(select * from sde.Property_Backup p1<br />
where substring(p1.func_key,1,5)='GEOSS' <br />
aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key,<br />
p2.Attrib_code<br />
from sde.Property_Backup p2<br />
where p2.func_key = 'PV000000'<br />
And p1.lis_key = p2.lis_key<br />
And P2.aCTIVE =1))<br />
begin<br />
<br />
Update p2<br />
set p2.Attrib_code = p1.Attrib_code<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS' <br />
And P1.aCTIVE =1<br />
end<br />
<br />
<br />
and It gives me an Error <br />
<br />

Msg 208, Level 16, State 1, Line 10
Invalid object name 'p2'.
/pre&gt;.

the Problem happenes on the Update.

Thanks

Re: What is Wrong with the Following Query

Tobias Schoenig1-Feb-08 1:53

Tobias Schoenig

1-Feb-08 1:53

How is your table called? p2? If p2 is the name of your table try the following update statement:

<br />
UPDATE p2 SET p2.Attrib_code = p1.Attrib_code <br />
INNER JOIN p1 ON p1.lis_key = p2.lis_key<br />
WHERE Substring(p2.func_key,1,5) = 'GEOSS' AND P1.active = 1

Re: What is Wrong with the Following Query

Vimalsoft(Pty) Ltd1-Feb-08 7:21

Vimalsoft(Pty) Ltd

1-Feb-08 7:21

Thanks i have Used a Cursor to achieve this

<br />
DECLARE		@CUR_LIS_KEY VARCHAR(20),<br />
			@CUR_ATTRIB_CODE VARCHAR(12)<br />
<br />
DECLARE PARENT_CURSOR CURSOR FOR<br />
	SELECT	 LIS_KEY, ATTRIB_CODE<br />
	FROM	SDE.PROPERTY_Summary P1<br />
	WHERE	EXISTS(SELECT 1 <br />
					FROM SDE.PROPERTY_Summary P2 <br />
					WHERE P2.FUNC_KEY LIKE 'GEOSS%' AND<br />
							P2.LIS_KEY = P1.LIS_KEY And p2.Attrib_code is Null<br />
													And p2.Archive_Date is Null <br />
                             <br />
)		<br />
<br />
OPEN PARENT_CURSOR<br />
	FETCH	NEXT FROM PARENT_CURSOR<br />
	INTO	@CUR_LIS_KEY, @CUR_ATTRIB_CODE<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
	UPDATE	SDE.PROPERTY_Summary<br />
	SET		ATTRIB_CODE = @CUR_ATTRIB_CODE<br />
	WHERE	FUNC_KEY LIKE 'GEOSS%' AND<br />
			LIS_KEY = @CUR_LIS_KEY<br />
	<br />
	FETCH	NEXT FROM PARENT_CURSOR<br />
	INTO	@CUR_LIS_KEY, @CUR_ATTRIB_CODE<br />
END<br />
<br />
DEALLOCATE PARENT_CURSOR<br />
<br />
<br />
<br />

Thanks for your Help

Help with this Complecated Query

Vimalsoft(Pty) Ltd31-Jan-08 20:47

Vimalsoft(Pty) Ltd

31-Jan-08 20:47

hi all

i will try by all means to Explain my Problem. i have records that Mach with a Field named "Lis_key" ,and differ in a Field named "Func_key".

Lis_key =========Attrib_code===============Func_key======
======================================================
01424545        1212033993               PV000000
01424545             Null                GEOSS001
01424545             Null                GEOSS002
01424545             Null                GEOSS003

.

Now from the Above table, i call records that have Func_key "Parents" and Records that has Func_key "Children". and in my table there are many Parents that have no Children. Am interested in those that have Children. As you can see the Attrib_code of Children is Null, i have this query that Find the Parents that has Children. and its like this

[CODE]
select p1.Attrib_code,p1.Lis_key
from sde.property_Backup p1
where p1.func_key = 'PV000000'
and exists (select 1
from sde.property_Backup p2
where p2.lis_key = p1.lis_key
and substring(p2.func_key,1,5)='GEOSS' And
P1.aCTIVE =1)
[/CODE]

Lis_key ========Attrib_code========Func_key======
=================================================
01424545        1212033993         PV000000
01424545        1215035993         PV000000
01424545        3599345445         PV000000
01424545        5035934544         PV000000

.
and now as you above, table this Parents have Children with a Fun_key that is like "GEOSS", and they are null. i want to Copy a Attrib_code of a Parent to a Child as long as the Lis_key as the same. and the Final results should be like this

Lis_key ========Attrib_code=========Func_key======
=================================================
01424545        1212033993         PV000000
01424545        1212033993         GEOSS001
01424545        1212033993         GEOSS002
01424545        1212033993         GEOSS003

.
No more Nulls for Attrbi_code for GEOSS, So i need an update Statement for the Children.

Thanks

Please Help

Select Distinct question

JohnnyHax31-Jan-08 20:07

JohnnyHax

31-Jan-08 20:07

I have a table with 5 columns of client information:

Id
FirstName
LastName
SocialSecurityNumber
AppointmentDate

A client can have more than 1 record.

I need a list of unique clients (Select Distinct FirstName, LastName, SocialSecurityNumber From Clients) which of course works fine.

The problem is that I need the Id field in the query so that I can join the results with other tables, and of course when I add the Id field to the query I get duplicates.

I am fairly new to SQL. I know how to write code that will loop through a datatable or whatever, but I really want to find a way to do this with a simple SQL query. Any help would be appreciated.

Thanks.

Re: Select Distinct question

neilarnold31-Jan-08 20:48

neilarnold

31-Jan-08 20:48

You really need to normalise the table - get rid of the appointment date field so that there's only one record per client, otherwise you don't know which of the possible IDs to join onto the other tables.

Re: Select Distinct question

Veldrain1-Feb-08 19:33

Veldrain

1-Feb-08 19:33

Remove the AppointmentDate column, then create a second table that stores just the appointments and an identifier. Then use a Join statement.

"A client can have more than 1 record" is about the worst thing a database can say.

Re: Select Distinct question

Mycroft Holmes3-Feb-08 22:00

Mycroft Holmes

3-Feb-08 22:00

Johnny
I STRONGLY advise you to get a book on database design, find a beginners book and at least get the principles. You are committing one of the most basic errors in data design and if you do not get the basics you are going to create a nightmare for yourself and the poor sod who has to fix it.

Never underestimate the power of human stupidity
RAH

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