Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Location
    Peterborough
    Posts
    5

    Unanswered: SQL Exam Updates?????

    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!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Location
    Peterborough
    Posts
    5

    Thanks

    Thank you, that's been really helpful!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •