It would seem if I want to update a record I will have to do it with a custom sql command for each record I want to update. As there is no key fields in the excel table I have no idea how to specify and update command either. I guess that is why the command builder couldn't come up with anything. I thought about deleting all the records in the excel database and writing back all the modified records from the datatable but then I read that the jet engine would not allow me to delete records from an excel db so I didn't even bother to try. My only choice now as far as I can see is remote control of excel or building another excel table and adding the records to that. I do not wish to do remote control of excel. It all would have been so much easier if I could have stayed connected to the excel spreadsheet read the record looked up the necessary data and written changes to the records then done a skip to get the next record and repeat. :sigh:

I load an excel spreadsheet via oledb driver into a datatable. I fill in some blank fields. Now I want to save the changes. I tried using a commandbuilder and issuing myExcelAdapter.Update(myExcelDt) but I got an error that it couldn't generate an update command for a table with out a key field. No new records were added and none were deleted. I just want to overwrite or update the spreadsheet with the copy in the datatable as it has some fields that were blank filled in now. How is the best way to accomplish this?

I'm pretty sure they wouldn't support me going to 2008 quite yet around here. Can I install 2008 on the same pc as 2005 and 2003 and not affect them? I have some program in 2003 and some in 2005. For any major changes to a 2003 program I move it to 2005.

Yes, I understand the while end while now. Somehow I was thinking that was to get each of the fields and not each row as I know there will only be one row the need for a while loop hadn't crossed my mind until after I read the code but it popped in as I though about the future so I added it into my response to you. I'm interested in .net 3.0 but that opens another line of questions. How do I go about using it in VB2005? I'm pretty sure I can but...

Thanks for the code. I'll play with it. The object of yesterdays question was to add the name and address associated with each of the phone numbers in that excel spreadsheet to the spreadsheet for our accountant. The object now is for me to know how to retrieve, update and otherwise manipulate data in ways I think I'll need to use in the future. I can only imagine I'll be asked many times in the future to get, update, add or delete data from a table or most likely multiple tables and I'll have to use VB. I just don't feel as ready to do that as I could in say Visual FoxPro. It's surprising when something like this comes up and I'm thinking hey I really haven't done that before. My VB database access to date has been with stored procedures and most of my heavy database work has been in Visual FoxPro as so, so, so much of our data is in VFP tables. But times are a-changing and we want to move away from VFP when and if we can. I'm already supposed to be writing a web service to lookup customer data. This customer data will come from multiple, you guessed it, VFP databases. So I'm learning all I can about accessing databases from VB using OleDb and Sql connections. Frankly I think my intimate knowledge of old dbase type databases and their access methods is preventing me from grasping how this is done in VB. It seems so much more complicated and inflexible. So I'm wondering what if the results of the query contain more than one record? Will this ExecuteReader accept that? I'll start playing with it and see what I can figure out. Thanks for responding again. P.S. I didn't mean to address the question to you personally I just didn't want you to see it and say, that's the same thing I helped him with yesterday.

Paul you helped me complete the mission using the excel spreadsheet as a linked server and a single sql query run on the sql server but I would like to know how to do this in VB now too. I feel I need to be able to do things like this in VB quickly and easily and I'm just not there yet. I'm hoping someone can help me understand how this can be done in VB. I've loaded the excel spreadsheet via oledb into a datatable. Every record in this sheet needs name and address info added to it (the fields are there but are blank). I'm looping through the datatable and looking up required data in a sql server database. I need some help reading in the values returned by the query. I assume I have only 3 options ExecuteReader to return a DataReader object, ExecuteScalar to return a single value, and ExecuteNonQuery which returns nothing. I've never worked with ExecuteReader before. Is this the simpliest way to retrieve a couple pieces of data from a database query? See code below. If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then Dim filename As String = MyOpenFileDialog.FileName Dim myExcelDt As New System.Data.DataTable Dim myExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & "; Extended Properties=""Excel 8.0""") Dim myExcelSqlCommand As String = "SELECT * FROM [all ANIs$]" Dim myExcelAdapter = New System.Data.OleDb.OleDbDataAdapter(myExcelSqlCommand, myExcelConnection) myExcelAdapter.fill(myExcelDt) Dim mySqlDt As New System.Data.DataTable Dim Sql2000Connection As New System.Data.SqlClient.SqlConnection(Sql2000ConnectionString) Dim Sql2000Command As New SqlClient.SqlCommand Sql2000Command.Connection = Sql2000Connection For Each excelrow As DataRow In myExcelDt.Rows Sql2000Command.CommandText = "select fullname, address, city, state, zip from addresslist where phone = '" & excelrow("phone") & "'" **** need help here **** Next End If

