locked
Check if item already exists in Access Database using c# RRS feed

  • Question

  • User-1283384789 posted

     Hi,

    i have the insert form to where users can add some links to its favorite list. However i dont know how to write the code that user will get the message if this link already exists in its favorite list.

    this is my insert form:

     

      protected void DodajPolnitevPriljubljene_Click(object sender, EventArgs e)
    {
    // Determine the currently logged on user's UserId
    MembershipUser currentUser = Membership.GetUser();

    Int32 currentUserId = (Int32)currentUser.ProviderUserKey;

    foreach (DataListItem item in DataList3.Items)
    {
    if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
    {
    TextBox PIDTextBox = (TextBox)item.FindControl("PIDTextBox");
    TextBox IDTextBox = (TextBox)item.FindControl("IDTextBox");
    TextBox NabojimeTextBox = (TextBox)item.FindControl("NabojimeTextBox");
    TextBox KroglaTextBox = (TextBox)item.FindControl("KroglaTextBox");
    TextBox teza_krogleTextBox = (TextBox)item.FindControl("teza_krogleTextBox");
    TextBox smodnikTextBox = (TextBox)item.FindControl("smodnikTextBox");


    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\Orozje-Reloading-Center.mdb";
    OleDbConnection conn = new OleDbConnection(connectionString);
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;

    cmd.CommandText = "SELECT * FROM [POLNITVE_QUERY] WHERE ([PID] = ?) ";
    cmd.CommandText = "INSERT INTO MOJE_PRILJUBLJENE_POLNITVE(PID, ID, UserId, Nabojime, Krogla, teza_krogle, smodnik, Zapisek) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
    cmd.Parameters.AddWithValue("@PID", PIDTextBox.Text);
    cmd.Parameters.AddWithValue("@ID", IDTextBox.Text);
    cmd.Parameters.AddWithValue("@UserId", currentUserId);
    cmd.Parameters.AddWithValue("@Nabojime", NabojimeTextBox.Text);
    cmd.Parameters.AddWithValue("@Krogla", KroglaTextBox.Text);
    cmd.Parameters.AddWithValue("@teza_krogle", teza_krogleTextBox.Text);
    cmd.Parameters.AddWithValue("@smodnik", smodnikTextBox.Text);
    cmd.Parameters.AddWithValue("@Zapisek", "Dodaj svoj zapis");
    cmd.ExecuteNonQuery();

    conn.Close();

    //
    PriljubljeneUspeh.Text = "Link added to favorites!";
    }

    }
    }
      thanks,
    Friday, May 29, 2009 12:46 PM

Answers

  • User-925286913 posted

     Change Code to:

     

    protected void DodajPolnitevPriljubljene_Click(object sender, EventArgs e)
      {
          // Determine the currently logged on user's UserId
          MembershipUser currentUser = Membership.GetUser();
    
          Int32 currentUserId = (Int32)currentUser.ProviderUserKey;
    
          foreach (DataListItem item in DataList3.Items)
          {
              if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
              {
                  TextBox PIDTextBox = (TextBox)item.FindControl("PIDTextBox");
                  TextBox IDTextBox = (TextBox)item.FindControl("IDTextBox");
                  TextBox NabojimeTextBox = (TextBox)item.FindControl("NabojimeTextBox");
                  TextBox KroglaTextBox = (TextBox)item.FindControl("KroglaTextBox");
                  TextBox teza_krogleTextBox = (TextBox)item.FindControl("teza_krogleTextBox");
                  TextBox smodnikTextBox = (TextBox)item.FindControl("smodnikTextBox");
    
    
                  string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\Orozje-Reloading-Center.mdb";
                  OleDbConnection conn = new OleDbConnection(connectionString);
                  conn.Open();
                  OleDbCommand cmd = new OleDbCommand();
                  cmd.Connection = conn;
    
                  OleDbCommand cmdCheck = new OleDbCommand();
                  cmdCheck.Connection = conn;
    
                      cmdCheck.CommandText = "SELECT COUNT(*) FROM [POLNITVE_QUERY] WHERE ([PID] = ?) ";
                      cmdCheck.Parameters.AddWithValue("@PID", PIDTextBox.Text);
                      if(Convert.ToInt32(cmdCheck.ExecuteScalar()) == 0)
                      {
                      cmd.CommandText = "INSERT INTO MOJE_PRILJUBLJENE_POLNITVE(PID, ID, UserId, Nabojime, Krogla, teza_krogle, smodnik, Zapisek) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
                      cmd.Parameters.AddWithValue("@PID", PIDTextBox.Text);
                      cmd.Parameters.AddWithValue("@ID", IDTextBox.Text);
                      cmd.Parameters.AddWithValue("@UserId", currentUserId);
                      cmd.Parameters.AddWithValue("@Nabojime", NabojimeTextBox.Text);
                      cmd.Parameters.AddWithValue("@Krogla", KroglaTextBox.Text);
                      cmd.Parameters.AddWithValue("@teza_krogle", teza_krogleTextBox.Text);
                      cmd.Parameters.AddWithValue("@smodnik", smodnikTextBox.Text);
                      cmd.Parameters.AddWithValue("@Zapisek", "Dodaj svoj zapis");
                      cmd.ExecuteNonQuery();
                      }
                      else
                      {
                      PriljubljeneUspeh.Text = "Link already added to favorites!";
                      }
                      conn.Close();
    
                      // 
                      PriljubljeneUspeh.Text = "Link added to favorites!";
                  }
    
          }
      }
      
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2009 2:05 PM

