sql won't execute HELP!!

Guest_imported · May 30, 2002

I have been working on this for a couple of days and I can not get this to work. I need to be able to pull up a report from the sql database into an html table using vbscript. My code is not working. Can anyone see what the problem is??

srtConn = "DRIVER=SQL Server;SERVER=;UID=;PWD=;DATABASE="

Set objDBConn = SERVER.CreateObject("ADODB.Connection&quot

objDBConn.Open strConn

strCommandText = "SELECT dbo.t_EMPLOYEES.CurrentComp," &_
"dbo.t_EMPLOYEES.LAST_NAME, " &_
"dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, " &_
"dbo.t_EMPLOYEES.POSITION, " &_
"dbo.t_EMPLOYEES.PROGRAM, " &_
"dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, " &_
"Left([CurrentComp],3) AS Comp, " &_
"dbo.t_EMPLOYEES.EmployeeID, " &_
"[LAST_NAME]&', '&[FIRST_NAME] AS Name " &_

"FROM dbo.t_EMPLOYEES, dbo.t_GOVERNMENT " &_
"JOIN dbo.t_GOVERNMENT ON dbo.t_EMPLOYEES.EmployeeID = dbo.t_GOVERNMENT.EmployeeID " &_
"GROUP BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME, dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, dbo.t_EMPLOYEES.POSITION, dbo.t_EMPLOYEES.PROGRAM, dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, Left([CurrentComp],3), dbo.t_EMPLOYEES.EmployeeID, [LAST_NAME]&', '&[FIRST_NAME] " &_
"HAVING (((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND " &_
"((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%')) " &_
"OR (((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%' " &_
" Or (dbo.t_EMPLOYEES.STATUS) Like 'term%')) " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

Set rsEmployee = objDBConn. Execute strCommandText, , _
adCmdText
%>

</head>

<body>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="20%"><%rs(LAST_NAME)%></td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
</tr>
</table>
</body>

</html>

FengShui1998 · May 30, 2002

bettyMarkley,

What kind of error are you getting? Are you able to access the database in the first place? You don't have any username or password in your connect statement.

fengshui_1998

Guest_imported · May 30, 2002

I took all of that stuff at the top out because I work for the government and they are a little wild about security. But the connection does have a username and password and database.

The exact error message I am getting is this:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/logistics_dev/lori/lorisAlignment.asp, line 35

Set rsEmployee = objDBConn. Execute strCommandText, , _
------------------------------------^

Thanks for your help in advance..

FengShui1998 · May 30, 2002

betty,

I think you have an unbalanced parenthesis in this part of the code. It doesn't display very well on this web page but it doesn't look right by glancing at it which is why you're getting "expected end of statement"

fengshui_1998

"HAVING (((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND " &_
"((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%')) " &_
"OR (((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%' " &_
" Or (dbo.t_EMPLOYEES.STATUS) Like 'term%')) " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

Guest_imported · May 30, 2002

I think that i balanced the paren. I am still getting the same error.

"HAVING ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND " &_
"((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%') " &_
"OR ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%') " &_
" Or ((dbo.t_EMPLOYEES.STATUS) Like 'term%') " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

JuanitaC · May 30, 2002

It looks like you have a space in here before the Execute:

Set rsEmployee = objDBConn. Execute

Try taking that out and see what happens.

Guest_imported · May 30, 2002

I took out the space, still no go. Does my connection string look o.k.? Am I using the execute method correctly?
Should I be using the get method or post method?

JuanitaC · May 30, 2002

Have you written out your SQL statement to make sure that everything is OK with that?

Just comment out your Execute line for a minute and Response.write your SQL statement and make sure you are getting what you expect there.

Guest_imported · May 30, 2002

I have tried the response.write and I don't see any problems. I have made some changes as follows and now I am getting a new error which is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/logistics_dev/lori/lorisAlignment.asp, line 11

the new code is:

<%@ LANGUAGE="VBSCRIPT"%>
<%Response.Buffer = True%>
<%

Set objDBConn = SERVER.CreateObject("ADODB.Connection&quot

srtConn = "driver=SQLServer;server=cpedis1;UID=makarowskitw;PWD=makarowskitw;database=30_pers"

objDBConn.Open strConn

strCommandText = "SELECT dbo.t_EMPLOYEES.CurrentComp," &_
"dbo.t_EMPLOYEES.LAST_NAME, " &_
"dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, " &_
"dbo.t_EMPLOYEES.POSITION, " &_
"dbo.t_EMPLOYEES.PROGRAM, " &_
"dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, " &_
"Left([CurrentComp],3) AS Comp, " &_
"dbo.t_EMPLOYEES.EmployeeID, " &_
"[LAST_NAME]&', '&[FIRST_NAME] AS Name " &_

"FROM dbo.t_EMPLOYEES, dbo.t_GOVERNMENT " &_
"JOIN dbo.t_GOVERNMENT ON dbo.t_EMPLOYEES.EmployeeID = dbo.t_GOVERNMENT.EmployeeID " &_
"GROUP BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME, dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, dbo.t_EMPLOYEES.POSITION, dbo.t_EMPLOYEES.PROGRAM, dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, Left([CurrentComp],3), dbo.t_EMPLOYEES.EmployeeID, [LAST_NAME]&', '&[FIRST_NAME] " &_
"HAVING ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND " &_
"((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%') " &_
"OR ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%') " &_
" Or ((dbo.t_EMPLOYEES.STATUS) Like 'term%') " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

Set rsEmployee = objDBConn.Execute(strCommandText)
%>

</head>

<body>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="20%"><%rsEmployee(LAST_NAME)%></td>
<td width="20%"><%rsEmployee(FIRST_NAME)%></td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
</tr>
</table>
</body>

Guest_imported · May 30, 2002

I have tried the response.write and I don't see any problems. I have made some changes as follows and now I am getting a new error which is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/logistics_dev/lori/lorisAlignment.asp, line 11

the new code is:

<%@ LANGUAGE="VBSCRIPT"%>
<%Response.Buffer = True%>
<%

Set objDBConn = SERVER.CreateObject("ADODB.Connection&quot

srtConn = "driver=SQLServer;server=cpedis1;UID=makarowskitw;PWD=makarowskitw;database=30_pers"

objDBConn.Open strConn

strCommandText = "SELECT dbo.t_EMPLOYEES.CurrentComp," &_
"dbo.t_EMPLOYEES.LAST_NAME, " &_
"dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, " &_
"dbo.t_EMPLOYEES.POSITION, " &_
"dbo.t_EMPLOYEES.PROGRAM, " &_
"dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, " &_
"Left([CurrentComp],3) AS Comp, " &_
"dbo.t_EMPLOYEES.EmployeeID, " &_
"[LAST_NAME]&', '&[FIRST_NAME] AS Name " &_

"FROM dbo.t_EMPLOYEES, dbo.t_GOVERNMENT " &_
"JOIN dbo.t_GOVERNMENT ON dbo.t_EMPLOYEES.EmployeeID = dbo.t_GOVERNMENT.EmployeeID " &_
"GROUP BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME, dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, dbo.t_EMPLOYEES.POSITION, dbo.t_EMPLOYEES.PROGRAM, dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, Left([CurrentComp],3), dbo.t_EMPLOYEES.EmployeeID, [LAST_NAME]&', '&[FIRST_NAME] " &_
"HAVING ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND " &_
"((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%') " &_
"OR ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%') " &_
" Or ((dbo.t_EMPLOYEES.STATUS) Like 'term%') " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

Set rsEmployee = objDBConn.Execute(strCommandText)
%>

</head>

<body>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="20%"><%rsEmployee(LAST_NAME)%></td>
<td width="20%"><%rsEmployee(FIRST_NAME)%></td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
</tr>
</table>
</body>

Guest_imported · May 30, 2002

I have tried the response.write and I don't see any problems. I have made some changes as follows and now I am getting a new error which is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/logistics_dev/lori/lorisAlignment.asp, line 11

the new code is:

<%@ LANGUAGE="VBSCRIPT"%>
<%Response.Buffer = True%>
<%

Set objDBConn = SERVER.CreateObject("ADODB.Connection&quot

srtConn = "driver=SQLServer;server=cpedis1;UID=makarowskitw;PWD=makarowskitw;database=30_pers"

objDBConn.Open strConn

strCommandText = "SELECT dbo.t_EMPLOYEES.CurrentComp," &_
"dbo.t_EMPLOYEES.LAST_NAME, " &_
"dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, " &_
"dbo.t_EMPLOYEES.POSITION, " &_
"dbo.t_EMPLOYEES.PROGRAM, " &_
"dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, " &_
"Left([CurrentComp],3) AS Comp, " &_
"dbo.t_EMPLOYEES.EmployeeID, " &_
"[LAST_NAME]&', '&[FIRST_NAME] AS Name " &_

"FROM dbo.t_EMPLOYEES, dbo.t_GOVERNMENT " &_
"JOIN dbo.t_GOVERNMENT ON dbo.t_EMPLOYEES.EmployeeID = dbo.t_GOVERNMENT.EmployeeID " &_
"GROUP BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME, dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, dbo.t_EMPLOYEES.POSITION, dbo.t_EMPLOYEES.PROGRAM, dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, Left([CurrentComp],3), dbo.t_EMPLOYEES.EmployeeID, [LAST_NAME]&', '&[FIRST_NAME] " &_
"HAVING ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND " &_
"((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%') " &_
"OR ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%') " &_
" Or ((dbo.t_EMPLOYEES.STATUS) Like 'term%') " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

Set rsEmployee = objDBConn.Execute(strCommandText)
%>

</head>

<body>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="20%"><%rsEmployee(LAST_NAME)%></td>
<td width="20%"><%rsEmployee(FIRST_NAME)%></td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
</tr>
</table>
</body>

FengShui1998 · May 30, 2002

bettyMarkley,

Your code needs some changing below

<td width="20%"><%=rsEmployee("LAST_NAME&quot

%></td>
<td width="20%"><%=rsEmployee("FIRST_NAME&quot

%></td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
</tr>
</table>
</body>

fengshui_1998

FengShui1998 · May 30, 2002

bettyMarkley,

Try using this connection string...

SQL_Svr = "MyServername"
UID = "MyUID"
PASSWORD = "MyPassword"

strConn = "PROVIDER=SQLOLEDB;" & _
"DRIVER={SQL Server};" & _
"SERVER=" & SQL_Svr & ";" & _
"DATABASE=" & DBase & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PASSWORD

fengshui_1998

Guest_imported · May 31, 2002

I made some additional changes, and did the correction string the way you said but still no go.

objDBConn.Open strConn
strCommandText="SELECT dbo.t_EMPLOYEES.CurrentComp," &_
"dbo.t_EMPLOYEES.LAST_NAME, " &_
"dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, " &_
"dbo.t_EMPLOYEES.POSITION, " &_
"dbo.t_EMPLOYEES.PROGRAM, " &_
"dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, " &_
"dbo.t_EMPLOYEES.EmployeeID " &_

"FROM dbo.t_EMPLOYEES, dbo.t_GOVERNMENT " &_
"WHERE dbo.t_EMPLOYEES.EmployeeID = dbo.t_GOVERNMENT.EmployeeID " &_
"GROUP BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME, dbo.t_EMPLOYEES.FIRST_NAME, " &_
"dbo.t_EMPLOYEES.STATUS, dbo.t_EMPLOYEES.POSITION, dbo.t_EMPLOYEES.PROGRAM, dbo.t_GOVERNMENT.JOB_SERIES, " &_
"dbo.t_GOVERNMENT.GRADE, Left([CurrentComp],3), dbo.t_EMPLOYEES.EmployeeID, [LAST_NAME]&', '&[FIRST_NAME] " &_
"HAVING ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'VAC%') AND ((dbo.t_EMPLOYEES.LAST_NAME) Not Like 'vac%') " &_
"AND ((dbo.t_EMPLOYEES.STATUS) Like 'perman%' Or (dbo.t_EMPLOYEES.STATUS) Like '%term%')) " &_
"ORDER BY dbo.t_EMPLOYEES.CurrentComp, dbo.t_EMPLOYEES.LAST_NAME "

Set rsEmployee = objDBConn.Execute(strCommandText)
%>

</head>

<body>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="20%"><%rsEmployee("LAST_NAME&quot

%></td>
<td width="20%"><%rsEmployee("FIRST_NAME&quot

%></td>
<td width="20%"> </td>
<td width="20%"> </td>
<td width="20%"> </td>
</tr>
</table>
</body>

</html>

Guest_imported · May 31, 2002

I am specifically getting an error in this line below.

Set rsEmployee = objDBConn.Execute(strCommandText)

FesterSXS · May 31, 2002

did you change this earlier line??? srtConn to strConn

srtConn = "driver=SQLServer;server=cpedis1;UID=makarowskitw;PWD=makarowskitw;database=30_pers"

Tony

http://www.phoenix-paints.co.uk/portfolio

FengShui1998 · May 31, 2002

bettyMarkley,

I think you need to this one thing at a time.

First see if you have a connection to the database by selecting one simple query and printing out a single value.

This will ensure you have a good connection. If so start adding more to your string query.

strSQL = "SELECT * FROM dbo.t_EMPLOYEES, dbo.t_GOVERNMENT WHERE .... etc

Cheers,
fengshui_1998

Guest_imported · May 31, 2002

SUCCESS!!! Thankyou.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

sql won't execute HELP!!

New member

MIS

New member

MIS

New member

Programmer

New member

Programmer

New member

New member

New member

MIS

MIS

New member

New member

Programmer

MIS

New member

Similar threads

Log in

Part and Inventory Search

Sponsor