Database

data from 2 different tables linked with a join.

3-Apr-06 23:05

As I remember that was a whole semester's worth of database lectures from my uni' days. How much information do you really want?

The shortest summary would be:
* Use transactions

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

massimo Nicoletti3-Apr-06 3:13

Re: data from 2 different tables linked with a join.

3-Apr-06 3:13

Hi I come from MSAccess programming and using access is easy to create a view of data coming from 2 different tables linked with a join.
I'm trying to port my application under VS2003 C# and I don't know how create the same thing with my dataset.
The brute-force mode I found is to create a new table copying the fist one, add a column and fill it with the data retrived from the second one with GetParentRow or GetChildRow methods. Is this the right way or there is a easiest way?

Thank in advance
Best regards
Massimo Nicoletti

Ricardo Casquete4-Apr-06 6:14

Re: data from 2 different tables linked with a join.

4-Apr-06 6:14

Hi man...

You only have to create the typed dataSet from the View.

I mean, as well you add a table to a dataset, you can add a view.

If the View is well done with the Primary Keys and the Not Null columns you can do updates and Insert aganist the View and you will find the both tables will be updated...

Ricardo Casquete

massimo Nicoletti4-Apr-06 20:43

Re: data from 2 different tables linked with a join.

4-Apr-06 20:43

Hello Ricardo,
my dataset is a Typed dataset so You think that I have all the info I need?
I can "reach" the joined column of a table using the info in the dataset?
How I can do this?
Thank you for your help

Thank you for your help
Massimo Nicoletti

Ricardo Casquete4-Apr-06 22:49

Re: data from 2 different tables linked with a join.

4-Apr-06 22:49

OK Man,

If you have a view in Access or SqlServer, you should be able to see it from the Server Explorer of the Visual Studio.

Instead of the Tables Node, From the View Node.

So now, to create a Typed DataSet from the View the only thing you have to do is to Drag and drop it over a dataSet or over a form ( creating also the OdbcDataAdapter ).

Doing so, when the DataAdapter has been created if you have done the View Properly ( I mean with all the PrimaryKeys of the Tables, and all the Columns that cannot be empty ) the DataAdapter willk generate the Update and DeleteCommand.

If you don't have all this information, you won't be able to delete or update.

Now the only thing you have to do is to work with the Data of the DataSet.

I mean

foreach ( System.Data.DataColumn col in this.dst.Tables [ 0 ].Columns )
{
col [ 0 ]....
}

foreach ( System.Data.DataRow row in this.dst.Tables [ 0 ].Rows )
{
row [ 0 ].ToString()....
}

to add a row in the tables of the View, you only have to add a row to the DataSet and call Update to the DataAdapter...

and more or less thats all

Ricardo Casquete

massimo Nicoletti4-Apr-06 23:27

Re: data from 2 different tables linked with a join.

4-Apr-06 23:27

Ok tested and work fine.
The last question is: if I'm working off-line, I mean with the dataset can be non syncronized with the source (access database) because I modify a record and I have not yet updated the source, before creating dataset I must update the database with my dataset or I can "start" with the data stored in the dataset?

Thank you
Massimo Nicoletti

Ricardo Casquete4-Apr-06 23:36

Re: data from 2 different tables linked with a join.

4-Apr-06 23:36

No, The dataSet is designed to work offline, you don't need to do a update foreach change you do in your dataSet...

Do all the changes you need and then Update All.

After that call the dataSet.AcceptChanges() Method and that's all

Regards

Ricardo Casquete

massimo Nicoletti5-Apr-06 0:00

Re: data from 2 different tables linked with a join.

5-Apr-06 0:00

Excuse my ignorance, Ricado,
work-dataset is the dataset I use in winform
view-dataset is the dataset I create vith access view (I use view-dataset with the .writexml method to save xml on disk)

