build a cbo box - Database Discussion Boards

Tristan Rhodes10-Nov-04 10:49

10-Nov-04 10:49

I have a bizzare problem with an SQL query I am trying to build. The problem is, I have a query with a conditional WHERE EXISTS statement that modifies every value in my table, but the SELECT part of the statement returns only the correct, single instance.

This is done in access, in VB, and any help would be greatly appreciated, the queries are listed below.

This select statement returns 1 instance:

SELECT SubCategory.SubCategoryDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)

But this update command turns every entry in my table into str_New:

UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
WHERE EXISTS (SELECT SubCategory.SubCategoryDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old))

Any ideas why this occurs? It's driving me NUTS!

Cheers

Cata

Re: Bizzare SQL Query Problem

Christian Graus10-Nov-04 11:28

Christian Graus

10-Nov-04 11:28

Well, the exists clause will return true all the time, so every row is updated.

I don't know enough about the Access subvariant of SQL to provide a definitive solution, but I'd have thought that this would work:

UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)

otherwise, this may work

UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
WHERE SubCategory.CategoryKey IN
(
SELECT SubCategory.CategoryKey
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)
)

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Re: Bizzare SQL Query Problem

Tristan Rhodes11-Nov-04 6:06

Tristan Rhodes

11-Nov-04 6:06

Had another crack with those two, but still getting errors.

Type Mismatch on JOIN expression:

str_SQL = "UPDATE SubCategory SET SubCategory.SubCategoryDesc = '" & str_New & "' " _
& "WHERE SubCategory.CategoryKey IN " _
& "(SELECT SubCategory.SubCategoryDesc FROM SubCategory " _
& "INNER JOIN Category ON Category.CategoryKey = SubCategory.CategoryKey " _
& "WHERE ((Category.CategoryDescription)= '" & GetCurrCategory & "') " _
& "AND ((SubCategory.SubCategoryDesc)= '" & str_Old & "'))"

Missing Operator in Query "FROM Category ..... = Subcategory.CategoryKey":

str_SQL = "UPDATE SubCategory " _
& "SET SubCategory.SubCategoryDesc = '" & str_New & "' " _
& "FROM Category " _
& "INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey " _
& "WHERE ((Category.CategoryDescription) = '" & GetCurrCategory & "') " _
& "AND ((SubCategory.SubCategoryDesc)= '" & str_Old & "')"

I've played around with it, but still can't get it to work. Any more ideas?

Cheers

Cata

Never Mind - Solved It

Tristan Rhodes11-Nov-04 8:30

Tristan Rhodes

11-Nov-04 8:30

Never mind Smile | :)

Thanks for the help, it was pretty much spot on.
Needed to be using SubCategoryKey rather than the CategoryKey. One was specific, the other one was general.

Cheers again

Cata

Re: Never Mind - Solved It

Christian Graus11-Nov-04 9:03

Christian Graus

11-Nov-04 9:03

No worries, glad to help Smile | :)

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Re: Bizzare SQL Query Problem

Michael Potter11-Nov-04 10:16

Michael Potter

11-Nov-04 10:16

There is no correlation between the queries in the update statement. No matter which row the SQL Server tests, the EXISTS statement will always resolve to true and therefore, update the row.

I don't think the EXISTS statement is a good solution for this problem (although you could make it work). Try the IN statement:

UPDATE 
     SubCategory 
SET 
     SubCategory.SubCategoryDesc = str_New
WHERE 
     SubCategory.SubCategoryDesc IN
          (SELECT 
               SubCategory.SubCategoryDesc 
           FROM 
               Category 
           INNER JOIN 
               SubCategory 
               ON Category.CategoryKey = SubCategory.CategoryKey
           WHERE 
               ((Category.CategoryDescription)= GetCurrCategor()) AND
               ((SubCategory.SubCategoryDesc)= str_Old))

Excel data type problem

@LX10-Nov-04 4:42

@LX

10-Nov-04 4:42

Hi,
In an excel sheet, I have a column (text format), but if I try open this using ODBC driver, this column is open as double data type. Exist any way to change the data type for each column?

I don't want to use a TEXT function from excel, this resolve my problem, but I want another way to chnage it.

Thank you.

ALX

build a cbo box

skicrud10-Nov-04 3:53

skicrud

10-Nov-04 3:53

What am I doing wrong? I do not get any output

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
OleDbConnection1.Open()

OleDbDataAdapter1.SelectCommand.CommandText = "SELECT Location, HotelID FROM Hotel"
OleDbDataAdapter1.SelectCommand.ExecuteNonQuery()
Do

ComboBox1.Items.Add(OleDbDataAdapter1.SelectCommand.CommandText)

Loop
OleDbConnection1.Close()
End Sub

Re: build a cbo box

Mekong River10-Nov-04 4:00

Mekong River

10-Nov-04 4:00

skicrud wrote:
What am I doing wrong? I do not get any output

Can you tell use, what do you want to do?

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: build a cbo box

Gavin Jeffrey10-Nov-04 5:08

Gavin Jeffrey

10-Nov-04 5:08

