locked
how to add multiple records RRS feed

  • Question

  • hi can someone please tell me how do i insert multiple records for just one column name! for example i have a farmer code and farmer name that will be purchasing diffrernt items on diffrernt days, i want the user not to get confused when he enters the records for this same farmer everyday! dont know if u get what im saying?
    Monday, July 4, 2011 1:00 PM

Answers

  •  is it possible to show records according to dates in the crystal reports? in that way the report  will only display records of each farmer and the work done on that day?

    Hi,

    yes its possible, and i have already explained how to do so.

    You need a sql query:

    "SELECT Dates FROM cottonpurchase WHERE FarmerName = @farmername";
    


    So @farmername is the parameter of the actual farmer name.

    And then you create a dataTable, and fill it with the values from this query.

    Then pass this dataTable to thw Crystal report.


    Mitja
    • Marked as answer by T.N.P Wednesday, July 6, 2011 12:02 PM
    Wednesday, July 6, 2011 8:26 AM

All replies

  • Hello.

    What is your dataBase table stucture (of that table, which stored these data)?


    Mitja
    Monday, July 4, 2011 1:02 PM
  • hi its :

    create table cottonpurchase

    (

     

    slipNo int,

    purchasedate DateTIME,

    farmercode int,

    farmername varchar (100),

    villagename varchar (100),

    basicprice int,

    weight int,

    totalamountbasic int,

    premium int,

    totalamountpremium int,

    totalamountpaid int,

    Yeildestimates int  

     

    Monday, July 4, 2011 1:08 PM
  • As I have explained to you (in mail), I dont think your structure is fine. I have given you an exmaple, how would it be better.

    1st of all, this table "cottonpurchase" does NOT have an ID column - it would be good to have it.

    Then you dont need both of these two fields: farmercode and farmername - if they are both meant for one farmer. Only one will do it. A better solution would be (as I have explained) to have a seperated table of Farmers (with fields: FarmerID, FarmerCode, FarmerName). - but this is here not so important, even this table`s structure should work.

     

    Next, each of those fields have to be fulfiled when inserting a new purchase (all of them each time).

    So whan user wants to do a new insertion, you simple fill up all the fields, I dont really get where is the problem here.

     

    ----------------

    Maybe I got it what is bothering you.

    For example if you want to insert for one insertion more then one thing, you can insert them as one item, seperated by comma. Comma will be your delimiter (seperator), so when you will want to show then to the user, you will use the Split method.

     

    Tell me in which field would you like to insert more items ?


    Mitja
    Monday, July 4, 2011 1:22 PM
  • maybe i could do that, but i dont want to have have a duplicate name in my database! i just want farmer name and the items purchased on different days!
    Monday, July 4, 2011 1:32 PM
  • because im going to later on have a report and i only want to show in what the farmer has purchased on  a particular day
    Monday, July 4, 2011 1:33 PM
  • WHERE is your table is any field ITEM???

    I dont see it, sorry.


    Mitja
    Monday, July 4, 2011 1:35 PM
  • i mean the prices 
    Monday, July 4, 2011 1:37 PM
  • hey mitja can i do it like this: i will have a crystal report and in that i can show what each farmer has done on a certain day? 
    Monday, July 4, 2011 1:40 PM
  • Sure, but you know the whole point are two things:

    1. Correct database structure

    2. Correcrt sql query

    ---------------------------------------------------------------------------

    Crystal Reports only show what you will include in the sql query, which will fill dataTable (this is my recommendation).

    I think you are missing some basics here. You actually dont understand the dataBase structure and how to look for wanted data. This is what is bothering you. And this is exactly what Iam trying to tell you for the last couple of post (and over email).

    There is actually not important how many item (prices) you save into dataBase, each row will have their own data (date and time of purchase, and the belonging prices.

    So if you want to get all the prices of today of some farmer you do the query:

    //use sqlConnection too, then you do:

    SqlComamnd cmd = new SqlCommand();
    string query = @"SELECT totalamoutpaid FROM cottonpurchase WHERE FarmerName = @farmerName AND purchasedate >= @dateStart AND @purchasedate <= @dateEnd";
    cmd.Parameters.Add("@farmerName", SqlDbType.VarChar, 50).Value ="John Someone";
    cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = DateTime.Today;
    cmd.Parameters.Add("@dateEnd", SqlDbType.DateTime).Value = DateTime.Today.AddDays(1);
    

     

    This query will get all the total Prices of today. You can use a DataTable to fill it up, and then do the sum of all rows to get the total for today:

    DataTable table = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(table); //table will have only one column of totalamountpaid
    
    //then you do the sum of all rows to get the total:
    decimal total = 0;
    foreach(DataRow dr in table.Rows)
    {
       total += Convert.ToDecimal(dr[0].ToString());
    }

    ------------------------------------------------------------------------------

    Or you can use DataReader class, to do the sum in the run time, like:

    decimal total = 0;
    sqlConn.Open();
    SqlDataReader = cmd.ExecureReader();
    while(reader.Read())
    {
        total += (decimal)reader[0];
    }
    MessageBox.Show("The farmer John SomeOne has made total of " + total.ToString("C") + " for today."); //this is an example !
    

     

    -----------------------------------------------------------------------------

    If you want to show a crystal report, you do the query like I did in this example, fill dataTable, and pass it to the report. But there is some additional code required to show the results from dataTable in reports (but this is not the issue of this thread).

    -----------------------------------------------------------------------------

    Do you understand what am I trying to tell you?



    Mitja
    Monday, July 4, 2011 2:44 PM
  • Hello, just checking how is it going?

    Tell me if there isany progress.

    Iam willing to help you futher if you still struggle.


    Mitja
    Tuesday, July 5, 2011 1:25 PM
  • hello thank you for your concern! :) 

    i dont think its a problem for the names to be the same since the details of what they will be doing everyday is going to vary! is it possible to show records according to dates in the crystal reports? in that way i the report  will only display records of each farmer and the work done on that day? does it make sense?

     

    ps: if it is silly please tell me lol

    Tuesday, July 5, 2011 9:04 PM
  •  is it possible to show records according to dates in the crystal reports? in that way the report  will only display records of each farmer and the work done on that day?

    Hi,

    yes its possible, and i have already explained how to do so.

    You need a sql query:

    "SELECT Dates FROM cottonpurchase WHERE FarmerName = @farmername";
    


    So @farmername is the parameter of the actual farmer name.

    And then you create a dataTable, and fill it with the values from this query.

    Then pass this dataTable to thw Crystal report.


    Mitja
    • Marked as answer by T.N.P Wednesday, July 6, 2011 12:02 PM
    Wednesday, July 6, 2011 8:26 AM
  • hey can i use excel as a report instead of crystal reports?
    Wednesday, July 6, 2011 12:01 PM
  • Sure. If its easier for you.

    You can write into csv or xls file.

    I would recommend you to use csv, which is simplier, you only use a delimiter - comma, to split into columns. To add rows only write row by row.

    Here is an example code:

          //Get data into DataTable:
          DataTable table = new DataTable();//"get your data into dataTable";
          //table creation bellow is my own example:
          //from here:
          table.Columns.Add("a", typeof(int));
          table.Columns.Add("b", typeof(string));
          table.Rows.Add(1, "aa");
          table.Rows.Add(2, "bb");
          table.Rows.Add(3, "cc");
          //to here!!
    
          string filePath = @"C:\test.csv";
          string delimiter = ";";
    
          StringBuilder sb = new StringBuilder();
          for(int i=0;i<table.Rows.Count;i++)
          {
            string[] columnsArray = new string[table.Columns.Count];
            for (int j = 0; j < table.Columns.Count; j++)
              columnsArray[j] = table.Rows[i][j].ToString();
            sb.AppendLine(String.Join(delimiter, columnsArray));
          }     
    
          System.IO.File.WriteAllText(filePath, sb.ToString());
    





    Mitja
    Wednesday, July 6, 2011 1:14 PM
  • how do i get a csv file?
    Wednesday, July 6, 2011 1:44 PM