isischen
asked on
SQL SELECT syntax error
I receive an error message on the attached code snippet. Not only my SELECT statement has syntax error but I also couldn't get the autonumberID passed to the SELECT statement's wordDefID right.
The attached code is in a seperate sub so when value passed to 'striv' variable in this sub comes at different intervals picking up 1st set of with...end ...with . And when picking up the second set of with..end...with I use recordset's update method to fill an empty filed of the same record added by(a previous call to) the 1st set of with..end..with code, I guess I have lost the value of the 'varbook' variable in this sub if I did not pass it to other sub and pass it back.
So, what is the correct syntax of SQL's SELECT statement?
And, how can I pass the current record's autonumberID value(the wordDefID in my sub) to the next call of a recordset's update method( the second set of with..end..with in the attached code snippet) to insert data to different fields at different intervals to the same record in the database?
The attached code is in a seperate sub so when value passed to 'striv' variable in this sub comes at different intervals picking up 1st set of with...end ...with . And when picking up the second set of with..end...with I use recordset's update method to fill an empty filed of the same record added by(a previous call to) the 1st set of with..end..with code, I guess I have lost the value of the 'varbook' variable in this sub if I did not pass it to other sub and pass it back.
So, what is the correct syntax of SQL's SELECT statement?
And, how can I pass the current record's autonumberID value(the wordDefID in my sub) to the next call of a recordset's update method( the second set of with..end..with in the attached code snippet) to insert data to different fields at different intervals to the same record in the database?
dim cn as ADODB.connection
dim fldrst as ADODB.recordset
Set cn = CurrentProject.Connection
Set fldrst = New ADODB.Recordset
If strcolnm = "wordList" Then
With fldrst
.Open
.ActiveConnection = cn
.Source = "SELECT " & strcolnm & " FROM wordDef WHERE wordDefID=108"
.CursorLocation = adUseClient
.Open
.AddNew
.Fields(Field & (strcolnm)) = striv
.Update
varbook = .AbsolutePosition
.Requery
End With
Else
With fldrst
.ActiveConnection = cn
.CursorLocation = adUseClient
.AbsolutePosition = varbook
.Update
.Fields(Field & (strcolnm)) = striv
.Requery
End With
If pf = p Then
fldrst.Close
Set fldrst = Nothing
End If
End If
imitchie
first off, change .AbsolutePosition to .Bookmark
>> Not only my SELECT statement has syntax error
which statement? this one?
.Source = "SELECT " & strcolnm & " FROM wordDef WHERE wordDefID=108"
an error's only possible if strcolnm is not a valid name or is blank. check the caller to the sub
which statement? this one?
.Source = "SELECT " & strcolnm & " FROM wordDef WHERE wordDefID=108"
an error's only possible if strcolnm is not a valid name or is blank. check the caller to the sub
This function will do your insert and return the autonumber value, you can then use it in subsequent calls to an update routine. (I have presumed that your <Where wordDefID = 108> means that you intended to assign 108 to that field in your new record):
Function ADOInsert(Value As Long) As Long
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With cn
.Execute "Insert INTO WordDer(wordDefID, wordList) Values(108, '" & striv & "')"
End With
rst.Open "Select @@Identity as ID", cn, adOpenForwardOnly
ADOInsert = rst!ID
End Function
Function ADOInsert(Value As Long) As Long
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With cn
.Execute "Insert INTO WordDer(wordDefID, wordList) Values(108, '" & striv & "')"
End With
rst.Open "Select @@Identity as ID", cn, adOpenForwardOnly
ADOInsert = rst!ID
End Function
The function parameter was wrong in my last post, it should have read:
Function ADOInsert(striv as string) As Long
Starting with version 2.1, ADO retrieves the value of an Autonumber column after an insert and puts it in your recordset, so there's no need for the requeries, etc. See:
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q233299
That means you can implement an easy solution by returning the recordset from your insert function and let the calling routines update the other fields directly. This code returns the recordset after the insert, the current record is the inserted record. The Where 1=2 condition just makes sure that there are no other records in your recordset.
Function rsWordDef(striv as string) As ADODB.Recordset
Dim cn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.Open "Select * from WordDef where 1=2", cn, , adLockOptimistic
.AddNew
!wordDefID = 108
!wordList = striv
.Update
End With
Set rsWordDef = rst
End Function
If you still want call a routine to do the updates then what you need to do is to declare your recordset at the module level so that it isn't lost when you return from the insert routine.
Function ADOInsert(striv as string) As Long
Starting with version 2.1, ADO retrieves the value of an Autonumber column after an insert and puts it in your recordset, so there's no need for the requeries, etc. See:
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q233299
That means you can implement an easy solution by returning the recordset from your insert function and let the calling routines update the other fields directly. This code returns the recordset after the insert, the current record is the inserted record. The Where 1=2 condition just makes sure that there are no other records in your recordset.
Function rsWordDef(striv as string) As ADODB.Recordset
Dim cn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.Open "Select * from WordDef where 1=2", cn, , adLockOptimistic
.AddNew
!wordDefID = 108
!wordList = striv
.Update
End With
Set rsWordDef = rst
End Function
If you still want call a routine to do the updates then what you need to do is to declare your recordset at the module level so that it isn't lost when you return from the insert routine.
ASKER
MikeTools
your code work for me bu I can't get the rst.id retrieved passed to the call of update an empty field for an existing row( which was added using your code). I've attached it below where I received an error message of "method or data member not found'
Please help, thank you.
your code work for me bu I can't get the rst.id retrieved passed to the call of update an empty field for an existing row( which was added using your code). I've attached it below where I received an error message of "method or data member not found'
Please help, thank you.
Public Function fillgRules(striv As String, Optional i As Integer, _ Optional rs As ADODB.Recordset) As Integer
Dim strcolnm As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.UpdateBatch
.Source "SELECT " & strcolnm & " FROM wordDef WHERE wordDefID = " & rs.id
.Fields(Field & (strcolnm)) = striv
.Update
End With
1. The recordset, rs, you pass as a parameter is immediately wiped out by <set rs = new adodb.recordset>
2. You're not passing the column name as a parameter
3. Since you've already got a recordset with the required row in it, you don't need any SQL
Try this:
Public Sub fillgRules(strcolnm as String, striv As String, rs As ADODB.Recordset)
With rs
.Fields(Field & (strcolnm)) = striv
.Update
End With
End Sub
The alternative, since you have returned a recordset to the calling routine, is to code the field updates there:
rs!MycolumnName = ThisColumnValue
....
You could then issue a single update, just before you make the next insert
rs.Update
2. You're not passing the column name as a parameter
3. Since you've already got a recordset with the required row in it, you don't need any SQL
Try this:
Public Sub fillgRules(strcolnm as String, striv As String, rs As ADODB.Recordset)
With rs
.Fields(Field & (strcolnm)) = striv
.Update
End With
End Sub
The alternative, since you have returned a recordset to the calling routine, is to code the field updates there:
rs!MycolumnName = ThisColumnValue
....
You could then issue a single update, just before you make the next insert
rs.Update
ASKER
Thank you for your continued response. I tried you last code to fill data to an empty filed of last added row. However, I got another error message " Item cannot be found in the collection corresponding to the requested name or ordianl." I've checked the value of strcolnm which is an valid colunm name in the datebase. What would the syntax be for my variable, strcolm, in your alternative? As
rs!strcolnm=striv
???
Should I try
with rs
.source "SELECT " & strcolnm & "FROM wordDef WHERE wordDefID = " rs.lastmodified
!strcolnm=striv
.update
end with
rs!strcolnm=striv
???
Should I try
with rs
.source "SELECT " & strcolnm & "FROM wordDef WHERE wordDefID = " rs.lastmodified
!strcolnm=striv
.update
end with
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am getting the message of " meta date or member of collection not found " after typing in
.field(strcolnm)=striv
I tried with not closing the ist recordset in which I have .addnew to start a new row and tried to work with second rs codes to started in the same module to add to blank field of the row added with the 1st rs code. However, F8 the code one at a time had stopped at .filed(strcolnm)=striv
I have been at this for over one week trying to do with my current application by insert date to one field one code block at a time. If there's no solution, I have to rewrite my entire application just so I can use SQL's INSERT INTO statement when I can supply all data to all colunms in one call of the query!!!
.field(strcolnm)=striv
I tried with not closing the ist recordset in which I have .addnew to start a new row and tried to work with second rs codes to started in the same module to add to blank field of the row added with the 1st rs code. However, F8 the code one at a time had stopped at .filed(strcolnm)=striv
I have been at this for over one week trying to do with my current application by insert date to one field one code block at a time. If there's no solution, I have to rewrite my entire application just so I can use SQL's INSERT INTO statement when I can supply all data to all colunms in one call of the query!!!
Can you check that in you Insert routine you retrieve All the columns when you open the recordset, i.e. by using an "*" as in my post
<.Open "Select * from WordDef where 1=2", cn, , adLockOptimistic
.AddNew>
Then if there's still a problem, the only for sure way to find out why is to:
1) F8 through the code till it stops on the .field(strcolnm)=striv statement.
2) In the Immediate window type
?strcolnm
and hit Enter. This will show the column you're trying to
3) In the Immediate Window type
For each F in rs.Fields: ?F.Name: Next F
and hit enter, that will list the field names in the recordset
- rs needs to be what ever name you're using in the routine you're debugging
My guess is that if you did that straight away you'd find that whatever is in strcolnm in not in the field list.
I did consider suggesting right at the beginning the idea of building a whole SQL insert string and do it all at once, but decided not to confuse the issue :-)
<.Open "Select * from WordDef where 1=2", cn, , adLockOptimistic
.AddNew>
Then if there's still a problem, the only for sure way to find out why is to:
1) F8 through the code till it stops on the .field(strcolnm)=striv statement.
2) In the Immediate window type
?strcolnm
and hit Enter. This will show the column you're trying to
3) In the Immediate Window type
For each F in rs.Fields: ?F.Name: Next F
and hit enter, that will list the field names in the recordset
- rs needs to be what ever name you're using in the routine you're debugging
My guess is that if you did that straight away you'd find that whatever is in strcolnm in not in the field list.
I did consider suggesting right at the beginning the idea of building a whole SQL insert string and do it all at once, but decided not to confuse the issue :-)
ASKER
The value of strcolnm is in the field list when I type
For each f in the rs.fields: ?f.name: next f
in the immediate window....
but code in the program still gets an error message of "Item cannot be found in the collection corresponding to name or ordinal"
I have experience with Word 2003 where I need to rename the variable so I rename the colunm but it still did not work.
Any more suggestions? Thanks.
For each f in the rs.fields: ?f.name: next f
in the immediate window....
but code in the program still gets an error message of "Item cannot be found in the collection corresponding to name or ordinal"
I have experience with Word 2003 where I need to rename the variable so I rename the colunm but it still did not work.
Any more suggestions? Thanks.