Using Row Data To Create New Columns

i have been trying to create new columns in a tabinvoice details using row data from another table and additional input from another form. Problem is i get no sql errors m database conection is ok usually works but doesn't write any new columns or values to the tblinvoicedetails here is the code i am using i hope someone ca tell me where i am going wrong.



<table width="100%" cellspacing="0" cellpadding="2" border="0">
<form name="form2" method="post" action="index.php" onsubmit="return v.exec()">
<?
$cid = mysql_real_escape_string($_GET['cid']);
$query = "select * from tblcustomerdetails where intCustomerID = '$cid'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$cid = $row["intCustomerID"];
?>
<tr>
<td><?print_r( $row['vcCCompanyName'])?></td><td align="left">Tel: <?print_r( $row['vcCDPhone'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCStreetNo'])?> <?print_r( $row['vcCStreet'])?></td><td align="left">Fax: <?print_r( $row['vcCFaxNo'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCCity'])?></td><td align="left">Email: <?print_r( $row['vcCEmail'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCState'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCPostalCode'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCCountry'])?></td><td> </td>
</tr>
</table>
<br />
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<tr>
<td> </td><td><b>Code</b></td><td><b>Name</b></td><td><b>Description</b></td><td><b>Price</b></td><td colspan="2" align="center"><b>Action</b></td>
</tr>
<?
$accountsql="select * from tblpricedetails where intCustomerID=$cid order by intProductID asc";
$tempdata=$DB_site->query($accountsql);
$count =$DB_site->num_rows($tempdata);
while($rowdata=$DB_site->fetch_array($tempdata))
{
$intPriceID = $row["intPriceID"];
$intProductID = $row["intProductID"];
$vcProductCode = $row["vcProductCode"];
$vcProductName = $row["vcProductName"];
$vcProductDescription = $row["vcProductDescription"];

?>
<tr>
<td class="accountsumb">
<?=$rowdata["intProductID"]?>
</td>
<td class="accountsume">
<?=$rowdata["vcProductCode"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductName"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductDescription"]?>
</td>
<td class="accountsumc">
<?=number_format($rowdata["ftAmount"],2,".",",");?>
</td>
<td class="accountsumc" align="right">
<input type="text" size="10" name="Quantity" />
</td>
<td class="accountsumc" align="center">
<input type="text" size="10" name="total" />
</td>
</tr>
<?
}
?>
<? if($count==0)
{
?>
<tr bgcolor="#EFEFEF" class="smallblack">
<td colspan="7" align="center" bgcolor="#EFEFEF" class="redtext">This user has no product data. </td>
</tr>
<?
$insertdate = mysql_query("INSERT INTO tblinvoicedetails (intProductID,vcProductCode,vcProductName,vcProductDescription,ftAmount) VALUES(".$row['intProductID'].",".$row['vcProductCode'].",".$row['vcProductName'].",".$row['vcProductDescription'].",".$row['ftAmount'].")") or die(mysql_error());

}

?>

<tr>
<td colspan="7" align="center"><br />
<input name="submit" type="submit" class="button" value="Submit" />
</td>
</tr>
<tr>
<td colspan="7" align="center">
<input class="button" type="reset" name="reset" value="Reset" />
</td>
</tr>
</table>
</form>



ADVERTISEMENT

A Word About Meta-data, Pass Through Columns And Derived Columns

Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):

Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.

This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.

Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.

Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.

Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS

View Replies View Related

When Then To Create Mult Columns

I have a colm day and colm sales, I would like to have a column for each day of week i think i can use when then but not sure how

View Replies View Related

Create Array Of Columns

Absolute beginner to SQL here...

I'm try to create an array of columns and then refer to these columns at the asp.net level.

Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.

Thanks in advance.

Larry

View Replies View Related

How To Create Rule Which Update Other Columns

Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev

View Replies View Related

Could Not Create A Foreign Key On 2 Unique Columns

Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.

View Replies View Related

How To Create Dynamic Columns In Sql Database

hi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.

Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3


help is appreciated

View Replies View Related

How To Dynamically Create Columns For A Table

Hi,
how to dynamically create columns for a table

View Replies View Related

Avoid Typing To Create Columns

I need to create table in sql server 2005 database, I have plenty of columns. Well I know how to create them manually. but does any body know or tried before to create columns automatically, or through some batch process. I have column names already typed in TXT file (comma delimited).

all I am trying to avoid that typing work.

any advice.

Thanks

View Replies View Related

Is There A Way To Create Columns From A Row Value In A Select Statement?

 

Hi!
 
I have a select question that look like this:
 

DECLARE @ID uniqueidentifier;

SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';
 

SELECT

rp.ID AS ReportPackId

,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier

,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier

FROM

ReportPack rp

INNER JOIN ReportPackAdditionalParty rpap

ON rpap.fk_ReportPack = rp.ID

WHERE rp.ID = @ID
 
The result a get when running the select question is:








ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2
 
My problem is that the result I want is the following:
 








ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2
 
I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?

View Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View Replies View Related

Create Unique Index On Multiple Columns

Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
 
I dont care about one column having duplicate values, I want to protect against  Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?

View Replies View Related

How To Create A Unique Constraint On Composite Columns

I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.

Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.

Thanks in advance

John.

View Replies View Related

Create Table With Veriable Number Of Columns

 

Hi everyone,
 
I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.
 
How can i create table with variable number of columns?
 
Thanks,
 

Alex

View Replies View Related

Is It Possible To Dynamically Create Columns In A Table In SSRS

 

Hi,
    I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning  the header data  (in a detail column) based on the some feilds in the sproc...  and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
 
So is it possible that show only the columns that have data in  it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
 
any help is appreciated..
Regards
Karen
 
 
 

View Replies View Related

How To Create Dynamic Columns In A Temporary Table

Hi there,
           i have a requirement that a temporary table contains dynamic columns depending on where condition.

my actual table is like





Key
Value


X1
x

X3
x

X5
x

Y1
y

Y2
y
when user select x, the input variable passed to stored proc and the result is shown like

column names
 X1      X3    X5 as column headers.

the select query is from temporary table.

these out put is based on the user selection. so the temporary table created with columns dynamically.

please help me out.
please let me know if you didn't understand.

thanks
Praveen.

View Replies View Related

When To Create Columns And Metadata For Custom Asynchronous Component Output

I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.

This component takes in a name string passed from upstream and parses the name components into standardized output fields.  I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma.  I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.

In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection.  Is this method where this should occur?  It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)