Every time I call the .fill methot of dataadapter to fill the view-dataset with data stored in the access database, this must be syncronized with my work-dataset.
So, I think, every time I need data from view, first I must check if my work-dataset is syncronized with access, and If not Syncronize it, and after call the . fill method to populate the view-dataset.
Is it correct?

Thank for your big big patience
Massimo Nicoletti

Ricardo Casquete5-Apr-06 0:06

Re: data from 2 different tables linked with a join.

5-Apr-06 0:06

Yes thats it...

You can ask your work dataSet.HasChanges(), that returns a bool value....
This value will gave you must syncronize the DataSet or not...

Also have a look, maybe could be interested for you to use the DataSet1.Merge( dataSet2 ) method that joins two dataSet with the changes...

hope it works...

and don't worry for my patience... I have a lot...

Ricardo Casquete

massimo Nicoletti18-Apr-06 20:22

Query Analyzer and Languages

18-Apr-06 20:22

Hi Ricardo,
It works very well !!!
Thank you very, very much.

Best regards
Massimo Nicoletti

phimix3-Apr-06 2:49

phimix

3-Apr-06 2:49

Aloha,

We have a lot of data with textstrings in English, Russian and other languages. The data exists as a lot of files with SQL statements.

The statements looks fine in Query Analyzer (Server Mgmt. Studio) on my local machine. I have SQL Server 2005 on my local machine.

It's when I upload my statements to the production server that things start to go wrong. The statements still look fine in e.g. Notepad, but when I open them in Query Analyzer all the Russian texts are changed to garbage strings. The product server have SQL Server 2000, so I access the statements using an older version of Query Analyzer than on my local machine.

Any idea on how I get Query Analyzer to show my Russian texts correctly and thereby be able to execute the statements correctly? An other way of executing the statements could also be useful as long as the Russian texts are preserved.

Thanks and do remember that today (April 4) is National Workplace Napping Day,

phi
phimix.com OMG | :OMG:

User Creation in SQL Server 2005

Brendan Vogt2-Apr-06 21:08

2-Apr-06 21:08

Can someone please help me to create a new user that can access a database? I want to add this in my connection string in my web.config file. I don't want to supply the sa password for this. I'm struggling to get this done in SQL Server 2005, not sure where to go.

Please can someone help.

Regards,
ma se Confused | :confused:

Array in SQL

Brendan Vogt2-Apr-06 21:02

2-Apr-06 21:02

Hi all!!

I need some advise please. I have a stored procedure that adds a member to tblMember, and in the same stored procedure I add the member ID and role ID to the tblMemberInRoles table. Now what happens if the member, for example, has more than one role? How do I pass this "array of roles" to the stored procedure?

How would the C# code look like when passing these parameters? I am using framework 2.0 and SQL Server 2005.

Is this the correct waty to do it? Or is there a different way that you guys would do it? Please let me know!!

Any help or advise is appreciated!!

Cheers.
ma se

Colin Angus Mackay2-Apr-06 23:18

2-Apr-06 23:18

Two ways I can think of... [1] Call the stored procedure multiple times for each role (or create a separate SP for adding the roles) or [2] pass the role names in as a comma separated string and parse the string in the stored procedure.

There may be a third possibility but I've not had a chance to really look at it and that is passing an XML fragment into the stored procedure. SQL Server 2005 has better XML processing capabilities than SQL Server 2000 and that may be a possibility. However, I've not really looked at the XML capabilities of SQL Server 2005 yet, so I cannot say for certain.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Brendan Vogt3-Apr-06 0:57

3-Apr-06 0:57

Thanks for the reply..

What I would've liked to have done was to add the member, return the new member ID, and loop through the roles array and just add the role ID and member ID to the MemberInRoles tables (by calling another stored procedure to add the roles), but I don't think you can loop in a stored procedure.

Can you maybe help to write some code to parse the comma-separated string, loop through the contents and call the sp_MemberInRoles_InsertMemberRole stored. But there can be 1 role per member, 5 roles, etc, you can't have a fixed loop, it differs.

