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.......... - Dim sSQL As String
-
Dim AConn As New ADODB.Connection
-
Dim rst As New ADODB.Recordset
-
-
Private Sub cmdAdd_Click()
-
-
Dim d As String
-
Adodc1.Recordset.AddNew
-
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)
-
-
-
-
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 & ");"
-
rst.Open sSQL, AConn
-
rst.AddNew
-
-
rst.Update
-
rst.MoveNext
-
rst.Close
-
Text1.SetFocus
-
End Sub
-
-
-
Private Sub cmdExit_Click()
-
Unload Me
-
End Sub
is that right.... if not plz give me d correct code i am new to this vb..............
25 2536
try to use this as SQL string.
sSQL = "INSERT INTO Table1 (Month,Year) VALUES ('" & m & "','" & y & "')"
Remember not to put quotes around any numeric values. This probably includes the year, and may or may not include the month.
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 - Private Sub cmdAdd_Click()
-
-
Dim d As String
-
-
Adodc1.Recordset.AddNew
-
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)
-
-
-
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 );"
-
rst.Open sSQL, cnn
-
rst.AddNew
-
rst.Update
-
rst.MoveNext
-
rst.Close
-
Text1.SetFocus
-
End Sub
and also its giving the error ::' -2147217900(80040e14)'
Syntax error in insert into statement
hi
can you please check the code of insert query
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?
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.
Good point. The concatenation is messed up. It stands out a bit better now that I've put in the CODE=vb tag.
- Dim sSQL As String
-
Dim AConn As New ADODB.Connection
-
Dim rst As New ADODB.Recordset
-
-
Private Sub cmdAdd_Click()
-
-
Adodc1.Recordset.AddNew
-
-
Text1.Text = Format(Now(), "DD-MMM_YY")
-
-
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)
-
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 );"
-
rst.Open sSQL, AConn 'error is coming in this line
-
rst.AddNew
-
rst.Update
-
'rst.MoveNext
-
'rst.Close
-
Text1.SetFocus
-
End Sub
can you please specify the error you getting
sSQL = "INSERT INTO Table1 (Month,Year) " & " VALUES ('" & m & "', '" & y & "' );"
can you try this one
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
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 - 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
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 ....................
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 - 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
but its giving the same error
syntax error in insert into statement
please post your table structure.
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
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 -
Dim m As String, y As Integer
-
m = MonthName(Month(Now))
-
y = Year(Now)
try to use this for month and year values -
Dim m As String, y As Integer
-
m = MonthName(Month(Now))
-
y = Year(Now)
i am getting the same error
error in insert into syntax
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
try this -
Dim CON As New ADODB.Connection
-
-
Private Sub Command1_Click()
-
Dim m As String, y As Integer, sql As String
-
m = MonthName(Month(Now))
-
y = Year(Now)
-
CON.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:\Graph\Graph\xpence.mdb"
-
sql = "insert into table1 values ('" & m & "'," & y & ")"
-
CON.BeginTrans
-
CON.Execute sql
-
CON.CommitTrans
-
MsgBox "done"
-
End Sub
-
IT IS WORKING.
NOTE :----change the field names ,month and year are not supported.
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
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. - sql = "insert into table1(monthname,yearvalue) values ('" & m & "'," & y & ")"
Hope that solved your problem.
hi
what iam saying is your
field name in the table
date
month
year
amount
Please rename or check this
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
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.
Then use UPDATE instead of INSERT in the SQL statment with condtion for date field.
thank u
thanks a lot for your precious time
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Fendi Baba |
last post by:
The function is called from opencalendar(targetfield). Thanks for any
hints on what could be the problem.
..............................................................
var...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
|
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...
| |