Link to home
Start Free TrialLog in
Avatar of devlins
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)";
Avatar of devlins
devlins

ASKER

string  sqlC2= "(select * from 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
>>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
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
pseudo code
private double CalculatedValue
{
      get
      {
            double first = double.parse(box.Text);
            double second = double.parse(list.SelectedValue);
            return Convert.ToDouble(retardation/ (first * second));
      }
}

using OleDbCommand
=================
cmd.CommandText = "INSERT INTO WavePlate (part_key,calculate) VALUES(?,?)";
cmd.Parameters.Add("part_key",part_key);
cmd.Parameters.Add("calculate",this.CalculatedValue);

using SqlCommand
===============
cmd.CommandText = "INSERT WavePlate (part_key,calculate) VALUES(@part_key,@calculate)";
cmd.Parameter.Add("@part_key",part_key);
cmd.Parameter.Add("@calculate",this.CalculatedValue);


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>
   
   

Avatar of devlins

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 ).........



SOLUTION
Avatar of dungla
dungla
Flag of Viet Nam image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of devlins

ASKER

I am getting an error operator  "/" cannot be applied to operand of  type string and string; from the

Calculate = int.Parse(drow["Retardation"].ToString()) * (Tx02.Text/DL.Text) statement...


What would cause that???
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of devlins

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["Retardation"].ToString()) * (Tx02.Text/DL.Text)
should be as

double dTx02 = Convert.ToDouble(Tx02.Text);
double dDL = Convert.ToDouble(DL.Text);
double dRetardation = Convert.ToDouble(drow["Retardation"].ToString());

double dCalculate = dRetardation  * (dTx02 / dDL  );

int iCalculate = Convert.ToDouble(dCalculate );

i have given step by step for easier undersanding- all steps may not be necessary

cheers
You can set DataRow["Calculate"] = dRetardation  * (dTx02 / dDL  );
Avatar of devlins

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" OnCancelCommand="CancelIt" OnUpdateCommand="OnUpdate_Click" DataMember="Main_Waveplate_Inv" DataSource="<%# DataS %>" AutoGenerateColumns="False" BorderColor="#DEBA84" BorderStyle="None" CellSpacing="2" BorderWidth="1px" BackColor="#DEBA84" CellPadding="3" DataKeyField="Part_Key">
                                             <FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
                                             <SelectedItemStyle Font-Bold="True" ForeColor="Silver" BackColor="#738A9C"></SelectedItemStyle>
                                             <EditItemStyle Font-Bold="True" BackColor="Gray"></EditItemStyle>
                                             <AlternatingItemStyle BackColor="#F7F7F7"></AlternatingItemStyle>
                                             <ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
                                             <HeaderStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#5283B5"></HeaderStyle>
                                             <Columns>
                                                  <asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Reserve"></asp:EditCommandColumn>
                                                  <asp:BoundColumn DataField="Inventory_Status" SortExpression="Inventory_Status" HeaderText="Inventory_Status"></asp:BoundColumn>
                                                  <asp:BoundColumn DataField="Part_Key" SortExpression="Part_Key" ReadOnly="True" HeaderText="Part_Key"></asp:BoundColumn>
                                                  <asp:BoundColumn DataField="Diameter" SortExpression="Diameter" ReadOnly="True" HeaderText="Diameter"></asp:BoundColumn>
                                                  <asp:BoundColumn DataField="Retardation" SortExpression="Retardation" ReadOnly="True" HeaderText="Retardation"></asp:BoundColumn>
                                                  <asp:BoundColumn DataField="Retardation_Dec" SortExpression="Part_Key" ReadOnly="True" HeaderText="Retardation_Dec"></asp:BoundColumn>
                                                  <asp:BoundColumn DataField="Reserve_Customer" SortExpression="Reserve_Customer" HeaderText="Reserve_Customer"></asp:BoundColumn>
                                                  <asp:TemplateColumn SortExpression="Reserve_User" HeaderText="Reserve_User">
                                                       <ItemTemplate>
                                                            <asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Reserve_User") %>' ID="Reserve_User"/>
                                                       </ItemTemplate>
                                                       <EditItemTemplate>
                                                            <asp:DropDownList runat="server" SelectedIndex='<%#GetSalesIndex(DataBinder.Eval(Container.DataItem, "Reserve_User").ToString()) %>' id="R_User">
                                                                 <asp:ListItem>ROBERTD</asp:ListItem>
                                                                 <asp:ListItem>EMA</asp:ListItem>
                                                                 <asp:ListItem>PHILD</asp:ListItem>
                                                                 <asp:ListItem>SYSADM</asp:ListItem>
                                                            </asp:DropDownList>
                                                       </EditItemTemplate>
                                                  </asp:TemplateColumn>
                                                  <asp:TemplateColumn SortExpression="Calculate" HeaderText="Calculate"></asp:TemplateColumn>
                                             </Columns>
                                             <PagerStyle NextPageText="NEXT" BorderColor="Gold" PrevPageText="PREVIOUS" HorizontalAlign="Right"
                                                  ForeColor="#5283B5" Position="TopAndBottom" BackColor="CornflowerBlue"></PagerStyle>
                                        </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/(Tx02.Text *DL.Text)";

                    DataA = new System.Data.SqlClient.SqlDataAdapter(sqlC, Conn);
                    DataTable DTable = new DataTable();
               
                    Retardation)(@Eq1,@Eq2)",Conn) ;
               
                    double d3 = double.Parse(DL.Text);
               
                    DataRow IRow;
                   
                    IRow = DTable.NewRow();
               

DataColumn Calculate = new DataColumn();
                   
                    Calculate.DefaultValue=0;
DataS.Tables[0].Columns.Add(Calculate);

int i1 = (int)(d1*d2/d3);

                    foreach(DataRow dRow in DataS.Tables[0].Rows)
                    {
                    dRow["Calculted"]=(int)( double.Parse(IRow["Retardation"].ToString()) ) * ( double.Parse(Tx02.Text) / double.Parse(DL.Text) );
                   
                   
                   
                    }
     
//Calculate = i1.ToString();
                    DataA.Fill(DataS, "Main_Waveplate_Inv");
                    DGrid.DataSource = DataS;
                    DGrid.DataMember = "Main_Waveplate_Inv";
               
               
                   
                   

                   
                             
               

                   
                         DataS.Tables.Add();
                         DGrid.DataBind();
                         Conn.Close();