SqlDataSource: Can't Gen INSERT/UPDATE/DELETE Statements; Using 2.0 Membership Aspnetdb
Jul 20, 2006
Hi,
I'm new to ASP.NET and having a problem configuring the SqlDataSource control. I am using the standard ASP.NET 2.0 "aspnetdb" database to manage user accounts. The problem is this:
When using the wizard to configure my SqlDataSource control, the option to auto-generate the Insert/Update/Delete SQL statements are grayed out. I've searched this forum and found that this can be a symptom of no primary keys in the tables. However, there are primary keys (UserId), which is the default schema as generated by asp.net (aspnet_regsql.exe).
When I use the wizard, I make the following choices:
How would you like to retrieve data from your database?
-> Select "Specify columns from a table or view"
-> Select the "vw_aspnet_MembershipUsers" view from the "Name:" drop-down list
-> Select "UserId", "Email", "UserName" from "Columns:"
After this, still no option to auto-generate I/U/D statements. Any thoughts on why this isn't working???
Thanks,
Leah.
View 1 Replies
ADVERTISEMENT
Apr 21, 2007
Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.I have been looking for an answer to my problem, but unfortunately I couldn’t find one. So I created a user here on www.asp.net just for making this post.
Before I continue I just want to apologies if there is another post where this question is already answered.
Please watch this Print Screen I just took: � http://www.bewarmaronsi.com/Capture.JPG “
As you can see the “INSERT, UPDATE, and DELETE Statements� are disabled, and that’s exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.
The MDF file is located in the App_data folder and is called ASPNETDB.
And when I try to add custom SQL statements, it gives me Syntax error near “=�. Something like that. I bought the Total Training Set1 package and it works perfect in their examples.
I just want to thank you for reading my post and I hope that you got some useful information for me.
By the way, I’, from Sweden so you have to excuse me if my English is rusty.
Thanks!
PS: Can it be that I’m running windows Vista?
View 4 Replies
View Related
Mar 26, 2008
I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance
the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG
View 7 Replies
View Related
Oct 25, 2004
Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?
They also want us to track schema changes. Is this possible?
Thanks, Dave
View 5 Replies
View Related
Jan 3, 2006
Hello Guys,
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?
View 4 Replies
View Related
Jul 4, 2006
i have default.aspx file and i put SqlDataSource into my designer form.when i want to configure SqlDataSource it not allowed my to insert, update and delete only insert is allowed.what is the problem and the solution for this case ...thx
View 1 Replies
View Related
Jan 18, 2008
hi
i have form view that retrieves a single row
i dont want to use SQLDataSource default Select/Update/Insert/Delete buttons
i am using Stored Procedures
I want to have my own buttons, the select has parameters, how to retrieve data for that parameter?
View 6 Replies
View Related
Apr 23, 2008
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
View 2 Replies
View Related
Mar 23, 2008
I can select the tables and fields, but when I click on Advanced the Check Box to Create the Commands is not available. they are dimmed out.
Why is this happening, is it a setting that I have missed. Any help is appreciated.
View 1 Replies
View Related
Feb 2, 2006
How do i insert the following information(Membership Username & Date Time) to my insert command in a SQLDatasource?<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DMC_dbconn %>" DeleteCommand="DELETE FROM [comment] WHERE [commentno] = @commentno" InsertCommand="INSERT INTO [comment] ([customerno], [comment], [commentdate], [category]) VALUES (Membership.GetUser, @comment, DateTime.Now, @category)" SelectCommand="SELECT * FROM [comment]" UpdateCommand="UPDATE [comment] SET [customerno] = @customerno, [comment] = @comment, [commentdate] = @commentdate, [category] = @category WHERE [commentno] = @commentno">
View 5 Replies
View Related
Apr 8, 2008
I've set up the membership and authentication tables/stored procedures on an SQL2005 Express database. I'm now trying to get the login on my forms to work. I set up two 'members' through the Web Site Administration Tool and everything seems to work fine as far as logging in is concerned.
However, I need to retrieve some information from the membership tables through the codebehind but I've discovered that the data created by the WSAT wasn't stored in my database but in the ASPNETDB.MDF file in my application's Bin folder. How can I ensure that the data created by the WSAT is stored in the correct database? What have I missed?
View 5 Replies
View Related
Jan 1, 2008
Hello - I am using the generic aspnetdb membership database (aspnet_regsql.exe) which I am currently connecting to as a sysadmin user from my website (via web.config). Before going live with my first website I now want to create a sql user for the website to login with, with the minimum permissions required to run the website - the reason for this is to make the db more secure.
I have created a new sql user and am now looking at what acces they should have. I am OK giving them the required access to the NEW views, sprocs and tables I have created to link in with the generic aspnetdb database etc... however how do I know what access they require to all the BUILT-IN tables, sprocs, views etc. Also, when I am viewing Securables it also brings up all the MASTER DB sprocs, etc.. - will the user need access to these? Should I add the new user to a database role - which one? (currently I have not given the new user a database role) This is the first time I will have created a sql user for an asp.net website so any help greatly appreciated.
Cheers
Marco
View 1 Replies
View Related
Jul 17, 2015
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?Â
View 3 Replies
View Related
Aug 3, 2006
I'm referring to this bug, which I've seen a lot of people (including me) run into:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937
Has anyone heard if MS plans to fix this soon?
Thanks
View 3 Replies
View Related
May 14, 2002
Hello,
I am creating my companys' database and I have a small problem that must be
solved.
I have a pictures table:
PicturesTable
-------------
ProductID int ForeignKey
Picture nvarchar(30)
...
(A product can have many pictures & the ProductID is unique for any product,
but not for the table of pictures).
What I want to do is to somehow do a procedure to:
1) Check if any images (for a productID) exists in the table
2) if they do not exist then add the appropriate images into the table
3) if the images exist, then update the images with the new one that I have.
What I thought was to just delete all the images from the table for the
specific product:
DELETE FROM PicturesTable
WHERE ProductID = '10-11'
and then add the appropriate images:
INSERT INTO PicturesTable (ProductID, Picture)
VALUES ('10-11', 'Dir1/Pic1.gif')
INSERT INTO PicturesTable (ProductID, Picture)
VALUES ('10-11', 'Dir1/Pic2.gif')
INSERT INTO PicturesTable (ProductID, Picture)
VALUES ('10-11', 'Dir1/Pic3.gif')
but I do not like a lot this idea because if a user tries to read the pictures
for that product (at the same time I was deleting them) s/he would get
nothing. Is any other way that I can do it please?
I would appreciate it if someone answers me.
Yours, sincerely
Efthymios Kalyviotis
ekalyviotis@comerclub.gr
View 1 Replies
View Related
Apr 8, 2008
Our existing DW's ETL was written in a very complex fashion by the previous team. They use DTS package lookups to read a row in the Source SQL Server database see if that row exists in the taget SQL Server database. If the row does not exist, they use ActiveX scripts to INSERT the row in the target SQL Server database. If it exists, they update the row on the target side. How would you do this in SSIS? Apologize if this sounds like a basic question, however, I would have done this via Stored Procedures or SQL Scripts especially since it involves SQL Servers alone. Appreciate any help.
View 6 Replies
View Related
Mar 11, 2004
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanks
View 14 Replies
View Related
Dec 13, 2007
Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
View 3 Replies
View Related
Jan 8, 1999
I am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table.
I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks.
UPDATE MemberList
Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd
FROM MemberList, MemberListUpd
WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd
INSERT ?
DELETE MemberListUpd
View 2 Replies
View Related
Jan 22, 2008
Im trying to keep a mirror image of some data Im getting from Quickbooks.
As the records are inserted into the database I need to check if a record exists and either update or insert a new one.
it seems easier just to delete using the tnxid and reinsert vs updating
my question is if I go
begin
INSERT INTO QBInvoicesQue(100s of feilds)
end
begin
delete from QBInvoices where txnid = @TxnID
end
and there is not matching txnid to delete from will it cause any problems? before going to the insert statement?
begin
INSERT INTO QBInvoices(100s of feilds)
end
View 6 Replies
View Related
Oct 16, 2007
I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server. Does anyone know if this a still a true statement in SQL Server 2005?
Thanks,
-shl
View 10 Replies
View Related
Jul 24, 2007
I've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written.
So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
View 11 Replies
View Related
Jul 24, 2007
for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...
View 2 Replies
View Related
Oct 20, 2007
I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
View 1 Replies
View Related
Nov 13, 2005
Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy"
UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)">
<DeleteParameters>
<asp:Parameter Name="Original_testID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyNL" />
<asp:Parameter Name="BuyFR" />
<asp:Parameter Name="original_BuyID" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="BuyID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True"
SortExpression="BuyID" />
<asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" />
<asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" />
<asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" />
</Columns>
</asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart
View 3 Replies
View Related
Apr 7, 2006
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.
How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.
Maybe I didn't explain it good enough for you, please tell me then!!
Thanks!!
View 3 Replies
View Related
Aug 15, 2001
I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.
DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?
PLEASE SUGGEST ME..ITS URGENT.
THANKS IN ADVANCE
HARISH
View 2 Replies
View Related
Dec 6, 2007
Hello,
What is faster / better for performance?
Check if something already excist and update the changed values? or delete the whole table and then insert everything?
Thanks!
View 2 Replies
View Related
Jul 10, 2006
SQL2K sp4
Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).
View 1 Replies
View Related
Feb 18, 2014
How to Insert,Update and delete through script without using merge.I have simple requirement of Deleting,Inserting and Update from one table to other table...Here is the Sample Data
CREATE TABLE #Table1
(ID INT ,Name VARCHAR(30),DATEKEY INT)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (1,'Mohan',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (2,'Raj',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (3,'Majja',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (4,'Majjaa',20131231)
[code]...
So now i need to update 1st record and add another new record..So i need to update as well as delete the existing data in Target table.
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (5,'Macha',20131231)
My output should come like this one :
IDNameDATEKEY
1Mohan20131231
5Macha 20131231
with out using merge how can i handle Update,insert and Delete through TSQL
View 5 Replies
View Related
Dec 2, 2014
I have to create a trigger that will log who changed information on a table and when (NOT what they have changed).
My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.
Here is the SQL I have so far.
-- Primary Database Tables --
CREATE TABLE Physician (
Physician_ID int not null identity(1,1) primary key,
First_Name varchar(100),
Last_Name varchar(100),
Mobile_Number varchar(15),
Pager_Number varchar(15)
[code].....
View 1 Replies
View Related
Mar 20, 2006
I am still learning SSIS.
What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns
For ex
If "the user in my source table has changed the status" Then
Update the user row in the destination table
Else skip the row
If "the user in my source table has delete flag =1 " Then
Delete the user in the destination table
Else skip the row
If "the user in my source table is new(has a new ID) " Then
Insert the user in the destination table
Else skip the row
Any suggestions ....
Cheers
Siaj
View 6 Replies
View Related
Sep 2, 2006
hi,
I have a table which contained 5 columns and with 2 primary keys
Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
ab | 1 | abc | null | null
ab | 2 | def | null | null
Col 1 and Col 2 both are primary keys.
How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it?
Thanks
View 3 Replies
View Related
