locked
Help with insert statement RRS feed

  • Question

  •  String InsertStmt1 = "insert into log(Date&TimePrinted) where Barcode1Printed=Barcode values'" + (DateTime.Now.ToShortTimeString())+"'";
                    OleDbCommand cmd22 = new OleDbCommand(InsertStmt1, dbConn);
                    OleDbDataAdapter dap22 = new OleDbDataAdapter(cmd22);
                    DataSet dst22 = new DataSet();
                    dap22.Fill(dst22);
    i need help with this insert statement , i want to insert the date into the 

    (Date&TimePrinted) column in the same row the barcode is and there must be a value in the

    Barcode1Printed column

    Thanx

    Wednesday, September 19, 2012 6:18 AM

Answers

  • This is not a INSERT, but an UPDATE as the row already exists and you want to modify it. See SqlCommand.Parameters. The SQL itself should look like

    UPDATE Log 
    SET [Date&TimePrinted] = @Printed 
    WHERE NOT [Barcode1Printed] IS NULL;

    But I'm not sure whether I see the sense behind it. Can you explain your goal here?

    • Proposed as answer by Jason Dot Wang Thursday, September 20, 2012 6:51 AM
    • Marked as answer by Hefff Thursday, September 20, 2012 10:57 AM
    Wednesday, September 19, 2012 6:38 AM
  • i guess insert is not needed here, you can go for update. one thing, i guess you want to match Barcode1Printed value with 'Barcode', right?

    String InsertStmt1 = "update [log] set [Date&TimePrinted] = '" + (DateTime.Now.ToShortTimeString()) + "' where Barcode1Printed is not null and Barcode1Printed = 'Barcode'";
    OleDbCommand cmd22 = new OleDbCommand(InsertStmt1, dbConn);
    OleDbDataAdapter dap22 = new OleDbDataAdapter(cmd22);
    DataSet dst22 = new DataSet();
    dap22.Fill(dst22);

    regards

    joon

    • Proposed as answer by Jason Dot Wang Thursday, September 20, 2012 6:51 AM
    • Marked as answer by Hefff Thursday, September 20, 2012 10:57 AM
    Wednesday, September 19, 2012 7:07 AM

