How do add months between two timedatepickers in VB.NET ?

Question

0.00/5 (No votes)

See more:

I have a code that I input data inside my SQL, here is the code :

<pre>   
Dim db As ADODB.Connection
        Dim connstr As String
        Dim adoRS As ADODB.Recordset
        Dim sqlstr As String


        If (DateTimePickerFixCosts1.Text = "" Or Val(DateTimePickerFixCosts1.Text) = 0) Then
            Call MsgBox("Please insert Date")
            Exit Sub
        End If
        If (TxtIDFixCosts.Text = "") Then
            TxtIDFixCosts.Text = ""
        End If


        If (TxtItemDescFixCosts.Text = "") Then
            TxtItemDescFixCosts.Text = 0
        End If
        If (TxtPaymentFixCosts.Text = "") Then
            TxtPaymentFixCosts.Text = 0
        End If
        If (txtPriceFixCosts.Text = "") Then
            txtPriceFixCosts.Text = 0
        End If


        If (txtNumberofMonths.Text = "") Then
            txtNumberofMonths.Text = 0
            Exit Sub
        End If


        db = New ADODB.Connection
        db.ConnectionTimeout = 10
        connstr = "Provider = sqloledb;Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
        db.ConnectionString = connstr
        db.Open()
        sqlstr = "Select * from Test_Table where ID = '" & Val(TxtIDFixCosts.Text) & "'"
        adoRS = New ADODB.Recordset
        adoRS.Open(sqlstr, db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

        If (adoRS.EOF = True) Then
            ' insert
            adoRS.AddNew()
            adoRS.Fields("Date").Value = DateTimePickerFixCosts1.Value

            adoRS.Fields("Department").Value = TxtFixCosts.Text
            adoRS.Fields("Description").Value = TxtItemDescFixCosts
            adoRS.Fields("Payment").Value = TxtPaymentFixCosts.Text
            adoRS.Fields("Price").Value = txtPriceFixCosts.Text
            MsgBox("Your Record has been inserted")
            adoRS.Update()

        Else
            If (MsgBox("This item in this date is already inserted " & vbCrLf & "Would you like to overwrite it?", MsgBoxStyle.YesNo, "This Item exist") = vbYes) Then
                ' overwrite
                adoRS.Fields("Date").Value = DateTimePickerFixCosts1.Value
                adoRS.Fields("Department").Value = TxtFixCosts.Text
                adoRS.Fields("Description").Value = TxtItemDescFixCosts.Text
                adoRS.Fields("Payment").Value = TxtPaymentFixCosts.Text
                adoRS.Fields("Price").Value = txtPriceFixCosts.Text

                MsgBox("Your data has been overwrited")
                adoRS.Update()
            End If
        End If
        adoRS.Close()
        db.Close()

The field down is what I tried, I also tried to connect them but no success, if there is someone who can help me I would be very greatful.

What I have tried:

' Dim originDate As DateTime = DateTime.Parse(DateTimePickerFixCosts1.Text)
       ' Dim result1 As DateTime = originDate.AddMonths(MonthssToAdd)
       ' MessageBox.Show(result1.ToLongDateString())

Posted 12-Jul-20 4:33am

Member 13410460

Updated 12-Jul-20 22:30pm

Add a Solution

Comments

Maciej Los 13-Jul-20 4:14am

Do you use VB.NET or VB?
If it's VB.nET, please stop using ADODB! Use ADO.NET instead.

Member 13410460 13-Jul-20 4:16am

Thank you for your answer, it is VB:NET and yes i do use ADODB because i am more familiar to it.

Laxmidhar tatwa technologies 13-Jul-20 11:31am

Dim result1 As DateTime = DateTimePickerFixCosts1.Value.AddMonths(1)

2 solutions

Solution 2

I don't know exactly what you are looking for but with this "then i tried to use a textfield which i insert 12( as 12 months) and to record all those values 12 times for one year. ", am giving you a solution that may meet your needs else let me know.

VB

For i = 1 To 12
  'Place your insertion codes here
Next

This will cause your code to execute 12 times cos according to you, you want to save the values 12 times

Posted 12-Jul-20 22:26pm

Otekpo Emmanuel

Comments

Member 13410460 13-Jul-20 4:30am

Thank you for your asnwer Mr.Emmanuel, this will record datas 12 times in the same date in my database, I want to record them Monthly for exmaple;
On 01.January.2015 all the 5 records to be inserted then
On 01.February.2015 again all the 5 records to be inserted and so on so on for 5 years.
Avoid data Format as I took it only for example.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2020-07-12T22:30:00

Solution 3

Try this:

VB.NET

Dim originDate As DateTime = DateTimePickerFixCosts1.Value
Dim result1 As DateTime = originDate.AddMonths(MonthssToAdd)

BTW:
1) Do not use ADODB within VB.NET. Use ADO.NET[^] instead!
2) Do not use concatenated text to build query. Use parameterized queries[^] instead, because your code is Sql Injection[^] vulnerable!

