C# and SQL Query

C# and SQL Query

• TonyTech06

  this._adapter.InsertCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("SKU", System.Data.Odbc.OdbcType.VarChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "SKU", System.Data.DataRowVersion.Current, false, null));

  this._adapter.InsertCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("UPC", System.Data.Odbc.OdbcType.VarChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UPC", System.Data.DataRowVersion.Current, false, null));

  this._adapter.InsertCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("FamilyCode", System.Data.Odbc.OdbcType.VarChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "FamilyCode", System.Data.DataRowVersion.Current, false, null));

  this._adapter.InsertCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("VendorCode", System.Data.Odbc.OdbcType.VarChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "VendorCode", System.Data.DataRowVersion.Current, false, null));

   

  Does that help at all with the command there are tons of different ones such as DeleteCommand UpdateCommand

   

  private System.Data.Odbc.OdbcCommand[] _commandCollection;

  or possibly you are looking for that.

   

  
  

• Latso

  u r right so far

  for other portion also u have to append the query

  SELECT TranDetail.EndDateTime, TranDetail.StoreNum, TranDetail.TranNum, TranDetail.TranType, TranDetail.SKU, TranDetail.DeptID, TranDetail.ClassID,
  TranDetail.Salesperson, TranDetail.Cashier, TranDetail.QtySold, TranDetail.Price, TranDetail.Cost, TranDetail.StatusFlags, TranDetail.CustNum
  FROM TranDetail, TranHeader WHERE "' + TranDetail.EndDateTime + '" = "' = TranHeader.EndDateTime + '" AND ("' + TranDetail.EndDateTime + '" = "' + serial.ToString() + '")
  

  The portion in yellow should also be done like the portion in red

  
  

• m1sterb0b

  Its still not going through... Im not sure if Im just missing a ' or  a ".

  "SELECT TranDetail.EndDateTime, TranDetail.StoreNum, TranDetail.TranNum, TranDetail.TranType, TranDetail.SKU, TranDetail.DeptID, TranDetail.ClassID,
                        TranDetail.Salesperson, TranDetail.Cashier, TranDetail.QtySold, TranDetail.Price, TranDetail.Cost, TranDetail.StatusFlags, TranDetail.CustNum
  FROM TranDetail, TranHeader
  WHERE     TranDetail.EndDateTime = TranHeader.EndDateTime AND (TranDetail.EndDateTime = " + serial.ToString() + "'")

   

  Thats exactly what it looks like in the query builder

  Also... I can change serial into a string in my code if that helps any.

  
  

• awperli

  ERROR [42S22] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Invalid column name: '' + serial + ''.
  ERROR [HY000] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in expression: ' + serial + '
  

• mcmarquez

  Now along with the parse error, I have Invalid text or symbol.

  "SELECT TranDetail.EndDateTime, TranDetail.StoreNum, TranDetail.TranNum, TranDetail.TranType, TranDetail.SKU, TranDetail.DeptID, TranDetail.ClassID,
  TranDetail.Salesperson, TranDetail.Cashier, TranDetail.QtySold, TranDetail.Price, TranDetail.Cost, TranDetail.StatusFlags, TranDetail.CustNum
  FROM TranDetail, TranHeader
  WHERE "' + TranDetail.EndDateTime + '" = "' = TranHeader.EndDateTime + '" AND ("' + TranDetail.EndDateTime + '" = "' + serial.ToString() + '")
  

  Thats my code.

  
  

• ngkay

  Hmm...

  Now I am getting Invalid Column Name " + TranDetail.EndDateTime + "

  When I remove the " and " from that it says that the serial.ToString() is an invalid column name. TranDetail.EndDateTime is a column... but serial is a variable. C# NEEDS TO UNDERSTAND ME!

  
  

• Joel Hensley

  soory one single cote is missing

  "SELECT TranDetail.EndDateTime, TranDetail.StoreNum, TranDetail.TranNum, TranDetail.TranType, TranDetail.SKU, TranDetail.DeptID, TranDetail.ClassID,
  TranDetail.Salesperson, TranDetail.Cashier, TranDetail.QtySold, TranDetail.Price, TranDetail.Cost, TranDetail.StatusFlags, TranDetail.CustNum
  FROM TranDetail, TranHeader
  WHERE TranDetail.EndDateTime = TranHeader.EndDateTime AND (TranDetail.EndDateTime = " + serial.ToString() + "'")

  all controls data/text should also be treated in this similar fashion

  like TranDetail.EndDateTime and other controls

  
  

• Shirley A

  @serial will not work in C# code

  You ca directly use serial variable.

  "SELECT FROM WHERE TranDetail.EndDateTime = TranHeader.EndDateTime AND (TranDetail.EndDateTime >= " + serial.ToString() + "'")

  
  

• CHEN YU-TIEN

  u r right so far

  for other portion also u have to append the query

  SELECT TranDetail.EndDateTime, TranDetail.StoreNum, TranDetail.TranNum, TranDetail.TranType, TranDetail.SKU, TranDetail.DeptID, TranDetail.ClassID,
  TranDetail.Salesperson, TranDetail.Cashier, TranDetail.QtySold, TranDetail.Price, TranDetail.Cost, TranDetail.StatusFlags, TranDetail.CustNum
  FROM TranDetail, TranHeader WHERE "' + TranDetail.EndDateTime + '" = "' = TranHeader.EndDateTime + '" AND ("' + TranDetail.EndDateTime + '" = "' + serial.ToString() + '")
  

  The portion in yellow should also be done like the portion in red

  
  

• Kamen

  Ok... now Im getting "Unable to parse query text" when I try to finish the query builder. And when i compile it compiles fine but doesnt fill the datagrid with anything. I tried setting serial to a certain number 'serial = 38866;' and it still acted like it wasnt seeing my variable. Sorry for all the beginners questions! Thanks for the help.
  

• Romantic_touch

  "SELECT '"+ TranDetail.EndDateTime+ "','"+ TranDetail.StoreNum+"'"

  Just like this u have to append the controls value in the string

  put single quote for string datafield but do not put quote for integer values

  
  

• cikitani

  This is all of the code for my simple little program that doesnt work for me.

  There is tons os hidden code but im not sure which parts of it you need.

  using System;

  using System.Collections.Generic;

  using System.ComponentModel;

  using System.Data;

  using System.Drawing;

  using System.Text;

  using System.Windows.Forms;

  namespace WindowsApplication1

  {

  public partial class Form1 : Form

  {

  public Form1()

  {

  InitializeComponent();

  }

  private void tranDetailBindingNavigatorSaveItem_Click(object sender, EventArgs e)

  {

  this.Validate();

  this.tranDetailBindingSource.EndEdit();

  this.tranDetailTableAdapter.Update(this.dataSet1.TranDetail);

  }

  private void Form1_Load(object sender, EventArgs e)

  {

  int serial;

  int a = DateTime.Now.Month;

  int b = DateTime.Now.Day;

  int c = DateTime.Now.Year;

  //serial = 38866;

  if( a > 2 )

  serial=(int)(365.25*c)+(int)(30.6001*(a+1))+b-694037;

  else

  serial=(int)(365.25*(c-1))+(int)(30.6001*(a+13))+b-694037;

  string dateSerial = Convert.ToString(serial);

  label1.Text = dateSerial;

  textBox1.Text = dateSerial;

  }

  private void fillByToolStripButton_Click(object sender, EventArgs e)

  {

  try

  {

  this.tranDetailTableAdapter.FillBy(this.dataSet1.TranDetail);

  }

  catch (System.Exception ex)

  {

  System.Windows.Forms.MessageBox.Show(ex.Message);

  }

  }

  private void label1_Click(object sender, EventArgs e)

  {

  }

  private void textBox1_TextChanged(object sender, EventArgs e)

  {

  }

  private void button1_Click(object sender, EventArgs e)

  {

  }

  private void tranDetailDataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)

  {

  }

  }

  }

  
  

