473,324 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

update year and month

66
hi......
Its very urgent.............. how can i take only the year and month part and it should save in the database in the month and year field separately......... when i enter the date in the form it should automatically update the year and month field in the db........ in my proj i am drawing a graph on daily, monthly and yearly basis..... i got how to draw a graph on day basis but i dont know how to draw a graph on month and year basis can any one help me plz.......... i am using vb6.0 and back end ms access........

i am using adodc1......... and when i enter the date in textbox it is storing in the database but i want to simultaneously store only month and year part in the database in different fields......................
in database the date format is medium date (1-jun-2007)


and my code is like that..........

Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2. Dim AConn As New ADODB.Connection
  3. Dim rst As New ADODB.Recordset
  4.  
  5. Private Sub cmdAdd_Click()
  6.  
  7. Dim d As String
  8. Adodc1.Recordset.AddNew
  9. d = Text1.Text
  10. Dim m As String
  11. Dim d1 As String
  12. Dim y As String
  13. m = (d)
  14. d = Mid(d, 1, 2)
  15. y = Mid(d, 4, 2)
  16.  
  17.  
  18.  
  19. With AConn
  20.   .ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
  21.   .Open
  22. End With
  23. sSQL = "INSERT INTO Table1 (Month,Year) " & " VALUES ('" & m & "', " & y & ");"
  24.  rst.Open sSQL, AConn
  25.  rst.AddNew
  26.  
  27.  rst.Update
  28.  rst.MoveNext
  29. rst.Close
  30. Text1.SetFocus
  31. End Sub
  32.  
  33.  
  34. Private Sub cmdExit_Click()
  35. Unload Me
  36. End Sub
is that right.... if not plz give me d correct code i am new to this vb..............
Oct 14 '07 #1
25 2536
debasisdas
8,127 Expert 4TB
try to use this as SQL string.

sSQL = "INSERT INTO Table1 (Month,Year) VALUES ('" & m & "','" & y & "')"
Oct 14 '07 #2
Killer42
8,435 Expert 8TB
Remember not to put quotes around any numeric values. This probably includes the year, and may or may not include the month.
Oct 14 '07 #3
rekhasc
66
Remember not to put quotes around any numeric values. This probably includes the year, and may or may not include the month.
When I run this it's giving an error msg
Run time error :3709
the connection can not be used to perform this operation. it is closed or invalid in this context


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2.  
  3. Dim d As String
  4.  
  5. Adodc1.Recordset.AddNew
  6. d = Text1.Text
  7. Dim m As String
  8. Dim d1 As String
  9. Dim y As String
  10. m = (d)
  11. d = Mid(d, 1, 2)
  12. y = Mid(d, 4, 2)
  13.  
  14.  
  15. With AConn
  16.   .ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
  17.   .Open
  18. End With
  19. sSQL = "INSERT INTO Table1 (Month,Year) " & " VALUES ('" & m & "', & y  );"
  20.  rst.Open sSQL, cnn
  21.  rst.AddNew
  22.   rst.Update
  23.  rst.MoveNext
  24. rst.Close
  25. Text1.SetFocus
  26. End Sub


and also its giving the error ::' -2147217900(80040e14)'

Syntax error in insert into statement
Oct 15 '07 #4
chandru8
145 100+
hi
can you please check the code of insert query
Oct 15 '07 #5
Killer42
8,435 Expert 8TB
When I run this it's giving an error msg
Run time error :3709
the connection can not be used to perform this operation. it is closed or invalid in this context
...
Two questions...
  • Which line produces the error?
  • Can you provide more details about Aconn and rst?
