Solved

SQL SELECT syntax error

Posted on 2007-12-01
11
387 Views
Last Modified: 2008-02-01
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?
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

Open in new window

0
Comment
Question by:isischen
  • 5
  • 4
  • 2
11 Comments
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
first off, change .AbsolutePosition to .Bookmark
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
>> 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
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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.  
0
 

Author Comment

by:isischen
Comment Utility
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.
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

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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
0
 

Author Comment

by:isischen
Comment Utility
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
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
Comment Utility
<Should I try
 with rs
.source "SELECT " & strcolnm & "FROM wordDef WHERE wordDefID = " rs.lastmodified>

No!
1) You already have a recordset positioned on the correct row 2) The statement would have no effect without reopening the recordset.

I would still suspect that the problem is in the column name

I've just noticed a possible problem with your original code which I've propagated into my posts via cut & paste:

<.Fields(Field & (strcolnm)) = striv>

What is the actual column name in your database?
In this you're treating Field as a variable and concatenating whatever is in it onto the front of strcolnm. But I'm guessing that you intended to prefix strcolnm with the string "Field" in order to make up your field name? That would need to be:
.Fields("Field" & (strcolnm)) = striv

Unless you have the statement ...
Option Explicit
... at the top of a module , VBA will helpfully (in reality unhelpfully) automatically declare any variable you use.

If I'm right you don't have Option Explicit and Field is an automatically declared Variant containing Null, thus there is no prefix to the value in strcolnm.

Choose Tools-Options and tick Require Variable Declaration to have Access place Option Explicit at the top of any new code module you create - it saves an awfull lot of debug time!

The syntax for addressing a member of a collection has a few different forms, for the Fields collection of a recordset it goes like this:

rs.Fields("My Field Name")
rs.Fields(strFieldName)                ' Where strFieldName is a variable containing the string "My Field Name")
rs.Fields![My Field Name]             ' The [] are needed because there are special characters in the name
rs.Fields(2)                                   ' The position of the column in the recordset where the first col is 0

Because Fields has been defined by Access as the default collection of a recordset, the above can be simplified to:
rs("My Field Name")
rs(strFieldName)
rs![My Field Name]  
rs(2)        

I've found that this also works, but I don't recommend it:
rs.[My Field Name]

So <!strcolnm=striv> will only work if the name of the column actually  is strcolnm.

Hope this helps
Mike
0
 

Author Comment

by:isischen
Comment Utility
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!!!
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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 :-)
0
 

Author Comment

by:isischen
Comment Utility
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now