Click here to Skip to main content
14,691,675 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Everyone,
I want to update my tables in SQL based on the number of months I insert in text.box,
this is my code :

<pre>  Dim startdate As DateTime = DateTimePickerFixCosts1.Value
 Dim enddate As DateTime = startdate.AddMonths(Convert.ToInt32(txtNumberofMonths.Text))
        
        Do While (startdate < enddate)
            Const query As String = "Update Test_Table set [Status] =@status, [Date] = @date "
            

            Using conn As SqlConnection = New SqlConnection("Data Source=####\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;")
                Using command As SqlCommand = New SqlCommand(query, conn)   
                    command.Parameters.AddWithValue("@date", startdate)
                    command.Parameters.AddWithValue("@status", ComboBox1.Text)
                    
                    conn.Open()
                    command.ExecuteNonQuery() ' 
                    startdate = startdate.AddMonths(1)
                    

                End Using
            End Using
        Loop
        MsgBox("Your Data has been Updated")


What I have tried:

This code works perfectly fine if I use :
Const query As String = "INSERT INTO Test_Table(Date,Description,Payment,Quantity,Price,Department,Status) VALUES (@date,@description,@payment,@quantity,@price,@department,@status)"


but when I want to update it updates all the records I have in Table based on values in text.box.

Any Help is appreciated,
Best
E
Posted
Updated 25-Jul-20 10:59am
Comments
0x01AA 25-Jul-20 15:26pm
   
Have you ever heard of WHERE in an SQL statement that limits the records to be edited?
Sandeep Mewara 25-Jul-20 15:35pm
   
Don't see a month field in your DB. You store a date there. If you want to target all the dates of a month while updating then first you need to figure out those dates and then update the records corresponding to them.

1 solution

Quote:
but when I want to update it updates all the records I have in Table based on values in text.box.

Update works just like a select, by default, it is all records, if you want to update only a few records, you have to tell which ones with a where clause.
SQL WHERE Clause[^]
   
Comments
Member 13410460 25-Jul-20 17:07pm
   
Thank you for the nice answer, I tried were and it gives me an error like this: " Incorrect syntax near the keyword 'where'."
Patrice T 25-Jul-20 17:14pm
   
keyword is WHERE.
Member 13410460 25-Jul-20 17:16pm
   
I did it , and now it shows me nothing, no changes.
here is the code : Const query As String = ("Update Test_Table SET [Date]=@date,[Status]=@status WHERE [Date]=@date AND [Status]=@status;")
Patrice T 25-Jul-20 17:31pm
   
Use Improve question to update your question.
So that everyone can pay attention to this information.
Add actual query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900