Yes, I also noticed some of the phone numbers they gave me to match up had letters in them! I don't know how they got that--must have been watching to much HeeHaw (BR549). I didn't say anything just let it go. Thanks for all your help.

Yes, Thanks! I was afraid it'd come to joins. Tomorrow maybe I can figure out exactly how that works. Next I think I'll send the results to the person who requested them. I'm sure they will not be happy as only 135 of the 4,000 records had matches. Hey, I can only work with the data I'm given.

First I got: Server: Msg 117, Level 15, State 2, Line 8 The number name 'amyexcel...allanis$' contains more than the maximum number of prefixes. The maximum is 3. So I changed it to: update amyexcel...allanis$ set street=addresslist.street, city=addresslist.city, state=addresslist.state, zip=addresslist.zip from masterdb.dbo.addresslist addresslist where allanis$.phone=addresslist.phone but now it gives me: Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'allanis$' does not match with a table name or alias name used in the query.

I toyed with that for a couple hours this AM. It didn't like the name of the sheet in excel but after fixing that I have the table excel sheet added just like the article. Now if only I could get my command to work. update amyexcel...allanis$ set street=addrlist.street set city=addrlist.city set state=addrlist.state set zip=addrlist.zip from masterdb.addrlist where allanis$.phone=addrlist.phone I wish to update the street, city, state and zip fields in the excel spreadsheet/table allanis$ with the same fields from the addrlist table which is in the masterdb database. Matching them up by the phone field. I'm not a sql expert either and if you could help with this I'd appreciate it. select top 5 * from amyexcel...allanis$ works from where I'm sitting in SQL query analyzer. I'm not sure how to specify that addrlist is in the master db and I think this is why I'm not getting things to work. How do I get it to understand that allanis$ and addrlist are in different databases? Thanks.

Thanks.

I have two databases I open. The first is a list of customer numbers I have to open in an oledb connection. The second is the master customer table with customer number and address info--this is opened with a sqldb connection. The fields in both are the same in respects to the data I'll be accessing. I need to somehow add the address info to the first table from the second. I'm wondering if there is anyway to do something like: update oletable set oletable.street=sqltable.street set oletable.city=sqltable.city set oletable.state=sqltable.state set oletable.zip=sqltable.zip where oletable.cust_no=sqltable.cust_no doubt my syntax is correct but you see what I wish to do. I'm just not sure if this is even possible since the two tables resite in different databases and the first can only be opened via oledb connection. Can anyone help?

Ok, I just spoke with my boss and he's still deciding on what monitoring package to use so I kind of need to wait until I see what it is capable of reading now. I appreciate all your help.

Is the performance counter the same thing as WMI?

I have a TCP/IP server (VB .net 2.0 Windows App) that accepts connections from clients receives a string from them and passes back an answer. The TCP/IP Server creates a thread for each connection and that thread handles that connection then terminates. Each of these connection threads increments a counter class when it starts and decrements it when it finishes. The main thread in the TCP/IP server program queries the value of the counter class periodically to see how many threads are being handled at that time. This is working perfectly and has been for well over a year. Now, I need to share the value of this counter with other programs. In specific we are going to start using Microsoft's System Center Manager to monitor and alert of potential problems etc. After a discussion with my Boss it is clear that he would like a way to reach into my program and pull this value vs. having my program write it to an environment variable or even a file that could be read. I think this should be doable but I'm not sure exactly what I should be looking to do. Keep in mind the TCP/IP server is a Windows App. Any advise would be appreciated.

Can someone tell me what the caspol command would be to "My Computer / Runtime Security Policy / Machine / Code Groups / All_Code / LocalIntranet_Zone" to "FullTrust"???? Something like: Caspol -cg localintranet_zone fulltrust perhaps????? I don't want to screw this stuff up. Thanks

The directory was indead renamed. Somehow it got renamed back. I can't explain. I rebooted the pc and then renamed it again and this time it worked.

I needed to start project test over. I did what I always do in VB 2003. I closed VB and renamed the directory the project was in, test, to oldtest. I then opened VB and told it to create a new project called test. It told me it could not because there was already a project by that name?????? Somehow it found the project even as I tried to prove to it it didn't exist any more by clicking test in the recently used projects section and after a long wait it came up!!! I looked in explorer and it had renamed the directory oldtest back to test. Damn it!!!! How do I delete a project?!

.net 2003 I have a form with 1 button and several labels on it. I don't want the button to be the default if enter is pressed. I want the user to have to actually click it for it to work. I also don't want it to have that "selected" look with the dotted line around the text. What can I do about all this? Thanks.

Thanks!