Gladimir

Hmmm... you may need to use apostrophes before and after your TimeOfDay value. VALUES(" & Today & ", '" & Right(TimeOfDay, 8) & "')" It's a barely-educated guess.

Is it possible to simply determine the next id at the time the item is added to the table regardless of the interface being used to add it? So, both the web and non-web interface would have a LookupNextID(Product) function that looks to see if an ID already exists for a given Product and adds a new ID if the product is unique. Here is a code snippet from an application where I basically do the same thing with purchasing data: // obtain description id int intDescID = LookupDescID(strDescription); if (intDescID < 1) // id does not exist in master list { InsertNewDescription(strDescription, CategoryCode.CSCPurchase); intDescID = LookupDescID(strDescription); } If this sounds to be on the right track and you want to see the code for LookupDescID and InsertNewDescription, just let me know.

A Chance... I'm going to take a stab and suggest putting brackets ([]) around the word Numeri in your INSERT statement. SqlCommand command = new SqlCommand("insert into burattif.NumeriProva ([Numeri]) " + "values ('"& Pippo.Text &"')", connection);

I don't believe you are actually opening the connection, so the state is still closed. Try this: try { con.Open() // do your stuff here } finally { con.Close() }

SOLUTION Nevermind... I think the UPDATE command will do the trick.

Patience for a slow one... Ok, so these responses would indicate to me that I need two queries; one to select the records matching the description and a second to insert that description's id into the DescID column. Please correct me if I am wrong. Here is a sample of information from table REF_CSDSoftware: recID Description DescID 339480 expinst 0 339481 Internet Explorer ReadMe 0 It's the zeros in DescID that the INSERT will correct. Here is a sample from table REF_MasterDescription: DescID Description CategoryCode 1 Adobe Acrobat Writer 1 2 Adobe PageMaker 5.0 1 In a separate function, I select the distinct descriptions from REF_CSDSoftware, assign each one a unique DescID, and insert that into the REF_MasterDescription table. I then take that same unique DescID and the corresponding description and pass it to a function that queries all records from REF_CSDSoftware matching the description. I would like to simply insert that DescID into the results of that query, which is the one I'm trying to build. Here is some code: int intDescID = 0; string strDescription = null; // drc is distinct descriptions from table REF_CSDSoftware foreach (DataRow row in drc) { strDescription = row["Description"].ToString(); // does description already exist in REF_MasterDescription intDescID = LookupDescID(strDescription); if (intDescID < 1) // id does not exist in master list { // insert new Description and DescID in the master list InsertNewDescription(strDescription, eCode); intDescID = LookupDescID(strDescription); } // Get all records matching description from REF_CSDSoftware // and assign the new DescID to all rows with that description InsertMasterDescriptionID(intDescID, strDescription, eCode); Application.DoEvents(); } So, am I correct in assuming InsertMasterDescriptionID will first have to SELECT all rows matching strDescription, the follow that with a separate query to INSERT the corresponding intDescID?

Here is my current SQL statement that produces a syntax error: "INSERT INTO " + strTableName + " (DescID) " + "SELECT DescID FROM " + strTableName + " WHERE (Description='" + strDescription + "') " + "VALUES(" + intDescID.ToString() + ")"; I came across one of those SQL Tutorial sites that said something like this would be legal, but I had my doubts from the beginning. What I am trying to do is INSERT INTO myTable (DescID) VALUES(intDescID) WHERE (Description='strDescription')". I just don't know the proper syntax or even if it is possible to do with a single SQL statement. Background I select distinct descriptions and come up with about 4000 rows. For each of those distinct descriptions, I am trying to use my INSERT statement to update records in another table. Sometimes 'Description' equals strDescription in only one row, and other times that criteria will return just under 10,000 rows or matches.

SOLUTION... And the answer is... Place '[' and ']' brackets around the word Domain in the SELECT statement.

Database Connection is OK... I double-checked on your suggestion. The Fill method checks the status of the related connection object and automatically Opens that object if it is closed. I've also tried playing with the "Billabe=" portion of the SELECT statement, including using Yes, True, and 1. Thanks for the feedback.

When I try to fill a dataset table using the SELECT statement listed below, I get the following error: Unspecified error: E_FAIL(0x80004005) Here is the relevant code: string strSelectA = null; strSelectA = "SELECT ComputerName, Domain FROM REF_CSDComputers " + "WHERE (Billable='" + true.ToString() + "')"; OleDbDataAdapter assetAdapter = new OleDbDataAdapter(strSelectA, master); try { assetAdapter.Fill(ds1, "lookup"); } finally { master.Close(); } Obviously, the error occurs when I attempt to use the Fill method of the assetAdapter. The dataset ds1 definitely exists, and in fact, there is a line of code calling the Fill method of another DataAdapter just above the assetAdapter. Any help is greatly appreciated.

