none
How to get message user exist IF UserNo=@UserNo and DatePrint not in same day RRS feed

  • Question

  • Problem exist validation for user no not work and give me wrong result 

    i dont need insert data coding .

    my problem here i need to solve function UserExistNotToday function give me wrong result

    so that how to solve problem

    I work in windows form vs 2015 c#

    I have data in excel and i need to validate if exist tell give me exist in excel sheet file 2007 .

    I need to validate exist UserNo to excel file based on date today

    meaning he can add UserNo more time in same day or today but

    if he come in next day cannot insert UserNo again if he found before .

    example what i need

    PrintDate 22/02/2017 UserNo 1016 can insert more THAN one UserNo in same day meaning i can have UserNo 1016 more than one time in 22/02/2017 but

    UserNo 1016 come next day as PrintDate 23/02/2017 and UserNo already found yesterday in date 22/02/2017 i cannot add again in next day

    and it will give me UserNo Exist .

    so please help me

    pesudo cod

    if (userno found in same day i add)

    add user no

    else

    user exist(if i add userno in 23/02/2017 and it found in date 22/02/2017)

    my code

    connection as below :

     string connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=D:\\Attendance Sheet.xlsx;Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;");
    public bool UserExistsNotToday(string UserNo,string DatePrint)
            {
    
                OleDbConnection cn = new OleDbConnection(connection);
                string str = "SELECT UserNo FROM  [Sheet1$] WHERE UserNo=UserNo AND DatePrint=@DatePrint";
                OleDbCommand cmd = new OleDbCommand(str, cn);
    
                
                cmd.Parameters.AddWithValue("@UserNo", UserNo);
                cmd.Parameters.AddWithValue("@DatePrint", DatePrint);
                cn.Open();
                var reader = cmd.ExecuteReader();
                return reader.HasRows;
    
    
            }


    under button  click

    i write

     QrClasses qrc = new QrClasses();
                bool b = qrc.UserExistsNotToday(textBox1.Text,textBox2.Text);
                if (b == true)
                {
    
                  
                    label3.Text = "User Exist";
    
                }
                else
                {
    // here i will insert data
                    label3.Text = "User Not Exist";
    
    
                }

    in form load i get date as following

     DateTime thisDay = DateTime.Today;
                textBox2.Text = thisDay.ToString("dd/MM/yyyy");

    and excel sheet i using as following :

    Wednesday, February 22, 2017 11:20 AM

All replies

  • You're working with datetime values and I suspect the values you are getting don't line up. When you're looking at this data in Excel you're looking at it through a formatter so it may not be the actual underlying value. I would recommend that you change your query to simply search for the UserNo. Then enumerate each row returned back and convert the DatePrint column to a DateTime (or at least verify the format). You can then determine if any row has that date time using Any or another LINQ command.

    Michael Taylor
    http://www.michaeltaylorp3.net

    Wednesday, February 22, 2017 3:15 PM
  • thank you for reply

    i need to change this line query 
     "SELECT UserNo FROM  [Sheet1$] WHERE UserNo=UserNo AND DatePrint=@DatePrint";
    to accept if userno not exist


    Wednesday, February 22, 2017 3:39 PM
  • Not exist? I thought you wanted to determine if they did exist for the given date? To determine if they don't exist then run the query as is. If you get back any rows they exist, if you get back no rows then they don't exist. Changing the query wouldn't tell you anything.

    Wednesday, February 22, 2017 4:21 PM
  • Maybe you should also write ‘… WHERE UserNo=@UserNo …’?

    Wednesday, February 22, 2017 5:37 PM
  • Hi ahmedbarbary,

    Thank you for posting here.

    According to your question is more related to database, I will move it to ADO.NET Managed Providers forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 23, 2017 7:36 AM
  • Hi ahmedbarbary,

    Agree with Viorel, you write a wring SQL statement, which need add "@" at the parameter named UserNo.

    In addition, you want to DataReader, I would suggest that you could use close after you use it. please modify your issue like this:

    public bool UserExistsNotToday(string UserNo,string DatePrint)
            {
    
                OleDbConnection cn = new OleDbConnection(connection);
                string str = "SELECT UserNo FROM  [Sheet1$] WHERE UserNo=UserNo AND DatePrint=@DatePrint";
                OleDbCommand cmd = new OleDbCommand(str, cn);
    
                
                cmd.Parameters.AddWithValue("@UserNo", UserNo);
                cmd.Parameters.AddWithValue("@DatePrint", DatePrint);
    
    bool retrunValue = false;
    
                cn.Open();      
                var reader = cmd.ExecuteReader();
                if(reader.HasRows)
                   retrunValue = true;
                
    reader.close();
    cn.close();
    return retrunValue;
    
    
            }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 23, 2017 9:24 AM
    Moderator