SQL Execute Reader Error

Question

Post reply

SQL Execute Reader Error

Mordred

SSCrazy

Points: 2904
More actions
August 29, 2012 at 11:55 am

#99723

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mordred SSCrazy Points: 2904 More actions · Answer 1

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

ElseThe 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 SSC Guru Points: 143329 More actions · Answer 2

How many rows does this query return?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mordred SSCrazy Points: 2904 More actions · Answer 3

Ok so I think I've solved this one withIf 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 SSCrazy Points: 2904 More actions · Answer 4

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 iex = x + SCqryStrI 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 SSC Guru Points: 323504 More actions · Answer 5

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 SSCrazy Points: 2904 More actions · Answer 6

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 SSCrazy Points: 2904 More actions · Answer 7

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 SSC Guru Points: 323504 More actions · Answer 8

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!