update [QuiTry].[dbo].[qusr] set res=
case when a.qans=b.uans
then 1
else 0 END
FROM [QuiTry].[dbo].[quiz] a,[QuiTry].[dbo].[qusr] b
but then for this I have to execute it everytime after data being entered by user .
So,for this reason I thought to write it within the code of vb.net page so that it will get updated automatically and there will be no need of going back to sql query section of databse and re-executing again and again.
I wrote the above query in vb.net and with those sqlcommand and con and it doesn't show error but its not working .
And I also tried with Sqldatasource on form ,I wrote update query in it and it got executed .
But,Later on when I click the button on web page then the res column doesn't get updated.
It just shows NULL for each row in res column.
Then after refreshing again and again now it shows Whole column of row as '1'.
That is actually wrong.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
con.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
con.Open()
cmd.CommandText = "update [QuiTry].[dbo].[qusr] set res= case when a.qans=b.uans then 1 else 0 END FROM [QuiTry].[dbo].[quiz] a,[QuiTry].[dbo].[qusr] b"
cmd.Connection = con
cmd.ExecuteNonQuery()
End Sub
When I am using sqldatasourcce,I don't use this code.
As both together does'n make sense.
but then for this I have to execute it everytime after data being entered by user .
So,for this reason I thought to write it within the code of vb.net page so that it will get updated automatically and there will be no need of going back to sql query section of databse and re-executing again and again.
Use trigger to execute the update command whenver entry will be added/updated to qusr table.
Run this query into your database, than you will not need to run this command ever, it will automatically call on action.
CREATE TRIGGER TriggerName
ON [QuiTry].[dbo].[qusr]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
update [QuiTry].[dbo].[qusr] set res=
case when a.qans=b.uans
then 1
else 0 END
FROM [QuiTry].[dbo].[quiz] a,[QuiTry].[dbo].[qusr] b
END
IAmateur
Member
96 Points
388 Posts
SqldataSource
Apr 13, 2012 05:40 AM|LINK
I had written this in sql query database table :
update [QuiTry].[dbo].[qusr] set res=
case when a.qans=b.uans
then 1
else 0 END
FROM [QuiTry].[dbo].[quiz] a,[QuiTry].[dbo].[qusr] b
but then for this I have to execute it everytime after data being entered by user .
So,for this reason I thought to write it within the code of vb.net page so that it will get updated automatically and there will be no need of going back to sql query section of databse and re-executing again and again.
I wrote the above query in vb.net and with those sqlcommand and con and it doesn't show error but its not working .
And I also tried with Sqldatasource on form ,I wrote update query in it and it got executed .
But,Later on when I click the button on web page then the res column doesn't get updated.
It just shows NULL for each row in res column.
Then after refreshing again and again now it shows Whole column of row as '1'.
That is actually wrong.
Can somebody tell me the solution
Time to go Long way...
basheerkal
Star
10672 Points
2426 Posts
Re: SqldataSource
Apr 13, 2012 06:26 AM|LINK
Show Your code please.
(Talk less..Work more)
IAmateur
Member
96 Points
388 Posts
Re: SqldataSource
Apr 13, 2012 06:32 AM|LINK
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
con.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
con.Open()
cmd.CommandText = "update [QuiTry].[dbo].[qusr] set res= case when a.qans=b.uans then 1 else 0 END FROM [QuiTry].[dbo].[quiz] a,[QuiTry].[dbo].[qusr] b"
cmd.Connection = con
cmd.ExecuteNonQuery()
End Sub
When I am using sqldatasourcce,I don't use this code.
As both together does'n make sense.
Time to go Long way...
yrb.yogi
Star
14460 Points
2402 Posts
Re: SqldataSource
Apr 13, 2012 09:30 AM|LINK
Use trigger to execute the update command whenver entry will be added/updated to qusr table.
Run this query into your database, than you will not need to run this command ever, it will automatically call on action.
.Net All About
IAmateur
Member
96 Points
388 Posts
Re: SqldataSource
Apr 13, 2012 11:12 AM|LINK
It worked thanku v much yrb.yogi :)
Time to go Long way...