|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Hi Ricardo,
It works very well !!!
Thank you very, very much.
Best regards
Massimo Nicoletti
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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)
"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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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))
|
|
|
|
|
Try one of these aproaches:
Pass the comma, semicolon (whatever character that never can be a part of role name) separated string of the roles.
Alternatively you may call the same sproc several times within one transaction (to ensure data integrity).
Second approach requires several client - Sql Server roundtrips, but it a bit easier to implement.
Best regards,
-----------
Igor Sukhovhttp://sukhov.net
|
|
|
|
|
hi..
HOw can i store User's photo in sql db?
how can i upload photo by ASP.net page?
computer science..Ainshams UNV..
IS group.
|
|
|
|
|
See reply in ASP.Net forum.
|
|
|
|
|
Hi
I have a database containing several Tables. In a winfoarm, when I want to fill the dataset using data adapter, one of them generates this error (on the Fill statement)...
The strange part is taht when I delete the oleDataAdapter control from the form and create a new one, and then generate dataset from that, this error won't raise...
But the next time I open the solution, again the error continues to raise, unless I recreate the adapter!!!
Could any one tell me what is wrong (while I have recreated the data Table in the database and that didn't help...)
My data Table has two unicode right-to-left Text filds and two Memo fields...
Thanks
|
|
|
|
|
Sorry if this is a dumb question. I have a database that uses a number of user defined types, for example varchar(10) is aliased as "T_TableKey".
I would like to work with same aliases in C# for consistency, but there seems to be no equivalent to the C++ typedef statement.
Any suggestions?
TIA
|
|
|
|
|
I am using database which content are more than 100 000 rows.
In VB 6, with open data connection, I can view my data after 2 sec. but in VB .NET I am using DataSet but while data are loading to dataset it's take me 30 sec. and set DataGridView.DataSource = MyDataSet takes me next 10 .sec and waiting 40 sec. is too much.
Is posible using open conection in .Net and read only rows what I just need to view alike in VB 6?
DataSet is not just the best solution if you using large Database....
Can any body help me with this problem... thx
|
|
|
|
|
Pius__X wrote: I am using database which content are more than 100 000 rows.
That's not large. The database I'm working on at the moment grows at a rate of around a million rows per week.
Pius__X wrote: Is posible using open conection in .Net and read only rows what I just need to view alike in VB 6?
Since I've never used VB6 I can't answer that exact question. However, If you want to get data out and processing as it arrives rather than wait for the whole set to arrive first then you need a Data Reader. It provides a fast, forward only view of the data.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
DataReader is fast, but how to bind DataReader with DataGrid?
Because bind DataReader with DataGrid manually is too compliacetd.. may be not possible.....
manually - I thing, read rows which should be currently view in DataGrid.... not all rows which content database and when you scroll DataGrid then read next rows..... OH MY WEAK ENGLISH
Colin Angus Mackay <-- "million rows per week" are you using DataGrid for viewing these to many rows?
thx
|
|
|
|
|
Pius__X wrote: "million rows per week" are you using DataGrid for viewing these to many rows?
Good grief, No! I was just suggesting a new perspective on what you think of as a large database. In fact, SQL Server 2005 Enterprise Edition can handle a database that is up to a billion terrabytes in size. (Remember that SQL Server can handle many databases also)
Pius__X wrote: DataReader is fast, but how to bind DataReader with DataGrid?
If you must use a data grid then you have some other solutions. For example: Populate the data grid with less data. This is common, but requires extra work if you don't want the user to notice. However, normally, there would be page forward/backward buttons for the user to scroll through the data.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|