August 29, 2012 at 11:55 am
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
August 29, 2012 at 1:06 pm
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
August 29, 2012 at 1:14 pm
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]
August 29, 2012 at 1:19 pm
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
August 29, 2012 at 1:23 pm
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 + 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
August 29, 2012 at 1:27 pm
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
August 29, 2012 at 1:47 pm
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
August 29, 2012 at 1:50 pm
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
August 29, 2012 at 1:51 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply