Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Execute Reader Error


SQL Execute Reader Error

Author
Message
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
Hi all,

I have some code that is supposed to make a SQL connection, set the query, and then read the results.
ConnString = "Data Source=SQL2008T1;Initial Catalog=EconAnalysis;Integrated Security=True"

SQLConn.ConnectionString = ConnString
SQLConn.Open()
SQLStr = "SELECT vSeries_Number FROM(tblVSeriesList) where vSeries_Table_Number = '3260020'"
SQLCmd.Connection = SQLConn
SQLCmd.CommandText = SQLStr
SQLdr = SQLCmd.ExecuteReader()
While SQLdr.Read
MsgBox(SQLdr(0))
End While
Do While SQLdr.NextResult

Loop
SQLdr.Close()

SQLConn.Close()
When I step through the code and get to
SQLdr = SQLCmd.ExecuteReader()
I get the following error:
Incorrect syntax near ')'.
I don't get this error at all and was wondering if someone knows what this means?!?

Regards:
Mordred
Keep on Coding in the Free World
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
I forgot to make sure that the code knows if there is another row to look at. Amended code:
If SQLdr.HasRows Then
Do While SQLdr.Read
SCqryStr = SQLdr.GetString(0) 'SQLdr(SQLdr(0))
SCqryStr = SCqryStr + ","
x = x + SCqryStr
Debug.Print(x)
Loop
Else
The problem with this now is that it doesn't seem to want to exit the loop.

Regards:
Mordred
Keep on Coding in the Free World
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
How many rows does this query return?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
Ok so I think I've solved this one with
If SQLdr.HasRows Then
While SQLdr.Read()
SCqryStr = SQLdr.GetString(0) 'SQLdr(SQLdr(0))
SCqryStr = SCqryStr + ","
x = x + SCqryStr
End While
End If


Regards:
Mordred
Keep on Coding in the Free World
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
RBarryYoung (8/29/2012)
How many rows does this query return?
There are just over 2000 rows. I'm going to remove the redundant code ie
x = x + SCqryStr
I added that because I kept getting errors in that block but I don't think it is necessary anymore.

Regards:
Mordred
Keep on Coding in the Free World
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
SQLdr.Read would move the pointer to the next row; my code is very similar to yours, so i think RBarryYoung is right...it seems like you have a boatload of rows?


Dim myConn As New SqlConnection
myConn.ConnectionString = mySqlTrustedConnectionFormat
myConn.Open()
Dim sql As String
sql = "SELECT " & vbCrLf
sql = sql & " USER_ID() AS [USER_ID], " & vbCrLf
sql = sql & " USER_NAME() AS [USER_NAME], " & vbCrLf
sql = sql & " SUSER_ID() AS [SUSER_ID], " & vbCrLf
sql = sql & " SUSER_SNAME() AS [SUSER_SNAME], " & vbCrLf
sql = sql & " IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin], " & vbCrLf
sql = sql & " IS_MEMBER('db_owner') AS [Is_DB_owner], " & vbCrLf
sql = sql & " IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin], " & vbCrLf
sql = sql & " IS_MEMBER('db_datareader') AS [Is_DB_Datareader], " & vbCrLf
sql = sql & " ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN] " & vbCrLf

Dim myCommand = New SqlCommand(sql, myConn)
Dim myreader As SqlDataReader
myreader = myCommand.ExecuteReader()
While myreader.Read
results = results & RPad(TheServer, 20) & RPad(myreader!SUSER_SNAME, 20) & IIf(myreader!Is_ServerAdmin_Sysadmin = 1, "ServerAdmin", "User") & vbCrLf
End While
myreader.Close()
myConn.Close()



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
Hi Lowell and thanks. Can I ask why you pad the side of the string like that?

Regards:
Mordred
Keep on Coding in the Free World
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
With changes I've made to my code this only takes about 5 or so seconds to complete now.

Regards:
Mordred
Keep on Coding in the Free World
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
that one is a snippet from a specific report that gets sent as a raw text;
it's being padded so that in courior font it uses lines up in pretty columns.

it's part of a bunch of analysis type stuff i threw together once.

nothing exciting, really...if i did the report again, I'd send it via html.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search