Comments by Member 13410460

Member 13410460 7-Jun-22 3:39am View

I have no idea where to find tha :(

Member 13410460 7-Jun-22 3:36am View

hmm actually you are right, I check where is the "bigger" date which has the value and take that value and use it..
That is genius. Please write it as a solution so I can make it as "answered" :)

Member 13410460 7-Jun-22 3:21am View

Thank you for your reply, I also thought of that but that will bring the "biggest" value, I need the latst value, it could happen that the latest value wont be "bigger" then the rest..

Member 13410460 25-Nov-20 3:29am View

If you have any other suggestions please free to share, I would be more than happy to see a different approach.

Thank you

Member 13410460 25-Nov-20 3:29am View

@UserName is just an example. I have done so many experiments with different fields to see the changes and it's stuck that one. But I assure you that is not the problem. Thank you for repying

Member 13410460 25-Nov-20 3:27am View

That's one reason for it :D

Member 13410460 22-Sep-20 4:11am View

Thank you for your Reply.

Member 13410460 22-Sep-20 3:57am View

Thank you for your tip, but in this case how do I fix this issue by changing string to datetime ?

Member 13410460 22-Sep-20 3:35am View

Thank you for your Reply, but in this case how do I use datetime instead of String, Do i just write this : command.Parameters.AddWithValue("@Day", day.Trim() & "-" & DateTime.Now() ?

Member 13410460 22-Sep-20 1:33am View

Thank you for your Reply sir, I will try it today when I go home. It's not for the currenct Month, I have a Datetimepicker text which has values like "yyyy-MM" and I can choose months within the year to Show me datacharts with values.

Member 13410460 22-Sep-20 1:32am View

Thank you for your Reply, in SQL they are stored as DateTime, but when I bring them to Vb.net Dashboard I convert them into that Format beacuse I Need in my Dashboard to Show me the months of the year , not the days. But now when I want to see all the Information stored in specific day of the year I Kind of Need to get to the days of the month.

Member 13410460 21-Sep-20 10:21am View

Thank you for your Reply, This would be good if I would have date as normal Format ('dd-MM-yyyy') but in this case I have date like this 'yyyy-MM' since I use for my Charts and different results in the Dashboard.

Member 13410460 21-Sep-20 10:00am View

I am converting to a string because my date is formated in this way '2020-09'. I dont know if this makes sense.

Member 13410460 27-Aug-20 11:39am View

SELECT Store as 'Store',Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA,SUM(Price) AS 'Total'
FROM [dbo].[ShareCost]
where CONVERT(char(7), Date, 120) = '2020-08'
GROUP BY Store
ORDER BY SUM(Price) DESC

I used this and it worked, Thank you Mate

Member 13410460 27-Aug-20 10:39am View

Do you mean like this ?
SELECT SUM(Price) AS 'Total', Description as 'Description' , Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA, Store as 'Store',
CONVERT(char(7), Date, 120) as 'Month',
Datepart(Day,date) as 'Day'
FROM [dbo].[ShareCost]
where CONVERT(char(7), Date, 120) ='2020-08' -- @DateD
GROUP BY Store
HAving CONVERT(char(7), Date, 120)
ORDER BY SUM(Price) DESC

Member 13410460 27-Aug-20 9:38am View

Thank you for your reply, Well I have the sum of Price and the quantity but I cant Sum the STORE which is LIDL and EDEKA since they are NVARCHAR(50).

And what I want is that in the Month 2020-08 I have total Sum of each Store which in this case are LIDL, EDEKA...

Thank you

Member 13410460 27-Aug-20 9:22am View

Thank you for your reply, But in this case, how do I do join?

Member 13410460 3-Aug-20 11:19am View

I mean when I use it this way: Labels.Text = LabelTotal.Text / textDivide.Text it works just the comma is not in the proper place and there is no error.

Member 13410460 3-Aug-20 11:18am View

Thank you for the reply, How can I parse only the EURO Sign out of it?

Member 13410460 3-Aug-20 11:09am View

Numbers are integer, just that in labelsum.the text comes value with EURO Sign in it.

Member 13410460 2-Aug-20 16:20pm View

Thank you for your help, Is there a way to do it with euro sign ? its kind of money-related. Thank you

Member 13410460 2-Aug-20 12:25pm View

Hello, Thank you for your reply, it has EURO sign in front of the number.

Member 13410460 2-Aug-20 12:24pm View

Thank you for your reply, it won't allow me to use tryparse.

Member 13410460 30-Jul-20 14:46pm View

Thank you for explanation, I did all that and now I get this error :
Invalid connection string attribute.

Blessings

Member 13410460 29-Jul-20 18:06pm View

Nothing, I just thought here are people with experiences of different fields and somebody would know how to fix it.

Member 13410460 27-Jul-20 10:37am View

Thank you for your reply and help, I fixed it.

Member 13410460 27-Jul-20 10:13am View

Yep, the second one is right, my mistake. But still, It won't record how many times I want.
Thank you for your answer.
Bests

Member 13410460 27-Jul-20 6:36am View

This is based on quarterly, annually, semi-annually insertion based on the number of how many times to repeat insertion. ITs not the same.
Thank you

Member 13410460 25-Jul-20 17:16pm View

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;")

Member 13410460 25-Jul-20 17:07pm View

Thank you for the nice answer, I tried were and it gives me an error like this: " Incorrect syntax near the keyword 'where'."

Member 13410460 25-Jul-20 13:40pm View

Thank you for your response, Do you have any link that I can take as an example for the loop?

Member 13410460 25-Jul-20 13:08pm View

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

Member 13410460 25-Jul-20 10:46am View

ASo, than regarding the SQL topic I am clear, What about the installation package, I have seen some tutorials on Youtube which explains how to do it but it does not say what kind of Prerequisites I have to include so it runs perfectly fine in other machines. I do know that I have to include the version of .net that it runs but what else ?

Member 13410460 25-Jul-20 9:53am View

Thank you for your reply, what does this mean " unless the SQL server is staying in the same place " does that mean in one machine?

Member 13410460 24-Jul-20 18:07pm View

This Changes all 5 spline lines to green.

Member 13410460 24-Jul-20 10:32am View

I Did it, Thank you.

Member 13410460 24-Jul-20 10:25am View

HEllo , Thank you for help, I looked in google how to change the colors in the loop but I could not find anything so specific. Could you help me somehow on that topic please.

Bests
E

Member 13410460 24-Jul-20 10:22am View

So, I use SQL to show informaton into Chart, and what I want now is that when I go to the point showed in vb.net Charts to show me the description of what has been bought that day(something that is not specified in SQL script above). Perhaps a new query that I will write to show me the description and to connect to that query through these lines :

im thisPt As New PointF(CSng(Chartday.Series("Day").Points(Result.PointIndex).XValue),
CSng(Chartday.Series("Day").Points(Result.PointIndex).YValues(0)))

or here as you mention but I dont know how to construct it :

.X = thisPt.X + 1
.Y = thisPt.Y + 1
.Text = thisPt.ToString
I work in VIsual studio 2015.
Thank you for your patience.

Member 13410460 24-Jul-20 10:08am View

Is there a chance that I can Connect my SQL Query inside of it ?
For example here in this form :

Dim thisPt As New PointF(CSng(Chartday.Series("Day").Points(Result.PointIndex).XValue),
CSng(Chartday.Series("Day").Points(Result.PointIndex).YValues(0)))

And if yes , how can I do that ?

Member 13410460 24-Jul-20 10:04am View

But I dont need to change the question , I still need help regarding how to format the total sum format number in SQL.

Member 13410460 24-Jul-20 9:54am View

The Label on my form shows the direct result from SQL Total sum , so I need to format that number. I tried different ways how to do it in my vb.net but I could not find anything useful.

Member 13410460 23-Jul-20 21:55pm View

THanks mate, that worked perfect.

Member 13410460 23-Jul-20 12:50pm View

Thank you for your information , based on the link I figure it out and fix it.
Thanks

Member 13410460 22-Jul-20 16:39pm View

I looked into that but still don't know how to do it, it looks fairly complicated for my level of coding.

Member 13410460 22-Jul-20 13:30pm View

Thank you for your reply and help, I will test it now.
Bests

Member 13410460 22-Jul-20 13:13pm View

Thank you sir, I will try that now.

Member 13410460 22-Jul-20 13:08pm View

Thank you for your reply, But how do I change then the format of the number? do I have to work it out on vb.net?

Member 13410460 22-Jul-20 11:43am View

This is also useful, THanks mate for the help.
Best REgards

Member 13410460 22-Jul-20 10:58am View

thank you mate, that worked beautifully
Best Regards

Member 13410460 19-Jul-20 10:31am View

The latest code is the one that gives me the number of days only in January, in the last code you only had to put the DATENAME(month,[Date]) in GROUP BY and it would be correct one, so please do it like this :
SELECT DATENAME(MONTH,[Date]) AS ForMonth
,COUNT(*) AS NumberOfDays
,YEAR([Date]) AS ForYearGR

FROM [dbo].[Expenses]
where [Date] IS NOT NULL
GROUP BY YEAR([Date]), MONTH([Date]), Datename(MOnth,[Date])

So it would match the question.

Thank you a lot , you helped me. !

Member 13410460 19-Jul-20 8:08am View

haha, I just replied to the results of YOUR code, that's all. But thank you

Member 13410460 19-Jul-20 8:07am View

Hi, Thank you for your answer, I need to count only the days that I have bought something.

Member 13410460 19-Jul-20 8:04am View

Hi, Thank you for your answer, I tried the code and it says that the 'Datename function requires 2 arguments'

Member 13410460 17-Jul-20 9:12am View

Noted :)
Thank you