In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail.  I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)

Thanks.

View Replies View Related

Create Output Columns Based On Input In Custom Component

 

I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
 
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
 

View Replies View Related

How Create Multiple Driildowns For All Columns In Table Using Ssrs Report

 

hai, iam new to ssrs please help me,
 
 my report having  10 columns, i can explain my need with example that is,  assume like this  customer id  is first column of my table customer id is 101 it  having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.              
 
i want output like this when clicking + customer id driildwon it display  3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
 
above explanation is only one column of the table, like that iam also displaying this  driiling procedure for remaining different columns.
 
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
 
 
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table

 
if  any body give procedure for creating fromat for  drilldown report which having multiple drilldowns for all columns in a table  is appriciate.
 
 
thanks
 
 
jacks

 

View Replies View Related

Import Data From Excel-Sheet Via OleDb In VB.Net - How To Get A Columns Data As String?

 Hello,

i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:



 



Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next
 
 


it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.

So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?

Thanks for Reading

- Pierre from Berlin

 
[seems i got redirected into the wrong forum, please move into the correct one]

View Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Hello:
 
I am running into an issue with RS2k PDF export.
 
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion.  Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
 
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
 
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
 
Any help or suggestion on this issue would be appreciated

View Replies View Related

Data Flow: Converting Data In Multiple Columns

Hi,

I'm just wondering what's the best approach in Data Flow to convert the following input file format:

Date, Code1, Value1, Code2, Value2

1-Jan-2006, abc1, 20.00, xyz3, 35.00

2-Jan-2006, abc1, 30.00, xyz5, 6.30

into the following output format (to be loaded into a SQL DB):

Date, Code, Value

1-Jan-2006, abc1, 20.00

