Visual Basic

13-Nov-07 11:01

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?

pmarfleet13-Nov-07 11:41

13-Nov-07 11:41

What is your first data source, the one you are accessing via an OLEDB connection? You might be able to set it up as a linked server in your SQL Server, allowing you to run queries that directly reference both databases.

Have a look at this article[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433114-Nov-07 8:39

14-Nov-07 8:39

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.

pmarfleet14-Nov-07 9:31

14-Nov-07 9:31

Your update query syntax isn't correct. Your query should look something like this

update amyexcel...allanis$
set street=addresslist.street,
city=addresslist.city,
state=addresslist.state,
zip=addresslist.zip
from masterdb.dbo.addrlist addresslist
where amyexcel...allanis$.phone=addresslist.phone

I have assumed that dbo is the owner of the addrlist table.

Try this and let me know how you get on.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433114-Nov-07 10:00

14-Nov-07 10:00

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.

pmarfleet14-Nov-07 10:12

14-Nov-07 10:12

Change your query to

update allanis
set street=addresslist.street,
city=addresslist.city,
state=addresslist.state,
zip=addresslist.zip
from amyexcel...allanis$ allanis 
inner join masterdb.dbo.addresslist addresslist
on allanis.phone = addresslist.phone

Does this work?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433114-Nov-07 10:27

14-Nov-07 10:27

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.

pmarfleet14-Nov-07 10:35

14-Nov-07 10:35

JABOSL wrote:
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.

Unfortunately, GIGO is a fact of life for those of us who have to deal with data from end users Smile | :)

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433114-Nov-07 10:55

14-Nov-07 10:55

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.

Pankaj - Joshi13-Nov-07 23:56

Pankaj - Joshi

13-Nov-07 23:56

This is only possible when you are having both table in sql server.

Regards
Pankaj Joshi

cj433114-Nov-07 8:22

14-Nov-07 8:22

Thanks.

Time Picker

AAGTHosting13-Nov-07 9:30

AAGTHosting

13-Nov-07 9:30

Is there a way to do a week day time picker with VB.NET?

I dont want to do the date, just time. I want the user to be able to select from a range of times such as 8:00 AM - 10:00 PM on a certain day of the week like Monday or Tuesday. I figured I could do it using a combo box, but if there is a function that will use a time and day picker id use it.

I know there is a date time picker, will that do it?

Re: Time Picker

Christian Graus13-Nov-07 10:28

Christian Graus

13-Nov-07 10:28

There is a date picker, there is no time picker. You'll have to write one, or look for a third party one ( for example in the articles on this site )

Christian Graus - Microsoft MVP - C++

"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Re: Time Picker

Tom Deketelaere13-Nov-07 20:59

Tom Deketelaere

13-Nov-07 20:59

wouldn't the datetime picker be enough for this?

If you set the format property to time you get a timepicker you will have to put the showupdown property to true so that you don't see the dropdown calender.

if you set it to custom and insert 'dddd' into the customformat property you get the weekday but the only way to change this is with the dropdown calender so it might be better to use a combobox for this.

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistakes.

Buttons

roger689713-Nov-07 8:47

roger6897

13-Nov-07 8:47

I have a button that when clicked the total of set of figures in a listbox is displayed in a textbox. How can I disable that button so that if there is nothing in the listbox and it is clicked, my program does not crash, and it is enabled again when there are contents in the listbox.

pmarfleet13-Nov-07 9:18

13-Nov-07 9:18

listbox.Items.Count will give you the number of items in the listbox.

button.Enabled enables/disables the button.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

dexter970313-Nov-07 21:39

dexter9703

13-Nov-07 21:39

index = listbox1.SelectedIndex
listbox1.Items.RemoveAt(index)

The above code is used to remove an item from the listbox. I have tried listbox1.items.count but program still crashed when button was clicked and there were no items in the listbox

pmarfleet13-Nov-07 21:46

13-Nov-07 21:46

It's pretty obvious that this code isn't going to work if there are no items in the listbox.

listbox1.SelectedIndex will be -1 if there are no items in the list. Don't execute the second line of code if this is the case.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

dexter970314-Nov-07 0:02

dexter9703

14-Nov-07 0:02

Is there a line of code I can use then if

index = listbox1.SelectedIndex
listbox1.Items.RemoveAt(index)

is used, so that the program does not crash when index =-1

I tried using an if statement:
if listbox1.selectedindex =-1 then
msgbox(" "),
but it still crashed.

pmarfleet14-Nov-07 0:06

14-Nov-07 0:06

You should check the details of the error and amend your code accordingly.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Database connection

s3rro13-Nov-07 8:30

s3rro

13-Nov-07 8:30

I want to connect to access database, and check if the entered pin numbers and the ones in the database match or not.

Public Function mdbValidatePin(ByVal mailbox As String, ByRef retcode As String) As DB_RETURN_CODE

'open database and check/validate the PIN.

mdbValidatePin = DB_RETURN_CODE.MDB_SUCCESS

End Function

But I am not sure how to open and check/validate the pin from the database. Can anyone help me with it?

Thanks

Re: Database connection

pmarfleet13-Nov-07 9:15