Solved

SQL SELECT syntax error

Posted on 2007-12-01
11
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20387526
first off, change .AbsolutePosition to .Bookmark
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20387540
>> 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
ID: 20387610
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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 27

Expert Comment

by:MikeToole
ID: 20388311
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
ID: 20390347
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 20390633
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
ID: 20392694
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
ID: 20394845
<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
ID: 20401523
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
ID: 20401657
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
ID: 20454178
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 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