----------------------------------------------------------- New Message on MumbaiUserGroup
----------------------------------------------------------- From: khushalthakker Message 2 in Discussion Dear, Your code is perfectly fine, but the problem is that it will work for first time when the value is not in the database. As you have written your select statement in the if condition which returns rows to the any datacontainer you bind with it (datagrid,gridview,formview). And moreover if i am not wrong you coded it properly in your very first mail in which you were inserting the rows if it does not exists and the select statement was out of this condition, means that in any case you are returning some data which is binded to your control upon executed. and there is no need to execute thru whle loop, was perfect the way you did it earlier, you just need to put all the query in stored procedure. And i am totally agree with Jasmine that there will be performance issues while using raw SQL queries instead of stored procedure. That way i think your problem will defnitely will be solved and you can follow the steps i send you by last mail. Please do let me know if it still exists......... Warm Regards, Khushal Thakker >From: "Liz" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: <[EMAIL PROTECTED]> >Subject: RE: [C#.NET] Re: Problem with parameters in select statement >Date: Tue, 5 Jun 2007 15:14:45 +0100 > >Hi Khushal - folks. > > > >I got somewhere with this. > > > >""" > >ALTER PROCEDURE dbo.StoredProcedure1 > > > > ( > > > > @UserName nvarchar(50) > > > > ) > > > >AS > >IF NOT EXISTS (SELECT UserName, Email, AccountID > > FROM Accounts > > WHERE (UserName = @UserName)) > >BEGIN > > INSERT INTO Accounts(UserName) > > VALUES (@UserName) > > > > SELECT UserName, Email, AccountID > > FROM Accounts > > WHERE (UserName = @UserName) > > END > > > >"""..... when I tested it as a stored proc it worked first time - using the >test pain in aqldatasource! It either > >returned the existing record, or created a new one and returned that which >is both brilliant and a > >giant leap in the right direction. > > > >The only problem that I seem to have now is that I cant get it to return >the >data to the fields in the > >formview that uses the sqldatasource. The formview shows up the >emptydatatemplate. Is it possible > >for the code in the stored proc to be confusing the form view somehow ? At >design time the formview does however add the fields UserName,Email and >AccountID to its templates. > > > >I created a tidied up version using a while loop to see if it made any >difference ... > >""" > >ALTER PROCEDURE dbo.StoredProcedure1 > > ( > > @UserName nvarchar(50) > > ) > > > >AS > >while (not exists(SELECT UserName, Email, AccountID > > FROM Accounts > > WHERE (UserName = @UserName) )) > >begin > > INSERT INTO Accounts(UserName) > > VALUES (@UserName) > >end > >""" ... but made matters worse, as for some reason this fails to return or >create anything when tested. Also stuck trying to trace this stuff. I >placed breakpoints in the code, but cant seem to get it to halt at any >point >so I can find out whats happening. > > > >Thanks all for help so far - Liz > > > > > > > >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On >Behalf >Of khushal thakker >Sent: 04 June 2007 19:30 >To: [EMAIL PROTECTED] >Subject: RE: [C#.NET] Re: Problem with parameters in select statement > > > >hi dear, > >i also tried to do the same its running perfectly. > >try running query in sql studio, there can be error in ur sql statement, if >not please check what the query which i had run in my application. > >IF NOT EXISTS (SELECT [docid] FROM [TempIssueCodeResults] >WHERE [docid] = @docid) >BEGIN >INSERT INTO TempIssueCodeResults(docid) VALUES(@docid) >END > >SELECT [docid] FROM [TempIssueCodeResults] WHERE [docid] = @docid > >please go thru the steps and confirm it: > >1) add sqldatasource to your webform, >2) configure it by selecting custom query or stored procedure radio button, >click next >3) select 'select' tab then write ur query in the text box given, click >next >4) here you will be prompted for parameter value of the variable declared, >select session from the >parameter source and then give name of the parameter in 'SessionField' >(below parameter source) >'username' (which is name of session variable Session["username"] in ur >case), click next and then click finish. > >Do let me know if problem still persists, as it is working fine with me. > >Regards >Khushal Thakker > > >From: "Liz" <[EMAIL PROTECTED] <mailto:itslz%40yahoo.co.uk> > > >Reply-To: [EMAIL PROTECTED] <mailto:CSharpNET%40yahoogroups.com> > >To: <[EMAIL PROTECTED] <mailto:CSharpNET%40yahoogroups.com> > > >Subject: RE: [C#.NET] Re: Problem with parameters in select statement > >Date: Mon, 4 Jun 2007 18:10:07 +0100 > > > >Thanks Mailrahn, there is no stored procedure, and would rather make do > >without for now if I could. The code I presented is SQL in the select > >statement of an sqldatasource object dropped on a form. I have used the > >"Where" window in the objectdatasource configuration window to assign @ > >UserName to a session variable - but that doesn't really make any > >difference. I have tried testing the sql outside of the object >datasource. > >I tried testing it with the sql pain having brought up the Accounts table > >in > >the server explorer. Before I modify the select statement, when I execute > >the sql, it asks for the user name. I then enter one and it works. Its > >when I try to add the extra code to insert the a record for that user if >it > >doesn't already exist then it fails with @UserName. > > > > > > > >Thanks Mailrahn - Lz > > > > > > > > > > > >From: [EMAIL PROTECTED] <mailto:CSharpNET%40yahoogroups.com> >[mailto:[EMAIL PROTECTED] <mailto:CSharpNET%40yahoogroups.com> ] On > >Behalf > >Of mailrahn > >Sent: 04 June 2007 16:11 > >To: [EMAIL PROTECTED] <mailto:CSharpNET%40yahoogroups.com> > >Subject: [C#.NET] Re: Problem with parameters in select statement > > > > > > > >Is @UserName declared in your stored procedure as something that gets > >passed to it? > > > >Something like: > > > >CREATE PROCEEDURE UserSomethingOrAnother > >( @UserName varchar(500) ) > >AS > >begin > >INSERT INTO Accounts(UserName) > >VALUES (@UserName) > >SELECT [UserName], [Email], [AccountID] FROM [Accounts] WHERE > >([UserName] = @UserName) > >end > > > >For testing, you can declare and set that variables in your query. > >Declare @userName varchar(500) > >Set @username = 'Fred' > >BEGIN > >INSERT INTO... > > > >Hope that helps. > > > >--- In [EMAIL PROTECTED] <mailto:CSharpNET%40yahoogroups.com> ><mailto:CSharpNET%40yahoogroups.com> , > >"itslz" <[EMAIL PROTECTED]> wrote: > > > > > > Hi again all, thanks for all the help last time. > > > > > > I have a little problem with sql - mainly because I know very > >little > > > about sql. I've been 5 miles round the net and cant get the > >answers > > > I'm after - but I'm sure zillions of you must know the answer (she > > > hopes n prays). > > > > > > All I wanted to do was customise the select statement of my > > > sqldatasource to create a record for the user if it didnt allready > > > exist - trivial ha! > > > > > > Suppose I'd best lay down the gory details. > > > > > > I have a table in the database called "Accounts", which has a > >primary > > > key called "AccountsID" which is set as an incremental seed. I > >have > > > a field for the users name called "UserName" which I have > >configured > > > sqldatasource to get from Session["UserName"](which I set up in the > > > form). I then have a field called Email, which is to be submited > >by > > > the user via the form, and others that dont need to be mentioned. > > > > > > I configure the select to generate insert update and delete and it > > > returned the following select statement and it tests ok. > > > > > > "SELECT [UserName], [Email], [AccountID] FROM [Accounts] WHERE > > > ([UserName] = @UserName)" > > > > > > I then tried changing it via the selectquery property to > > > > > > " if not exists(SELECT [UserName], [Email], [AccountID] FROM > > > [Accounts] WHERE ([UserName] = @UserName)) > > > > > > begin > > > INSERT INTO Accounts(UserName) > > > VALUES (@UserName) > > > SELECT [UserName], [Email], [AccountID] FROM [Accounts] WHERE > > > ([UserName] = @UserName) > > > end > > > " .... when I try to execute the query it now says it cant > >represent > > > the quiery on the diagram (acceptable I suppose), and that I must > > > declare scalar variable @UserName. I tried the above substituting > > > @UserName with a fixed name like 'fred' > > > > > > > > > " if not exists(SELECT [UserName], [Email], [AccountID] FROM > > > [Accounts] WHERE ([UserName] = 'fred')) > > > begin > > > INSERT INTO Accounts(UserName) > > > VALUES ('fred') > > > SELECT [UserName], [Email], [AccountID] FROM [Accounts] WHERE > > > ([UserName] = 'fred') > > > end > > > ", and it worked. No matter what I try I cant seem to get any > >other > > > statement to work with parameters other than the one sqldatasource > > > supplies. > > > > > > Do you achieve this sort of thing with stored procs by any chance ? > > > Can it really be achieved simply the way I wanted to ? > > > > > > > > > Greatful as always for any help - Liz > > > > > > > > > > > > > > >[Non-text portions of this message have been removed] > > > >__________________________________________________________ >Catch the best matrimonial profiles in town. Visit Shaadi.com >http://www.shaadi.com/ptnr.php?ptnr=mhottag > > > > > >[Non-text portions of this message have been removed] > _________________________________________________________________ Catch the best matrimonial profiles in town. Visit Shaadi.com http://www.shaadi.com/ptnr.php?ptnr=mhottag ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/MumbaiUserGroup/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
