Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Execute Reader Error Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 11:55 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
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
Post #1351800
Posted Wednesday, August 29, 2012 1:06 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
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
Post #1351862
Posted Wednesday, August 29, 2012 1:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #1351869
Posted Wednesday, August 29, 2012 1:19 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
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
Post #1351872
Posted Wednesday, August 29, 2012 1:23 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
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
Post #1351876
Posted Wednesday, August 29, 2012 1:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 12,905, Visits: 32,157
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351880
Posted Wednesday, August 29, 2012 1:47 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
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
Post #1351892
Posted Wednesday, August 29, 2012 1:50 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
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
Post #1351894
Posted Wednesday, August 29, 2012 1:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 12,905, Visits: 32,157
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351899
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse