none
Updating a SQL Server table from a datatable RRS feed

  • Question

  •  

    Hi,

     

    I am writing a console application that is meant to cycle through all the files in particular directory and store the FilePath, FileName, Created, DateLastAccessed and DateLastModified properties of each file into a table. In the event that the file already exists in the table it should simply update the specified record. Otherwise, it should insert the record. To do this I wrote the following code snippet:

     

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

     

                    DirectoryInfo theFolder = new DirectoryInfo(FilePath);

                    //put the file information into a database

                    string strConnection = "blah blah blah";
                    SqlConnection con = new SqlConnection(strConnection);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataAdapter adap;
                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable("tblFileData");

     

                    cmd = con.CreateCommand();

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT FilePath, FileName, Created, DateLastAccessed, DateLastModified " +
                        "FROM [tblFileData]";

                    adap = new SqlDataAdapter(cmd);
                    adap.SelectCommand = cmd;
                    adap.Fill(dt);

                    SqlCommandBuilder cmb = new SqlCommandBuilder(adap);

     

                    //Now cycle through each file in the Directory and add the data into the datatable
                    foreach (FileInfo nextFile in theFolder.GetFiles())
                    {
                        //check to see if the file already exists
                        blnEntryFound = false;
                        string strFilePath = nextFile.DirectoryName + "\\" + nextFile.Name;
                        DataRow dr;

                        for (int intX = 0; intX < dt.Rows.Count - 1; intX += 1)
                        {
                            if (dt.Rows[intX][0].ToString() == strFilePath)
                            {
                                dr = dt.Rows[intX];
                                dr["FilePath"] = strFilePath;
                                dr["FileName"] = nextFile.Name;
                                dr["Created"] = nextFile.CreationTime.ToLongDateString();
                                dr["DateLastAccessed"] = nextFile.LastAccessTime.ToLongDateString();
                                dr["DateLastModified"] = nextFile.LastWriteTime.ToLongDateString(); ;
                                blnEntryFound = true;
                            }
                        }
                        if (blnEntryFound == false) //the records doesnt exist so insert it
                        {
                            dr = dt.NewRow();
                            dr["FilePath"] = nextFile.DirectoryName + "\\" + nextFile.Name;
                            dr["FileName"] = nextFile.Name;
                            dr["Created"] = nextFile.CreationTime.ToLongDateString();
                            dr["DateLastAccessed"] = nextFile.LastAccessTime.ToLongDateString();
                            dr["DateLastModified"] = nextFile.LastWriteTime.ToLongDateString();
                            dt.Rows.Add(dr);
                        }
                    }

                    //Now update the actual table
                    adap.Update(dt);

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

     

    When I run the above I get the following error message:

    "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"

     

    Could somebody tell me why?

     

    Thanks,

    Kabir


     

    Tuesday, August 7, 2007 3:45 PM

All replies