SQL Exam Updates?????

Amberrosereynolds · #1 (**permalink**) 01-18-12, 04:57

Hello, Yesterday I asked... "My company have created a website where employees will be able to take a test to earn points (CPD hours) which will be updated every time an employee takes the test. I am trying to find a way to update the number of CPD hours each employee has earned by using SQL, and displaying this on their profile page. The number of CPD hours awarded will need to be updated every time they earn more points. Every time they take the test, 2 CPD hours will be added."

We have created the tables for this, but when loading the page, an error occurs. This is what we have at the minute,

Dim CPDHourUpdate As SqlDataSource = New SqlDataSource() CPDHourUpdate.ConnectionString = ConfigurationManager.ConnectionStrings("Applicatio n").ToString() CPDHourUpdate.UpdateCommand = "UPDATE UserDetails SET CPD_Hours = CPD_Hours + 2 WHERE UserId = @UserId" CPDHourUpdate.UpdateParameters.Add("UserId", Session("UserID").ToString()) Dim rowsAffected As Integer = CPDHourUpdate.Update()

Any help would be much appreciated! Thanks in advance!

healdem · #2 (**permalink**) 01-18-12, 08:07

an error occurs
where
what was the message
what environment are you using vb, vb.net, C#? Im guessing its going to be VB.NET

if you are going to post code please post the code using the code tags
[ c o d e ]my code goes in here [ / c o d e ] NTOE Remove all spaces inbetween the []

eg

Code:

Dim CPDHourUpdate As SqlDataSource = New SqlDataSource()CPDHourUpdate.ConnectionString = ConfigurationManager.ConnectionStrings("Application").ToString() 
CPDHourUpdate.UpdateCommand = "UPDATE UserDetails SET CPD_Hours = CPD_Hours + 2 Dim CPDHourUpdate As SqlDataSource = New SqlDataSource() CPDHourUpdate.ConnectionString = ConfigurationManager.ConnectionStrings("Applicatio n").ToString() CPDHourUpdate.UpdateCommand = "UPDATE UserDetails SET CPD_Hours = CPD_Hours + 2 WHERE UserId = @UserId" CPDHourUpdate.UpdateParameters.Add("UserId", Session("UserID").ToString()) Dim rowsAffected As Integer = CPDHourUpdate.Update() 
CPDHourUpdate.UpdateParameters.Add("UserId", Session("UserID").ToString())  
Dim rowsAffected As Integer = CPDHourUpdate.Update()

however as a wild guess Im expecting userid to be a tring / text / char variabls and if so it must be encapsualted in '

Code:

select my, column, list from mytable
where UserID = 'Amberrosereynolds'

I don't know what CPD_Hours is but if its an actual time then you should be using a date time variable to store the value and using appropriate date time functions to manipulate the values. if its elapsed hours then its probably fine

incidentally I'd strongly recommend that you assign the value of your SQL to a variable before sending it to the SQL engine or the object handling hte SQL action. why?
becuase it makes debugging the SQL much easier. you can examine the SQL being sent as opposed to guessing what is is sent

Code:

strSQL = "UPDATE UserDetails SET CPD_Hours = CPD_Hours + 2 WHERE UserId = @UserId" 
CPDHourUpdate.UpdateCommand = strSQL

if you get an error message its realtively easy to debug the SQL. bear in mind not only are you trying to check for compilation errors and logic errod int he code but the same things in your SQL. however SQL errors will not be reported so easily as code errors in the IDE

Amberrosereynolds · #3 (**permalink**) 01-18-12, 11:34

Thank you, that's been really helpful!

Internet Services

Web Development

Digital Marketing

Consumer Tech