Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
rs.Update Detail - How to Get
- Thread starter Sware
- Start date
Weird looking field data but the problem may be here
"INSERT INTO GHM_V70.DBF(FldNams)"
I would expect to see the actual field names comma separated here.
Andy Baldwin
"Testing is the most overlooked programming language on the books!"
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.
"INSERT INTO GHM_V70.DBF(FldNams)"
I would expect to see the actual field names comma separated here.
Andy Baldwin
"Testing is the most overlooked programming language on the books!"
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.
- Thread starter
- #22
Andy,
I'm more confused than ever. You may be correct from an SQL statement syntax standpoint. FldNams is an array with the names of the fields. It looks as though what is generated is the RESULT of the insert (i.e. the record that would result).
jadams, it looks like your code would produce a series of 87 separate (individual) SQL statements, each with UPDATE....... in it. Also, I don't understand the second part of the UPDATE statement (i.e. Where SomeOtherField....)
In any case, we're getting far afield from the original problem of why the original rs1.Update statement is generating the "Statement Too Long" error.
I'm more confused than ever. You may be correct from an SQL statement syntax standpoint. FldNams is an array with the names of the fields. It looks as though what is generated is the RESULT of the insert (i.e. the record that would result).
jadams, it looks like your code would produce a series of 87 separate (individual) SQL statements, each with UPDATE....... in it. Also, I don't understand the second part of the UPDATE statement (i.e. Where SomeOtherField....)
In any case, we're getting far afield from the original problem of why the original rs1.Update statement is generating the "Statement Too Long" error.
Is this construct
a special for Foxpro? If so you may need to ask in the appropriate Foxpro forum.
Great pity that you didn't follow up on my first post with the Debug.Print 3 days ago - could have saved a lot of time and energy
___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints
Code:
INSERT INTO GHM_V70.DBF(FldNams)Great pity that you didn't follow up on my first post with the Debug.Print 3 days ago - could have saved a lot of time and energy
___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints
- Thread starter
- #24
johnwm,
As I mentioned in a post that immediately follows your "first post," I don't understand how to use your code for the 87 fields I have in my records. I also don't understand the "NEW" and WHERE myID = 1 parts of the code. If you can explain I am more than willing to try your code.
As I mentioned in a post that immediately follows your "first post," I don't understand how to use your code for the 87 fields I have in my records. I also don't understand the "NEW" and WHERE myID = 1 parts of the code. If you can explain I am more than willing to try your code.
The code was just to show you how to get a visible version of your SQL statement (which was your original question) so that we could see where the problem lies. It is a fairly standard technique, and the request for that data has been repeated by several other posters in this thread. The query I posted was a generic query, since I didn't know at that time anything about your DB.
The result which you posted at 1530 today appears to show a construct that I am not familiar with, hence my follow-up question about Foxpro.
___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints
The result which you posted at 1530 today appears to show a construct that I am not familiar with, hence my follow-up question about Foxpro.
___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints
- Thread starter
- #26
Okay, y'all. What follows is from a FoxPro Trace log. It contains the SQL statement that was generated by the FoxPro driver from the rs1.Update statement in my original code.
I'm confused about the content before the WHERE clause. It appears to be a random selection of 27 of the 87 fields. What is after the WHERE are all of the 87 fields in the order in which they appear in the record (including duplicates of the fields before the WHERE). Further, I don't understand the WHERE aspect at all (why is it needed?). Further yet, the start of the traced statement indicates a SQL error and the end of it indicates a Statement Too Long error -- why both on the generated statement?
----------------------------------------------------------
VB6 1014-11cc EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 043D3630
WCHAR * 0x0427D828 [ -3] "UPDATE `GHM_V70.DBF` SET `rounds_ytd`=?,`usga_index`=?,`trnd_index`=?,`lowest_rnd`=?,`first_nine`=?,`hand_calc`=?,`index_calc`=?,`gs_sr01`=?,`gs_sr02`=?,`gs_sr03`=?,`gs_sr04`=?,`gs_sr05`=?,`gs_sr06`=?,`gs_sr07`=?,`gs_sr08`=?,`gs_sr09`=?,`gs_sr10`=?,`gs_sr11`=?,`gs_sr12`=?,`gs_sr13`=?,`gs_sr14`=?,`gs_sr15`=?,`gs_sr16`=?,`gs_sr17`=?,`gs_sr18`=?,`gs_sr19`=?,`gs_sr20`=? WHERE `gs_id`=? AND `play_over`=? AND `play_stat`=? AND `play_dues`=? AND `play_flags`=? AND `p_sex`=? AND `p_type`=? AND `p_crse`=? AND `p_card`=? AND `name`=? AND `rounds_ytd`=? AND `round_ytdo`=? AND `rounds_slr`=? AND `usga_home`=? AND `usga_index`=? AND `tot_diff`=? AND `nine_home`=? AND `nine_index`=? AND `nine_diff`=? AND `usga_avg_d`=? AND `init_index`=? AND `trnd_home`=? AND `trnd_index`=? AND `trnd_diff`=? AND `trnd9_home`=? AND `trnd9_inde`=? AND `trnd9_diff`=? AND `hand_date`=? AND `lowest_rnd`=? AND `first_nine`=? AND `hand_scrs`=? AND `nine_p_scr`=? AND `hand_calc`=? AND `index_calc`=? AND `gs_id_pw`=? AND `ih_01`=? AND `ih_02`=? AND `ih_03`=? AND `ih_04`=? AND `ih_05`=? AND `ih_06`=? AND `ih_07`=? AND `ih_08`=? AND `ih_09`=? AND `ih_10`=? AND `ih_11`=? AND `ih_12`=? AND `gs_sr01`=? AND `gs_sr02`=? AND `gs_sr03`=? AND `gs_sr04`=? AND `gs_sr05`=? AND `gs_sr06`=? AND `gs_sr07`=? AND `gs_sr08`=? AND `gs_sr09`=? AND `gs_sr10`=? AND `gs_sr11`=? AND `gs_sr12`=? AND `gs_sr13`=? AND `gs_sr14`=? AND `gs_sr15`=? AND `gs_sr16`=? AND `gs_sr17`=? AND `gs_sr18`=? AND `gs_sr19`=? AND `gs_sr20`=? AND `gs_sr21`=? AND `gs_sr22`=? AND `gs_sr23`=? AND `gs_sr24`=? AND `gs_sr25`=? AND `gs_sr26`=? AND `gs_sr27`=? AND `gs_sr28`=? AND `gs_sr29`=? AND `gs_sr30`=? AND `gs_sr31`=? AND `gs_sr32`=? AND `gs_sr33`=? AND `gs_sr34`=? AND `gs_sr35`=? AND `gs_sr36`=? AND `gs_sr37`=? AND `gs_sr38`=? AND `gs_sr39`=? AND `gs_sr40`=?\ 0"
SDWORD -3
DIAG [S1000] [Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long. (812)
I'm confused about the content before the WHERE clause. It appears to be a random selection of 27 of the 87 fields. What is after the WHERE are all of the 87 fields in the order in which they appear in the record (including duplicates of the fields before the WHERE). Further, I don't understand the WHERE aspect at all (why is it needed?). Further yet, the start of the traced statement indicates a SQL error and the end of it indicates a Statement Too Long error -- why both on the generated statement?
----------------------------------------------------------
VB6 1014-11cc EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 043D3630
WCHAR * 0x0427D828 [ -3] "UPDATE `GHM_V70.DBF` SET `rounds_ytd`=?,`usga_index`=?,`trnd_index`=?,`lowest_rnd`=?,`first_nine`=?,`hand_calc`=?,`index_calc`=?,`gs_sr01`=?,`gs_sr02`=?,`gs_sr03`=?,`gs_sr04`=?,`gs_sr05`=?,`gs_sr06`=?,`gs_sr07`=?,`gs_sr08`=?,`gs_sr09`=?,`gs_sr10`=?,`gs_sr11`=?,`gs_sr12`=?,`gs_sr13`=?,`gs_sr14`=?,`gs_sr15`=?,`gs_sr16`=?,`gs_sr17`=?,`gs_sr18`=?,`gs_sr19`=?,`gs_sr20`=? WHERE `gs_id`=? AND `play_over`=? AND `play_stat`=? AND `play_dues`=? AND `play_flags`=? AND `p_sex`=? AND `p_type`=? AND `p_crse`=? AND `p_card`=? AND `name`=? AND `rounds_ytd`=? AND `round_ytdo`=? AND `rounds_slr`=? AND `usga_home`=? AND `usga_index`=? AND `tot_diff`=? AND `nine_home`=? AND `nine_index`=? AND `nine_diff`=? AND `usga_avg_d`=? AND `init_index`=? AND `trnd_home`=? AND `trnd_index`=? AND `trnd_diff`=? AND `trnd9_home`=? AND `trnd9_inde`=? AND `trnd9_diff`=? AND `hand_date`=? AND `lowest_rnd`=? AND `first_nine`=? AND `hand_scrs`=? AND `nine_p_scr`=? AND `hand_calc`=? AND `index_calc`=? AND `gs_id_pw`=? AND `ih_01`=? AND `ih_02`=? AND `ih_03`=? AND `ih_04`=? AND `ih_05`=? AND `ih_06`=? AND `ih_07`=? AND `ih_08`=? AND `ih_09`=? AND `ih_10`=? AND `ih_11`=? AND `ih_12`=? AND `gs_sr01`=? AND `gs_sr02`=? AND `gs_sr03`=? AND `gs_sr04`=? AND `gs_sr05`=? AND `gs_sr06`=? AND `gs_sr07`=? AND `gs_sr08`=? AND `gs_sr09`=? AND `gs_sr10`=? AND `gs_sr11`=? AND `gs_sr12`=? AND `gs_sr13`=? AND `gs_sr14`=? AND `gs_sr15`=? AND `gs_sr16`=? AND `gs_sr17`=? AND `gs_sr18`=? AND `gs_sr19`=? AND `gs_sr20`=? AND `gs_sr21`=? AND `gs_sr22`=? AND `gs_sr23`=? AND `gs_sr24`=? AND `gs_sr25`=? AND `gs_sr26`=? AND `gs_sr27`=? AND `gs_sr28`=? AND `gs_sr29`=? AND `gs_sr30`=? AND `gs_sr31`=? AND `gs_sr32`=? AND `gs_sr33`=? AND `gs_sr34`=? AND `gs_sr35`=? AND `gs_sr36`=? AND `gs_sr37`=? AND `gs_sr38`=? AND `gs_sr39`=? AND `gs_sr40`=?\ 0"
SDWORD -3
DIAG [S1000] [Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long. (812)
Yes, in your CODE it is an array that holds the field names but you are constructing an SQL statement to execute in an ado connection.
you need to parse the array much the same way you did the data and get the field names into the sql as well.
Example
Insert into MyTable (FirstName, LastName, Sex) Values('Me', 'Myself', 'M')
Andy Baldwin
"Testing is the most overlooked programming language on the books!"
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.
you need to parse the array much the same way you did the data and get the field names into the sql as well.
Example
Insert into MyTable (FirstName, LastName, Sex) Values('Me', 'Myself', 'M')
Andy Baldwin
"Testing is the most overlooked programming language on the books!"
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.
Sware, look at the very long WHERE clause which is being produced because of the lack of a unique key.
Add a primary key, and then try it using that, if needed, set the Where clause yourself to update based on the unique key.
That will not only reduce the size of the sql statement being generated, but also help to determine what
the problem is, if it still continues to exist.
Add a primary key, and then try it using that, if needed, set the Where clause yourself to update based on the unique key.
That will not only reduce the size of the sql statement being generated, but also help to determine what
the problem is, if it still continues to exist.
- Status
Similar threads
