problem with update sql statement.

[tiffany]

Hello! i'm using ms sql. I had some problem with my update sql statement. Can help me with it? Thanks! There is a syntax error near ')'.

Dim ds As New DataSet
Dim da As New SqlDataAdapter("UPDATE msg SET topicID =(SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID) WHERE (SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID)", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds

Can tell me what wrong with the code? When i debug the page, there is no error. But when i click on the button it show that error.

Thanks!
Tiffany.

 

[jmcilhinney]

I think you'll find that it's the WHERE clause in your main statement. A WHERE clause is supposed to be a series of "Column = Value" expressions but your is just a query. I'm guessing that what you actually want is something like "WHERE topicID IN (SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID)". I'm not 100% sure but you may not be able to assign the result of a query to the topicID field either.

 

[jmcilhinney]

Moved to more appropriate forum.

 

[tiffany]

Hi, thank for you reply. Actually for the update statement that i had problem with, initially i wanted to get a copy of "topicID" from Table1 and copy to Table2.

The process goes like this: Once the button is being clicked it will insert the values into Table1 with topicID and copy(update statement) the topicID from Table1 to Table2.

But the update sql statement giving me an syntax error mear ")".

Dim ds AsNew DataSet
Dim da AsNew SqlDataAdapter("UPDATE msg SET topicID =(SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID) WHERE (SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID)", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds


Regards,
Tiffany

 

[abhiramvikrant]

I think you are missing column name in WHERE clause .It should be like

UPDATE msg SET topicID =(SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID) WHERE <some colum name= or in> (SELECT thread.topicID FROM thread WHERE thread.userID = msg.userID)", cn)