1-Jan-2006, xyz3, 35.00

2-Jan-2006, abc1, 30.00

2-Jan-2006, xyz5, 6.30

I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.

 

View Replies View Related

Create Query To Average Data & Total/sum Data

Hello,
I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.

I have about 8000 records close to 2000 records for the last 4 years
and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.

Here are my field names:
[year] ***4 choices 2007, 2006, 2005, 2004***
[name]
[rush_no] ***integer***
[rush_net] ***integer***
[YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***

I also need to create the same table that will "total/sum" the same records.

View Replies View Related

Approach Help To Load Data From Flatfiles Into Relational Table Where Data Is Coming As Spaces In Few Columns From Flatfiles

Hi ,
 
My Input is a flat file source and  it has spaces in few columns  in the data . These columns are  linked to another table as a foreign key and  when i try loading them in a relational structure  Foreigh key violation is occuring , is there a standard method to  replace these spaces .
 
what approach should i take so that data gets loaded in a relational structure.
 
 for example
 
Name  Age Salary  Address
 dsds  23               fghghgh
 
Salary  description   level
2345    nnncncn       4
 
 here salary is  used in this example ,  the datatype is char in real scenario 
 
what approach should i take to load the data in with cleansing the spaces in ssis

View Replies View Related

How To Create A Data Warehouse Or Data Mart

i want to create a data mart from an existing OLTP database. for example northwind or i will create an OLTP database. i dont know how i can create data mart from OLTP database. i want to learn that step by step. help me??? please!!

View Replies View Related

Sum Data In Two Columns

Hi All

Please can someone help me
I Have a problem whereby I cannot get the contents of two columns to add together

my code is
update RN_20000718115255CounterParty1set[RN_20000718115255CounterParty1].[19] =
([RN_20000718115255CounterParty1].[19]) + ([RN_20000718115255CounterParty1].[17])
could someone please advise where i have gone wrong

thanks & regards

View Replies View Related

Cant Add New Columns To Data Tables

Hi All,I am having  a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks

View Replies View Related

Testing Columns For Data

Can someone advise me as to the best way to loop through a collection of columns for each row in a table testing the value of each column for nulls etc and then entering the column name into another column where the condition is true..

View Replies View Related

Combine Data Into One Row, Add Columns

Hey guys,

I realize I've posted something like this before, but i'm confused how to do something new, what I am trying to do is combine rows of data when certain columns equal one another.

For example, I have to sets of data who have in common the columns ctp_code and a mod_code (they are the same) however they have two other columns called billing_amt and amount_owed that need to be added together (literally taking the data and doing the math, so 100.00 + 100.00 = 200.00)


Code:

id ctp_code mod_code billing_amt amount_owed
1 1 1 100.00 100.00
2 1 1 100.00 100.00



My results should be this:


Code:

id ctp_code mod_code billing_amt amount_owed
1 1 1 200.00 200.00



Any help, thoughts?

View Replies View Related

Query: Two Columns With Same Data

Hi,
I have 3 tables:
Table SLA: SLA_code, SLA_name
Table industry: Industry_code, Industry_name
Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq

I would like to query to get in one table:
SLA_name for DESTINATION, Industry_name, Freq

Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.

How can I query to get the Freq and Industry for those destination SLAs?

I have make a relationship between:
Table SLA.SLA_code with Table14.SLA_code_destination and
Table_industry.Industry_code with Table14.Industry_code

But I am not sure if just a
SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq
FROM SLA, Industry, Table14
would select the SLA_destination and not the SLA_origin.

Thanks very much in advance,

Maria

View Replies View Related

Data From Different Columns For One Column

Hi,

I have a tabel called "Daily" which has 5 columns, "TesterID", "Activity", "Hours Given", "Hours Used", "Delta".
The data for "TesterID" and "Hours Given" columns are taken from a table called "Tester".
Data for columns "Activity" and "Hours Used" taken from table called ALD.
"Delta" column is the difference between Hours Given and Hours Used.
For "Hours Given" in table "Daily", the data source should change every 12 hours.
For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" .

How to do that? Please Help. Thank you.

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved