By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,558 Members | 2,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,558 IT Pros & Developers. It's quick & easy.

Executing Long Query Strings

P: n/a
I am attempting to execute a *long* query string via a
ADODB.Recordset.Open (queryStr) call. Most of the time, the query string
will be less than 100 characters, but in some cases, it may be up to
2000 chracters. Right now the code looks like this:

Private Sub Command3_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Dim rslt As ADODB.Recordset, rslt2 As ADODB.Recordset
Dim sqlstr As String, sqlstr2 As String
Dim interestID As Long, interestName As String, IID As String,
interestSection As String, interestEmail As String

Set rslt = New ADODB.Recordset
Set rslt.ActiveConnection = CurrentProject.Connection
' select all interests in this (Access) database set to display on
the web
sqlstr = "SELECT id, name, cat_id, email FROM interests WHERE
web_display = Yes ORDER BY display_order"
rslt.Open (sqlstr)

Set rslt2 = New ADODB.Recordset
Set rslt2.ActiveConnection = CurrentProject.Connection
sqlstr2 = "DELETE FROM visitdl_interests"
rslt2.Open (sqlstr2)

Do Until rslt.EOF
interestID = rslt.Fields("id").Value
IID = interestID
interestName = Nz(Trim(rslt.Fields("name").Value), "")
interestSection = rslt.Fields("cat_id").Value
interestEmail = Nz(Trim(rslt.Fields("email").Value), "")
sqlstr2 = "INSERT INTO visitdl_interests values (" + IID +
",""" + interestName + """," + interestSection + ",""" + interestEmail +
""");"
rslt2.Open (sqlstr2)
rslt.MoveNext
Loop
rslt.Close
Set rslt = Nothing
Set rslt2 = Nothing
msgbox ("Update Complete.")
End Sub

The problem I am having is that the interestEmail variable is empty most
of the time, but in some instances will hold a list of up to 350 email
addresses. When I execute the function, I get the following error:

---
Run-time error '-2147217833 (80040e57)':

The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data.
---

Therefore, I'm thinking that I will have to simply use multiple SQL
queries to perform this task. Here is what I am thinking about:

* Execute: "INSERT INTO interests values (" + IID + ",""" + interestName
+ """," + interestSection + ","""");" This would keep the first query
under 100 characters.

* I could then create a string_to_array function to split the
interestEmail variable into smaller chuncks

* Execute: "UPDATE interests SET email = CONCAT(email,""" + thisChunk +
""" WHERE id = " + IID for each of the array elements from the split string

Does anyone see problems with this or have suggestions?

TIA!

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Fri, 15 Oct 2004 15:43:26 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

You're using the Open method on an Insert query - that's a no-no.
Rather, you should use the Execute method.

If it then still fails, one of the field (likely: email) is too short
for the amount of text you're trying to save in it.

-Tom.
I am attempting to execute a *long* query string via a
ADODB.Recordset.Open (queryStr) call. Most of the time, the query string
will be less than 100 characters, but in some cases, it may be up to
2000 chracters. Right now the code looks like this:

Private Sub Command3_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Dim rslt As ADODB.Recordset, rslt2 As ADODB.Recordset
Dim sqlstr As String, sqlstr2 As String
Dim interestID As Long, interestName As String, IID As String,
interestSection As String, interestEmail As String

Set rslt = New ADODB.Recordset
Set rslt.ActiveConnection = CurrentProject.Connection
' select all interests in this (Access) database set to display on
the web
sqlstr = "SELECT id, name, cat_id, email FROM interests WHERE
web_display = Yes ORDER BY display_order"
rslt.Open (sqlstr)

Set rslt2 = New ADODB.Recordset
Set rslt2.ActiveConnection = CurrentProject.Connection
sqlstr2 = "DELETE FROM visitdl_interests"
rslt2.Open (sqlstr2)

Do Until rslt.EOF
interestID = rslt.Fields("id").Value
IID = interestID
interestName = Nz(Trim(rslt.Fields("name").Value), "")
interestSection = rslt.Fields("cat_id").Value
interestEmail = Nz(Trim(rslt.Fields("email").Value), "")
sqlstr2 = "INSERT INTO visitdl_interests values (" + IID +
",""" + interestName + """," + interestSection + ",""" + interestEmail +
""");"
rslt2.Open (sqlstr2)
rslt.MoveNext
Loop
rslt.Close
Set rslt = Nothing
Set rslt2 = Nothing
msgbox ("Update Complete.")
End Sub

The problem I am having is that the interestEmail variable is empty most
of the time, but in some instances will hold a list of up to 350 email
addresses. When I execute the function, I get the following error:

---
Run-time error '-2147217833 (80040e57)':

The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data.
---

Therefore, I'm thinking that I will have to simply use multiple SQL
queries to perform this task. Here is what I am thinking about:

* Execute: "INSERT INTO interests values (" + IID + ",""" + interestName
+ """," + interestSection + ","""");" This would keep the first query
under 100 characters.

