• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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)";
0
devlins
Asked:
devlins
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
devlinsAuthor Commented:
string  sqlC2= "(select * from Waveplate where part_key = part_key set Calculate = Retardation/(Tx02.Text *DL.Text)";
0
 
dunglaCommented:
you can use parameter:

INSERT INTO Waveplate (Calculate) VALUES (Retardation/(Tx02.Text *DL.Text)) WHERE part_key = part_key
0
 
rajaloysiousCommented:
>>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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rajaloysiousCommented:
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
0
 
b1xml2Commented:
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>
   
   

0
 
devlinsAuthor Commented:
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 ).........



0
 
dunglaCommented:
So you want to query something, then based on the result (that have column name Retardation) fill data to column name Calculate right? Try this one

SqlConnection cn = new SqlConnection("ConnectionString");
SqlDataAdapater adapter = new SqlDataAdapater("select * from Waveplate where part_key = " + part_key, cn);
DataTable dt = new DataTable("Waveplate");
adapater.Fill(dt);

foreach (DataRow drow in dt.Rows)
{
if (drow["Retardation"] != DBNull.Value)
{
Calculate = int.Parse(drow["Retardation"].ToString()) * (Tx02.Text/DL.Text);
}
}


If you make sure that part_key is unique or identity then you can use the code below instead of

SqlConnection cn = new SqlConnection("ConnectionString");
SqlCommand cmd = new SqlCommand("SELECT Retardation FROM Waveplate WHERE part_key=" + part_key, cn);
cmd.Connection.Open();

object objResult = cmd.ExecuteScalar();
if (objResult != null)
{
Calculate = int.Parse(objResult.ToString()) * (Tx02.Text/DL.Text);
}
0
 
devlinsAuthor Commented:
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???
0
 
rajaloysiousCommented:
You have to convert them to double

double d1 = Convert.ToDouble(Tx02.Text)/Convert.ToDouble(DL.Text);

Calculate = int.Parse(drow["Retardation"].ToString()) * d1 ;

cheers
0
 
devlinsAuthor Commented:
Now it gives me the cannot convert datacolumn to type 'double' error is there any way to correct it?

0
 
rajaloysiousCommented:
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
0
 
dunglaCommented:
You can set DataRow["Calculate"] = dRetardation  * (dTx02 / dDL  );
0
 
devlinsAuthor Commented:

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();
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now