Dont exactly know what you are trying to do here but shouldn't you instead of putting executeNonQuery() put in executeReader()??
I dont know what the do loop is for either??
Try filling a datatable or dataset with you adapter i.e oledbDataAdapter1.fill(myDatatable)
then use your datatable to fill your comboBox ie.
comboBox1.datasource = myDataTable
comboBox1.valueMember = "hotelID"
comboBox1.displayMember = "Location"
ComboBox1.DataBind()

Something like that anyways.

Urgent about connection to MSDE 2000

wk_vigorous10-Nov-04 3:26

wk_vigorous

10-Nov-04 3:26

Hi, guys.

This is an urgent problem that I can only conncet to MSDE 2000(release A, not sp3 or sp3a) from local computer but can not connect to the one running on remote PC. Every time such information is prompted:"SQL Server doesn't exist or access denied."

By the way, the SQL Server service manager running on my PC can start or stop MSDE on both local and remote PC.

Help please. Thanks.

vigorous

Re: Urgent about connection to MSDE 2000

Mekong River10-Nov-04 4:06

Mekong River

10-Nov-04 4:06

wk_vigorous wrote:
This is an urgent problem that I can only conncet to MSDE 2000(release A, not sp3 or sp3a) from local computer but can not connect to the one running on remote PC

What is the version of your windows operating system? Does your windows install any service pack?

wk_vigorous wrote:
SQL Server doesn't exist or access denied

Try to check the information from here[^] and google.

wk_vigorous wrote:
This is an urgent problem that I can only conncet to MSDE 2000(release A, not sp3 or sp3a) from local computer but can not connect to the one running on remote PC

Try to check the connection library.[^]

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: Urgent about connection to MSDE 2000

wk_vigorous10-Nov-04 5:53

wk_vigorous

10-Nov-04 5:53

The operating system is xp sp2. I thought maybe the firewall block relevant port but even i close the firewall i still can't connet to MSDE.

vigorous

Re: Urgent about connection to MSDE 2000

Richard Deeming10-Nov-04 7:42

Richard Deeming

10-Nov-04 7:42

This is probably an SP2 issue. Have a look at MS KB 841249[^] - How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server.

Another problem I have seen is if all protocols are disabled. Run the server network utility (svrnetcn.exe), and make sure you have at least one protocol in the "Enabled protocols" list.
http://www.trinet.co.uk/kb.asp?kbid=25[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Re: Urgent about connection to MSDE 2000

wk_vigorous10-Nov-04 22:56

wk_vigorous

10-Nov-04 22:56

Thank you very much.

It's because the protocol is disabled. Now MSDE could be connected after enable the protocol.

vigorous

A couple of SQLXML issues

RichardGrimmer9-Nov-04 22:03

RichardGrimmer

9-Nov-04 22:03

Hi there guys,

Have a couple of issues to do with SQLXML on SQL2K...

Firstly, have created a new stored procedure that I wish to expose as a SOAP method. Have added the sproc and can execute within QA, but when I come to add it to the virtual names in SQLXML, when browsing the list of available sprocs, it's not there....have tried all sorts of ways to "force" it to appear in the lsit, but to no avail....On the one occasion I did manage to get it sorted, when calling the method, my app just hung. Completely. Totally....any ideas?

Second probelm seems to be that with an OCX on an ASP page, when calling ANY SOAP method exposed by SQLXML, the session is destroyed, and hence all the session variables....again - any ideas?

Cheers guys.....

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

A custom key

totig9-Nov-04 21:56

totig

9-Nov-04 21:56

Sorry, could not think of a better subject line for this. I need an int stored in my table that is sequential, but will fill in any missing numbers. By this I mean if I have 4 records, these will be numbered 1,2,3,4. If I delete row 3, the next row to be inserted should use row 3. I just need some advice on how to do this.

Re: A custom key

Mekong River10-Nov-04 4:14

Mekong River

10-Nov-04 4:14

I never code this style but this is what I just think and express my concept to you.

toticow wrote:
if I have 4 records, these will be numbered 1,2,3,4. If I delete row 3, the next row to be inserted should use row 3.

First make sure that you have have the table structure and the first field of your table is set to number (data type) and order it by ascending. When you delete any record from your database, you have to requery your table and order the first field. Then you have to count the total amount of your record in the database, after that make a loop from the first row to the second, third... until the end of row (the amount of loop process is base on the total number of record in the database). And in the each loop process you have to change the value of the first field to match the amount of loop proces. This might help you to solve the problem, if you still have the problem I will try to do it and post the code for your.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: A custom key

totig10-Nov-04 19:26

totig

10-Nov-04 19:26

Hi, firstly I agree with you about coding in this style. I always use auto numbers. This table in fact has one, but this other id is actualy for a memory location. We have a device with a limited amount of memory, that can only be written to by memory location. This table will always be small, less than 100 records, but deletions and additions will occur, which prevent me using the primary key. I am probably wrong, but I thought that this would be the best way to do it. I am in the process now of trying your suggestion. Thank you

Re: A custom key

Colin Angus Mackay10-Nov-04 6:53

Colin Angus Mackay

