Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: SQL Functions Programmer's Reference
This is the forum to discuss the Wrox book SQL Functions Programmer's Reference by Arie Jones, Ryan K. Stephens, Ronald R. Plew, Robert F. Garrett, Alex Kriegel; ISBN: 9780764569012
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: SQL Functions Programmer's Reference 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
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 10th, 2009, 04:35 PM
Registered User
 
Join Date: Feb 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Post dataset

Hi,

I'm very new at vb and sql.
I have a form that takes user informations and insert them into 5 different tables..
have a dataset that acts as actor which takes all the info and parse them into the 4 tables, but i am not getting the insertion done...

In order word, what i want to do is to
1) have a form that will insert into 5 tables such as complaint table, complainant table, offender table, sender table, correspondence table

Please help..

Thanks a lot
  #2 (permalink)  
Old February 10th, 2009, 04:47 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Why a dataset???

Are you selecting records from some table and then allowing them to be edited? Is that why the dataset?

If your <FORM> contains "all new" data, I don't see any reason for a dataset. It would just get in the way for the most part.
  #3 (permalink)  
Old February 11th, 2009, 07:38 AM
Registered User
 
Join Date: Feb 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Post

No. I'm not selecting records from the tables, I'm inserting records..the form is collecting data for 5 tables.

This is what i want to do..

I have a form in vb that connects to sql database..

let say a New Complaint form will gather informations from the complainant(lastname, firstname etc..), offender(lasrname, firstname, etc..) dutyofficer(lastname, firstname,etc..) ,complaint(complaintID, complaintDesc, complaintStatus, Recommendation)

Please help

Thanks.

Last edited by wnmichaud; February 11th, 2009 at 08:24 AM. Reason: to be clearer
  #4 (permalink)  
Old February 12th, 2009, 12:29 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Just because you are trying to insert to so many tables, I would probably opt for the "brute force" approach. Also known as the ASP.NET Version 1 approach.
Code:
Using conn As New SqlConnection(...your connection string...)         
    conn.Open
    Dim SQL1 String = "INSERT INTO table1 (field1, field2, field3 ) VALUES(@field1,@field2,@field3)"
    Dim cmd1 As New SqlCommand( SQL1, conn )
    cmd1.Parameters.Add("@Field1", SqlDbType.Int) 'safest is to specify type explicitly        
    cmd1.Parameters("@ID").Value = CINT( formField1.Text ) ' and then add data of appropriate type
    cmd1.Parameters.AddWithValue("@Field2", formField2.Text ) ' but this is find for text fields
    cmd1.Parameters.AddWithValue("@Field3", formField3.Text ) ' ditto

    cmd1.ExecuteNonQuery()             

    ' then repeat this with cmd2, cmd3, etc.  One command object and one SQL string 
    ' per table.  

    conn.Close( )
End Using
If you are ready to tackle Stored Procedures in SQL Server, then you COULD do it by using *ONE* stored procedure to which you pass all the field values for all the table to be affected. And then inside the SP you just do one INSERT per table, using the appropriate parameters.

It's almost like there are too many ways to do this.

But the above is perhaps the easiest to understand. It works with one table and one INSERT at a time.

You can (and should) add in some exception handling (TRY blocks) and data validation, but the basic concept is sound.
  #5 (permalink)  
Old February 12th, 2009, 09:21 AM
Registered User
 
Join Date: Feb 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Post

yes i want to use store procedure to pass all the fields for all the tables and do one insert respectively. that's i what i want to do. please help..

the tables are: RecievedCorr, complaintInfo, complainant, offender, and dutyofficer..

the form: Receivedcorrespondence.vb

when the form loads, i want a blank form loads..
the user enters the necessary info and save the data to database and print the form data...

and i also want to have a search function

Please help me..I am in hot seat here wioth this project..

Thanks
  #6 (permalink)  
Old February 12th, 2009, 05:29 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default Okay...100% off top of my head..

Based on what you said:
Quote:
let say a New Complaint form will gather informations from the complainant(lastname, firstname etc..), offender(lasrname, firstname, etc..) dutyofficer(lastname, firstname,etc..) ,complaint(complaintID, complaintDesc, complaintStatus, Recommendation)
And assuming that the tables look something like this:
Code:
TABLE Complainant
    complainantID IDENTITY PRIMARY KEY,
    lastname NVARCHAR(50),
    firstname NVARCHAR(50),
    ... other fields...

TABLE Offender
    offenderID IDENTITY PRIMARY KEY,
    lastname NVARCHAR(50),
    firstname NVARCHAR(50),
    ... other fields...

TABLE DutyOfficer
    officerID IDENTITY PRIMARY KEY,
    lastname NVARCHAR(50),
    firstname NVARCHAR(50),
    ... other fields...

TABLE ComplaintStatus
   statusID IDENTITY PRIMARY KEY,
   statusText NVARCHAR(200)

TABLE Complaint
   complaintID IDENTITY PRIMARY KEY,
   complainantID INT REFERENCES Complainant(complainantID),
   offenderID INT REFERENCES Offender(offenderID),
   officerID INT REFERENCES DutyOfficer(officerID),
   statusID INT REFERENCES ComplaintStatus(statusID),
   complaintDesc NTEXT,
   recommendation NTEXT
