I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
I'm having a bit of trouble updating my script. I have a script used for MS Access and I need to transfer it to be compatible with MS SQL Server. When I run the script it gives the error ''ODBC driver does not support the requested properties.'' This is the line of the script that I have to change to work with sql: ''oRS.Open SqlTxt , oConn'' and the sql text is: ''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;". Could anybody please help me out in solving this problem?
I've created access project using upsizing wizard. Project is created ok on slq server 7.0(ie tables, stored proc, views etc.) but when i open tables or forms in A2k it doesn't allow me to enter or modify data. Though all permissions are Ok.
Could anyone please let me know what could be wrong?
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) I am using SQL server Express 2005 on my local machine. And ASP.net 2.0 with C#.
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
Hello Fellow Members, Background: Hopefully someone out there can help me. Here is the situation. I have a documents table which stores "documents". A document can either be a folder or a actually document(word, excel ...). This is determined by a field in the table called 'Type' which is either a '0' or '1'The table stores the following data for each 'document'--------------------------------------------------------------------------------DocID - intDocument - Actual document in bytesLeafName - Actual document nameType - Determines type of document (0 - document, 1- folder)DirName - Which folder the document resides in (can be null) Problem:I want to create a query which navigates through the entire table and deletes all documents. For example, if a user has a folder called (Temp) and that folder has 30 documents and another folder called (Temp2) that also has documents. Now the user wants to delete (Temp). So what I want the query to do is go through the database and delete then entire folder (Temp) and all of its contents associated with it. Question:How can I traverse through the table to delete the documents?All of your help would be greatfull.
Ran sp_removedbreplication @dbname = 'FooDatabase' Did NOT want to do that! Have the replication scripted out but getting errors when trying to run the script to recreate. All Push
I tried sp_droppublication 'Foopubname' sp_MSload_replication_status
Error 21776: [SQL-DMO] the 'publication name' was not found in the TransPublications collection. I cannot create a new publication of the same name.
I need to create a procedure to output mean and deviation AND then I also need to create a trigger. Below is the table 'Account' I made.. but the procedure and trigger I made below that is not correct. Can someone revise this for me? I just can't figure it out.
Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.
CREATE PROCEDURE project2question2 AS
DECLARE @Mean MONEY
DECLARE @Deviation MONEY
SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)
SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation
The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.
Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)
CREATE TRIGGER trigger_ex2
ON ActionHistory
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE ActionHistory
SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)
SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)
Hello Everyone. Im sorry for this urgent post, but have critical issue that needs a solution quick. So for my issue. I am adjusting our sales order tables to handle a couple different scenarios. Currently we have 2 tables for sales orders
SALESORDERS ------------ SORDERNBR int PK, { Addtl Header Columns... }
SALESORDERDETAILS ------------------- SODETAILID int, SORDERNBR int FK, PN varchar, SN varchar(25), { Addtl Detail Columns ... }
Currently the sales order line item is serial number specific. I need to change the tables to be able to handle different requests like :
Line Item Request ( PN, QTY ) Line Item Request ( SN ) Line Item Request ( PN, GRADE, QTY ) ETC.
I am thinking i need to create a new table to hold the specifics for a particular line item. Maybe like this :
SALESORDERSPECS ---------------- SOSPECID int, SODETAILID int FK, SPECTYPE varchar, IE : SN, PN, GRADE. { one value per row } SPECVALUE varchar IE : GRADE A
Im thinking i would need to rename the SALESORDERDETAILS table to SALESORDERITEMS. SALESORDERITEMS would just contain header info like SalePrice, Warranty, Etc...
Then rename SALESORDERSPECS to SALESORDERDETAILS...
Anyone understand what im trying to do? If you need more info please ask. You can also get a hold of me through IM.
My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. Please help.
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
I am having problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.
hello, i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.it works like:insert into table() values()select @id = (newly created id number) from tableinsert into table1() values(@id) something like that.
Hello all, I am working on a tight deadline with a massive project. This project uses stored procedures for the database work. All help is insanely appreciated! There are 5 different SP's for writing to the database, all separate sections. The problem is when I try to write with 5 sets of Insert parameters in my SQL data source it throws back "Procedure or function has too many arguments specified." When I remove them all and just leave the ones that procedure will use, it works fine. Examples below. There is a stored procedure for all number sets such as (SP1 - @Num1, @MaxBS1, @MinBS1) (SP2 - @Num2, @MinBS2, @MaxBS2) This works fine because its the "1's" that are being written (Num1, MinBS1, MaxBS1, Etc.):1 <InsertParameters> 2 3 <asp:SessionParameter SessionField="Jackalope" Name="Jackalope" /> 4 5 <asp:ControlParameter ControlID="Textbox1" PropertyName="Text" Name="Num1" /> 6 7 <asp:ControlParameter ControlID="MaskedTextbox1" PropertyName="Text" Name="MinBS1" /> 8 9 <asp:ControlParameter ControlID="MaskedTextbox2" PropertyName="Text" Name="MaxBS1" /> 10 11 <asp:ControlParameter ControlID="RadioButtonList1" PropertyName="SelectedValue" Name="Bonus1" /> 12 13 <asp:ControlParameter ControlID="MaskedTextbox151" PropertyName="Text" Name="MinBonus1" /> 14 15 <asp:ControlParameter ControlID="MaskedTextbox152" PropertyName="Text" Name="MaxBonus1" /> 16 17 <asp:SessionParameter SessionField="UserId" Name="UserId" /> 18 19 </InsertParameters> 20
This on the other hand will not work.. im guessing even though the stored procedure only contains the parameters it needs, it still try's to force them all in? 1 <InsertParameters>2 <asp:SessionParameter SessionField="Jackalope" Name="Jackalope" />3 <asp:ControlParameter ControlID="Textbox1" PropertyName="Text" Name="Num1" />4 <asp:ControlParameter ControlID="Textbox2" PropertyName="Text" Name="Num2" />5 <asp:ControlParameter ControlID="Textbox3" PropertyName="Text" Name="Num3" />6 <asp:ControlParameter ControlID="Textbox4" PropertyName="Text" Name="Num4" />7 <asp:ControlParameter ControlID="Textbox5" PropertyName="Text" Name="Num5" />8 <asp:ControlParameter ControlID="MaskedTextbox1" PropertyName="Text" Name="MinBS1" />9 <asp:ControlParameter ControlID="MaskedTextbox3" PropertyName="Text" Name="MinBS2" />10 <asp:ControlParameter ControlID="MaskedTextbox5" PropertyName="Text" Name="MinBS3" />11 <asp:ControlParameter ControlID="MaskedTextbox7" PropertyName="Text" Name="MinBS4" />12 <asp:ControlParameter ControlID="MaskedTextbox9" PropertyName="Text" Name="MinBS5" />13 <asp:ControlParameter ControlID="MaskedTextbox2" PropertyName="Text" Name="MaxBS1" />14 <asp:ControlParameter ControlID="MaskedTextbox4" PropertyName="Text" Name="MaxBS2" />15 <asp:ControlParameter ControlID="MaskedTextbox6" PropertyName="Text" Name="MaxBS3" />16 <asp:ControlParameter ControlID="MaskedTextbox8" PropertyName="Text" Name="MaxBS4" />17 <asp:ControlParameter ControlID="MaskedTextbox10" PropertyName="Text" Name="MaxBS5" />18 <asp:ControlParameter ControlID="RadioButtonList1" PropertyName="SelectedValue" Name="Bonus1" />19 <asp:ControlParameter ControlID="RadioButtonList2" PropertyName="SelectedValue" Name="Bonus2" />20 <asp:ControlParameter ControlID="RadioButtonList3" PropertyName="SelectedValue" Name="Bonus3" />21 <asp:ControlParameter ControlID="RadioButtonList4" PropertyName="SelectedValue" Name="Bonus4" />22 <asp:ControlParameter ControlID="RadioButtonList5" PropertyName="SelectedValue" Name="Bonus5" />23 <asp:ControlParameter ControlID="MaskedTextbox151" PropertyName="Text" Name="MinBonus1" />24 <asp:ControlParameter ControlID="MaskedTextbox153" PropertyName="Text" Name="MinBonus2" />25 <asp:ControlParameter ControlID="MaskedTextbox155" PropertyName="Text" Name="MinBonus3" />26 <asp:ControlParameter ControlID="MaskedTextbox157" PropertyName="Text" Name="MinBonus4" />27 <asp:ControlParameter ControlID="MaskedTextbox159" PropertyName="Text" Name="MinBonus5" />28 <asp:ControlParameter ControlID="MaskedTextbox152" PropertyName="Text" Name="MaxBonus1" />29 <asp:ControlParameter ControlID="MaskedTextbox154" PropertyName="Text" Name="MaxBonus2" />30 <asp:ControlParameter ControlID="MaskedTextbox156" PropertyName="Text" Name="MaxBonus3" />31 <asp:ControlParameter ControlID="MaskedTextbox158" PropertyName="Text" Name="MaxBonus4" />32 <asp:ControlParameter ControlID="MaskedTextbox160" PropertyName="Text" Name="MaxBonus5" />33 <asp:SessionParameter SessionField="UserId" Name="UserId" />34 </InsertParameters> Which is being called by: 1 If Val(TextBox1.Text) >= 1 Then2 Session("Jackalope") = "Environmental Engineer"3 SqlDataSource1.InsertCommand = "ScreenD1"4 SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.StoredProcedure5 SqlDataSource1.Insert()6 End If Is there a way to keep all of my controls parameters centralized in one SQLDataSource and still call my Stored Procedures?
Hello, I'm new to SQL. I wrote this Stored Procedure but it does not work. When I'm executing it I get these errors:
Server: Msg 170, Level 15, State 1, Procedure Test, Line 12 Line 12: Incorrect syntax near '='. Server: Msg 170, Level 15, State 1, Procedure Test, Line 15 Line 15: Incorrect syntax near ')'. Server: Msg 170, Level 15, State 1, Procedure Test, Line 19 Line 19: Incorrect syntax near '='. Server: Msg 170, Level 15, State 1, Procedure Test, Line 24 Line 24: Incorrect syntax near '='
Can anyone please help me with that? Thank you in advance.
CREATE PROCEDURE Test As BEGIN TRANSACTION Select PVDM_DOCS_1_5.DOCINDEX1, TableTest.DOCINDEX2, TableTest.DOCINDEX3, TableTest.DOCINDEX4 From PVDM_DOCS_1_5, TableTest
WHERE TableTest.DOCINDEX1 = PVDM_DOCS_1_5.DOCINDEX1
AND (TableTest.DOCINDEX2 Is NULL or TableTest.DOCINDEX2 ='' OR TableTEst.DOCINDEX3 Is NULL or TableTest.DOCINDEX3 ='' or TableTest.DOCINDEX4 is NULL or TableTEst.DOCINDEX4 = '');
IF TableTest.DOCINDEX2 is NULL or TableTest.DOCINDEX2 = '' UPDATE TableTest.DOCINDEX2 = DOCINDEX2 WHERE (DOCINDEX1 IN (SELECT DOCINDEX1 FROM PVDM_DOCS_1_5)) END IF
If TableTest.DOCINDEX3 is NULL or TableTest.DOCINDEX3 = '' UPDATE TableTest.DOCINDEX3 = PVDM_DOCS_1_5.DOCINDEX3 WHERE (DOCINDEX1 IN (SELECT DOCINDEX1 FROM PVDM_DOCS_1_5)) END IF
If TableTest.DOCINDEX4 is NULL or TableTest.DOCINDEX4 = '' UPDATE TableTest.DOCINDEX4 = PVDM_DOCS_1_5.DOCINDEX4 WHERE (DOCINDEX1 IN (SELECT DOCINDEX1 FROM PVDM_DOCS_1_5)) END IF;
DELETE PVDM_DOCS_1_5 WHERE DOCINDEX1 = DOCINDEX1
IF (@@ERROR <> 0) GOTO on_error
COMMIT TRANSACTION -- return 0 to signal success RETURN (0)
on_error: ROLLBACK TRANSACTION -- return 1 to signal failure RETURN (1) GO
We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.
I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.
Any ideas how how to make MSDE let anyone connect? Thanks in advance!
My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. In Surface Area Conforuration missing all the service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. I already try to install those package, no matter how i try to install those service package, it keep saying i didn't install any of them. Please help.
I'm trying to import data into an SQL Server (7.0) and I'm wondering which Source (Microsoft Data Link, Microsoft ODBC Driver for Oracle, Microsoft ODBC Driver for SQL Server, etc.) -- I THINK we would use the SQL Server driver but I'm not sure... to use AND WHERE TO GO FROM THERE? So far, I get seem to get things to work in my favor. I appreciate any help :) The data I'm trying to import is from Microsoft Excell. If there is anything else you need to know, please email me at iami@iami.org Please provide email/forum-based technical support.
Executed as user: GWfmnlasa. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.