Finally:

VB.NET

Dim dt As DataTable = New DataTable()
Try
    Using connection As New SqlConnection("ConnectionStringHere")
        conection.Open()
        Using command As New SqlCommand("commandTextHere", connection)
            Using reader As SqlDataReader = command.ExecuteReader()
                dt.Load(reader)
            End Using
        End Using
    End Using
Catch Ex As Exception
    Console.WriteLine(Ex.Message)
End Try
If dt.Rows.Count=0 Then
    'something went wrong!
End If

Good luck!

Posted 12-Jul-20 22:30pm

Maciej Los

Comments

Member 13410460 13-Jul-20 4:31am

Thank you for your Response Maciej Los, i will try this and let you know the results.
Bests

Maciej Los 13-Jul-20 4:32am

You're very welcome!

Member 13410460 13-Jul-20 4:53am

Hi again, I did some Research on what you said and I saw the 'parameterized queries' uses SQL commands which I am also not so good, I know the Basics but I dont know how to connect the 5 values that Need to be inserted with the textfield of numbers of ('result1').

Maciej Los 13-Jul-20 5:03am

Can you be more specific? I don't get this part: "how to connect the 5 values that Need to be inserted with the textfield of numbers of ('result1')."

Member 13410460 13-Jul-20 5:14am

Sorry if I am not clear English is not my native language, So as I looked into the codes i figure it out how could i've done and this is the Code (sqlcommand) :
string sqlText = "Insert into Expenses " +
"SET Description = '" + txtItemDescFixCost.Text + "', " +
"SET Payment = '" + txtPaymentFIxCost.Text + "', " +
"SET Price = '" + txtPriceFixCost.Text + "', " +
"Date = " + datetimepicker1.Text + " " +
"WHERE ID = " + Val(txtID.text);
And now in this part where or how should I insert the "result1".

Again thank you for your patiance with me.

Maciej Los 13-Jul-20 6:00am

Sql query should look like:

Dim sqlText As String = "UPDATE Expenses SET [Description] = @description, Payment = @payment, Price = @price, [Date] = @date WHERE ID = @id"

Next, you have to add parameters to the command:

command.Parameters.Add(new SqlParameter("@description", txtItemDescFixCost.Text))
command.Parameters.Add(new SqlParameter("@payment", txtItemDescFixCost.Text))
command.Parameters.Add(new SqlParameter("@price", txtPriceFixCost.Text ))
command.Parameters.Add(new SqlParameter("@date", datetimepicker1.Value))
command.Parameters.Add(new SqlParameter("@id", txtID.text))

Finally, you have to call command.ExecuteNonQuery() method to update your database.

Dim rowsafected = command.ExecuteNonQuery()If rowsafected = 0 Then
    'update failed!
Else
    'success!
End If

Maciej Los 13-Jul-20 6:03am

Alternatively, you can use: AddWithValue() method to add new parameter with value. See: SqlParameterCollection.AddWithValue(String, Object) Method (System.Data.SqlClient) | Microsoft Docs[^]

Member 13410460 13-Jul-20 6:15am

Thank you for writing the code, very kind of you, still, this code that you wrote does the same thing as the one that I wrote but does it in ado.net way but it won't answer my original question which is how can I insert these values every month for 5 years automatically.

Maciej Los 13-Jul-20 6:22am

You need to use loop ;)

Dim startdate As DateTime = datetimepicker1.Value
Dim enddate As DateTime  = startdate.AddYears(5);
Do While (startdate<=enddate)
    'your insert code here
    'query = "INSERT INTO TableName (Field1, Field2,... FieldN) VALUES (@param1, @param2, ... @paramN)"
    'add parameters here
    startdate = startdate.AddMonths(1)
Loop

Member 13410460 13-Jul-20 6:32am