Regards,
ma se

Colin Angus Mackay3-Apr-06 2:11

3-Apr-06 2:11

ma se wrote:
but I don't think you can loop in a stored procedure

Of course you can loop in a stored procedure. You can use WHILE[^]

DECLARE @remainingRoles NVARCHAR(4000);
DECLARE @commaIndex INT;
DECLARE @currentRole SYSNAME;
SET @remainingRoles = @roles; -- @roles == the value sent into the stored proc
SET @commaIndex = CHARINDEX(',',@remainingRoles);
WHILE(@commaIndex <> 0)
BEGIN
    SET @currentRole = LEFT(@remainingRoles, @commaIndex);
    -- Do stuff with the current role here!
    SET @remainingRoles = RIGHT(@remainingRoles, @commaIndex + 1);
END
IF LEN(@remainingRoles) <> 0
BEGIN
    -- Do stuff with the remaining role - Same code as above, but substituting @currentRole
    -- with @remainingRoles
END

DISCLAIMER: The above has not been tested or optimised. E&:OE

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Brendan Vogt4-Apr-06 4:14

4-Apr-06 4:14

Hi Colin,

I managed to work through your code, and struggled a bit. Your RIGHT statement is incorrect. The input @Role value is "AnotherTestAdmin,Member,SystemAdmin,TestAdminn".

My changed code is (PLEASE FEEL FREE TO COMMENT AND CHANGE AS PLEASED), not sure if I'm using the variables in the right places??

ALTER PROCEDURE [dbo].[sp_Member_InsertMember]
(
@Roles nvarchar(200)
)

AS

BEGIN

DECLARE @RemainingRoles nvarchar(4000);
SET @RemainingRoles = @Roles;

DECLARE @CommaIndex int;
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);

DECLARE @CurrentRole sysname;

WHILE(@CommaIndex <> 0)
BEGIN
SET @CurrentRole = LEFT(@RemainingRoles, @CommaIndex - 1);
PRINT @CurrentRole;
SET @RemainingRoles = RIGHT(@RemainingRoles, LEN(@RemainingRoles) - @CommaIndex);
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
END

IF LEN(@RemainingRoles) <> 0
PRINT @RemainingRoles

END

Not sure what you mean by:
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles

And why do you have the following declaration?
DECLARE @currentRole SYSNAME;

Hope you can shead some more light??

Thanks
ma se

Colin Angus Mackay4-Apr-06 5:21

4-Apr-06 5:21

ma se wrote:
Not sure what you mean by:
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles

Where you have put the PRINT statements is where you need to put your code that deals with the roles - e.g. insert them into your table. In the loop the variable that contains the role name is @CurrentRole, after the loop the variable that contains the last role is @RemainingRoles.

ma se wrote:
And why do you have the following declaration?
DECLARE @currentRole SYSNAME;

Force of habit. SQL Server defines a role name as sysname when it is stored internally: sysusers table[^]

SYSNAME is a synonym for NVARCHAR(128)

Brendan Vogt10-Apr-06 3:56

10-Apr-06 3:56

Just getting back to the this stored procedure. Just a little confused.

The stored procedure is called sp_InsertMember.

What it does is to add the member details, like first and last name, DOB, username and password to the tblMember table. If it @@ERROR is equal to 0, then it loops through the roles string passed as a parameter. The stored procedure used to add the member role is called sp_MemberInRoles_InsertMemberRole, and this I call using the EXECUTE function.

Now my questions are:

In sp_InsertMember I start off with a BEGIN TRANSACTION. If the member was added successfully, where do I add the COMMIT TRANSACTION? Before or after looping through the string of roles?

In the sp_MemberInRoles_InsertMemberRole I also have a BEGIN TRANSACTION and a COMMIT TRANSACTION. Lets say the member was added succesully, then it starts to loop through the string of roles. And lets say the member ahs 3 different roles. After the inserting the first role, it encounters an error, so in sp_MemberInRoles_InsertMemberRole it does a ROLLBACK TRANSACTION. Hoe far does it do this rollback? Will it remove the first role it added? What I want it to do is if the member was inserted successfully, and there is an error with inserting roles, that it removes all roles added, plus the member details.