* I could then create a string_to_array function to split the
interestEmail variable into smaller chuncks

* Execute: "UPDATE interests SET email = CONCAT(email,""" + thisChunk +
""" WHERE id = " + IID for each of the array elements from the split string

Does anyone see problems with this or have suggestions?

TIA!


Nov 13 '05 #2

P: n/a
Tom van Stiphout wrote:
On Fri, 15 Oct 2004 15:43:26 GMT, Justin Koivisto <sp**@koivi.com>
wrote:
I am attempting to execute a *long* query string via a
ADODB.Recordset.Open (queryStr) call. Most of the time, the query string
will be less than 100 characters, but in some cases, it may be up to
2000 chracters. Right now the code looks like this:

Private Sub Command3_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Dim rslt As ADODB.Recordset, rslt2 As ADODB.Recordset
Dim sqlstr As String, sqlstr2 As String
Dim interestID As Long, interestName As String, IID As String,
interestSection As String, interestEmail As String

Set rslt = New ADODB.Recordset
Set rslt.ActiveConnection = CurrentProject.Connection
' select all interests in this (Access) database set to display on
the web
sqlstr = "SELECT id, name, cat_id, email FROM interests WHERE
web_display = Yes ORDER BY display_order"
rslt.Open (sqlstr)

Set rslt2 = New ADODB.Recordset
Set rslt2.ActiveConnection = CurrentProject.Connection
sqlstr2 = "DELETE FROM visitdl_interests"
rslt2.Open (sqlstr2)

Do Until rslt.EOF
interestID = rslt.Fields("id").Value
IID = interestID
interestName = Nz(Trim(rslt.Fields("name").Value), "")
interestSection = rslt.Fields("cat_id").Value
interestEmail = Nz(Trim(rslt.Fields("email").Value), "")
sqlstr2 = "INSERT INTO visitdl_interests values (" + IID +
",""" + interestName + """," + interestSection + ",""" + interestEmail +
""");"
rslt2.Open (sqlstr2)
rslt.MoveNext
Loop
rslt.Close
Set rslt = Nothing
Set rslt2 = Nothing
msgbox ("Update Complete.")
End Sub

The problem I am having is that the interestEmail variable is empty most
of the time, but in some instances will hold a list of up to 350 email
addresses. When I execute the function, I get the following error:

---
Run-time error '-2147217833 (80040e57)':

The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data.
---

Therefore, I'm thinking that I will have to simply use multiple SQL
queries to perform this task. Here is what I am thinking about:

* Execute: "INSERT INTO interests values (" + IID + ",""" + interestName
+ """," + interestSection + ","""");" This would keep the first query
under 100 characters.

* I could then create a string_to_array function to split the
interestEmail variable into smaller chuncks

* Execute: "UPDATE interests SET email = CONCAT(email,""" + thisChunk +
""" WHERE id = " + IID for each of the array elements from the split string

Does anyone see problems with this or have suggestions?

TIA!


You're using the Open method on an Insert query - that's a no-no.
Rather, you should use the Execute method.


Good catch, I'll try that out right now... Great, it works Somehow I had
missed the Execute method... THANKS!

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.