All replies

  • User-925286913 posted

     Change Code to:

     

    protected void DodajPolnitevPriljubljene_Click(object sender, EventArgs e)
      {
          // Determine the currently logged on user's UserId
          MembershipUser currentUser = Membership.GetUser();
    
          Int32 currentUserId = (Int32)currentUser.ProviderUserKey;
    
          foreach (DataListItem item in DataList3.Items)
          {
              if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
              {
                  TextBox PIDTextBox = (TextBox)item.FindControl("PIDTextBox");
                  TextBox IDTextBox = (TextBox)item.FindControl("IDTextBox");
                  TextBox NabojimeTextBox = (TextBox)item.FindControl("NabojimeTextBox");
                  TextBox KroglaTextBox = (TextBox)item.FindControl("KroglaTextBox");
                  TextBox teza_krogleTextBox = (TextBox)item.FindControl("teza_krogleTextBox");
                  TextBox smodnikTextBox = (TextBox)item.FindControl("smodnikTextBox");
    
    
                  string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\Orozje-Reloading-Center.mdb";
                  OleDbConnection conn = new OleDbConnection(connectionString);
                  conn.Open();
                  OleDbCommand cmd = new OleDbCommand();
                  cmd.Connection = conn;
    
                  OleDbCommand cmdCheck = new OleDbCommand();
                  cmdCheck.Connection = conn;
    
                      cmdCheck.CommandText = "SELECT COUNT(*) FROM [POLNITVE_QUERY] WHERE ([PID] = ?) ";
                      cmdCheck.Parameters.AddWithValue("@PID", PIDTextBox.Text);
                      if(Convert.ToInt32(cmdCheck.ExecuteScalar()) == 0)
                      {
                      cmd.CommandText = "INSERT INTO MOJE_PRILJUBLJENE_POLNITVE(PID, ID, UserId, Nabojime, Krogla, teza_krogle, smodnik, Zapisek) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
                      cmd.Parameters.AddWithValue("@PID", PIDTextBox.Text);
                      cmd.Parameters.AddWithValue("@ID", IDTextBox.Text);
                      cmd.Parameters.AddWithValue("@UserId", currentUserId);
                      cmd.Parameters.AddWithValue("@Nabojime", NabojimeTextBox.Text);
                      cmd.Parameters.AddWithValue("@Krogla", KroglaTextBox.Text);
                      cmd.Parameters.AddWithValue("@teza_krogle", teza_krogleTextBox.Text);
                      cmd.Parameters.AddWithValue("@smodnik", smodnikTextBox.Text);
                      cmd.Parameters.AddWithValue("@Zapisek", "Dodaj svoj zapis");
                      cmd.ExecuteNonQuery();
                      }
                      else
                      {
                      PriljubljeneUspeh.Text = "Link already added to favorites!";
                      }
                      conn.Close();
    
                      // 
                      PriljubljeneUspeh.Text = "Link added to favorites!";
                  }
    
          }
      }
      
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2009 2:05 PM
  • User-1283384789 posted

     Hi,

     Thank you for your help.. It works, but i always get the same message "Link added to your favorites" even when i refresh the page and click again.

    Friday, May 29, 2009 2:31 PM
  • User-1283384789 posted

     I just noticed that if i use different username and if i want to add for example item #5 to my list it wont add it as different username has already added it to database. 

    This means that only one user can have certian item on its list..is it possible to change that so it will be possible and still avoid to add same item for same user?

     

    Thursday, June 4, 2009 2:00 PM
  • User-925286913 posted
    Change Code to:

      

    protected void DodajPolnitevPriljubljene_Click(object sender, EventArgs e)
    {
    // Determine the currently logged on user's UserId MembershipUser currentUser = Membership.GetUser(); Int32 currentUserId = (Int32)currentUser.ProviderUserKey; foreach (DataListItem item in DataList3.Items)
    {
    if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
    {
    TextBox PIDTextBox = (TextBox)item.FindControl("PIDTextBox");
    TextBox IDTextBox = (TextBox)item.FindControl("IDTextBox");
    TextBox NabojimeTextBox = (TextBox)item.FindControl("NabojimeTextBox");
    TextBox KroglaTextBox = (TextBox)item.FindControl("KroglaTextBox");
    TextBox teza_krogleTextBox = (TextBox)item.FindControl("teza_krogleTextBox");
    TextBox smodnikTextBox = (TextBox)item.FindControl("smodnikTextBox");


    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\Orozje-Reloading-Center.mdb";
    OleDbConnection conn = new OleDbConnection(connectionString);
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;

    OleDbCommand cmdCheck = new OleDbCommand();
    cmdCheck.Connection = conn;

    cmdCheck.CommandText = "SELECT COUNT(*) FROM [POLNITVE_QUERY] WHERE ([PID] = ?) AND ([UserId] = ?)";
    cmdCheck.Parameters.AddWithValue("@PID", PIDTextBox.Text);
    cmd.Parameters.AddWithValue("@UserId", currentUserId);
    if(Convert.ToInt32(cmdCheck.ExecuteScalar()) == 0)
    {
    cmd.CommandText = "INSERT INTO MOJE_PRILJUBLJENE_POLNITVE(PID, ID, UserId, Nabojime, Krogla, teza_krogle, smodnik, Zapisek) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
    cmd.Parameters.AddWithValue("@PID", PIDTextBox.Text);
    cmd.Parameters.AddWithValue("@ID", IDTextBox.Text);
    cmd.Parameters.AddWithValue("@UserId", currentUserId);
    cmd.Parameters.AddWithValue("@Nabojime", NabojimeTextBox.Text);
    cmd.Parameters.AddWithValue("@Krogla", KroglaTextBox.Text);
    cmd.Parameters.AddWithValue("@teza_krogle", teza_krogleTextBox.Text);
    cmd.Parameters.AddWithValue("@smodnik", smodnikTextBox.Text);
    cmd.Parameters.AddWithValue("@Zapisek", "Dodaj svoj zapis");
    cmd.ExecuteNonQuery();
    }
    else { PriljubljeneUspeh.Text = "Link already added to favorites!";
    }
    conn.Close();

    // PriljubljeneUspeh.Text = "Link added to favorites!";
    }

    }
    }
     
    Thursday, June 4, 2009 2:28 PM
  • User-1283384789 posted

     actually i tried that and it doesnt add to database at all...i also tried to change to
    cmdCheck.Parameters.AddWithValue("@UserId", currentUserId);

    and it works...but i need to see with differetn usernames to confirm :)...

     

    btw...why do i always get the same message?

     PriljubljeneUspeh.Text = "Link added to favorites!";

    I get this message even if i try to add same item to favorites. 

    Sohuldnt i get this message if i try to add same item?

    PriljubljeneUspeh.Text = "Link already added to favorites!";

     

    Thursday, June 4, 2009 3:00 PM