Inserting Formulas In Tables
Dec 9, 2005Sorry, I made a mistake. How can I delete this message?
View 3 Replies
ADVERTISEMENT
Inserting Data Into Two Tables (Getting ID From Table 1 And Inserting Into Table 2)
Oct 10, 2007I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
Inserting Into Two Tables?
Feb 24, 2006I just got started with ASP.NET and SQL Express a few days ago. I have a little bit of SQL (MySQL) experience, but nothing professional. I'm trying to build a toy application just as a refresher for SQL and to learn a few more things.Anyway, I've got two tables:Users:user_id, user_nameRegistration:reg_id, user_id, fname, lname, passwdNow, when someone new registers on the site, I need to update both tables. When I add a user to the Users table, the user_id is autoincremented. I'd like to get that user_id, somehow, so that I can then update the registration table with the correct user_id.What would be the "best" way to do this? SQL query? Stored procedure? Trigger?
View 2 Replies View RelatedInserting 2 Tables With Pk/Fk
Oct 28, 2006Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.
Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)
I'll be executing SQL statements from my service - INSERTs, etc...
What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.
Is there a way I can insert into both tables with one SQL statement?
Thanks!! Curt.
Formulas & Precision
Sep 21, 2006I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
Thanks.
Column Formulas
Jul 20, 2005Looking for a decent source that has some examples of column formulas aswell as a list of column formula functions that can be used and how to usethem.Hard time finding something online. Any suggestions or references would begreatly appreciated.Fairly new to this.Thanks
View 1 Replies View RelatedFormulas In RS Like In Crystal
Jan 24, 2008ive come from a crystal background and im having difficulty writing reports.
An example of the problem im having is with saved formulas (crystal calls them formulas)
eg
i would create a forumula called Difference
it would be something like this
=DateDiff("d",Field1, Field2) save that and then it was stored
then in a text box i would reference it like so.
=iif( Difference <= 23,"Too New","Just Right")
is this possible in RS??? as i have reports i need to convert from crystal to RS, and the iif statements are becoming huge, especially when you reference the same formula in the expression multiple times.
bit long winded i know, and i hope this makes sense, but i am witts end with this issue
thanks in advance
Jonny
Custom Formulas
Sep 4, 2007im moving over from a crystal background, in crystal i was able to create custom formulaes and use them somewhere in the report, can this be done in RS, if so how?
thanks
Jonny
Inserting Into Two Differnt Tables
Sep 21, 2007Hi, I have two different tables that I want to insert into, i have the code (as below) my only problem is i want to insert a qty into tbl_multi and a different qty into tbl_stock, how do i do this. I have been comfusing myself with it all afternoon, maybe someone can point it out for me int i = 0;
int scount = 0;for (i = 1; i <= 5; i++)
{if (((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text != "")
{string sConnMAXID = "Data Source=sql380.mysite4now.com;Initial Catalog=nashdfDB1;User ID=******;Password=******";
SqlConnection objConnOID = new SqlConnection(sConnMAXID);int intStockIDMAX = (Convert.ToInt32(Session["intMAXSID"])) + 1;
int intQtyP1 = Convert.ToInt32(((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text);int intLocationP1 = Convert.ToInt32(((DropDownList)FormView1.FindControl("ddlP1_" + i.ToString())).SelectedValue);
scount = intQtyP1 + scount;using (objConnOID)
{
objConnOID.Open();string sqlInsert = "INSERT INTO tbl_stock_multi_location(stock_ID, qty, location) " + "VALUES (@stock_ID, @qty, @location);"; <<<<<<<<<<<<<<<< need to try and add the SQL onto the end here
//INSERT INTO tbl_stock(qty)" +// "SELECT qty AS SQTY FROM tbl_stock VALUES (@SQTY);";
SqlCommand objCmd1 = new SqlCommand(sqlInsert, objConnOID);
objCmd1.Parameters.AddWithValue("@stock_ID", intStockIDMAX);
objCmd1.Parameters.AddWithValue("@qty", intQtyP1);objCmd1.Parameters.AddWithValue("@location", intLocationP1);
// objCmd1.Parameters.AddWithValue("@tbl_stock.qty", scount); <<<<<<< this was the param that i was playing about with
// either will work
// objCmd1.ExecuteNonQuery();
objCmd1.ExecuteScalar();
objConnOID.Close();
Inserting In Multiple Tables
Feb 22, 2004i have 3 tables i want to insert data in, customer,gaurdian,customergaurdian
where the primary key in customer is Cust_id ,in gaurdian GD_id, in customer gaurdfian Cust_id & GD_id which are forgien keys from the opther 2 tables how can i preform the insertion in the three tables taking into consideration that all insertions must be commited and non will be ignored i.e. all the 3 will take place !!!
customer ----link------customergaurdian-----link-----gaurdian
is there some kind of statement or something that gaurantee that insertion will take place in this form?
Inserting Into Related Tables
Mar 24, 2004Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
Inserting Into 3 Tables With One SPROC
Apr 19, 2000I have a shopping cart app in ASP. I don't want to upload to the DB until the credit card goes through. Once the credit card goes through, I update my tables: Shipping(info), Orders, and Billing(info).
I'm not done yet though. I have to loop though the shopping cart and pass the paramters to an SPROC each time through the loop. If I have three items in the cart, then I need to call the SPROC three times.(let me know if there is a better way to do this)
So I am thinking I will need 2 SPROC's, one for the first three tables, and one for the OrderDetail table. My concern is grabbing the proper foreign key from the Order table. If I use SELECT MAX(id), I may get the wrong ID if someone elses has inserted another row before I grab the proper ID.
I need to figure out how use my second SPROC to grab the proper ID from the Order table, and then insert that record into the OrderDetail table.(Am I being paranoid about another record being inserted? I think this could happen between the execution of my first SPROC and the second one.)
Thanks all
Inserting Into Two Tables With One Sql Statement
Jul 20, 2005Anyone who can help,I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fieldsK2 and F1. F1 is the foreign key relating to K1 and F2 is the foreignkey relating to K2.My initial question has been how can I add a row to both tables withaccurate Fx values in one SQL INSERT statement? I have been told itcannot be done.If it can be done in one statement, what is that statement?If it cannot be done in one statement, how can it be done in the leastnumber of statements?Thanks,Greg the SQL student
View 4 Replies View RelatedHelp - Formulas With Functions Across Databases
Apr 28, 2004I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?
---- Jake
Formulas For Column Properties
Jun 25, 2006I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.
Thanks
Creating Excel Formulas In SRS
Jan 16, 2008I'm creating a report that is designed to be exported to Excel so that the end user can manipulate the data. There are two main columns that I'm concerned with - TimeTaken and OTTimeTaken (for overtime).
Our application does not track OTTimeTaken so it's default will just be 0, but I need the excel file to have a formula that automatically adds the TimeTaken to the OTTimeTaken in a third cell for the total number of hours as that value will be added by the A/R department for invoicing.
I thought adding this would give me what i need:
="=SUM(C" & RowNumber(Nothing) & ": D" & RowNumber(Nothing) & ")"
when exporting to excel, it dumps it in as plain text, requiring that the user double click on the cell in order to "activate" the formula.
Is there a way to have SRS create this formula so that it works in excel rather than requiring the additional user interaction?
Thanks!
Report Builder Formulas
Dec 27, 2007Trying to get the blank out of my report and replace with (0). My formula is SUM(Loan Amount). If there is nothing to sum there is a blank space. Would like it to have a (0) in it.
View 1 Replies View RelatedInserting Data In Multiple Tables
Jun 22, 2006i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it?
i think; it should be implemented through transaction statements but not much aware about these
Inserting Into Tables Using Stored Procedure
Dec 7, 2007I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
Inserting Values Into Multiple Tables
Jan 25, 2008Hi All, am new to sql server in my application I am having one Asp.net web page, the user has to enter values for 5 fields(Empno,Empname,salary,deptno,deptname) in that web page and for that i created two tables in sql serverEmp Table- empno(primary key),empname,salaryDept Table- Deptno(primary key),empno(Foreign key ),Deptname(with some check constraint.) After the user enter all the values in the Asp.net web page now i want to store data into database for that i wrote the following stored procedure...create procedure usp_EmpDept @empno integer, @empname varchar(15), @salary money, @deptno integer, @deptname varchar(10) As Insert into emp(empno,empname,salary)values(@empno,@empname,@salary) Insert into dept(deptno,Empno,deptname)values(@deptno,@empno,@deptname) but the problem is whenever some constaint violation for eg. if some check constraint violation in Dept table its inserting the values in the Emp table only, but my requirement is, It must enter into both tables if there is no constaraint violation otherwise it has to ignore both the tables. And also please suggest is there any better way to insert values into two tables other than using the stored procedure Any help will be greatly appreciated..Thanks,Vision..
View 6 Replies View RelatedSelect And Then Inserting Results Into Two Different Tables
Dec 2, 2003Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?
Inserting Rows For Multiple Tables?
Sep 3, 2013Say for instance I got 2 tables
Subject Table and a Student Table
The Subject Table consist of the following attributes:
Subject_ID [PK], Subject_Name, Course_ID and Course_Name
The Student Table consists of the following attributes:
Subject ID [FK], Students_Name, Students_bday, Students_age, Students_height and Students_weight
How can I use the INSERT function when I would like to add a row with the following details:
Course_Name : Biotechnology
Students_Name : Fred
Students_bday : 01/JAN/1990
Stundets_age : 54
how to use the INSERT function for multiple tables.
Inserting Into Two Tables And Transaction Problem
Jun 30, 2006CREATE PROCEDURE SimpleInsert (@custname custname_type,@carname carname_type)ASBEGIN TRANSACTIONINSERT Customers (CustName) VALUES (@custname)IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 ENDDECLARE @custid intSELECT @custid = scope_identity()INSERT Cars (ID, CarName) VALUES (@custid, @carname)IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 ENDCOMMIT TRANSACTIONIF no error this works but to test transaction, I chanded the tablename of second insert to 'car' in which doesn't exist and this erroroccured (calling sp by ASP .NET page):Invalid object name 'car'. Transaction count after EXECUTE indicatesthat a COMMIT or ROLLBACK TRANSACTION statement is missing. Previouscount = 0, current count = 1.ThanksHabib
View 2 Replies View RelatedInserting Data Into Tables Having Triggers...
Jul 20, 2005When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
View 1 Replies View RelatedHow To Restrict Tables From Inserting Duplicates
Nov 26, 2007hi all
I have been posting in VB.NET forum ,but i am new to this forum.
I am working on an application using vast amount of data . So we get data in flatfiles. We used to get data for two or three months in more than three or four flat files .Some time we try to load the same files more than once .So it leads to duplicates in tables
I heard that there is some settings in table , so that it will not allow the same data once it is imported .
if anybody knows about this please help me
thanks & regards
Problem Inserting Data Into Onw Of My Tables
May 14, 2007
i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2
My Problem:
It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.
Total Specification Requirements:
i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.
Any Help would be graetlty appreciated
Kind Regards
Carel Greaves
Inserting Multiple Records In Different Tables
Apr 10, 2007i wants to insert fields of one form in more than one table using stored procedure with insert query,but i gets error regarding foreign key
View 3 Replies View RelatedDynamic Formulas Driven By Table
Jul 23, 2005I'm working with a DB design that seems to me to be rather complex.This is a very slimmed down version of what I'm doing, but I believe itis enough to get my question resolved.Here is my layout.These 4 tables are used to generate a questionaire.Survey OrderID========= ==========SurveyID OrderIDOrderID QuestionGrpIDQGrp Questions============= =============QuestionGrpID QuestionIDQuestionID QuestionTextThe following two tables are used to calculate a report that is sent tothe customer.RawData=========================OrderIDQuestionIDValue is string but is Cast as decimal for numeric formulasMetrics==============================================QuestionGroupID | ReportText | Formula | MetID==============================================2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1The OP references are questionIDsNow to calculate the result for the report we programatically parse theformula creating a temp table (table name = Temp & orderID & _ &QuestionID) with OrderID and OPxx as the field names. We create onetable for each question.We then use dynamic SQL again to calculate the result using the aboveformulaSELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERETemp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =Temp5_23.OrderIDThis select is used to create a single table of calculated values.This table is in turn used to tell the customer how they compare toother customers. Percentile, Mean, Median, Std Dev, and a few others. Idon't claim this part of the project, but I'm not sure how I might havedone it, had it been assigned to me.MY PROBLEM!!!Sometimes a 0 is valid data and is the denominator of a devisioncalculation. Since this is so dynamic and it might be difficult todetermine when division is used. I need a way to default divide by 0execptions to NULL. This DB is on a hosted server.Thanks for bearing with me,Greg Kelley
View 2 Replies View RelatedCreating Mathematical Formulas And Calculations
Aug 5, 2007I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2
4
3
6
0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
Any help will be appreciated.
How To Handle NULL Dates In Formulas?
Mar 19, 2008Re: SQL Server Reporting Services
I have welders who have active dates and inactive dates, and I need to create an "isactive" column in the report. My formula will be pretty obvious to most of you. However, if the user does not input an inactive date, that means none has been given and therefore the welder is currently active. The problem is that there seems to be no way to check if inactive date is null or not in the formula.
Please help! Thanks!
Export To Excel - Rare Bug With Formulas?
Apr 27, 2007Hi,
I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.
Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:
Code Snippet
=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value
This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.
99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...
I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.
Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;
- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)
- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)
Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.
And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.
Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.
*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...
Thanks in advance for any advice
Inserting Data Into Multiple Tables In 1 Transaction
Feb 7, 2007Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
View 5 Replies View RelatedInserting Simultaneous Tables Using Stored Procedure
Feb 8, 2007I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are:
tbltest
ECID – int (PK)