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)

hughveal February 7th, 2009 06:47 PM

Sample code needed for Professional ADO.NET with VB .NET
 
I have this book but could not find the sample code. Could someone e-mail it to me or tell me where to find it?

jminatel February 7th, 2009 08:04 PM

I think you are going to be out of luck unless you can find one of the authors who has this code. In 2003, APress acquired this book and many others from Wrox's former owners. Up until a few months ago, APress distributed the code. A few months ago, they stopped distributing the code for all of the Wrox books they own the rights to. We are not allowed to distribute their code.

They other option would be to return that old book and buy a current actual Wrox book on a current .NET version. That old book is .NET 1.0 era. Our 2 current Wrox ADO.NET books are:
Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL
and
Professional ADO.NET 3.5 with LINQ and the Entity Framework
and we have all the code you need for them.

Hope this helps.

slake June 29th, 2009 06:37 PM

Foreign key conatraint in database
 
Hi
I have 2 tables connected to each other and when i try to insert data int them from a vb form the foreign key relationship wont hold. Their respective primary keys are identity columns .
when data is inserted in the vb form the primary keys for both tables are generated.Foreign keys wont show.
I have created a data table with all the columns defined as well as the foreign key constraints but still nothing happens.

If anyone has dealt with a similar problem please advice as im reallly desperate to get this working and dont know what to do.

Please provide any code examples you might have as im new at dealing with programs and posting to forums.

Thanks in advance

peace95 June 30th, 2009 03:18 AM

slake:
It would be very helpful for you to show your two tables with the Primary Key(s) and Foreign Key(s) identified. Without seeing the two tables, I am making the suggestion:
  • The ONLY way for two tables to have identical keys, in a RDMS (Relational Database Management System, is for TableA to have a Key identified as the Primary Key i.e. TableA (A_ID PK, Anext). and a TableB(B_ID PK, Bnext, A_ID FK). Note: Primary Key in Table is the Foreign Key in TableB. It is this relationship, 1-to-many, that the Primary Key and Foreign Key constraints will hold true for adds, inserts, updates and Cascade deletes.
Hope this helps. (This is the second attempt of responding to this thread)

slake June 30th, 2009 03:54 PM

FOREIGN KEY NOT INPUT IN TABLE FROM VB.NET
 
hi there
Really gald you could answer so promptly. This is the scenario

Cust(C_id,CName,Address) , Book(B_id,Title,Author,C_id). Now these are my two table. I am inserting data into from my form in VB. I do not have any text boxes for the primary keys as they are both identity columns. When i insert the CName,Address then Title,Author from the text boxes in VB the info is saved and primary keys for both tables created.
However. I need to have the Primary key of the Cust table automatically inserted into the Book table as a foreign key. For example

Cust(1,J.Smith,45 london str) Book(11,The drive,M.Andrews,1). In this eample i know the the primary keys will auto generate when the data in inserted but 1 will not appear as a foreign key in Book unless i go back into the table and manually insert it. In my case i need to create some from of function to insert it for me everytime insert data into my tables. My user needs to only enter the required data and not bother about primary or foreign keys . I read about triggers that can be created in the database. Im not sure if this will work and if it does how do i write one(Please advise with an example) or if there is any other way of doing it.Please advise.

Thanks again in advance. i appreciate the help.

Kind regards

Slake

slake June 30th, 2009 06:13 PM

foreign key works but other data wont appear
 
Hi
I have created the trigger for my problem but then when i insert it all the other columns wont appear. Exampl

Student(studid,name,address) course(cid,coursename,date,stuid)
studid and cid are primary keys in there respective tables and forign key (studid) in course.

when i insert the vales in for everything except the primary and foreign key. all vales in the parent(student) table are generated.The child table will generate the first column with the foreign key and then another column without a foreign key.
So for every single parent column i generate 2 columns of the child table. Each having half of what i want. I need all the data to be insert in the same column


[CREATE TRIGGER TG_INS_STUDENT

ON dbo.STUDENT
FOR
INSERT
AS
BEGIN

INSERT INTO dbo.COURSE(STUDID)
SELECT STUDID FROM INSERTED
END]

please help me rectify it if you can i came so close.Look forwaard to hearing from you.

peace95 July 1st, 2009 05:00 AM

FOREIGN KEY NOT INPUT IN TABLE FROM VB.NET
 
Slake:

Thanks for the detail. One of your problems is due to the fact the relationship is not correct. What if the book is read, or bought by more than one Customer? In my opinion, the relationship between your Customer and your Book tables are many-to-many. You need a junction table:
Code:


CustTbl( C_id, CName, CAddress)---<CustBkTbl(CusBkID,C_id,B_id)>----BkTbl(B_id,....)
 
In the Book Table I removed the C_id and placed it in the junction table, CustBkTbl. The junction allows for the Customer to buy/read 1 or more books and it allows for the 1 or more Books to be read/bought by more than one Customer. The -----< & >----- indicates the relationships between the 3 tables.

Another situation I see in your Book Table is with the Author in that you may have more than 1 author per book....as in many WROX books, for example. To rectify that build the following::
AuthorTbl (authorID, author name) and AuthorBookTbl(aubkID, authorID,B_id), which is the junction table for Author and Book, the BkTbl(B_id,Title, ISBN).

Remember: "....The KEY is the KEY', from 70's movie Jumpin' Jack Flash

Hope this hellps.

peace95 July 1st, 2009 05:29 AM

foreign key works but other data wont appear
 
Slake:

You need to use the junction table between your Student Tasble or Entity and the Course Table or Entity. Remember to remove the Student ID from the Course Table. Follow instructions in previous response.

I forgot to mention, the tables on the right and left of the junction table MUST exist or be defined in the database BEFORE defining the junction table, the one in the middle,. Also in your forms you can use the Book, Author, Customer and Course Tables as LOVs after you populate them.

Redefine your RDBMS schemes...... Yes you are very close.....Don't Give Up!!

Hope This Helps.

slake July 1st, 2009 06:00 PM

foreign key constraint in sql
 
Hi Peace
Thanks for the helpon breaking the tables down. That i have done but i still need to auto generate the foreign keys of these relationships automatically. In ms sql i manually insert them as the primary keys are auto generated. I tried to write an insert trigger which did not work as i expected though it auto generated the foreign key but after the original data was inserted.

Do you know how to resdolve this issue please. If so let me know.I will be so greatful

Kind Regards

Slake

peace95 July 1st, 2009 09:01 PM

foreign key constraint in sql
 
Slake:
It sounds as if the problem or problems are in the Database Tables. Did you define the relationships between the tables? If not, this MUST be done. Is the RDBMS Oracle or MS SQL? I thought it was Oracle. Let me know.

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]

peace95 July 9th, 2009 02:13 AM

foreign key constraint in sql
 
Slake:

Yes. that is exactly what I am saying, but you will have to go through the Oracle Application Express by Rick Greenwald, chapter by chapter, page by page to understand it all. You also learn to build interactive reports and reports with pie charts. There are screen shots thru out the book along with explanations and instructions. I can't begin to tell you all the information and knowledge you will gain.

peace95 July 9th, 2009 03:06 AM

Slake:
Glad to hear you read the synopsis on Oracle Application Express. I am not the author of the book that honor goes to Rick Greenwald and my pic is no where on Amazon.com. I did write comments about the book on Amazon.com. I do write, but nothing on OODatabases, not my genre.


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

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