locked
Incrementing parameters in SQL RRS feed

  • Question

  • User-2126353969 posted

    Hi all, have a project currently that allow users to rate a certain wine. My code is made to update the rating they chosen according to the wineID

            Dim conString As String = ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
    
            If rating = "1" Then
                Dim updateSql As String = "UPDATE Rating SET RatingOne=@RatingOne WHERE RatingWineID='" & WineID + "'"
            ElseIf rating = "2" Then
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=@RatingTwo WHERE RatingWineID='" & WineID + "'"
            ElseIf rating = "3" Then
                Dim updateSql As String = "UPDATE Rating SET RatingThree=@RatingThree WHERE RatingWineID='" & WineID + "'"
            ElseIf rating = "4" Then
                Dim updateSql As String = "UPDATE Rating SET RatingFour=@RatingFour WHERE RatingWineID='" & WineID + "'"
            Else
                Dim updateSql As String = "UPDATE Rating SET RatingFour=@RatingFour WHERE RatingWineID='" & WineID + "'"
    
    
                Dim con As New SqlConnection(conString)
                Dim cmd As New System.Data.SqlClient.SqlCommand(updateSql, con)
    
                If rating = "1" Then
                    cmd.Parameters.Add("@RatingOne", SqlDbType.Int).Value = "1"
                ElseIf rating = "2" Then
                    cmd.Parameters.Add("@RatingTwo", SqlDbType.Int).Value = "1"
                ElseIf rating = "3" Then
                    cmd.Parameters.Add("@RatingThree", SqlDbType.Int).Value = "1"
                ElseIf rating = "4" Then
                    cmd.Parameters.Add("@RatingFour", SqlDbType.Int).Value = "1"
                ElseIf rating = "5" Then
                    cmd.Parameters.Add("@RatingFive", SqlDbType.Int).Value = "1"
                End If
    
            End If

     

    This is part of my updating to sql code. Basically, its a addrating function that will take in WineID value and the user's selected rating value.

    It will update the rating according to the wineID. My question is, how can I increment My RatingOne, Two, Three, Four, Five? supposedly a user rated wine 1 with 4 star, it should increment ratingfour by 1 instead of putting a value inside? Thanks in advance. 

    Wednesday, March 21, 2012 2:38 AM

Answers

  • User1829879277 posted

    Try this:

    If rating = "1" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingOne=(RatingOne+1) WHERE RatingWineID='" & WineID + "'" 
            ElseIf rating = "2" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=(RatingTwo+1) WHERE RatingWineID='" & WineID + "'" 
            ElseIf rating = "3" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingThree=(RatingThree+1) WHERE RatingWineID='" & WineID + "'" 
            ElseIf rating = "4" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(RatingFour+1) WHERE RatingWineID='" & WineID + "'" 
            Else 
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(RatingFour+1) WHERE RatingWineID='" & WineID + "'" 
     
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 21, 2012 2:54 AM
  • User2105156359 posted

    hi

    Change the flow of the code like this and try

    Public Function AddRating(ByVal WineID As Integer, ByVal rating As Integer) As Boolean
    
            Dim Result As Boolean = False 
            Dim conString As String = ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
    
            If rating = 1 Then
                Dim updateSql As String = "UPDATE Rating SET RatingOne=RatingOne +1 WHERE RatingWineID='" & WineID
            ElseIf rating = 2 Then
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=RatingTwo +1 WHERE RatingWineID='" & WineID
            ElseIf rating = 3 Then
                Dim updateSql As String = "UPDATE Rating SET RatingThree=RatingThree +1 WHERE RatingWineID='" & WineID
            ElseIf rating = 4 Then
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour +1 WHERE RatingWineID='" & WineID
            Else
                Dim updateSql As String = "UPDATE Rating SET RatingFive=RatingFive +1 WHERE RatingWineID='" & WineID
              End If
                Dim con As New SqlConnection(conString)
                Dim cmd As New System.Data.SqlClient.SqlCommand(updateSql, con)
    
                Try
                    con.Open()
                    Result = cmd.ExecuteNonQuery() > 0 'if 0 means nothing is executed. '
    
                Catch ex As SqlException
                    Throw New Exception(ex.ToString())
    
                Finally
                    con.Close()
                End Try
    
            Return Result
        End Function
    
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 21, 2012 5:19 AM