Member 13410460 17-Jul-20 7:20am View

Thank you mate, that worked perfectly fine.

Member 13410460 16-Jul-20 13:08pm View

Thank you sir, and sorry for confusing messages, I will mark this answer as a solution, just would be good if you modify it up so for the other people to have it better.

Thanks a lot

Member 13410460 16-Jul-20 12:50pm View

Member 13410460 16-Jul-20 12:46pm View

Thank you

Member 13410460 16-Jul-20 12:45pm View

Yes, it appears just as you said, but could I put the 'Grand Total' into new Row? with the sum of course.

Member 13410460 16-Jul-20 11:42am View

How do I use Union to add a single row? I am sorry I am really basics here :X

Member 13410460 16-Jul-20 11:09am View

This Code replaces The "NULL" with The 'Grand Total' name, IS there a way to put the 'Grand Total' title next to Year with the Total Value under it. Like a new row with the total sum ?

Member 13410460 16-Jul-20 10:29am View

Do you know how to put the RollUP total in a new row with named "Grand Total" ?

Member 13410460 16-Jul-20 10:17am View

It is Date, and I tried with Big D but still an error, I tried also extra brackets but still no result.

Member 13410460 16-Jul-20 10:10am View

How can I put the ROLLUP in New ROw as 'Grand Total' ?