Here is a modified copy of sp_Member_InsertMember:

ALTER PROCEDURE [dbo].[sp_Member_InsertMember]
(
-- input parameters here
)

AS

BEGIN

DECLARE @ErrorCode int
SET @ErrorCode = 0

DECLARE @TranStarted bit
SET @TranStarted = 0

DECLARE @RemainingRoles nvarchar(4000);
SET @RemainingRoles = @Roles;

DECLARE @CommaIndex int;
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);

DECLARE @CurrentRole sysname

IF (@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END

IF (EXISTS(SELECT MemberUsername
FROM dbo.Members
WHERE MemberUsername = @Username))
BEGIN
SET @ErrorCode = -2 -- Username already exists
GOTO CleanUp
END

INSERT INTO Members
(
-- column names
)
VALUES
(
-- input parameters
)

IF @@ERROR <> 0
BEGIN
SET @ErrorCode = -1
GOTO CleanUp
END

-- Loop thru roles string
WHILE(@CommaIndex <> 0)
BEGIN
SET @CurrentRole = LEFT(@RemainingRoles, @CommaIndex - 1);
EXECUTE dbo.sp_MemberInRoles_InsertMemberRole @@IDENTITY, @CurrentRole
SET @RemainingRoles = RIGHT(@RemainingRoles, LEN(@RemainingRoles) - @CommaIndex);
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
END

-- If all is cool
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN 0

CleanUp:
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode

END

My sp_MemberInRoles_InsertMemberRole has the exact same structure, but just different input values and column names. Also, please comment on the wat that I have doe things here.

Regards,
ma se
South Africa

HimaBindu Vejella3-Apr-06 2:16

HimaBindu Vejella

3-Apr-06 2:16

We can't directly pass array to stored procedure. But the trickish thing is to make the selected ids as a string seperated by commas(,) as "id1,id2,id3 etc. And pass this value to the stored procedure as a varchar in the stored procedure

Code can be written as

CREATE proc UspTestArray
@ids Varchar(100)
As
DECLARE @sql as varchar(500)
Set @sql = 'SELECT * FROM tablename WHERE id in (' + @ids + ')'
Exec(@sql)
go

http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp[^]

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

PaulMcKenzie4-Apr-06 11:07

PaulMcKenzie

4-Apr-06 11:07

I use would use this technique:

First create a user function (one of my many tools):

--==========================================
CREATE FUNCTION tools_Splitter
(
@IDs VARCHAR(1000)
)

RETURNS @TableIDs TABLE(ID INT)

AS

BEGIN
-- Append comma
SET @IDs = @IDs + ','

-- Indexes to keep the position of searching
DECLARE @Pos1 INT, @pos2 INT

-- Start from first character
SET @Pos1=1
SET @Pos2=1

WHILE @Pos1 < Len(@IDs)
BEGIN
SET @Pos1 = CharIndex(',' , @IDs, @Pos1)

INSERT INTO @TableIDs SELECT Cast(Substring(@IDs,
@Pos2, @Pos1-@Pos2) AS BIGINT)

-- Go to next non comma character
SET @Pos2 = @Pos1+1

-- Search from the next charcater
SET @Pos1 = @Pos1 + 1
END
RETURN
END
--==================================================

Then your sproc could look like this:

declare @UserRoles varchar(1000)
declare @UserID int
SET @UserRoles=''
--Build a comma sep list of roles to insert
select @UserRoles=@UserRoles+Convert(varchar(20),OrderID)+',' from tblRoles WHERE 1=1
--Then insert the roles calling the tool you created earlier...
INSERT INTO tblRoles (@UserID,tools_Splitter(@UserRoles))