I have been successful with DTS packages and various SQL statements. However, I have a new challenge. I have a table in an SQL Server database. One of the columns is employee number and a column for department number(which is not populated) In a remote AS400 file I have the employees number and department number. I want to create a package to connect to remote table and update SQL Server table with department number where the two tables match on the employee number.
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
HiI have have two linked SQL Servers and I am trying to get remote writesworking correctly (fast).I have configured the DB link on both machines to:Point at each others DB.I have security set up to map each others server loginsand Server Options: Collation Compatible, Data Access, RPC, RPC Out, UseRemote Collation all checkedMy problem is that when a SP performsBegin TransactionUpdate Local TableUpdate Remote TableCommit TranIt takes several seconds to complete. (about 7 seconds not acceptable tous)This is due to the remote update - how can I improve the response time?example of a stored procedures that takes timewhere ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
I have a windows based software application that connects remotely to a sql server 2005 and calls stored procedures. There is someone that I work with that is cautioning the company strongly that this is a bad thing to do in terms of security. Isn't this a very common thing to do? Assuming that the SQL Server installation is setup appropriately. Is this a bad practice?
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Hello, My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type. The dropdown options on the form will be as follows: ComplimentsComplaintsGeneral CommentsSuggestions Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item. For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter. However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated. If you need more information, I will provide whatever is needed. As always, thanks for everyone's assistance. Chris
I don't know if the title for the subject is appropriate here, anywayhere goes:This process was set up by someone and I have inherited it. I have asql2000 database that has about 13 tables that get populated with datafrom 3 different databases. I have identified where each of this datacomes from, and the stored procedures that do the updates, inserts, anddeletes, and the jobs that run these stored procedure to do theupdates, except for one table. The updates for all the other tablesare done through scheduled jobs. For the one table I know where thedata comes from and the stored procedure that needs to run to do theupdate on the table, however I have not been able to identify theprocess that runs the stored procedure.I am hoping that someone can give me a clue as to how to find out wherea stored procedure is being used - or any other hint as to how I couldgo about finding out how this table gets updated.ThanksKR
HiI am using Visual Web Developer 2005 Express and SQL Server Express 2005.From Northwind I can display data to Gridview.Could someone point me in the right direction on two points.How do I start a new database (.mdf) - I cannot find this option.I can change Northwind around - but I want to start my own database. Once I have that sorted - how can I "insert" data into database using a dropdown box?A "dropdown" box such as you would find online. Thanks in advance.Stephen
I'm using Visual Basic 2005 Express and SQL Server 2005 Express. I have textboxes on a VB form linked to 2 database tables.
I am wondering if it is possible to use just ONE BindingNavigator to manage data entry and updates to THE database tables. I initially thought I could manage the tables but have I encountered some problems:
i)When I entered a new record, and clicked on the SAVE BUTTON the new record the textboxes for the 1st table saves the record to the database, but the textboxes for 2nd table still retained data in them and are not saving theirs to the database.
ii) The same textboxes for the 2nd table are NOT allowing for updates too! Or, could it simply be that it is not possible to use this method for data entry and updates?
I need to make a job that will update up to 8000 rows with the list description of 'berkhold' to 'berknew' in SQL 2000. This is something that I have to do with several projects manually every day by doing the following 2 steps.
SELECT ListDescription, CRRecordID FROM dbo_BerkleyGroupInventory WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC' ORDER BY CRRecordID
I then scroll to the 8000th row and copy the CrrecordID and run the following query
UPDATE dbo.berkleygroupinventory SET listdescription ='berknew' WHERE ListDescription ='BerkHold' AND CRRecordID <=5968432 AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'
I'm sure there's an easier way to do this, but I'm very new to SQL and haven't figured it out yet
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
I need help to resolve the following problem. I have a form that is supposed to insert data into my data base. But I get an error message when I click submit. Below is the Code and after that is the error message that I get. Thank you in advance.
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) End SubProtected Sub insertSubmitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) SqlDataSource1.Insert()End Sub </script><html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> <script type="text/javascript"> function pageLoad() { }
Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.] System.Web.UI.ClientScriptManager.ValidateEvent(String uniqueId, String argument) +2132728 System.Web.UI.Control.ValidateEvent(String uniqueID, String eventArgument) +108 System.Web.UI.WebControls.DropDownList.LoadPostData(String postDataKey, NameValueCollection postCollection) +55 System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, NameValueCollection postCollection) +11 System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad) +353 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1194
I'm trying to make a query to insert data form xls file to my database and it's working
Here's my code
INSERT INTO MY_TABLE SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="C: ipTMP_Aubrygsi_osat.xls"; User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$ WHERE SAP_NBR is not null AND PERIOD is not null AND ANSWER is not null AND OSAT is not null AND SCORE is not null
And I would like to verify if there no sap_nrb and period already the same in the database so I add : AND SAP_NBR not in MY_TABLE.SAP_NBR AND PERIOD not in MY_TABLE.PERIOD
I am running a Access97 front end with a SQL Server 7 backend. On records with an ntext datatype, you are only allowed to update records if the ntext field is null. The tables are linked from access. You get a "cannot update linked table" and "ODBC error #306." Any suggestions??
We have a legacy database whose data needs to be included in our yet-to-be-built sql 2005 data warehouse. Some of the tables don€™t have any natural candidates for a primary key. (Of course, we also need to add other data to the mix.)
Suppose we load the empty warehouse initially. In following loads we don€™t want to include those records that haven€™t changed from the first load (€œduplicates€?) but we also don€™t want to delete the contents of the entire warehouse because of the load time. Any ideas/best practices how to handle €œincremental updates€? to a warehouse would be appreciated.
I got anywhere from a couple hundred to a hundred thousand records that need to be updated or inserted into their SQL Server 2005 end destination. What are some of the best ways to accomplish this? Right now we are doing it manually through line by line updates and inserts. Would I use BC or some other bulk import tool?
Hello, I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail: There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies? Thanks.
I got Form Authentication to work, but I am getting this error sometimes. To reproduce this error consistantly is to run iisreset.exe to have a fresh start. Go to my report server login and open any report.
The page will display: The remote server returned an error: (500) Internal Server Error.
If i click "F5" to refresh the page the report will show up or if you go to another report it will work also. It is just the first time after iisreset.exe. It is very strange.
Here is my log file:
aspnet_wp!library!6!03/11/2008-15:20:36:: i INFO: Exception dumped to: c:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson aspnet_wp!library!6!03/11/2008-15:20:47:: Call to GetPermissionsAction(/). aspnet_wp!library!1!03/11/2008-15:20:47:: Call to GetPropertiesAction(/, PathBased). aspnet_wp!library!6!03/11/2008-15:20:48:: Call to GetSystemPermissionsAction(). aspnet_wp!library!1!03/11/2008-15:20:48:: Call to ListChildrenAction(/, False). aspnet_wp!library!6!03/11/2008-15:20:49:: Call to GetSystemPropertiesAction(). aspnet_wp!library!1!03/11/2008-15:20:49:: Call to GetSystemPropertiesAction(). aspnet_wp!library!a!03/11/2008-15:20:53:: Call to GetPermissionsAction(/Sentrack). aspnet_wp!library!1!03/11/2008-15:20:53:: Call to GetPropertiesAction(/Sentrack, PathBased). aspnet_wp!library!a!03/11/2008-15:20:54:: Call to GetSystemPermissionsAction(). aspnet_wp!library!1!03/11/2008-15:20:54:: Call to ListChildrenAction(/Sentrack, False). aspnet_wp!library!a!03/11/2008-15:20:56:: Call to GetSystemPropertiesAction(). aspnet_wp!library!1!03/11/2008-15:20:56:: Call to GetSystemPropertiesAction(). aspnet_wp!library!6!03/11/2008-15:20:58:: Call to GetPermissionsAction(/Sentrack/Shared Reports). aspnet_wp!library!1!03/11/2008-15:20:59:: Call to GetPropertiesAction(/Sentrack/Shared Reports, PathBased). aspnet_wp!library!6!03/11/2008-15:20:59:: Call to GetSystemPermissionsAction(). aspnet_wp!library!1!03/11/2008-15:20:59:: Call to ListChildrenAction(/Sentrack/Shared Reports, False). aspnet_wp!library!6!03/11/2008-15:21:00:: Call to GetSystemPropertiesAction(). aspnet_wp!library!1!03/11/2008-15:21:00:: Call to GetSystemPropertiesAction(). aspnet_wp!library!a!03/11/2008-15:21:04:: Call to GetPermissionsAction(/Sentrack/Shared Reports/Report). aspnet_wp!library!1!03/11/2008-15:21:04:: Call to GetSystemPropertiesAction(). aspnet_wp!library!a!03/11/2008-15:21:05:: Call to GetPropertiesAction(/Sentrack/Shared Reports/Report, PathBased). aspnet_wp!library!1!03/11/2008-15:21:05:: Call to GetSystemPermissionsAction(). aspnet_wp!library!a!03/11/2008-15:21:06:: Call to GetPropertiesAction(/Sentrack/Shared Reports/Report, PathBased). aspnet_wp!library!1!03/11/2008-15:21:06:: Call to ListEventsAction(). aspnet_wp!library!1!03/11/2008-15:21:07:: Unhandled exception was caught: System.Web.HttpException: File does not exist. at System.Web.StaticFileHandler.ProcessRequestInternal(HttpContext context) at System.Web.DefaultHttpHandler.BeginProcessRequest(HttpContext context, AsyncCallback callback, Object state) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) aspnet_wp!library!1!03/11/2008-15:21:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Web.HttpException: File does not exist. at System.Web.StaticFileHandler.ProcessRequestInternal(HttpContext context) at System.Web.DefaultHttpHandler.BeginProcessRequest(HttpContext context, AsyncCallback callback, Object state) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) --- End of inner exception stack trace ---
Hi,I'm new at asp .net and have a problem. I have a page where the logged in user can add another user to his list of friends. To do this, I have a page where all the users are listed by using a datalist, and I have a form with an invisible field which passes the value of <%#DataBinder.Eval(Container.DataItem, "UserName")%> in post when the user clicks on "add as a friend" so that in the next page I can get that value and add it as well as the username of the logged in user to the table friendships. This is not working, can anyone help please? The code of the page with the datalist is:<script language="VB" runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) ' Session("username") = User.Identity.Name Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() End Sub Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select u.*,f.buddyId as userNameIfFriend from aspnet_Users u left outer join aspnet_friendship f ON u.UserId=f.buddyId AND f.userId=@Param1 where IsAnonymous='False' and u.UserId<>@Param1", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser) DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: <form id="teste" name="teste" method="post" action="add_buddy.aspx"> <input name="UserId" type="hidden" id="UserId" value="<%#DataBinder.Eval(Container.DataItem, "UserName")%>" /> </form> <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<a href='add_buddy.aspx'>Add as buddy</a> ", "Already buddy")%> </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList></body></asp:Content>The code of the page where the user is redirected to add a friend and where I want to make the insert is: <script runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() End Sub Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() ' Declaring variables Dim UserId As String ' A Function to check if some field entered by user is empty ' Receiving values from Form UserId = (Request.Form("UserId")) Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("insert @Param2, @Param1 into aspnet_friendship.userId, aspnet_friendship.buddyId", MyConnection) MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand.SelectCommand.Parameters.AddWithValue("@Param2", UserId) ' Creating Connection Object and opening the database Dim DS As DataSet DS = New DataSet() MyCommand.Fill(DS, "aspnet_friendship") ' Done. Close the connection End Sub </script> I am not sure if sending the information in a form in post to another page and executing the query there is the best option or if I can do it all within the if statement. I appreciate any help, since I am having difficulties in solving this.Thank you.
using a query or vba ,transfer data from one form to another, table1 customers-form1 customer address, table2 orders-form2 order address, how do i transfer data from customer address to order address yours M [/center]
I am researching ideas for the best way to have users enter data into SQL.
I am familiar with VB and Access Forms and HTML coding but what I am looking for is a way to have a standalone app or website where users can enter information but I want the form to have branches that change depending on user input (such as selecting a meeting type then having specific questions open up related to it).
Any ideas on the best way to do this?
If a different forum would be better for this request please advise and I will move appropriately.
I have a table that is increasing quite largely each day. By now, I have average 300 million of records over 2.5 years. Before we received our new interface, the data we received was aggregated and thus not that big.The problem is that the table is so huge that I cannot use the Slowly Changing Component. I was thinking about making a temp table where I load the incremental data before I load it into the final data mart table.Based on this temporary table I use a script to compare the temp data with the already existing data in the data mart. However, this requires a compare of each records (300 mil records).
I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..
May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..
I have a flat file that I want to merge with table in SQL server 2005.
1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).
Now here is my question. I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).
I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..
Here is an example: Table A -------------- 1 test test1 test2 test3 test4 test5 2 test test6 test7 test8 test9 test10
Table X ------------ 1 test test1 test2 test99 test4 test5 2 test test98 test97 test 96 test95 test94 -------------------------------------------------------- Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1.. The new Table A should look like
NEW Table A' -----------------
test test1 test2 test3 test4 test5 test test6 test7 test8 test9 test10 test test98 test97 test 96 test95 test94
------------------------------------ I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?
I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.
Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -
UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations) FROM tblColumnNames
hi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this: experienceFirst job.textboxSecondjob.textboxThirdjob.textbox i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ....which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @@identity to get the ID from my first table(personel)which is autogenerate number. It works fine with Firstjob table but not secondjob table. Generated error message: (1 row(s) affected) (1 row(s) affected) Msg 547, Level 16, State 0, Line 6 The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'. The statement has been terminated. INSERT INTO dbo.Personnel (Name) VALUES ('Jon') insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel) values('Teachier, 'Gam', '7thn', scope_identity()) insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values('Musician', 'KKSB', '8thn', scope_identity()) any suggestions....
Hello. As the subject heading says, I'm not able to insert data typed into the contact form on my page into a database table. I'm using an SqlDataSource object. Here's the code for this page:
I'm trying to insert data into a table from two sources (a table and form controls) at the same time. --this data from the tradeitem table and is inserted into the selections table --based on the variable @builderid --no problem it works fine as is INSERT INTO Selections ([TradeItemID], [TradeID], [ProductName], [OrigSalesPrice], [RevSalesPrice]) SELECT [TradeItemID], [TradeID], [TradeItem], [Price], [Price] FROM tradeitems WHERE BuilderID = '1' However, I need these also to pull from the form controls --These variables will pull from form controls and need to be associated --with each record added to the seleciton table INSERT INTO Selections ([UserDataID] = '1', [DefaultQty] = '0') --How do I get these combined into one statement so I get the following result?
Hey all, I have a form in one page and when the user clicks the submit button it has to save the data into my SQL database which i have created.It doesnt show any error and it successfully redirects to another page but not saving the data .Could someone help please.Here is my code under submit button SqlDataSource txtDataSource = new SqlDataSource(); txtDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString(); txtDataSource.InsertCommandType = SqlDataSourceCommandType.Text; txtDataSource.InsertCommand = "INSERT INTO Hamburgdata(user_name, report_type, company_name , street_address, city, state, zip_code, tax_ID) VALUES (@user_name,@report_type,@company_name ,@street_address,@city,@state,@zip_code,@Tax_ID)"; txtDataSource.InsertParameters.Add("user_name", User.Identity.Name); txtDataSource.InsertParameters.Add("report_type", ReportType.Text); txtDataSource.InsertParameters.Add("company_name", CompanyName.Text); txtDataSource.InsertParameters.Add("street_address", StreetAddress.Text); txtDataSource.InsertParameters.Add("city", City.Text); txtDataSource.InsertParameters.Add("state", State.Text); txtDataSource.InsertParameters.Add("zip_code", ZipCode.Text); txtDataSource.InsertParameters.Add("tax_ID", TaxID.Text); int rowAffected = 0; try { rowAffected = txtDataSource.Insert(); } catch (Exception exp) { } finally { txtDataSource = null; } } ThanksVik
I want to display the data in datagrid using the stored procedure,
Can you please tell me, how i can create the stored procedure for the following: using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)
I want to display the stored procedure data in my Datagrid.
Thank you very much for the help. the following is complete inline code on my webform.
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection")) Dim objCmd As New SqlCommand Dim dataAdapter As SqlDataAdapter
objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
objConn.Open()
dataAdapter = New SqlDataAdapter dataAdapter.TableMappings.Add("Table", "Orders") dataAdapter.SelectCommand = objCmd
dataSet = New DataSet("Orders") dataAdapter.Fill(dataSet) dtgOrders.DataSource = dataSet dtgOrders.DataBind()
Hi I am trying to develop a web based application. I am trying to insert data from my web form into sql server database using stored procedures. I am having a problem while i try to exceute the command. The error given by system is System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteNonQueryany suggestion what am i missing?With Regards,Sameer Jindal
Hi friends, I,m familiar with accessing data from a SQL DB using ASP. Lets say I have a cinema website with lots of info about upcoming shows, well, how do I export data form the database to a third party?? Lets say a local newspaper wants the show-time info. How do I get the data to them. Also how are different files .xls etc,exported? Thanks for any help. Meltdown ///// ~ ~ @ @ < \__/