All replies

  • This is not a INSERT, but an UPDATE as the row already exists and you want to modify it. See SqlCommand.Parameters. The SQL itself should look like

    UPDATE Log 
    SET [Date&TimePrinted] = @Printed 
    WHERE NOT [Barcode1Printed] IS NULL;

    But I'm not sure whether I see the sense behind it. Can you explain your goal here?

    • Proposed as answer by Jason Dot Wang Thursday, September 20, 2012 6:51 AM
    • Marked as answer by Hefff Thursday, September 20, 2012 10:57 AM
    Wednesday, September 19, 2012 6:38 AM
  • See this website

    http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx

    Look at the line below.  In your case both lines are from the same table.  You need to add a "Relations() method to your dataset.

         

                   DataColumn parentColumn =
                        dataSet.Tables["Suppliers"].Columns["SupplierID"];
                   DataColumn childColumn =       
                        dataSet.Tables["Products"].Columns["SupplierID"];
                    DataRelation relation =
                        new System.Data.DataRelation("SuppliersProducts",
                        parentColumn, childColumn);
                    dataSet.Relations.Add(relation);

     


    jdweng

    Wednesday, September 19, 2012 6:58 AM
  • i guess insert is not needed here, you can go for update. one thing, i guess you want to match Barcode1Printed value with 'Barcode', right?

    String InsertStmt1 = "update [log] set [Date&TimePrinted] = '" + (DateTime.Now.ToShortTimeString()) + "' where Barcode1Printed is not null and Barcode1Printed = 'Barcode'";
    OleDbCommand cmd22 = new OleDbCommand(InsertStmt1, dbConn);
    OleDbDataAdapter dap22 = new OleDbDataAdapter(cmd22);
    DataSet dst22 = new DataSet();
    dap22.Fill(dst22);

    regards

    joon

    • Proposed as answer by Jason Dot Wang Thursday, September 20, 2012 6:51 AM
    • Marked as answer by Hefff Thursday, September 20, 2012 10:57 AM
    Wednesday, September 19, 2012 7:07 AM
  • i'm already doing that using parameters......
     OleDbCommand cmd2 = dbConn.CreateCommand();
                    cmd2.CommandText = "update Statements set Barcode1Printed=?,BarCode=? where barcode='" + txtScannedValue.Text + "'";
                    cmd2.Parameters.AddWithValue("Barcode1Printed", txtScannedValue.Text);   
                    cmd2.Parameters.AddWithValue("BarcodeValue", rowBarcode);
    
    I just want to insert the system date and time into a new column... for e.g.... i scan barcode 0001_0089  then the system date and time should be inserted into the additional column......   Something like  

    where Barcode1Printed=Barcode then insert the date and time

    The empty row hasn'tbeen scanned yet, when i scan it ,

    will update from barcode column into barcode1Printed column... im going to add a column name Date&Time

    next to the barcode1printed column, when i scan and update i want the time to be added aswell and keep its row

    • Proposed as answer by Jason Dot Wang Thursday, September 20, 2012 6:51 AM
    Wednesday, September 19, 2012 8:10 AM
  • Hi Heff,

      Welcome to MSDN Forum Support.

      Do you think the post written Stefan Hoffmann and Joon84 have solved your problem? If that,please feel free to mark their replies as answers. First I propose their replies as answers depending on my understanding,Later if you feel my proposal is right, please mark them. Thank you for participation.

      Sincerely,

      Jason Wang


    Jason Wang [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, September 20, 2012 6:51 AM
  •   OleDbCommand cmd2 = dbConn.CreateCommand();
                    cmd2.CommandText = "update Statements set Barcode1Printed=?,BarCode=? where barcode='" + txtScannedValue.Text +  "'";
                    cmd2.Parameters.AddWithValue("Barcode1Printed", txtScannedValue.Text);   
                    cmd2.Parameters.AddWithValue("BarcodeValue", rowBarcode);
                    
                    String InsertStmt1 = "INSERT INTO Statements ([Date&TimePrinted]) Values ('" + (DateTime.Now.ToString()) +"') where barcode1Printed=barcode'";
                    OleDbCommand cmd22 = new OleDbCommand(InsertStmt1, dbConn);
                    OleDbDataAdapter dap22 = new OleDbDataAdapter(cmd22);
                    DataSet dst22 = new DataSet();
                    dap22.Fill(dst22);

    can someone help me fix my sql statement: error is Missing semicolon (;) at end of SQL statement.

    i need the insertion of my 

    + (DateTime.Now.ToString()) +  to keep the row where the barcode is
    Thursday, September 20, 2012 9:24 AM
  • I added the semicolon in the correct loation.  I think you are going to need a FROM clause for it to work.

    String InsertStmt1 = "INSERT INTO Statements ([Date&TimePrinted]) Values ('" + (DateTime.Now.ToString()) +";') where barcode1Printed=barcode'";

    jdweng

    Thursday, September 20, 2012 9:30 AM
  • same error , note i'm using SQL Access 
     String InsertStmt1 = "INSERT INTO Statements ([Date&TimePrinted]) Values ('" + (DateTime.Now.ToString()) + "')" ;
    
    
    

    this alone works,   in the previous statements im places the where clause so that it keeps the row of the value im entering in my textbox..... im having this syntax error
    • Edited by Hefff Thursday, September 20, 2012 10:20 AM
    Thursday, September 20, 2012 10:16 AM
  • The easy way is to using the query designer in access.  Open your database in access and then go to Create window and use either the Query Wizard or Query Design.  When you get the syntax correct copy the string from access into your Visual Studio Project.

    jdweng

    Thursday, September 20, 2012 10:39 AM
  • Guys this is the answer i was looking for.... u were all right about not using the insert but instead an update statements but then again

    Mijta helped by explaining why i shouldn't use data sets in my case bt instead parameters as Stafan suggested earlia .thanx for all your help.

    this is my final solution to the issue:

    String InsertStmt1 = "UPDATE Statements SET [Date&TimePrinted] =@p1  WHERE barcode='" + txtScannedValue.Text + "'";
                    OleDbCommand cmd22 = new OleDbCommand(InsertStmt1, dbConn); 
                    cmd22.Parameters.AddWithValue("@p1", DateTime.Now);
                    cmd22.ExecuteNonQuery();

    Friday, September 21, 2012 7:02 AM