Member 13410460 16-Jul-20 10:02am View

Sure, but in this case, I am talking about the code that you wrote, and it doesn't work.

Member 13410460 16-Jul-20 9:36am View

Deleted

I took a look and i saw how it's done but here my data is a lit bit more complicated and I am no expert on this so I don't know how to do all that.
This is my SQL query which I intend to use text.boxes instead of the dates :

SELECT SUM(Price) AS 'Total'
,CONVERT(char(7), date, 120) AS 'year'
FROM [dbo].[Expenses]
WHERE Department = 'Grocery '
GROUP BY Rollup (CONVERT(char(7), date, 120))
HAVING CONVERT(char(7), date, 120) >= '2017-01'
AND CONVERT(char(7), date, 120) <= '2020-01'

Member 13410460 16-Jul-20 9:20am View

Thank you for your reply, I will have look.

Member 13410460 16-Jul-20 9:20am View

Thank you for your reply, I updated the question.

Member 13410460 16-Jul-20 9:16am View

Yes, there are , each day of only one month is full with information.

Member 13410460 16-Jul-20 9:07am View

It works now, it gives me the Total, I tried a lit bit of change and it won't give me all the total between months:
SELECT
Sum(Price) As Total,
CONVERT(char(7), date, 120) As [Year]
FROM
dbo.Expenses
WHERE
CONVERT(char(7), date, 120) Between '2017-01' And '2019-01'
And
Department = 'Grocery'
GROUP BY ROLLUP
( CONVERT(char(7), date, 120))

and it gives me only 2 months of 2018 " cleary there is more then 2 months in 2018"

Member 13410460 16-Jul-20 8:47am View

I am using SQL Server 2012

Member 13410460 16-Jul-20 8:23am View

Haha, no it is DateTime for sure, I just checked it, its a normal DateTime format, thank you for your reply.

Member 13410460 16-Jul-20 8:16am View