You would need a SET of functions and stored procedures in your DB something like this:
Code:
-- a simple function to find an existing complainant and return his/her id
-- or, if not found, add that complainant and return the newly given id
Create Function FindOrAddComplainant(
    @last NVARCHAR(50),
    @first NVARCHAR(50)
) RETURNS int
AS BEGIN
    DECLARE @id INT
    SELECT @id = complainantID FROM Complainants 
    WHERE lastname = @last AND firstname = @first
    IF ISNULL(@id,0) = 0 
    BEGIN
        INSERT INTO Complainants( lastname, firstname ) VALUES(@last,@first)
        SELECT @id = @@IDENTITY
    END
    RETURN @id
END

-- you'd have virtually identical functions for the other tables:
Create Function FindOrAddOffender( @last, @first )

Create Function FindOrAddOfficer( @last, @first )

Create Function FindOrAddStatus( @statusDescription NVARCHAR(200) )
    
-- and then the big one:
Create Procedure AddFullComplaint(
    @complainant_last NVARCHAR(50),
    @complainant_first NVARCHAR(50),
    @offender_last NVARCHAR(50),
    @offender_first NVARCHAR(50),
    @officer_last NVARCHAR(50),
    @officer_first NVARCHAR(50),
    @complaintStatusText NVARCHAR(200),
    @complaintDesc NTEXT,
    @Recommendation NTEXT,
    @newComplaintId INT OUT
) AS
DECLARE @complainantID INT, @offenderID INT, @officerID INT, @statusID INT

SET NOCOUNT ON 
SET @complainantID = FindOrAddComplaint(@complainant_last,@complainant_first)
SET @offenderID = FindOrAddOffender(@offender_last,@offender_first)
SET @officerID = FindOrAddOfficer(@officer_last,@officer_first)
SET @statusID = FindOrAddStatus( @complaintStatusText )

INSERT INTO Complaints( complainantID, offenderID, officerID,
                                  statusID, complaintDesc, recommendation )
VALUES( @complainantID, @offenderID, @officerID, @statusID,
            @complaintDesc, @recommendation )

SELECT @newComplaintId = @@IDENTITY
Again, this is NOT TESTED and is CONCEPT only.

Hope it gets you started.
  #7 (permalink)  
Old February 13th, 2009, 07:40 AM
Registered User
 
Join Date: Feb 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Post

I'm using SQL Express and Microsoft visual studio 2005 version 8 for this project...I'm creating the tables in sql express edition, I can only set primary key no other constraints. but i dont see where to add those constraints...
meaning (complaintid int identity primary key) and so forth..not this option...guide me..and i can get started..

Thanks a lot

Last edited by wnmichaud; February 13th, 2009 at 08:45 AM. Reason: to be clearer
  #8 (permalink)  
Old February 13th, 2009, 05:16 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You don't have to set the foreign key constraints. It's much better database practice, and it will catch any errors you make in linking records from one table to another, but if the contraints aren't there, the code will still run just fine.

So, for now, don't worry about it.

You *CAN* add those contraints using SQL, but you can also do that later.
  #9 (permalink)  
Old February 16th, 2009, 08:27 AM
Registered User
 
Join Date: Feb 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Post

I tried the code exactly the way you supplied them to me. Not the right function and procedure syntax for the tool that I am using.
I would like to follow your logic so I can get this project done..
I can build on it for other forms that will do similar things.

Using Visual Studio and SQL Express. This is a vb.Net project...

I can create a Form under Visual lets say formA and database projectA.db with a table name project with one record.

I create a dataset formADS.xsd. I load the FormA.vb, I can see the first record but add additional record is not possible. that is my initial problem. and the big one is to accomplish my goal.

Thanks a lot

Last edited by wnmichaud; February 16th, 2009 at 03:34 PM.
  #10 (permalink)  
Old February 16th, 2009, 09:25 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, I *did* say that was all off the top of my head.

And when I actually tried to do it, SQL Server objected to the use of INSERT inside of a FUNCTION.

So I had to "inline" all those functions, but I was able to do so successfully.

And all using Visual Web Developer Express 2005, so I'm sure you can do it all with full Visual Studio.

Some of the steps aren't obvious. For example, in order to create the tables the way I wanted to, using relationship contraints, I had to create a stored proc whose only purpose was to create them!

But it worked.

So...

I can give you a "skeleton" of what I did and you can try to duplicate it. Or maybe you can stop using Visual Studio, alone, and use the "ssmsee" to do it all, instead?
 


Thread Tools
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
Fill a DataSet from another DataSet kamranzafar C# 1 February 7th, 2007 10:14 AM
Fill(dataset) or dataset.load() salemkoten SQL Server 2005 1 November 2nd, 2006 10:04 PM
Converting a untyped dataset to a typed dataset daphnean Visual Studio 2005 0 July 13th, 2006 01:16 AM
Copy dataset to another dataset kapila VB.NET 2 November 13th, 2005 05:25 AM
Re: SQL Server dataset to ACCESS dataset dazzer ADO.NET 0 March 22nd, 2004 04:28 AM



All times are GMT -4. The time now is 05:37 PM.


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