devlins
asked on
Proper Sql Statement
What is the proper way to write the statement below for a asp.net web application where I am trying to insert a value into a column based on the value from a differenct column during page load...
sqlC2= "(insert into Waveplate where part_key = part_key set Calculate = Retardation/(Tx02.Text *DL.Text)";
sqlC2= "(insert into Waveplate where part_key = part_key set Calculate = Retardation/(Tx02.Text *DL.Text)";
you can use parameter:
INSERT INTO Waveplate (Calculate) VALUES (Retardation/(Tx02.Text *DL.Text)) WHERE part_key = part_key
INSERT INTO Waveplate (Calculate) VALUES (Retardation/(Tx02.Text *DL.Text)) WHERE part_key = part_key
>>I am trying to insert a value into a column based on the value from a differenct column during page load...
UPDATE Waveplate SET Calculate = Retardation/(Tx02.Text *DL.Text) WHERE part_key = <part_key>
if part_key is non string type then enclose <part_key> with single quotes like 'PART1'
cheers
UPDATE Waveplate SET Calculate = Retardation/(Tx02.Text *DL.Text) WHERE part_key = <part_key>
if part_key is non string type then enclose <part_key> with single quotes like 'PART1'
cheers
oops
>>if part_key is non string type then enclose <part_key> with single quotes like 'PART1'
I meant
if part_key is string type then enclose <part_key> with single quotes like 'PART1'
cheers
>>if part_key is non string type then enclose <part_key> with single quotes like 'PART1'
I meant
if part_key is string type then enclose <part_key> with single quotes like 'PART1'
cheers
pseudo code
private double CalculatedValue
{
get
{
double first = double.parse(box.Text);
double second = double.parse(list.Selected Value);
return Convert.ToDouble(retardati on/ (first * second));
}
}
using OleDbCommand
=================
cmd.CommandText = "INSERT INTO WavePlate (part_key,calculate) VALUES(?,?)";
cmd.Parameters.Add("part_k ey",part_k ey);
cmd.Parameters.Add("calcul ate",this. Calculated Value);
using SqlCommand
===============
cmd.CommandText = "INSERT WavePlate (part_key,calculate) VALUES(@part_key,@calculat e)";
cmd.Parameter.Add("@part_k ey",part_k ey);
cmd.Parameter.Add("@calcul ate",this. Calculated Value);
basic insert statement
-------------------------
INSERT INTO <TABLE_NAME>
(<COLUMN_1>,<COLUMN_2> ....)
VALUES (<value1>,<value2> ...)
another insert statement
==================
another insert statement
INSERT INTO <TABLE_NAME>
(COLUMN_1>,<COLUMN_2>...)
SELECT
ITEM AS <COLUMN1>,
YADDA AS <COLUMN2>
FROM
<OTHER_TABLE>
WHERE
<...CONDITIONALITY>
private double CalculatedValue
{
get
{
double first = double.parse(box.Text);
double second = double.parse(list.Selected
return Convert.ToDouble(retardati
}
}
using OleDbCommand
=================
cmd.CommandText = "INSERT INTO WavePlate (part_key,calculate) VALUES(?,?)";
cmd.Parameters.Add("part_k
cmd.Parameters.Add("calcul
using SqlCommand
===============
cmd.CommandText = "INSERT WavePlate (part_key,calculate) VALUES(@part_key,@calculat
cmd.Parameter.Add("@part_k
cmd.Parameter.Add("@calcul
basic insert statement
-------------------------
INSERT INTO <TABLE_NAME>
(<COLUMN_1>,<COLUMN_2> ....)
VALUES (<value1>,<value2> ...)
another insert statement
==================
another insert statement
INSERT INTO <TABLE_NAME>
(COLUMN_1>,<COLUMN_2>...)
SELECT
ITEM AS <COLUMN1>,
YADDA AS <COLUMN2>
FROM
<OTHER_TABLE>
WHERE
<...CONDITIONALITY>
ASKER
Maybe I worded this wrong, I want to return a query search and then dynamically populate a column with calculated
values from the Retardation column,
Example:
column Calculate = Retardation *(Tx02.Text /DL.Text ).........
values from the Retardation column,
Example:
column Calculate = Retardation *(Tx02.Text /DL.Text ).........
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am getting an error operator "/" cannot be applied to operand of type string and string; from the
Calculate = int.Parse(drow["Retardatio n"].ToStri ng()) * (Tx02.Text/DL.Text) statement...
What would cause that???
Calculate = int.Parse(drow["Retardatio
What would cause that???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now it gives me the cannot convert datacolumn to type 'double' error is there any way to correct it?
Convert all variables involved in the calculation into double
Calculate = int.Parse(drow["Retardatio n"].ToStri ng()) * (Tx02.Text/DL.Text)
should be as
double dTx02 = Convert.ToDouble(Tx02.Text );
double dDL = Convert.ToDouble(DL.Text);
double dRetardation = Convert.ToDouble(drow["Ret ardation"] .ToString( ));
double dCalculate = dRetardation * (dTx02 / dDL );
int iCalculate = Convert.ToDouble(dCalculat e );
i have given step by step for easier undersanding- all steps may not be necessary
cheers
Calculate = int.Parse(drow["Retardatio
should be as
double dTx02 = Convert.ToDouble(Tx02.Text
double dDL = Convert.ToDouble(DL.Text);
double dRetardation = Convert.ToDouble(drow["Ret
double dCalculate = dRetardation * (dTx02 / dDL );
int iCalculate = Convert.ToDouble(dCalculat
i have given step by step for easier undersanding- all steps may not be necessary
cheers
You can set DataRow["Calculate"] = dRetardation * (dTx02 / dDL );
ASKER
I attached the datagrid code and the code behind to see if this is being done correctly..
The only problem is that it is not populating the Calculate column with the computed solution......
or the defualt value............
<asp:datagrid id=DGrid runat="server" Width="896px" AllowSorting="True" PageSize="250" Height="431px" OnEditCommand="Edit_Click"
<FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></Foot
<SelectedItemStyle Font-Bold="True" ForeColor="Silver" BackColor="#738A9C"></Sele
<EditItemStyle Font-Bold="True" BackColor="Gray"></EditIte
<AlternatingItemStyle BackColor="#F7F7F7"></Alte
<ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></Item
<HeaderStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#5283B5"></Head
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Reserve"></asp:E
<asp:BoundColumn DataField="Inventory_Statu
<asp:BoundColumn DataField="Part_Key" SortExpression="Part_Key" ReadOnly="True" HeaderText="Part_Key"></as
<asp:BoundColumn DataField="Diameter" SortExpression="Diameter" ReadOnly="True" HeaderText="Diameter"></as
<asp:BoundColumn DataField="Retardation" SortExpression="Retardatio
<asp:BoundColumn DataField="Retardation_Dec
<asp:BoundColumn DataField="Reserve_Custome
<asp:TemplateColumn SortExpression="Reserve_Us
<ItemTemplate>
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList runat="server" SelectedIndex='<%#GetSales
<asp:ListItem>ROBERTD</asp
<asp:ListItem>EMA</asp:Lis
<asp:ListItem>PHILD</asp:L
<asp:ListItem>SYSADM</asp:
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn SortExpression="Calculate"
</Columns>
<PagerStyle NextPageText="NEXT" BorderColor="Gold" PrevPageText="PREVIOUS" HorizontalAlign="Right"
ForeColor="#5283B5" Position="TopAndBottom" BackColor="CornflowerBlue"
</asp:datagrid></DIV
++++++++++++++++++++++++++
++++++++++++++++++++++++++
Conn.Open();
string sqlC = "(select top 200 * from Main_Waveplate_Inv where ((Retardation between " + E0.Text + " and " + E.Text + ") or (Retardation between " + E.Text + " and " + E0.Text + ")) and Diameter = '" + D.SelectedItem.Text + "') order by Retardation desc" ;
Eq1.Text + " and Diameter = '" + Diameter.Text + "')";
Waveplate.Retardation/(Tx0
DataA = new System.Data.SqlClient.SqlD
DataTable DTable = new DataTable();
Retardation)(@Eq1,@Eq2)",C
double d3 = double.Parse(DL.Text);
DataRow IRow;
IRow = DTable.NewRow();
DataColumn Calculate = new DataColumn();
Calculate.DefaultValue=0;
DataS.Tables[0].Columns.Ad
int i1 = (int)(d1*d2/d3);
foreach(DataRow dRow in DataS.Tables[0].Rows)
{
dRow["Calculted"]=(int)( double.Parse(IRow["Retarda
}
//Calculate = i1.ToString();
DataA.Fill(DataS, "Main_Waveplate_Inv");
DGrid.DataSource = DataS;
DGrid.DataMember = "Main_Waveplate_Inv";
DataS.Tables.Add();
DGrid.DataBind();
Conn.Close();
ASKER