Oct 15 '07 #6
Killer42
8,435 Expert 8TB
can you please check the code of insert query
Good point. The concatenation is messed up. It stands out a bit better now that I've put in the CODE=vb tag.
Oct 15 '07 #7
rekhasc
66
Good point. The concatenation is messed up. It stands out a bit better now that I've put in the CODE=vb tag.

Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2. Dim AConn As New ADODB.Connection
  3. Dim rst As New ADODB.Recordset
  4.  
  5. Private Sub cmdAdd_Click()
  6.  
  7. Adodc1.Recordset.AddNew
  8.  
  9. Text1.Text = Format(Now(), "DD-MMM_YY")
  10.  
  11. Dim d As String
  12.  
  13. d = Text1.Text
  14. Dim m As String
  15. Dim d1 As String
  16. Dim y As String
  17. m = (d)
  18. d = Mid(d, 1, 2)
  19. y = Mid(d, 4, 2)
  20. With AConn
  21.  .ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
  22.   .Open
  23. End With
  24. sSQL = "INSERT INTO Table1 (Month,Year) " & " VALUES ('" & m & "', & y  );"
  25. rst.Open sSQL, AConn  'error is coming in this line
  26. rst.AddNew
  27.  rst.Update
  28.  'rst.MoveNext
  29. 'rst.Close
  30. Text1.SetFocus
  31. End Sub
Oct 15 '07 #8
chandru8
145 100+
can you please specify the error you getting
sSQL = "INSERT INTO Table1 (Month,Year) " & " VALUES ('" & m & "', '" & y & "' );"
can you try this one
Oct 15 '07 #9
rekhasc
66
can you please specify the error you getting
sSQL = "INSERT INTO Table1 (Month,Year) " & " VALUES ('" & m & "', '" & y & "' );"
can you try this one

i tried that one also but its giving syntax error in insert into statement............

is this right code to extract the month and year part from the date what we entered in the text box

Dim d As String
d = Text1.Text
Dim m As String
Dim d1 As String
Dim y As String
m = (d)
d = Mid(d, 1, 2)
y = Mid(d, 4, 2)

after getting this i m inserting this to db
Oct 15 '07 #10
debasisdas
8,127 Expert 4TB
you are trying to insert a record ,right

then what is the need to open the recordset ,that to with insert statment.

after handling the first part, simply try this

Expand|Select|Wrap|Line Numbers
  1. With AConn
  2.  .ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
  3.   .Open
  4. End With
  5. sSQL = "INSERT INTO Table1 (Month,Year) VALUES ('" & m & "'," & y & ")"
  6.  
  7.  
  8. aconn.begintrans
  9. aconn.execute ssql
  10. aconn.committrans
Oct 15 '07 #11
chandru8
145 100+
hi
after you getting the month by using mid string
m = (d)
d = Mid(d, 1, 2)

d will contain only month value

y = Mid(d, 4, 2)

after that y will be empty

d1 = Text1.Text
Dim m As String
Dim d1 As String
Dim y As String
m = (d)
d = Mid(d1, 1, 2)
y = Mid(d1, 4, 2)

try this ....................
Oct 15 '07 #12
rekhasc
66
you are trying to insert a record ,right

then what is the need to open the recordset ,that to with insert statment.

simply try this

Expand|Select|Wrap|Line Numbers
  1. With AConn
  2.  .ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
  3.   .Open
  4. End With
  5. sSQL = "INSERT INTO Table1 (Month,Year) VALUES ('" & m & "'," & y & ")"
  6.  
  7.  
  8. aconn.begintrans
  9. aconn.execute ssql
  10. aconn.committrans


but its giving the same error
syntax error in insert into statement
Oct 15 '07 #13
debasisdas
8,127 Expert 4TB
please post your table structure.
Oct 15 '07 #14
rekhasc
66
please post your table structure.

my table has 4 fields

fieldname - data type Format
date date\time medium date
month text
year number
Amount number
Oct 15 '07 #15
debasisdas
8,127 Expert 4TB
my table has 4 fields

fieldname - data type Format
date date\time medium date
month text
year number
Amount number
try to use this for month and year values

Expand|Select|Wrap|Line Numbers
  1. Dim m As String, y As Integer
  2. m = MonthName(Month(Now))
  3. y = Year(Now)
Oct 15 '07 #16
rekhasc
66
try to use this for month and year values

Expand|Select|Wrap|Line Numbers
  1. Dim m As String, y As Integer
  2. m = MonthName(Month(Now))
  3. y = Year(Now)


i am getting the same error
error in insert into syntax
Oct 15 '07 #17
chandru8
145 100+
i am getting the same error
error in insert into syntax
hi
syntax error may due to the use of keyword in the table structure
Oct 15 '07 #18
debasisdas
8,127 Expert 4TB
try this