All replies

  • User551462331 posted

    how about updating the rating column like this

    If rating = "1" Then
                Dim updateSql As String = "UPDATE Rating SET RatingOne=RatingOne + 1 WHERE RatingWineID='" & WineID + "'"
            ElseIf rating = "2" Then
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=RatingTwo +1 WHERE RatingWineID='" & WineID + "'"
            ElseIf rating = "3" Then
                Dim updateSql As String = "UPDATE Rating SET RatingThree=RatingThree + 1 WHERE RatingWineID='" & WineID + "'"
            ElseIf rating = "4" Then
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour + 1 WHERE RatingWineID='" & WineID + "'"
            Else
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour +1 WHERE RatingWineID='" & WineID + "'"

    hope this helps...

    Wednesday, March 21, 2012 2:46 AM
  • User-1618234021 posted

    You should use the following:

     If rating = "1" Then
                Dim updateSql As String = "UPDATE Rating SET RatingOne=RatingOne+1 WHERE RatingWineID=@wineID" 
            ElseIf rating = "2" Then
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=RatingTwo+1 WHERE RatingWineID=@wineID"
            ElseIf rating = "3" Then
                Dim updateSql As String = "UPDATE Rating SET RatingThree=RatingThree+1 WHERE RatingWineID=@wineID"
            ElseIf rating = "4" Then
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour+1 WHERE RatingWineID=@wineID"
            Else
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour+1 WHERE RatingWineID=@wineID"
    

    In the end add wineID as parameter and run the query. No need for the second if condition.

    Wednesday, March 21, 2012 2:51 AM
  • User1829879277 posted

    Try this:

    If rating = "1" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingOne=(RatingOne+1) WHERE RatingWineID='" & WineID + "'" 
            ElseIf rating = "2" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=(RatingTwo+1) WHERE RatingWineID='" & WineID + "'" 
            ElseIf rating = "3" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingThree=(RatingThree+1) WHERE RatingWineID='" & WineID + "'" 
            ElseIf rating = "4" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(RatingFour+1) WHERE RatingWineID='" & WineID + "'" 
            Else 
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(RatingFour+1) WHERE RatingWineID='" & WineID + "'" 
     
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 21, 2012 2:54 AM
  • User-2126353969 posted

    Hi all, thanks for the quick reply.

     

    i have this problem when i run the rating page:

     

    Line 31: Dim updateSql As String = "UPDATE Rating SET RatingOne=(RatingOne +1) WHERE RatingWineID='" & WineID + "'"""

    [FormatException: Input string was not in a correct format.]
    Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) +201
    Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat) +68

    [InvalidCastException: Conversion from string "'"" to type 'Double' is not valid.]

     

    Input string not in correct format. My WineID is an Integer value brought in. RatingOne -five are all int too. 

    This is my function header:

     Public Function AddRating(ByVal WineID As Integer, ByVal rating As Integer) As Boolean 

     

    Anyone know where it might go wrong? 

    Wednesday, March 21, 2012 3:11 AM
  • User1829879277 posted

    Convert the Varchar value to Integer. Try this:

            If rating = "1" Then  
                Dim updateSql As String = "UPDATE Rating SET RatingOne=(CAST(RatingOne AS Integer)+1) WHERE RatingWineID='" & WineID + "'" 
     
            ElseIf rating = "2" Then  
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=(CAST(RatingTwo AS Integer)+1) WHERE RatingWineID='" & WineID + "'" 
     
            ElseIf rating = "3" Then  
                Dim updateSql As String = "UPDATE Rating SET RatingThree=(CAST(RatingThree AS Integer)+1) WHERE RatingWineID='" & WineID + "'" 
     
            ElseIf rating = "4" Then  
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(CAST(RatingFour AS Integer)+1) WHERE RatingWineID='" & WineID + "'" 
     
            Else  
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(CAST(RatingFour AS Integer)+1) WHERE RatingWineID='" & WineID + "'" 
     
    
    Wednesday, March 21, 2012 3:25 AM
  • User-2126353969 posted

    Hi Sum8,

     

    Have tried using the CAST. However, still display the exact same error. 

    Wednesday, March 21, 2012 3:30 AM
  • User1829879277 posted

    Replace your WHERE conditions to this:

    WHERE RatingWineID=" & WineID

    For example:

    Dim updateSql As String = "UPDATE Rating SET RatingOne=(RatingOne+1) WHERE RatingWineID=" & WineID

    Wednesday, March 21, 2012 3:35 AM
  • User2105156359 posted

    hi

    If rating = "1" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingOne=(CAST(RatingOne AS Integer)+1) WHERE RatingWineID=" & WineID 
    
     
            ElseIf rating = "2" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=(CAST(RatingTwo AS Integer)+1) WHERE RatingWineID=" & WineID
    
     
            ElseIf rating = "3" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingThree=(CAST(RatingThree AS Integer)+1) WHERE RatingWineID=" & WineID 
    
     
            ElseIf rating = "4" Then 
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(CAST(RatingFour AS Integer)+1) WHERE RatingWineID=" & WineID 
    
     
            Else 
                Dim updateSql As String = "UPDATE Rating SET RatingFour=(CAST(RatingFour AS Integer)+1) WHERE RatingWineID=" & WineID 
    
    Wednesday, March 21, 2012 3:36 AM
  • User551462331 posted

    i have this problem when i run the rating page:

     

    Line 31: Dim updateSql As String = "UPDATE Rating SET RatingOne=(RatingOne +1) WHERE RatingWineID='" & WineID + "'"

    [FormatException: Input string was not in a correct format.]
    Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) +201
    Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat) +68

    [InvalidCastException: Conversion from string "'"" to type 'Double' is not valid.]

    the error indicates that it is trying to do addition of WineID and string "'"

    use & to concatenate the string...

    Dim updateSql As String = "UPDATE Rating SET RatingOne=RatingOne +1 WHERE RatingWineID='" & WineID & "'"""

    hope this helps...

    Wednesday, March 21, 2012 3:41 AM
  • User-2126353969 posted

    hi all, thanks again for the reply. Have changed accordingly and now there is no input string not in correct formet error. However, the value that is being inputted in doesn't seem to go into the database. 

     

    Public Function AddRating(ByVal WineID As Integer, ByVal rating As Integer) As Boolean
    
            Dim Result As Boolean = False 
            Dim conString As String = ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
    
            If rating = 1 Then
                Dim updateSql As String = "UPDATE Rating SET RatingOne=RatingOne +1  WHERE RatingWineID='" & WineID
            ElseIf rating = 2 Then
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=RatingTwo +1  WHERE RatingWineID='" & WineID
            ElseIf rating = 3 Then
                Dim updateSql As String = "UPDATE Rating SET RatingThree=RatingThree +1  WHERE RatingWineID='" & WineID
            ElseIf rating = 4 Then
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour +1  WHERE RatingWineID='" & WineID
            Else
                Dim updateSql As String = "UPDATE Rating SET RatingFive=RatingFive +1  WHERE RatingWineID='" & WineID
    
                Dim con As New SqlConnection(conString)
                Dim cmd As New System.Data.SqlClient.SqlCommand(updateSql, con)
    
    
                Try
                    con.Open()
                    Result = cmd.ExecuteNonQuery() > 0 'if 0 means nothing is executed. 
    
                Catch ex As SqlException
                    Throw New Exception(ex.ToString())
    
                Finally
                    con.Close()
                End Try
    
            End If
            Return Result
        End Function

    At my aspx page, i have:

    value2 = AddRating(wineID.Text, RatingDropDownlist.SelectedValue)
    If value2 = True Then
    Label1.Text = "Added sucessfully!"
    Else
    Label1.Text = "Error!"
    End If

     Everytime i hardcode a wine id and choose a rating,

    the page always display "Error!" 

     

    Wednesday, March 21, 2012 4:56 AM
  • User2105156359 posted

    hi

    use this instead of your code..

     Dim Result As Integer = 0;
    Wednesday, March 21, 2012 5:00 AM
  • User-2126353969 posted

    Hi Sandeep,

     

    Have tried changing my result to integer and return an integer..but still is an error. 

    Wednesday, March 21, 2012 5:05 AM
  • User2105156359 posted

    when you changed the result into Integer , did you changed the return type of the function too ??

    Wednesday, March 21, 2012 5:07 AM
  • User-2126353969 posted

    yeap, did changed. :)

    Wednesday, March 21, 2012 5:08 AM
  • User1829879277 posted

    Result = cmd.ExecuteNonQuery() > 0 'if 0 means nothing is executed.

    What value are you getting here?

    Also, remove the single quote at RatingWineID='" & WineID from the query. It should be like:

    Dim updateSql As String = "UPDATE Rating SET RatingOne = (RatingOne + 1) WHERE RatingWineID=" & WineID

    Wednesday, March 21, 2012 5:11 AM
  • User-2126353969 posted

    Hi sum8,

     Placed the result there to see if the cmd is run successfully then return to the page. If true then label will display "successful" if false then "error!"

    The result is always giving a FALSE.

     

    Have removed the single quote already. Still showing error.

    Wednesday, March 21, 2012 5:15 AM
  • User2105156359 posted

    hi

    Change the flow of the code like this and try

    Public Function AddRating(ByVal WineID As Integer, ByVal rating As Integer) As Boolean
    
            Dim Result As Boolean = False 
            Dim conString As String = ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
    
            If rating = 1 Then
                Dim updateSql As String = "UPDATE Rating SET RatingOne=RatingOne +1 WHERE RatingWineID='" & WineID
            ElseIf rating = 2 Then
                Dim updateSql As String = "UPDATE Rating SET RatingTwo=RatingTwo +1 WHERE RatingWineID='" & WineID
            ElseIf rating = 3 Then
                Dim updateSql As String = "UPDATE Rating SET RatingThree=RatingThree +1 WHERE RatingWineID='" & WineID
            ElseIf rating = 4 Then
                Dim updateSql As String = "UPDATE Rating SET RatingFour=RatingFour +1 WHERE RatingWineID='" & WineID
            Else
                Dim updateSql As String = "UPDATE Rating SET RatingFive=RatingFive +1 WHERE RatingWineID='" & WineID
              End If
                Dim con As New SqlConnection(conString)
                Dim cmd As New System.Data.SqlClient.SqlCommand(updateSql, con)
    
                Try
                    con.Open()
                    Result = cmd.ExecuteNonQuery() > 0 'if 0 means nothing is executed. '
    
                Catch ex As SqlException
                    Throw New Exception(ex.ToString())
    
                Finally
                    con.Close()
                End Try
    
            Return Result
        End Function
    
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 21, 2012 5:19 AM
  • User-2126353969 posted

    thx all for the help. Finally the code is able to insert in and the increment works as well! :D

    Thx to sandeep. your code let me realised that end if should come before the try connection. *silly me for putting at the end.

     Edit as followed and finally it can work successfully.

      Dim updateSql As String = ""
            If rating = 1 Then
                updateSql = "UPDATE Rating SET RatingOne=(RatingOne +1) WHERE RatingWineID='" & WineID & "'"


     Thanks all for the help in the increment and other input string error too! Thanks!:D

    Wednesday, March 21, 2012 5:39 AM