SOLUTION! That is beautiful Mehyar. It is working perfectly in the Access Query Builder and here it is in my code: string strScalar = "SELECT COUNT(MemID) AS CountA " + "FROM (SELECT DISTINCT MemID FROM ReferenceADGroups " + "WHERE (Description='" + strDescription + "') " + "AND NOT(MatchCode) = '" + strMatchCode + "')"; Thanks for you help!

Mehyar, thanks for you reply, but that produces an error in MS Access SQL Builder: ERROR: syntax error (missing operator) in query expression 'COUNT (DISTINCT ReferenceADGroups.MemID)' SELECT COUNT (DISTINCT ReferenceADGroups.MemID) AS CountA FROM ReferenceADGroups WHERE ((Not (ReferenceADGroups.MatchCode)="0000") AND ((ReferenceADGroups.Description)="ARMS")); ERROR: undefined function 'DISTINCT' in expression SELECT COUNT (DISTINCT (ReferenceADGroups.MemID)) As CountA FROM ReferenceADGroups WHERE ((Not (ReferenceADGroups.MatchCode)="0000") AND ((ReferenceADGroups.Description)="ARMS")); I know we have to be close to something that works. Thanks for the help.

I found this thread on the same subject by Cassio, but the question seems to remain unanswered. I have two fields, MemID and Description, in a table named ReferenceADGroups. The following SQL statement produces a Count of 670: SELECT COUNT(MemID) FROM ReferenceADGroups WHERE (Description="ARMS"); However, some users are duplicated in these groups, so I need to add the DISTINCT function. The following SQL statement produces 665 rows: SELECT DISTINCT MemID FOM ReferenceADGroups WHERE (Description="ARMS"); How do I combine these two functions to write a statement that only produces a COUNT of the DISTINCT Member IDs? Thanks in advance for your help.

Might be a bug... Well, I've done some experimentation and think this could be a bug in the ListBox control. I started a new project, threw a listBox on the form, set the SelectMode to MultiExtended, and populated it use the Add method. The multi-select and deselect worked as expected. However, when I populate the same ListBox by assigning a DataSet table to the DataSource property, I get the disfunctional multi-select and deselect behavior. Any ideas?

I think I am doing something wrong with my listbox control. The Selection Mode is multi-extended and the DataSource is bound to a dataset table resulting from a query. Everything works wonderfully, until I deselect the last selected item in the Listbox. When I deselect the last selected item in the Listbox, by holding the Control key and clicking the last selected item, the first item in the list becomes selected. I have code in only one event for this listbox and that is the SelectedIndexChanged event and here is the code: private void listMatches_SelectedIndexChanged (object sender, System.EventArgs e) { buttonExclude.Enabled = false; buttonCommit.Enabled = false; // update exclude/commit buttons if (listMatches.SelectedItems.Count > 0) { buttonExclude.Enabled = true; buttonCommit.Enabled = true; } labelSelected.Text = listMatches.SelectedItems.Count.ToString(); }

Select Statement? I've only messed with WMI using vbscript, but don't you need a Select statement in there somewhere? 'Query WMI to get system information (mfg, model) Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" & _ oNet.computername & "\root\cimv2") Set colSettings = objWMIService.ExecQuery _ ("Select * from Win32_ComputerSystem") For Each objComputer In colSettings strComputer = trim(objComputer.Manufacturer) "|" & _ trim(objComputer.Model) Next

Brilliant! Wonderful! I greatly appreciate all your help. I replaced the FileInfo class with a public static string, which would not be necessary in your implementation; but I want to cling to the separation of the buttonSetFile click event and the buttonTables click event. I remain under the assumption that a static string is still necessary to pass the filename string returned by openFileDialog1 in the buttonSetFile click event as a parameter to the new formTables object in the buttonTables click event. My last step is figuring out how to return the tables selected on formTables back to formMain. It should be smooth sailing form there... Thanks again for all your help.

Great! That's why I'm here. Let's start with the why the 'null' declaration throws a null exception when I try to read the dbInfo.PathName property. static void Main() { Application.Run(new formMain()); } // line recommended by aewarnick public static FileInfo dbInfo = null; private void buttonSetFile_Click(object sender, System.EventArgs e) { switch (openFileDialog1.ShowDialog()) { case DialogResult.OK: // this line throws null exception dbInfo.PathName = openFileDialog1.FileName; labelDatabaseName.Text = dbInfo.FileName; buttonTable.Enabled = true; break; case DialogResult.Cancel: break; } } You're correct in that I do not want objects being created if they're never going to be used. For our immediate purposes, what changes would I have to make to get this to work 'as is'? Next, let's explore your first suggestion. I would like to display formTables in the click event of buttonTables. On formTables, I would like to use dbInfo.PathName in the connection string of my connection object. Let's say I don't make my dbInfo static. In that case, dbInfo will be created as a new instance of FileInfo in the buttonSetFile click event. The user now clicks buttonTable to bring up formTables. How does the dbInfo.PathName persist to the buttonTable click event and then to formTables? Thanks for any help you can give.

Very Cool, Thanks. It is now working, though not with the 'null' syntax. I actually had to go ahead an declare dbInfo as a 'new' FileInfo object. Thanks again.

I have created the public class fileInfo with two properties; 1) PathName and 2) FileName. I have two buttons on form Main, buttonSetFile and buttonTable. The click event for buttonSetFile allows the end-user to select a database file using openFileDialog1 (see code below). private void buttonSetFile_Click(object sender, System.EventArgs e) { switch (openFileDialog1.ShowDialog()) { case DialogResult.OK: fileInfo dbInfo = new fileInfo(); dbInfo.PathName = openFileDialog1.FileName; labelDatabaseName.Text = dbInfo.FileName; buttonTable.Enabled = true; break; case DialogResult.Cancel: break; } } I want to read the PathName and the FileName from the buttonSetFile click event in the buttonTable click event. In fact, I want to pass the PathName and FileName values to a new form made available to the end-user in the buttonTable click event. How/Where do I instantiate a fileInfo object to make the PathName and FileName properties available from all control events or even from another form?

