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

csharp , db Antarctica
  • 10 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
                    
                   
                }

     

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“You can stand on the shoulders of giants OR a big enough pile of dwarfs, works either way.”