Could not find Stored Procedure in VB script

A

axapta

Hi Guys I have the following VBS script, I get Could not find Stored
Procedure on the Execute line below.
When I examine the strSQLQuery data it contains "select * from people where
userid = 'js36'" (with quotes). It falls over too if I run it in QA with
the quotes too. How can I get this to work?

Thanks

' on error resume next

' if it's Monday then open the phonebook. Values are S(1), M(2),T(3), W(4),
T(5), F(6), S(7).
if (weekday(Date)) = 4 then

'get the login ID for the user
set net= CreateObject("wscript.network")
myLoginID=net.username
myLoginID ="'" & myLoginID & "'"

' build a SQL query and pass the login name to it
strSQLQuery = "SELECT * FROM PEOPLE WHERE USERID = " & myLoginID
strSQLQuery = """" & strSQLQuery & """"

wscript.echo strSQLQuery

' connect to the database
Set objCN = CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Native
Client};Server=SQL12;Database=phonebookproddb;Trusted_Connection=yes;"

objCN.Open strConnection

' execute the query
Set objRS=CreateObject("ADODB.Recordset")


Set objRS=objCN.Execute(strSQLQuery)


' open IE and call the phonebook at the logged-in user's page
Set ie = CreateObject("InternetExplorer.Application")
iePath = "http://intranet3/phonebook/Changes/MyDetails.asp?type=P&personid="
& objRS("Personid")
ie.Navigate iePath
Do Until counter > 1000000 or Not ie.Busy
counter = counter + 1
Loop
with ie.document.parentwindow
.moveto 0,0
.resizeto .screen.availWidth, .screen.availHeight
end with
ie.menubar = true
ie.toolbar = true
ie.visible = true
end if
 
R

rowe_newsgroups

Hi Guys I have the following VBS script, I get Could not find Stored
Procedure on the Execute line below.
When I examine the strSQLQuery data it contains "select * from people where
userid = 'js36'"  (with quotes).  It falls over too if I run it in QA with
the quotes too.  How can I get this to work?

Thanks

' on error resume next

' if it's Monday then open the phonebook. Values are S(1), M(2),T(3), W(4),
T(5), F(6), S(7).
if (weekday(Date)) = 4 then

'get the login ID for the user
set net= CreateObject("wscript.network")
myLoginID=net.username
myLoginID ="'" & myLoginID & "'"

' build a SQL query and pass the login name to it
strSQLQuery = "SELECT * FROM PEOPLE WHERE USERID = " & myLoginID
strSQLQuery = """" & strSQLQuery & """"

wscript.echo strSQLQuery

' connect to the database
Set objCN = CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Native
Client};Server=SQL12;Database=phonebookproddb;Trusted_Connection=yes;"

objCN.Open strConnection

' execute the query
Set objRS=CreateObject("ADODB.Recordset")

Set objRS=objCN.Execute(strSQLQuery)

' open IE and call the phonebook at the logged-in user's page
Set ie = CreateObject("InternetExplorer.Application")
iePath = "http://intranet3/phonebook/Changes/MyDetails.asp?type=P&personid="
& objRS("Personid")
ie.Navigate iePath
Do Until counter > 1000000 or Not ie.Busy
 counter = counter + 1
Loop
with ie.document.parentwindow
 .moveto 0,0
 .resizeto .screen.availWidth, .screen.availHeight
end with
ie.menubar = true
ie.toolbar = true
ie.visible = true
end if
Click to expand...

Wrong group - this is for Visual Basic.NET, and not VBScript.

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/
 
J

J.B. Moreno

axapta said:
' build a SQL query and pass the login name to it
strSQLQuery = "SELECT * FROM PEOPLE WHERE USERID = " & myLoginID
strSQLQuery = """" & strSQLQuery & """"
Click to expand...

The SQL you send to the server shouldn't be surrounded with quotes.
 
C

Chris Dunaway

Hi Guys I have the following VBS script, I get Could not find Stored
Procedure on the Execute line below.
When I examine the strSQLQuery data it contains "select * from people where
userid = 'js36'" (with quotes). It falls over too if I run it in QA with
the quotes too. How can I get this to work?

Thanks

' on error resume next

' if it's Monday then open the phonebook. Values are S(1), M(2),T(3), W(4),
T(5), F(6), S(7).
if (weekday(Date)) = 4 then

'get the login ID for the user
set net= CreateObject("wscript.network")
myLoginID=net.username
myLoginID ="'" & myLoginID & "'"

' build a SQL query and pass the login name to it
strSQLQuery = "SELECT * FROM PEOPLE WHERE USERID = " & myLoginID
strSQLQuery = """" & strSQLQuery & """"

wscript.echo strSQLQuery

' connect to the database
Set objCN = CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Native
Client};Server=SQL12;Database=phonebookproddb;Trusted_Connection=yes;"

objCN.Open strConnection

' execute the query
Set objRS=CreateObject("ADODB.Recordset")

Set objRS=objCN.Execute(strSQLQuery)

' open IE and call the phonebook at the logged-in user's page
Set ie = CreateObject("InternetExplorer.Application")
iePath = "http://intranet3/phonebook/Changes/MyDetails.asp?type=P&personid="
& objRS("Personid")
ie.Navigate iePath
Do Until counter > 1000000 or Not ie.Busy
counter = counter + 1
Loop
with ie.document.parentwindow
.moveto 0,0
.resizeto .screen.availWidth, .screen.availHeight
end with
ie.menubar = true
ie.toolbar = true
ie.visible = true
end if
Click to expand...

Several issues:

1. Use single quotes instead of double quotes in sql queries.
2. DON'T use string concatenation when creating sql queries as you
make yourself vulnerable to sql injection attacks.
3. This forum is for VB.Net related questions. You'd probably get
better results by asking in a VB Script or ADO forum.

Chris
 
Top