none
SQL DB Query RRS feed

  • Question

  • First i need to write an "Update Query" by vb.net Builder

    EX: i have a table with columns "Code, Name, Amount,Date".

    All columns values can be repeated, so i need to update "Amount" value in the table where the "Code=@code and Name=@name" and finally in which the date is oldest.

    Second: if i need to calculate total "Amount" value in where the code and the name are the same 

    EX: 5 + 7 from name Cups and Code 5

    how can it be done?

    Thank you.



    • Edited by AbanoubZak Thursday, May 30, 2019 1:16 PM
    Thursday, May 30, 2019 12:50 PM

Answers


  • All columns values can be repeated, so i need to update "Amount" value in the table where the "Code=@code and Name=@name" and finally in which the date is oldest.

    Second: if i need to calculate total "Amount" value in where the code and the name are the same 

    EX: 5 + 7 from name Cups and Code 5

    how can it be done?

    Thank you.



    1.You can find the oldest date:

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd1 = New SqlCommand("select top 1 Date from Login where Code=5 and Name='Cups' order by Date", conn)
                Dim reader As SqlDataReader = cmd1.ExecuteReader
                While reader.Read
                    oldestdate = reader("Date")
                End While
            End Using

    then update:

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd2 = New SqlCommand("Update Login set Amout=1 where Date=@date" and Code=5 and Name='Cups', conn)
                cmd2.Parameters.AddWithValue("@date", oldestdate)
                cmd2.ExecuteNonQuery()
            End Using


    2.

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd3 = New SqlCommand("select Amount from Login where Name='Cups' and Code=5 ", conn)
                Dim reader As SqlDataReader = cmd3.ExecuteReader
                While reader.Read
                    allAmount += reader("Amount")
                End While
                MsgBox(allAmount.ToString)
            End Using

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AbanoubZak Saturday, June 8, 2019 4:27 PM
    Friday, May 31, 2019 5:03 AM

All replies

  • Please provide more details.
    Thursday, May 30, 2019 12:54 PM
  • Hello,

    We need to see database type e.g. SQL-Server the table definition and current code (using the code button).

    My recommendation is to first write the query in the database e.g. MS-Access has a query builder, SQL-Server you can use SSMS (SQL-Server Management Studio) to write your query and in both cases use parameters rather than string concatenation.

    An update for calculating is your case is done using an UPDATE where multiple where conditions or to first locate the target record with a select statement, inspect values and then write an update to do the calculations from code and the first query or dependent on your SQL skills do the calculation in the UPDATE query.

    Some basic examples for update/calculate

    https://stackoverflow.com/questions/30420403/sql-how-to-update-all-rows-with-a-calculation

    EDIT Here is a statement to get a total, in code the DECLARE statements become parameters e.g. cmd.Parameters.AddWithValue("@Code", 5) etc.

    DECLARE @Code INT = 5;
    DECLARE @Name AS NCHAR(10) = 'Cups';
    
    SELECT SUM(Amount) AS Total
    FROM   dbo.TableForQuestion
    WHERE  Code = @Code
           AND Name = @Name;

    If were wanted to (and this does not make sense but presenting it anyways) update total on rows meeting the WHERE clause.

    DECLARE @Code INT = 5;
    DECLARE @Name AS NCHAR(10) = 'Cups';
    
    UPDATE dbo.TableForQuestion
    SET    Amount = (   SELECT SUM(Amount) AS Total
                        FROM   dbo.TableForQuestion
                        WHERE  Code = @Code
                               AND Name = @Name )
    WHERE  Code = @Code
           AND Name = @Name;
    And I added a primary key which can tell you which rows meet the where condition.
    DECLARE @Code INT = 5;
    DECLARE @Name AS NCHAR(10) = 'Cups';
    SELECT Id FROM dbo.TableForQuestion WHERE Code = @Code AND  Name = @Name



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, May 30, 2019 1:18 PM
    Moderator

  • All columns values can be repeated, so i need to update "Amount" value in the table where the "Code=@code and Name=@name" and finally in which the date is oldest.

    Second: if i need to calculate total "Amount" value in where the code and the name are the same 

    EX: 5 + 7 from name Cups and Code 5

    how can it be done?

    Thank you.



    1.You can find the oldest date:

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd1 = New SqlCommand("select top 1 Date from Login where Code=5 and Name='Cups' order by Date", conn)
                Dim reader As SqlDataReader = cmd1.ExecuteReader
                While reader.Read
                    oldestdate = reader("Date")
                End While
            End Using

    then update:

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd2 = New SqlCommand("Update Login set Amout=1 where Date=@date" and Code=5 and Name='Cups', conn)
                cmd2.Parameters.AddWithValue("@date", oldestdate)
                cmd2.ExecuteNonQuery()
            End Using


    2.

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd3 = New SqlCommand("select Amount from Login where Name='Cups' and Code=5 ", conn)
                Dim reader As SqlDataReader = cmd3.ExecuteReader
                While reader.Read
                    allAmount += reader("Amount")
                End While
                MsgBox(allAmount.ToString)
            End Using

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AbanoubZak Saturday, June 8, 2019 4:27 PM
    Friday, May 31, 2019 5:03 AM

  • All columns values can be repeated, so i need to update "Amount" value in the table where the "Code=@code and Name=@name" and finally in which the date is oldest.

    Second: if i need to calculate total "Amount" value in where the code and the name are the same 

    EX: 5 + 7 from name Cups and Code 5

    how can it be done?

    Thank you.



    1.You can find the oldest date:

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd1 = New SqlCommand("select top 1 Date from Login where Code=5 and Name='Cups' order by Date", conn)
                Dim reader As SqlDataReader = cmd1.ExecuteReader
                While reader.Read
                    oldestdate = reader("Date")
                End While
            End Using

    then update:

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd2 = New SqlCommand("Update Login set Amout=1 where Date=@date" and Code=5 and Name='Cups', conn)
                cmd2.Parameters.AddWithValue("@date", oldestdate)
                cmd2.ExecuteNonQuery()
            End Using


    2.

     Using conn = New SqlConnection(constr)
                conn.Open()
                cmd3 = New SqlCommand("select Amount from Login where Name='Cups' and Code=5 ", conn)
                Dim reader As SqlDataReader = cmd3.ExecuteReader
                While reader.Read
                    allAmount += reader("Amount")
                End While
                MsgBox(allAmount.ToString)
            End Using

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Can you tell me please how to write it as Query?

    Like: 

    Update Table
    Set Amount,
    WHERE (Code=@Code) AND (Name=@Name)..........
    last spot i need to make it oldest date
    Friday, May 31, 2019 11:44 PM
  • Hi,

    I test the code,it works well,

      Using conn = New SqlConnection(constr)
                conn.Open()
                cmd2 = New SqlCommand("Update TableName set Amount=100 where Date=@date and Code=@Code and Name=@Name", conn)
                cmd2.Parameters.AddWithValue("@date", oldestdate)
                cmd2.Parameters.AddWithValue("@Code", 2)
                cmd2.Parameters.AddWithValue("@Name", "TestName")
                cmd2.ExecuteNonQuery()
            End Using

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 3, 2019 1:40 AM