[RESOLVED] Update command in vb.net,asp.net
hi Everyone,
My program is doing good when I suddenly bumped into this problem.
I dont know how to do the next functionality that I need to accomplish.
I am done with inserting values, showing the gridview etc and it works great. Now my problem is the update command.
I have 2 tables
table:tblDest
Fields:
destLastName
destFname
destEmail
Table:tblSource
Fields:
Sourcelastname
SourceFname
SourceEmai
Now, when I run my update command in sql server, it works great! I just right click the database, click new query and i run this command:
update tblDest set DestEmail =(Select SourceEmail from tblSource where tblSource.SourceFname = tblDest.DestFname and tblSource.SourceLastname = tblDest.DestLastname)
what the query does, is it updates the table tbldest email where lastname and firstname exists in both tables that are equal,
the question is, I want to automate this, I am done with inserting values, the problem is, I dont know how to run the update command,
I tried doing this:
Dim sqlds As New SqlDataSource
sqlds.ConnectionString = ConfigurationManager.ConnectionStrings("sqlconnect").ConnectionString
sqlds.ProviderName = ConfigurationManager.ConnectionStrings("sqlconnect").ProviderName
sqlds.UpdateCommand = "update tblDest set DestEmail =(Select SourceEmail from tblSource where tblSource.SourceFname = tblDest.DestFname and tblSource.SourceLastname = tblDest.DestLastname)"
DestGrid.DataSource = sqlds
DestGrid.DataBind()
but nothing happened.
I want to thank you guys in advance for any help.
Re: Update command in vb.net,asp.net
SQL uses this format:
Code:
UPDATE table
SET column = expression
WHERE predicates;
http://www.techonthenet.com/sql/update.php
Re: Update command in vb.net,asp.net
Re: Update command in vb.net,asp.net
two things....
#1 : Have you checked the tables to see if the update has occured?? The command should complete regardless of the Where part of the statement. It's quite posible that tblSource is not been updated in the first place. and hence wont update tblDest ....
#2 : the code you end off with
Code:
DestGrid.DataSource = sqlds
DestGrid.DataBind()
will not work as you intend it too, as an update command does not return a dataset. however i also noticed that you've given the SqlDataSource an Update String but have not told it to execute it...
so your code should look a little like this..
Code:
Dim sqlds As New SqlDataSource
sqlds.ConnectionString = ConfigurationManager.ConnectionStrings("sqlconnect").ConnectionString
sqlds.ProviderName = ConfigurationManager.ConnectionStrings("sqlconnect").ProviderName
sqlds.UpdateCommand = "update tblDest set DestEmail =(Select SourceEmail from tblSource where tblSource.SourceFname = tblDest.DestFname and tblSource.SourceLastname = tblDest.DestLastname)"
sqlds.Update
Re: Update command in vb.net,asp.net
Thank you GremlinSA for the detailed explanation.