10-Nov-04 6:53

You could create a second table which contains a list of deleted numbers. If the table is empty the the next number you use is MAX(numberColumn)+1.

In order to populate the deleted numbers table you can create a delete trigger on the original table and insert the relevant row. When you are inserting a new record just take a number from the deleted numbers table and use that - remember to remove it from the deleted numbers table. You could create an insert trigger to do this for you.

However, this will still leave gaps in your sequence if you delete a row and don't insert one afterwards.

Do you want to know more?

How to update record

Mekong River9-Nov-04 17:20

Mekong River

9-Nov-04 17:20

Hi, I use the following statment in VB.Net to add new record in my database:

INSERT INTO tblSellTo (ProdID, CusName) VALUES(1,'abc')

and delete the record from the database using the following statement:

DELETE FROM tblSellTo WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc'

Both of these statement are working fine!!!

But my problem is occure when I want to update the record. Suppose that I have Product ID =1 and customer name is 'abc' and I want to update the information to Product ID =1 and customer name is change to 'wkl' and I use the following code:

INSERT INTO tblSellTo (ProdID, CusName) VALUES(1,'wkl') WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc'

But it is not success, it display an error message that missing character ; at the end of sql statement. But when I try to change the code to:

INSERT INTO tblSellTo (ProdID, CusName) VALUES(1,'wkl') WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc';

It still the same, it is not working. Does anyone have any idea about solveing this problem? Cry | :((

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: How to update record

Mekong River9-Nov-04 17:46

Mekong River

9-Nov-04 17:46

Bingo, now it is working. I use the following statement:

UPDATE tblSellTo SET tblSellTo.ProdID =1, tblSellTo.CusName ='wkl' WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc'

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

ado and radio button

skicrud9-Nov-04 6:06

skicrud

9-Nov-04 6:06

I have an access db with a boolean field called manager.
When my vb form loads it populates a combo box with user names from the same table.
As I select a user I need to have the appropriate radio button checked, either manager if true or employee is false, Cry | :((

based on the value of manager in the DB

Any help would be appreciated

Re: ado and radio button

Mekong River9-Nov-04 14:28

Mekong River

9-Nov-04 14:28

skicrud wrote:
When my vb form loads it populates a combo box with user names from the same table.

If it is possible, please post the structure of your table on this forum. I used to do the similar project like you too and my solution is add the other field beside the user name. The field could be boolean (Yes/No) or list of text to describe the function of each people. Remember to select the user name and function of the people in to recordset. Then display only the name in the combobox and the other field is hidden and just analayse only for your radio button.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

DAO and transactions

calliscope9-Nov-04 5:34

calliscope

9-Nov-04 5:34

Mon problème est de créer une transaction sous ACCESS 2003 en VB comprenant deux requêtes:

- une requête de suppression d'un enregistrement dans une table distante liée par un pont ODBC
- la suppression du même enregistrement dans la table locale liée au formulaire sur lequel est placé le bouton qui lance la routine suivante:

Un warning s'affiche à l'éxécution de la suppression de l'enregistrement courant:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

En répondant NON à ce warning : "voulez vous supprimer...", une erreur se produit(jusque là tout est normal),

Alors le wks.Rollback devrait restaurer la suppression effectuée (rst.Delete) or il n'en est rien
Ce qui désynchronise mes deux bases.

pourquoi le rollback ne s'effectue pas?

voici mon code:

'macro du formulaire lié à la table locale membres
Private Sub ButDel_Click()
'** Bouton Supprimer
Dim TransStarted As Boolean
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim rst As DAO.Recordset

'Creation du workspace
Set wks = DBEngine(0)
'début de la transaction
wks.BeginTrans
TransStarted = True

'base courante
Set db = wks(0)

'selection de l'enregistrement à supprimer dans la table fiches_annuaire(table liée ODBC)
sqltext = "select * from fiches_annuaire where code = " & Me.ID

Set rst = db.OpenRecordset(sqltext, dbOpenDynaset)

On Error GoTo ErrDel

rst.MoveFirst ' goto the first record that needs to be inserted
'suppression de l'enregistrement(table liée ODBC)
rst.Delete

'supression de l'enregistrement dans la table liée au formulaire
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "base locale mise à jour", vbOKCancel

'on termine la transaction
If TransStarted Then
wks.CommitTrans
rst.Close
wks.Close
Set wks = Nothing
End If
TransStarted = False

GoTo endsub

ErrDel:
'PopupError
message = "Une erreur est survenue. Pour assurer la synchronisation des deux bases, aucune suppression n'a été effectuée " & vbCrLf & _
"Veuillez recommencer ultérieurement"

glyph = vbExclamation
Title = "Transaction annulée"
'on annule la transaction

If TransStarted Then
wks.Rollback
rst.Close
wks.Close
Set wks = Nothing
MsgBox message & "rollback?", glyph, Title
Else
MsgBox "pas de rollback!", glyph, Title
End If

TransStarted = False
GoTo sql_error_exit:
'Resume Next

sql_error_exit:
Exit Sub

endsub:
On Error GoTo 0

End Sub

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.