Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 27th, 2004, 02:09 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query String Too Long....Max Length Error Message

Hi All,

I have a compliated and some what confusing problem. I think it is easier to understand if I list my codes(don't worry, the codes is really short). :-) The first thing to pay attention to is the "GetRows" method that I used to create an array. Second thing is, notice how I referenced the values of the firstValue variable that I got from the "ary" array. Finally, a special note is, the values of firstValue and secondValue variables are JUST strings. THEY ARE NOT ACTUAL query strings. HOWEVER, when I finally reference them in this statement: "SQL2 = strLeon" they becomes query strings. Make sense? When I run this whole script, I get the following error message:


Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The identifier that starts with 'select FormattedNm, EmplSocSecNo, EmplmtStatCd, TitleCd, sttl, CPG, Agency, Hrly_rate from SIMPEXTR left join paydetl on simpex' is too long. Maximum length is 128.


When I run my script, everything works fine until I get to here: rsResult.Open SQL2, sConnection

I have a big feeling it has something to do with the getRows method that I used to get the array values(maybe it is not ending the query). The reason I said this is, when I manually type in the query strings listed above(instead of referencing the strLeon variable), directly into "SQL2," my script runs fine. Also, if I were to do this: SQL2= secondValue, the script runs fine as well(of course there is an error b/c a query can't start will a 'WHERE' clause. But at lease the server recognize the script). Any idea?

Thank you for all your help!

Leon


myListMenuSelextedIndex= 1
secondValue="WHERE (((paydetl.CPG)='E1' Or (paydetl.CPG)='E2' Or.......))"

Set sConnection = Server.CreateObject("ADODB.Connection")
sConnection.Open "Provider=SQLOLEDB.1....................
    set rs=Server.CreateObject("ADODB.recordset")
    indexSQL="SELECT some_text_string FROM sCategory'"
    rs.Open indexSQL, sConnection
        ary = rs.GetRows(1,0,0)
    rs.close
        sConnection.close

' the for....loop will grab a particular value from the array that I want
' In this case, let say myListMenuSelectedIndex is 1, I will get this next line of string from the array:
' select Name, EmplSocSec from EmployeeTable.......

    For col = 0 to UBound( ary, 1 )
              firstValue= ary( col, myListMenuSelectedIndex )
    Next

' Combine the firstValue variable and secondValue variable together, to form a querystring.
    strLeon= Trim(cStr(firstValue + secondValue))
    Set sConnection = Server.CreateObject("ADODB.Connection")
    sConnection.Open "Provider=SQLOLEDB.1;...........
    set rsResult=Server.CreateObject("ADODB.recordset")

    SQL2= strLeon
    rsResult.Open SQL2, sConnection



Reply With Quote
  #2 (permalink)  
Old May 27th, 2004, 02:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

1)
For col = 0 to UBound( ary, 1 )
    firstValue= ary( col, myListMenuSelectedIndex )
    Response.write firstValue & "<br><br>"
'''''See what it prints here.

Next
Response.write firstValue
'''''See what it prints here.


Why do you have to use a FOR loop here? As per your code I assume the array has got just a single subscript value. Then it is better you don't have to use getrows() at all. Got to give a think here.

2)
    strLeon= Trim(cStr(firstValue + secondValue))
    Response.write strLeon
