p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ADO.NET (http://p2p.wrox.com/forumdisplay.php?f=109)
-   -   Sample code needed for Professional ADO.NET with VB .NET (http://p2p.wrox.com/showthread.php?t=72629)

slake July 2nd, 2009 06:14 AM

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

peace95 July 2nd, 2009 06:02 PM

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....

slake July 2nd, 2009 07:35 PM

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

peace95 July 3rd, 2009 05:47 AM

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?

slake July 4th, 2009 11:50 AM

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

peace95 July 6th, 2009 04:40 AM

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.

slake July 7th, 2009 03:48 PM

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

peace95 July 8th, 2009 03:31 AM

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.

slake July 8th, 2009 03:58 PM

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.

slake July 8th, 2009 04:02 PM

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]


All times are GMT -4. The time now is 12:35 PM.

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