Yes, it is DateTime, that's why I use CONVERT(char(7), date, 120). Thank you for your reply.

Member 13410460 16-Jul-20 8:08am View

Deleted

Hi Richard Deeming, Thank you for your reply and help, when I use this code it gives me an error which says: ' incorrect syntax near 'year' ' ( the last year)

Member 13410460 16-Jul-20 8:08am View

Hi Richard Deeming, Thank you for your reply and help, when I use this code it gives me an error which says: ' incorrect syntax near 'year' ' ( the last year)

Member 13410460 16-Jul-20 8:06am View

Hallo realUSOP, Thank you for reply, when i use this code it gives me an error which says 'Incorrrect syntax near the keyword Where ( the second one ) '

Member 13410460 15-Jul-20 10:12am View

Deleted

Hi there, I fixed after long hours of doing it, I choose the old ways since I already know how everything works in that way, now the adding records work perfectly fine but I have trouble on when I want to update but I think it has to do with ID in SQL since its " Identity increment".

Here is the code :

Dim startdate As DateTime = DateTimePickerFixCosts1.Value
Dim enddate As DateTime = startdate.AddMonths(txtNumberofMonths.Text)
Do While (startdate <= enddate)
db = New ADODB.Connection
db.ConnectionTimeout = 10
connstr = "Provider = sqloledb;Data Source=####\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 = startdat
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 = startdat
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
startdate = startdate.AddMonths(1)
Loop
adoRS.Close()
db.Close()

do you think I can update and Delete data with my txtNumberofMonths.text ?

Member 13410460 13-Jul-20 12:13pm View

Deleted

Yes, it has been, the database is fine I am using also different tables in the same database and everything is fine. This is my connection string = "rovider = sqloledb;Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;")"

Member 13410460 13-Jul-20 8:39am View

Yes I did. Is there another way maybe ?

Member 13410460 13-Jul-20 8:00am View

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

Member 13410460 13-Jul-20 7:53am View

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 6:58am View

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

Member 13410460 13-Jul-20 6:32am View

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

Member 13410460 13-Jul-20 6:15am View

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.

Member 13410460 13-Jul-20 5:14am View

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.

Member 13410460 13-Jul-20 4:53am View

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').

Member 13410460 13-Jul-20 4:39am View

I deleted and it says that is deleted but it's still there, Maybe it will be removed later.

Member 13410460 13-Jul-20 4:34am View

Thank you, I am new in Code Project :X

Member 13410460 13-Jul-20 4:33am View

Thank you for your Reply Mr.Emmanuel , to simplify it with words : As you can see the Code above, I insert or update those values based on one single datetimepicker.text in SQL ( which is for one date ), so what i Need is to Input 15 months of those Datas inside my SQL, i could do it manually 15 times but i would rether use another datetimepicker.text which Acts as end date of Insertion. So from one datetimpicker date (ex. 01-01-2015) to other end datetimepicker date (01-01-2020) on 24th of each month. I hope i was clear, this is just a method that i tried to use but i didnt had any success, 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.
And in my SQL Table I have only one Field as Date.

Member 13410460 13-Jul-20 4:31am View

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

Member 13410460 13-Jul-20 4:30am View

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.

Member 13410460 13-Jul-20 4:16am View

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

Member 13410460 13-Jul-20 4:15am View

Maciej Los How do you mean this is not an answer ? to which you are refering to ?

Member 13410460 13-Jul-20 4:05am View

Deleted

Thank you for your Reply Mr.Emmanuel , to simplify it with words : As you can see the Code above, I insert or update those values based on one single datetimepicker.text in SQL ( which is for one date ), so what i Need is to Input 15 months of those Datas inside my SQL, i could do it manually 15 times but i would rether use another datetimepicker.text which Acts as end date of Insertion. So from one datetimpicker date (ex. 01-01-2015) to other end datetimepicker date (01-01-2020) on 24th of each month. I hope i was clear, this is just a method that i tried to use but i didnt had any success, 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.
And in my SQL Table I have only one Field as Date.

Member 13410460 12-Jul-20 16:44pm View

Thank you for your response, it's lit bit confusing because I am not that good with codes, I found some addmonths function which I thought it could help me but no results from it, as I have shown in the part where it says that I tried. I think there are ways to make all my items insert into database by a range of dates.

Comments by Member 13410460 (Top 95 by date)