Some things to try... I'm not that familiar with VB.NET, but is it possible to get the length of the username and the length of the password read from the database table, then compare them to the length of the username and password entered by the user. If it turns out that any of these are different, you may have leading or trainling white spaces for some reason. In that case, use the Trim function to strip all white spaces from the leading and trailing end of the offending strings.

Writing dataset table to database table Well, let's assume we're talking about Microsoft Access, but I think the steps apply generally. If you already have a table in your database with the same fields (coulmns) as your dataset table, then all you need to do is whip up a connection object, a dataadapter object, and a commandbuilder object. That would allow you to use the data adapter's Update method to insert all the records from your dataset table to your database table. DataSet ds1 = new DataSet(); ds1.Tables.Add("assets"); // dataset table index 0 /* I have a class that use for my database connection. * I'll include that code at the bottom */ // initialize master data connection object dbMaster masterdb = new dbMaster(); OleDbConnection master = masterdb.m_master; // create and initialize data adapter objects string strassetSelect = "SELECT * FROM assets"; OleDbDataAdapter assetAdapter = new OleDbDataAdapter(strassetSelect, master); // create and initialize command object required to add new rows OleDbCommandBuilder assetCommand = new OleDbCommandBuilder(assetAdapter); // fill the dataset tables in memory from the database tables assetAdapter.Fill(ds1, "assets"); // table index 0 /* populate your dataset table and have all sorts of fun */ // update the database table in the master database assetAdapter.Update(ds1, "assets"); I'm a little new to this, but this will get you connected to an Access database, then you'll just need to create a data adapter for each database table you want to read/write from and a commandbuilder object for each database table that you want to actually update. Here is my public database connection class for an Access database. It also assumes the database is named 'master.mdb' and in a subdirectory named 'data'. public class dbMaster { public OleDbConnection m_master; public dbMaster() { string strStartupPath = Application.StartupPath; // comment these two lines out before deployement int intStartupPathLength = strStartupPath.Length; string strdbPath = strStartupPath.Remove(intStartupPathLength - 10, 10); string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strdbPath + "\\data\\master.mdb"; m_master = new OleDbConnection(strConnectionString); } }

I pull a string from one table and look for a matching string in another table. In this case, the string I pull from the first table is: G_App_Attorney'sBriefcaseCaliforniaEvidence I strip the first 6 chars from the string (G_App_) and search for the matching string: Attorney'sBriefcaseCaliforniaEvidence If I strip the apostrophe from the first string, then it will not match the second string. How is this situation typically handled?

I'm attempting to allow the .NET Garbage Collector to handle as much as possible automatically, but I know there are unhandled objects like filestreams and database connections. If the following function is called from the Form_load event, would I need to do any garbage collection? public string getlastMasterID() { // create dataset in memory DataSet ds1 = new DataSet(); ds1.Tables.Add("idTable"); // initialize the master data connection object dbMaster masterdb = new dbMaster(); OleDbConnection master = masterdb.m_master; // create and initialize data adapter object string stridSelect = "SELECT * FROM idTable"; OleDbDataAdapter idAdapter = new OleDbDataAdapter(stridSelect, master); // fill the table in memory from the database table idAdapter.Fill(ds1, "idTable"); // determine the last master id number and the last temp asset id DataRowCollection drc = ds1.Tables[0].Rows; string strlastMasterID = drc[0][1].ToString(); return strlastMasterID; }

I have a table whose sole purpose is to store the last masterID and lastTempID numbers used in other tables. So, the table only has a single row of data two columns wide. The first column holds the lastMasterID and the second column holds the lastTempID. The application contains various functions that process hundreds of thousands of rows of input tables. These functions might need to get and set the lastMasterID or the lastTempID depending on whether or not new data is found in the input tables. What is the best / slickest way to setting this up? I was simple making two public functions called getlastMasterID and getlastTempID that returns the value of the corresponding field. However, I think a public class that allows me to get and set in the same function might be more efficient (if I am using the correct terminology). Either way, do I simply create the necessary connection and adapter objects and populate a dataset using the adapter's Fill method? Or is that way too much overhead for what I need to do?