Expand|Select|Wrap|Line Numbers
  1. Dim CON As New ADODB.Connection
  2.  
  3. Private Sub Command1_Click()
  4. Dim m As String, y As Integer, sql As String
  5. m = MonthName(Month(Now))
  6. y = Year(Now)
  7. CON.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
  8. sql = "insert into table1 values ('" & m & "'," & y & ")"
  9. CON.BeginTrans
  10. CON.Execute sql
  11. CON.CommitTrans
  12. MsgBox "done"
  13. End Sub
  14.  
IT IS WORKING.

NOTE :----change the field names ,month and year are not supported.
Oct 15 '07 #19
rekhasc
66
hi
syntax error may due to the use of keyword in the table structure


Dim sSQL As String
Dim AConn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Private Sub cmdAdd_Click()

Adodc1.Recordset.AddNew


Dim m As String
Dim y As Integer


m = MonthName(Month(Now))
y = Year(Now)



With AConn
.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
.Open
End With
sSQL = "INSERT INTO Table1 (Month,Year) VALUES ('" & m & "'," & y & ")"
AConn.BeginTrans
AConn.Execute sSQL
AConn.CommitTrans

Text1.SetFocus
End Sub



this is my code ..........
i cheked the keyword also...i have given that correctly
Oct 15 '07 #20
debasisdas
8,127 Expert 4TB
NOTE :----change the field names ,month and year are not supported.
change the field names ,it is not supported in Access. Will work fine in Oracle.

use somthing like this.

Expand|Select|Wrap|Line Numbers
  1. sql = "insert into table1(monthname,yearvalue) values ('" & m & "'," & y & ")"
Hope that solved your problem.
Oct 15 '07 #21
chandru8
145 100+
hi
what iam saying is your
field name in the table

date
month
year
amount
Please rename or check this
Oct 15 '07 #22
rekhasc
66
hi
what iam saying is your
field name in the table

date
month
year
amount
Please rename or check this

the year & month is adding in db ..... but its not adding in the same record ....
its adding to the next row in the db
Oct 15 '07 #23
debasisdas
8,127 Expert 4TB
the year & month is adding in db ..... but its not adding in the same record ....
its adding to the next row in the db
Then use UPDATE instead of INSERT in the SQL statment with condtion for date field.
Oct 15 '07 #24
rekhasc
66
Then use UPDATE instead of INSERT in the SQL statment with condtion for date field.


thank u
thanks a lot for your precious time
Oct 15 '07 #25
daveftl
43
Then use UPDATE instead of INSERT in the SQL statment with condtion for date field.
Update will overwrite the values in the current row while Insert makes a new row in the Db.
Oct 17 '07 #26

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Fendi Baba | last post by:
The function is called from opencalendar(targetfield). Thanks for any hints on what could be the problem. .............................................................. var...
0
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database...
15
by: graham | last post by:
Hi all, <bitching and moaning section> I am asking for any help I can get here... I am at the end of my tether... I don;t consider myself a genius in any way whatsoever, but I do believe I have...
3
by: rola | last post by:
Hi Group! I am having a problem of using SUM under UPDATE statement. I understand that SQL does not allow me to use SUM in UPDATE, but unfortunately, I can not find a way to get around it. Can...
4
by: Ruben | last post by:
Hi, I have a continuous form that provides a listing of various instruments that are serviced on a monthly to annual basis, with general info about the instrument, last and next service dates,...
5
by: Kasrav | last post by:
I have another problem hopeful am not bothering you guys too much if u can help that would be wonderful. I have this code here def year2(): print'This program validates days and months of the...
1
by: teenagelcruise | last post by:
hi, i have a problem with my code which is i cannot update and addnew data into the database but i can delete the data.plz give me an idea.this is my code that i wrote. <html> <head> <meta...
5
by: qatarya3sal | last post by:
Imports System.XML Public Class Form1 Inherits System.Windows.Forms.Form Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ...
1
by: sibusiso | last post by:
HI Can Any one help I have extra field on a table like FDate, FYear, FMonth, FDay, FDatename I have a triger that I will update this field every time transaction hapened, this field must...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.