I will try it right now and let you know the results, Thank you a lot.

Member 13410460 13-Jul-20 6:58am

This is what i tried to do, and it doesn't show me at all, not even an error:

Dim dt As DataTable = New DataTable()
Dim startdate As DateTime = DateTimePickerFixCosts1.Value
Dim enddate As DateTime = startdate.AddMonths(txtNumberofMonths.Text)

Try
Using connection As New SqlConnection("Provider = sqloledb;Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;")
connection.Open()
Do While (startdate <= enddate)
Using command As New SqlCommand("UPDATE Test_Table SET [Description] = @description, Payment = @payment, Price = @price, [Date] = @date WHERE ID = @id", connection)
Using reader As SqlDataReader = command.ExecuteReader()
command.Parameters.Add(New SqlParameter("@description", TxtItemDescFixCosts.Text))
command.Parameters.Add(New SqlParameter("@payment", TxtPaymentFixCosts.Text))
command.Parameters.Add(New SqlParameter("@price", txtPriceFixCosts.Text))
command.Parameters.Add(New SqlParameter("@date", DateTimePickerFixCosts1.Value))
command.Parameters.Add(New SqlParameter("@id", TxtIDFixCosts.Text))
dt.Load(reader)

startdate = startdate.AddMonths(txtNumberofMonths.Text)
Dim rowsafected = command.ExecuteNonQuery()
If rowsafected = 0 Then
'update failed!
Else
'success!
End If

End Using

End Using
Loop
End Using

Catch Ex As Exception
Console.WriteLine(Ex.Message)
End Try

If dt.Rows.Count = 0 Then
'something went wrong!
End If

Maciej Los 13-Jul-20 7:36am

As i mentioned in my previous comment, to insert, update or delete, you need to use ExecuteNonQuery method. PLease, read my words carefully. To grab data (select) you have to use ExecuteReader method.

Member 13410460 13-Jul-20 7:53am

Excuse me, I am not so familiar with ado.net, today is my first day doing it. I will try to fix it up. Thank you

Member 13410460 13-Jul-20 8:00am

for example i changed to this, this is Exectutenonquerry method :
command.Parameters.AddWithValue("@description", TxtItemDescFixCosts.Text)

command.Parameters.AddWithValue("@payment", TxtPaymentFixCosts.Text)
command.Parameters.AddWithValue("@price", txtPriceFixCosts.Text)
command.Parameters.AddWithValue("@date", DateTimePickerFixCosts1.Value)
command.Parameters.AddWithValue("@id", TxtIDFixCosts.Text)

Still doesn't work

Maciej Los 13-Jul-20 8:37am

What you mean "doesn't work"? Did you check for record existence?

Member 13410460 13-Jul-20 8:39am

Yes I did. Is there another way maybe ?

Maciej Los 13-Jul-20 8:44am

Has your database been updated or not? What's your connection string?
There's many things to check out.

Maciej Los 13-Jul-20 17:04pm

Check this out to get proper connection string: SQL Server connection strings - ConnectionStrings.com[^]

Member 13410460 25-Jul-20 13:08pm

Hello, so I modified my code, just as a test I tried this one and it gives me infinite records in my data table, what I am doing wrong?

Dim startdate As DateTime = DateTimePickerFixCosts1.Text
Dim enddate As DateTime = startdate.AddMonths(Convert.ToInt32(txtNumberofMonths.Text))
' Dim enddate As DateTime = startdate.AddMonths(txtNumberofMonths.Text)

Const query As String = "INSERT INTO Test_Table(Date,Description,Payment,Quantity,Price,Department,Status) VALUES (@date,@description,@payment,@quantity,@price,@department,@status)"
Do While (startdate <= enddate)
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("@description", TxtItemDescFixCosts.Text)
command.Parameters.AddWithValue("@payment", TxtPaymentFixCosts.Text)
command.Parameters.AddWithValue("@price", txtPriceFixCosts.Text)
command.Parameters.AddWithValue("@date", DateTimePickerFixCosts1.Value)
command.Parameters.AddWithValue("@status", ComboBox1.Text)
command.Parameters.AddWithValue("@quantity", TextBoxIDd.Text)
command.Parameters.AddWithValue("@department", TxtFixCosts.Text)

conn.Open()
command.ExecuteNonQuery() ' NB: Don't use ExecuteReader when the query doesn't return anything!

End Using

End Using
Loop

Please help,
Blessings