Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old July 2nd, 2009, 06:14 AM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default foreign key constraint in sql

Hi peace 95
the relationships for the foreign keys are defined in the database. I am using MS SQL.
i also created the data relations in vb but everytime i insert it wont pick up the parent tables primary key and insert it in the child table.
Its really confusing.
Look forward to hearing from you.

Regards
Slake
Reply With Quote
  #12 (permalink)  
Old July 2nd, 2009, 06:02 PM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default foreign key constraint in sql

Slake:
Hold off a moment on the INSERTs until we get the tables correct. Although you have made the previous changes I suggested to the tables you MUST tell SQL Server about them by creating Foreign Key Contraints with in the tables.

Using T-SQL, SQL 2005 (Express Edition)

CREATE TABLE BookStudTbl(BKStud_ID int PRIMARY KEY, Bk_ID int REFERENCES BookTbl(Bk_ID), Stud_ID int REFERENCES StudentTbl(Stud_ID))

The above is an example of creating a junction table for two previously defined tables.

If still confusing, we may have to take this offline for better understanding so I can send visual illustratiion....
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #13 (permalink)  
Old July 2nd, 2009, 07:35 PM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default foreign key constraint in sql

Hi Peace 95

I was able to solve the problem thankfully. I already had the foreign key constraints. What i did was create an insert procedure with the select @studid=scope_identity() just after the insert for the parent table and then created an inset for the child table.

I stored that procedure.
Then in VB i called the procedure inserted the data into the text boxes and the data wa saved with the primary key in the parent table automatically inserted into thechild table as a foreign key .

Im quite happy with it. The table i have is just a sample. I did not normalise it or anything . Its not my main project.

ALTERPROCEDURE [dbo].[SP_COURSE]
-- Add the parameters for the stored procedure here
@STUDID INTOUTPUT,
@NAME CHAR(20),
@ADDREES VARCHAR(50),
@CID INT,
@CNAME CHAR(15),
@DATES DATETIME
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
-- Insert statements for procedure here
INSERTINTO STUDENT(NAME,ADDREES)
VALUES(@NAME,@ADDREES)
--CALLING THE AUTO INCREMENT
SELECT @STUDID =SCOPE_IDENTITY()
--INSERT INTO COURSE
INSERTINTO COURSE
(CNAME,DATES,STUDID)
VALUES(@CNAME,@DATES,@STUDID)
END

'THE CODE TO INSERT THE VALUES IN ARE.

'OPEN CONNECTION
objConnection.Open()
'create sql command and connect to connection and insert string
Dim objcommand AsNew SqlCommand()
objcommand.Connection = objConnection
objcommand.CommandType = CommandType.StoredProcedure
objcommand.CommandText =
"SP_COURSE"
'ADD PARAMETERS FOR STUDENT
objcommand.Parameters.AddWithValue("@STUDID", SqlDbType.Int)
objcommand.Parameters.AddWithValue(
"@NAME", txtname.Text)
objcommand.Parameters.AddWithValue(
"@ADDREES", txtaddress.Text).DbType = DbType.String
'ADD PARA FOR COURSE
objcommand.Parameters.AddWithValue("CID", SqlDbType.Int)
objcommand.Parameters.AddWithValue(
"@CNAME", txtcoursename.Text)
objcommand.Parameters.AddWithValue(
"@DATES", DateTimePicker1.Text).DbType = DbType.DateTime

'execute the sqlcommand to insert the values
Try
objcommand.ExecuteNonQuery()

Catch sqlExceptionErr As SqlException
MessageBox.Show(sqlExceptionErr.Message)

EndTry




objConnection.Close()

EndSub

IF THERE IS ANOTHER OF DOING IT PLEASE LET ME KNOW.

KIND REGARDS

SLAKE
Reply With Quote
  #14 (permalink)  
Old July 3rd, 2009, 05:47 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default Foreign Key Constraint in SQL

Slake:
You did it....