'''''See what it prints here.

    Set sConnection = Server.CreateObject("ADODB.Connection")

3) Just a point to check. Have you defined all the variables using DIM?
    SQL2= strLeon Why do you have to assign this to another variable? "strLeon" would do. Anyways that does not cause you any problem.

If you see the entire sql query string printed in the 2nd step then use strLeon directly in your last line of code.
rsResult.Open SQL2, sConnection

If that doesn't print the string as expected, then we got to narrow down still. Let me wait for your reply. Please post all the output of response.write I suggested along with step numbers indicated above.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old May 27th, 2004, 03:06 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

1)Results of the two response.write are identical: select Name, EmplSocSecNo,.....

The reason I used a FOR...LOOP is that I have more than one record in my database to put into the array.

2)Result:
"select FormattedNm, EmplSocSecNo, EmplmtStatCd, TitleCd, sttl, CPG, Agency, Hrly_rate from SIMPEXTR left join paydetl on simpextr.ghrs_id=paydetl.ghrs_id WHERE (((simpextr.CPG)='E1' Or (simpextr.CPG)='E2' Or (simpextr.CPG)='E3' Or (simpextr.CPG)='E4' Or (simpextr.CPG)='E5'))"

3)I defined the variables(but there shouldn't be a problem even if I don't, b/c I did not set Option Explicit)
I used strLeon by itself, and still get the same error

Step 2 did print the entire sql query string, and I did use strLeon directly in my last line of code. This is what I am so confused about. The string looks fine, the response.write of the string looks fine. All the query syntax looks fine. But it just won't work when I reference strLeon.

Thank you ViJay!

Leon

Reply With Quote
  #4 (permalink)  
Old May 27th, 2004, 03:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

ary = rs.GetRows(1,0,0)

' Some notes about .GetRows:
' The Method actually takes up to 3 optional arguments:
' 1. Rows - A long integer indicating the number of rows to
' retreive from the data source and put into the
' array. Defaults to adGetRowsRest which
' retreives all the remaining rows.
' 2. Start - An ADO bookmark indicating which row we should
' begin from. It can also be one of the following
' three ADO constants: adBookmarkCurrent,
' adBookmarkFirst, adBookmarkLast. Defaults to
' the current row, so if you've been moving around
' the RS it'll pick up wherever you left off.
' 3. Fields - A single field name or number or an array of
' names or numbers indicating which fields to
' retreive and place into the array. Defaults to
' all the columns.


Based on your getrows(), I think you are retrieving only one rows whatever the recordset has, IF I AM NOT WRONG. So raised the doubt on using FOR loop there.

Can you execute this part in your database directly and see if that works or results in some error?
"select FormattedNm, EmplSocSecNo, EmplmtStatCd, TitleCd, sttl, CPG, Agency, Hrly_rate from SIMPEXTR left join paydetl on simpextr.ghrs_id=paydetl.ghrs_id WHERE (((simpextr.CPG)='E1' Or (simpextr.CPG)='E2' Or (simpextr.CPG)='E3' Or (simpextr.CPG)='E4' Or (simpextr.CPG)='E5'))"

Just a Question on the out put, do you put Double QUOTES explicity at the start and end of the above SQL statement while storing into strLeon?

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #5 (permalink)  
Old May 27th, 2004, 03:27 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I did put a (1,0,0) in the getRows. But i have it as (50,0,0) before(because I have at least 10 records in my database). The reason I used one is just as an example.

I did execute the query string directly, and it works. No error whatsoever. I also open and close the string with "

I don't know what else it could be if everything works, except when I get to referencing the variable. Thanks again for all your help. I will try to see if I can do a "work-around."

Leon

Reply With Quote
  #6 (permalink)  
Old May 27th, 2004, 03:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Just a thought, if your response.write strLeon gave the output as
"select ....."
then you got to remove that from the string, is what I could think off now. Please update, if that helps.

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old May 27th, 2004, 03:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

One quick question, have you used any code that looks something like this?

Dim SQL2 As String

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #8 (permalink)  
Old May 27th, 2004, 03:56 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

for the double quotes, I did not put them in the database records. What I did is I used escape sequence to put in the double quotes before I use the strings.

No. I have not used Dim SQL2 As String. But I'll definitely try it out.

Thanks again ViJay.

I'll keep you posted.

Leon

Reply With Quote
  #9 (permalink)  
Old May 27th, 2004, 03:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I am sure this is got to do with single quotes in your string. But not getting the exact picture, without seeing what it contains in your variables.

May be a Replace of single quote ' with double single quotes '', would do the trick.

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #10 (permalink)  
Old May 27th, 2004, 04:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Dim SQL2 As String

I didn't mean to try this out, please don't. It is wrong definition in ASP. Just wanted to know if you have done something like that, as you said, you haven't used option explicit, so I thought something like this could have caused the problem.

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
max length property of multiline text box MunishBhatia ASP.NET 2.0 Professional 5 June 14th, 2007 01:55 AM
Max length venkata MySQL 2 May 3rd, 2006 12:39 AM
finding max string length if more than 2 elements srini XSLT 4 April 4th, 2006 01:08 PM
max length of textbox? JelfMaria VB How-To 3 May 10th, 2005 04:02 PM
string to "long long" without using atoll sarraju C++ Programming 2 August 4th, 2004 07:19 AM



All times are GMT -4. The time now is 10:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.