problem facing problem when updating my table in MS access...

• • Reply

  12 years ago

  by browndobee

  spong klong

  , Antarctica

  Joined 13 years ago

   wt i do i collecting filename from browser..
  and check with the database if file is present then update that row ..if the file is not present then create new file into the database..
  but i have write some code but i facing problem is that the file is updated and also create new file..
  but i want only one operation any time..i have attached code plz anyone tell me wts wrong with this code..
  its very urgent..
  
  Thanks and Regards
  shashi
  
  
  database table before updation
  //-------------------------------
  ID    text_data    filename              content_type                   
  9    binary data    msg.txt                     text/plain    
  12                   vcard.vcf                        text/x-vcard    
  13                  Mahavir Shete.vcf         text/x-vcard    
  51                  rahul saxena.vcf     text/x-vcard  
  
  //database after updation(if i got file name rahul saxena from browser)
  --------------------------
  ID    text_data    filename              content_type                    
  9    binary data    msg.txt                     text/plain    
  12        ''         vcard.vcf                     text/x-vcard    
  13                  Mahavir .vcf             text/x-vcard    
  51               rahul saxena.vcf         text/x-vcard   
  52               rahul saxena.vcf         text/x-vcard   
  53               rahul saxena.vcf         text/x-vcard   
  54               rahul saxena.vcf         text/x-vcard   
  

   //code in pageload
  

   HttpFileCollection uploadFiles = Request.Files;
          //create connectionstring for database
          string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/db2.mdb");//server.mappath("db2.mdb") + ";";
          //create oldbconnection object
          OleDbConnection cn = new OleDbConnection(strConn);
          // Loop over the uploaded files and save to disk.
          int i;
          for (i = 0; i < uploadFiles.Count; i++)
          {
              HttpPostedFile postedFile = uploadFiles[i];
              // Access the uploaded file's content in-memory:
              System.IO.Stream inStream = postedFile.InputStream;
              //convert the inputstream into the bytes
              Byte[] fileData = new Byte[postedFile.ContentLength];
              //read sequence of bytes from current stream ..
              inStream.Read(fileData, 0, fileData.Length);
              //close inputestream
              inStream.Close();
              //creaye arraylist object which is dyamically add the database data to list
              ArrayList list = new ArrayList();
              //get file name
              string filename = postedFile.FileName.ToString();
              Response.Write(filename);
              //retrieve all filename from database table
              try
              {
                  OleDbCommand cmd1 = new OleDbCommand("select filename from text_file ", cn);
                  OleDbDataAdapter adapter = new OleDbDataAdapter(cmd1);
                  //open the connection
                  cn.Open();
                  OleDbDataReader datareader = cmd1.ExecuteReader();
  
                  while (datareader.Read())
                  {
                      //list.Add(datareader["filename"]);
                      //add the filename into the list
                      list.Add(datareader["filename"].ToString());
  
                  }
              }
               
              catch (Exception ex)
              {
  
                  Response.Write(ex.ToString());
              }
              finally
              {   //close the connection
                  cn.Close();
              }
  
              //check the original data with new and update
              for (int i1 = 0; i1 < list.Count; i1++)
                  {
                     
                      if (filename == list[i1].ToString())
                      {
  
                          Response.Write("In Update table\n");
  
                          try
                          {
                              OleDbCommand cmd2 = new OleDbCommand("UPDATE text_file SET text_data=@textdata WHERE filename=@filename", cn);
                              OleDbParameter param1 = new OleDbParameter("@textdata", System.Data.OleDb.OleDbType.VarBinary, fileData.Length, ParameterDirection.Input, false, 0, 0, null, System.Data.DataRowVersion.Current, fileData);
                              cmd2.Parameters.Add(param1);
                              //Response.Write("text data updated");
                              //cmd2.Parameters.AddWithValue("@type", type);
                              cmd2.Parameters.AddWithValue("@filename", filename);
                              cn.Open();
                              cmd2.ExecuteNonQuery();
                              Response.Write("data successfully updated");
  
                          }
                          catch (Exception ex)
                          {
  
                              Response.Write(ex.ToString());
                          }
                          finally
                          {
                              cn.Close();
                          }
  
                      }
  //insert new record into the database
                      else
                      {
                          try
                          {
                              OleDbCommand cmd = new OleDbCommand("insert into text_file (text_data,filename,content_type) values(@,'" + postedFile.FileName + "','" + postedFile.ContentType + "')", cn);
                              OleDbParameter param = new OleDbParameter("@txt", System.Data.OleDb.OleDbType.VarBinary, fileData.Length, ParameterDirection.Input, false, 0, 0, null, System.Data.DataRowVersion.Current, fileData);
                              cmd.Parameters.Add(param);
                              cn.Open();
                              cmd.ExecuteNonQuery();
                              Response.Write("New Data Inserted");
  
                              //cn.Close();
                          }
                          catch (Exception ex)
                          {
  
                              Response.Write(ex.ToString());
                          }
                          finally
                          {
                              cn.Close();
                          }
  
                      }
  
                  }
                  //insert  new record into the database
                  
                 
              }
  
  

   

  Report abuse