• Jweige

  OK, let's clean this up a bit. First of all, your dateserial calculation seems to be a lot of wasted effort. That can be reduced to:

  const DateTime epoch = new DateTime(1900, 1, 1);
  

  private void Form1_Load(object sender, EventArgs e)
  {
  TimeSpan now = DateTime.Today - epoch;
  string dateSerial = now.TotalDays.ToString();
  label1.Text = dateSerial;
  textBox1.Text = dateSerial;
  }

  Now, as for the Click event, I still have to guess at the names at types of the objects in your class but here goes:

  Somewhere you have a OdbcCommand object, which has it's CommandText property set to:

  "SELECT ...

  FROM ...

  WHERE WHERE TranDetail.EndDateTime = TranHeader.EndDateTime AND (TranDetail.EndDateTime >= )"

  (and that's the way we want it, with the question mark).

  I'm going to call this OdbcCommand object OCmd because I don't know what you called it in your program:

  string dateSerial = textBox1.Text;
  OCmd.Parameters.Add(Convert.ToInt32(dateSerial));

  
  
  

• puffzotty

  >> WHERE TranDetail.EndDateTime = TranHeader.EndDateTime AND (TranDetail.EndDateTime = " + serial.ToString() + "'")

  The end of that wants to be similar to:

  "....(TranDetail.EndDateTime = '1/1/2006')"

  Now, we just have to replace the '1/1/2006' with our number. So, first we end the first part of the string after the opening single quote (That's equals, space, single quote, double-quote:

  "....(TranDetail.EndDateTime = '"

  then we add our date

  "....(TranDetail.EndDateTime = '" + serial.ToString()

  Finally, we add the closing quote & parentheses (that's double-quote, singlequote, paren, double-quote)

  "....(TranDetail.EndDateTime = " + serial.ToString() + "')"

  However, you really should learn how to use parameters to your queries. Had you shown me ANYTHING AT ALL about how you are callling this query, I could have shown you how to do that.

  
  
  
• C# and SQL Query