By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

update year and month

P: 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
Share this Question
Share on Google+
25 Replies


debasisdas
Expert 5K+
P: 8,127
try to use this as SQL string.

sSQL = "INSERT INTO Table1 (Month,Year) VALUES ('" & m & "','" & y & "')"
Oct 14 '07 #2

Expert 5K+
P: 8,434
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

P: 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
100+
P: 145
hi
can you please check the code of insert query
Oct 15 '07 #5

Expert 5K+
P: 8,434
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

Expert 5K+
P: 8,434
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

P: 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
100+
P: 145
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

P: 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
Expert 5K+
P: 8,127
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
100+
P: 145
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

P: 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
Expert 5K+
P: 8,127
please post your table structure.
Oct 15 '07 #14

P: 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
Expert 5K+
P: 8,127
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

P: 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
100+
P: 145
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
Expert 5K+
P: 8,127
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

P: 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
Expert 5K+
P: 8,127
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
100+
P: 145
hi
what iam saying is your
field name in the table

date
month
year
amount
Please rename or check this
Oct 15 '07 #22

P: 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
Expert 5K+
P: 8,127
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

P: 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

P: 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

Post your reply

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