I just have a couple of questions:
  1. Because you are using vb.net and MS SQL Server, the database OO (Object Oriented) or Relational?
  2. In your tables, if a course (CID) changes, will you hve to go through each record and update the changes and the same for student address change?
  3. How do you handle the student enrolling in multiple courses?
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #15 (permalink)  
Old July 4th, 2009, 11:50 AM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Hi Peace95
Hope all is well.Meant to answer you yesterday but was busy writing that procedure for my project.
What do you do? Im an international student in London.Anyway.
The database is a relational database. I've never dealt with a OOD before,have you. I might design one after september.Me and my mate are planning on re making our music website(lunakidzmusic.co.uk).As for your questions.
1. In your tables, if a course (CID) changes, will you hve to go through each record and update the changes and the same for student address change?
In my VB form i have a number of interfaces designed. I have not yet implemented them but when the user needs to update or add onto a record they can call one of the forms and do it from there. The change of CIDs in the situation can be automatically done woth UPDATE CASCADE. I include it in the design(on the child table).This will automatically change one i update the master table
2.How do you handle the student enrolling in multiple courses?
Now my table is not normalised but you would need to do the same thing you did,where you created a one to many relationship and that should alllow you to have a signle studentid with multiple course id's hence the enrollment of one student on many courses.

I have one as well;
How would you deploy this system if you havwe the database in mssql and the front end in vb(ide). I have to create an application that can be executed on another system. Hope i have answered your question.
By the way is there another way of doing this?

Kind Regards

Slake
Reply With Quote
  #16 (permalink)  
Old July 6th, 2009, 04:40 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

Hi Slake:

Not to worry about your response time... on this side of the ocean we (all US) were celebrating a National Holiday..... I took advantage of the time and spent all day and night watching movies.... it was fantastic.

I am a Consultant and right now I am very partial to Oracle Application Express (APEX) as another way of developing your application. I hope to complete the book in 2 weeks. It has explained soooo many questions I have had referencing ASP.Net, Oracle did not start out with "Hello World".

I considered your application as a backend dynamic data-centric application, using forms and templates for editing and displaying information.
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #17 (permalink)  
Old July 7th, 2009, 03:48 PM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default duplicates in my values in database

Hi Peace95

I am an international student from Uganda. i am a student a Hertfordshire university. Whats apex about? Any way I have a new one thats shocking me. My insert is duplicating values and i dont quite understand why. I have the insert done through a procedure but i dont understand why its duplicating all my values.
Do you know how i can avoid this? Look forward ty hearing from you. I have not used asp.net yet. We might be working on a website for that label and my mate says actionscript ,would you say asp is better. I have not used an of them.

Regards

Slake
Reply With Quote
  #18 (permalink)  
Old July 8th, 2009, 03:31 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

Slake:

I am not a VB/VB.NET developer. However, using SQL or SQL-Scripts to Alter the Database, i.e.INSERT, DELETE, UPDATE, are triggered by a button on a form. When the button is clicked, the SQL script runs. At this point, I have no idea why you are getting duplicate records.

Beginning Oracle Application Express (APEX) a wrox book uses the Oracle database, PL/SQL to build dynamic data-centric web-based applications quickly. Read about it.

Hope this helps.
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #19 (permalink)  
Old July 8th, 2009, 03:58 PM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default duplicates in my values in database

Hi peace95

It was my mistake. i basically run the noneexecute method twice. I fixed it now. i used oracle 10g but i had such a rough time with the interface. i would like to build my knowledge in oracle.
Had a look at your book on amazon. Is that you on the page? I like the chapters especially the one on deployment. Do you discuss anything on object oriented databases?
What other books have you written or are you writing? I would like to write a Programming book someday,but i dont think i can ever be that experienced.
Reply With Quote
  #20 (permalink)  
Old July 8th, 2009, 04:02 PM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default foreign key constraint in sql

Hi Peace95
As per this thread are you saying you can actually develop forms with the same fuctionality as my app? Please advise as i find this rather an interesting topic.

Regards

\slake
Hi Slake:

Not to worry about your response time... on this side of the ocean we (all US) were celebrating a National Holiday..... I took advantage of the time and spent all day and night watching movies.... it was fantastic.

I am a Consultant and right now I am very partial to Oracle Application Express (APEX) as another way of developing your application. I hope to complete the book in 2 weeks. It has explained soooo many questions I have had referencing ASP.Net, Oracle did not start out with "Hello World".

I considered your application as a backend dynamic data-centric application, using forms and templates for editing and displaying information.[/quote]
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sample code Professional ADO.NET Programming YUKOS All Other Wrox Books 6 January 30th, 2013 01:47 AM
Sample Code needed for C# Web Applications with VS .NET hughveal C# 1 February 7th, 2009 08:58 PM
Professional ADO.NET Programming source code pitzu1023 All Other Wrox Books 2 May 15th, 2006 08:19 AM
how to download code for Professional ADO.NET....? bobcow Wrox Book Feedback 1 July 19th, 2005 12:54 PM
can't find sample code r professional ADO.net prog calvinpost ADO.NET 2 January 27th, 2004 04:01 AM



All times are GMT -4. The time now is 08:10 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.