Too Many ASP and DB errors to name off!!!

Rexolio · Mar 2, 2004

Recently my site is running like a turtle with 3 legs. I'm not sure what's going on. I didn't make any major changes to it. The site runs slow, I get timeouts from time to time. Virtually every page talks to a SQL Server 2000 database.

One of the errors I've recently gotten is:

Code:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

Query-based update failed because the row to update could not be found. 

/includes/Scripts_Home.asp, line 139

This particular script determines whether or not the visitor has been to the home page, and if not, it updates the count for the home page (in a database table) for the current date.

I've had this script for a long time, and nothing has changed with it.

Currently, we are taking if and statements that display HTML and replacing it with Response.Write (I read to do this in a forum.) Other than that, I'm lost at what else I should check. I can't find any bad loops.

Your help is greatly appreciated.

[bugeyed]

guidet@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

YNOTU · Mar 2, 2004

I think you would find this post thread333-721855 helpful

baddos · Mar 2, 2004

One thing that would help out a lot is to use straight OLE-DB instead of ODBC. That would improve DB performance.

Instead of this...

strConn = "my odbc connection"

Try this...
strConn = "Provider=SQLOLEDB;Data Source=mysqlserversip;Initial Catalog=mydatabasename;UID=myuserid;PWD=mypassword;Network Library=dbmssocn"

Rexolio · Mar 2, 2004

Hi Guys - thanks for the help. DeCoJute, I'm reading through the PDF file you linked me to now trying to find some answers, so thanks. Baddos, I changed my DSN connection to a DSN-Less connection as you recommended, but the problem is still there.

Even if I go directly to the database via Enterprise Manager, everything is pretty slow. Also, not too long ago, I could go to one of my tables and do a search for, example, someone's name:

Code:

SELECT * FROM dbo.tblNames WHERE FirstName = 'John' AND LastName = 'Doe'

and it would return a result right away. The Indexed ID in that table is NameID. NOW, it times out. Even if I just do something like:

Code:

SELECT TOP 10 * FROM dbo.tblNames ORDER BY LastName, FirstName

However, if I do something using the index, it displays right away. For example

Code:

SELECT TOP 10 * FROM dbo.tblNames ORDER BY NameID

or

Code:

SELECT * FROM dbo.tblNames WHERE NameID = 350000

The bad thing is, this happened to me about a year or 2 ago. I remember this exact same problem. But I don't remember how I fixed it.

Sorry for the long post - any help is still much appreciated.

[bugeyed]

rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

baddos · Mar 2, 2004

Open up Query Analyzer and type sp_who2. Look at the connections it returns back and see if another connection is blocking a different connection.

Also... optimize your queries... You should NEVER use SELECT *. Even if you are selecting every field, you shouldn't use *.

baddos · Mar 2, 2004

Also... It sounds like what your trying to do can almost be entirely done w/ a stored proceedure instead of ASP. Can you post your code? Maybe we can change it to run in stored proceedure for faster execution.

Rexolio · Mar 2, 2004

baddos,

Thanks for the reply. I'll try sp_who2. Regarding "Select *", that was an example. Actually, I almost always name the fields unless I need to pull all fields, which is hardly ever.

I might post my code, but I'm a little shy to because I've taught myself ASP, and therefore is no where near being "quality"... I'm afraid you guys might laugh at me and rip my code apart. [sadeyes]

rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

baddos · Mar 2, 2004

There is plenty of stuff to laught at on the net, but not in this forum.

Go ahead and post away.

Rexolio · Mar 2, 2004

Here is my include file titled "Scripts_Professionals.asp". I'm sorry if this is way too much. My brain is fried and I no longer know what to do.

Code:

<%
'DIM VARIABLES ************************************************************************************************
Dim FID, MARKETID, CID, rsName, FName, IntOnly, Bundle, rsName2, CName, sDate, strDate
Dim rsCheck, sAction, FCount, RS, CmdUpdateRecord, SQL, sCAction, CCount


'RETRIEVE FRANCHISE ID AND CATEGORY ID *************************************************************************
MARKETID = Request.Querystring("MARKETID")
CID = Request.Querystring("CID")


'RETRIEVE FRANCHISE/MARKET NAME ********************************************************************************
Set rsName = cnSupport.Execute("SELECT Market, IntOnly, Bundle FROM tblMarkets WHERE MarketID = " & MARKETID & " ORDER BY Market")
do until rsName.eof
	FName = rsName("Market")
	IntOnly = rsName("IntOnly")
	Bundle = rsName("Bundle")
rsName.movenext
loop
rsName.close


'IF FRANCHISE DOES NOT PAY FOR MYSITE.COM, REDIRECT THEM TO MAP PAGE ******************************
if Bundle <> "Plat" then
	Response.Redirect ("index_choose.asp?SEL=NONE")
	Response.end
end if


'RETRIEVE CATEGORY NAME *****************************************************************************************
Set rsName2 = cnSupport.Execute("SELECT CategoryName FROM tblCategories WHERE CategoryID = " & CID & " ORDER BY CategoryName")
do until rsName2.eof
	CName = rsName2("CategoryName")
rsName2.movenext
loop
rsName2.close


'GET TODAYS DATE **********************************************************************************************
sDate = Now()
strDate = FormatDateTime(sDate, 2)


'RECORD NEW VISIT *********************************************************************************************
if Session("MYSITEVISIT") = MARKETID then
	' do not record
else
	'CHECK TO SEE IF THERE IS A CURRENT COUNT FOR TODAYS DATE **************************************************
	Set rsCheck = cnSupport.Execute("SELECT FCount FROM tblMarket_Hits WHERE MarketID = " & MARKETID & " AND CountDate = '" & strDate & "'")
	if rsCheck.eof then
		sAction = "Add"
		FCount = 1
	else
		sAction = "Update"
		FCount = rsCheck("FCount") + 1
	end if
	rsCheck.close


	'IF ADDING NEW COUNT ENTRY FOR TODAYS DATE FOR THIS FRANCHISE *********************************************
	if sAction = "Add" then
	
	
		Set RS = Server.CreateObject( "ADODB.Recordset" )	'//create recordset
		RS.ActiveConnection = cnSupport
		RS.CursorType = adOpenStatic
		RS.LockType = adLockPessimistic
		RS.Open "SELECT TOP 1 * FROM tblMarket_Hits"		'//add notification to database
		RS.AddNew
		RS("MarketID") = MARKETID
		RS("FCount") = FCount
		RS("CountDate") = strDate
		RS.Update
		RS.Close


	else
		
		'ELSE UPDATE CURRENT COUNT FOR THIS FRANCHISE, FOR TODAYS DATE *********************************************
		Set CmdUpdateRecord = Server.CreateObject("ADODB.Recordset")
		SQL = "SELECT * FROM tblMarket_Hits WHERE MarketID = " & MARKETID & " AND CountDate = '" & strDate & "'"
		CmdUpdateRecord.Open SQL, cnSupport, 1, 3
		CmdUpdateRecord.Fields( "FCount" ) = FCount
		CmdUpdateRecord.Update
		CmdUpdateRecord.Close
		Set CmdUpdateRecord = Nothing

	end if
	'ADD SESSION SO COUNT ISN'T REPEATED ***********************************************************************
	Session("MYSITEVISIT") = MARKETID
end if


'RECORD NEW CATEGORY VISIT *************************************************************************************
'CHECK TO SEE IF THERE IS A CURRENT COUNT FOR TODAYS DATE **************************************************
Set rsCheck = cnSupport.Execute("SELECT CCount FROM tblMarket_Hits_Categories WHERE MarketID = " & MARKETID & " AND CountDate = '" & strDate & "' AND CategoryID = " & CID)
if rsCheck.eof then
	sCAction = "Add"
	CCount = 1
else
	sCAction = "Update"
	CCount = rsCheck("CCount") + 1
end if
rsCheck.close


'IF ADDING NEW COUNT ENTRY FOR TODAYS DATE FOR THIS FRANCHISES CATEGORY *********************************************
if sCAction = "Add" then
	
	Set RS = Server.CreateObject( "ADODB.Recordset" )	'//create recordset
	RS.ActiveConnection = cnSupport
	RS.CursorType = adOpenStatic
	RS.LockType = adLockPessimistic
	RS.Open "SELECT TOP 1 * FROM tblMarket_Hits_Categories"		'//add notification to database
	RS.AddNew
	RS("MarketID") = MARKETID
	RS("CategoryID") = CID
	RS("CCount") = CCount
	RS("CountDate") = strDate
	RS.Update
	RS.Close

else
		
	'ELSE UPDATE CURRENT COUNT FOR THIS FRANCHISES CATEGORY, FOR TODAYS DATE *********************************************
	Set CmdUpdateRecord = Server.CreateObject("ADODB.Recordset")
	SQL = "SELECT * FROM tblMarket_Hits_Categories WHERE MarketID = " & MARKETID & " AND CountDate = '" & strDate & "' AND CategoryID = " & CID   
	CmdUpdateRecord.Open SQL, cnSupport, 1, 3  
	CmdUpdateRecord.Fields( "CCount" ) = CCount
	CmdUpdateRecord.Update
	CmdUpdateRecord.Close
	Set CmdUpdateRecord = Nothing

end if
														
%>

rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

Rexolio · Mar 2, 2004

Please don't kill me... now here is the .asp page (Professionals.asp) where the above include is called AND where more scripting is found:

Code:

<!-- #include file="includes/Connect.asp" -->
<!-- #include file="includes/Scripts_Professionals.asp" -->
<!-- #include file="includes/Banner_Ads.asp" -->
<!-- #include file="includes/Badge_Ads.asp" -->

<html>
<head>
<title>Services/Professionals</title>

<script language="JavaScript">
<!-- #include file="includes/PopUp.js" -->
</script>

</head>

<body topmargin="0" bgcolor="#FFFFFF" link="#7A3F99" vlink="#A66B88" alink="#B7C795">

<!-- #include file="header.asp" -->

<div align="center">
<table border="0" width="739" cellspacing="0" cellpadding="0">
<tr>
<!-- column for badges -->
<td width="145" valign="top" background="common/pg_badge_bg.gif">
<!-- badges -->
<table border="0" cellpadding="0" cellspacing="0" width="145" style="border-collapse: collapse" bordercolor="#111111">
<tr>
<td width="100%" background="common/pg_badge_hdr_bg.gif" align="center" height="20"><p align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=BadgeAltTag%></font></i></td>
</tr>
<tr>
<td width="100%" align="center" height="155"><p align="center"><%=BadgeString%><br>
<font size="1">&nbsp; </font></td>
</tr>
<tr>
<td width="100%" background="common/pg_badge_hdr_bg2.gif" align="center" height="20"><p align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=BadgeAltTag2%></font></i></td>
</tr>
<tr>
<td width="100%" align="center" height="155"><p align="center"><%=BadgeString2%><br>
<font size="1">&nbsp; </font></td>
</tr>
<tr>
<td width="100%" background="common/pg_badge_hdr_bg2.gif" align="center" height="20"><p align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=BadgeAltTag3%></font></i></td>
</tr>
<tr>
<td width="100%" align="center" height="155"><p align="center"><%=BadgeString3%><br>
<font size="1">&nbsp; </font></td>
</tr>
<tr>
<td width="100%" background="common/pg_badge_hdr_bg2.gif" align="center" height="20"><p align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=BadgeAltTag4%></font></i></td>
</tr>
<tr>
<td width="100%" align="center" height="155"><p align="center"><%=BadgeString4%><br>
<font size="1">&nbsp; </font></td>
</tr>
<tr>
<td width="100%" align="center">&nbsp;</td>
</tr>
</table>
<!-- end of badges -->
</td>
<!-- end of column for badges -->
<!-- column for space between badges and banners/categories -->
<td width="14" valign="top">&nbsp; </td>
<!-- end of column for space -->
<!-- column for page content -->
<td width="580" valign="top">
<!-- content -->
<table border="0" cellpadding="0" cellspacing="0" width="580">
<tr>
<td width="580" colspan="3">
<!-- banners -->
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="92"><img border="0" src="main/pg_banner_pic.gif"></td>
<td width="487">
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="33%" background="common/pg_banner_bg.gif" height="20" align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=AltTag%></font></i></td>
<td width="33%" background="common/pg_banner_bg.gif" height="20" align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=AltTag2%></font></i></td>
<td width="34%" background="common/pg_banner_bg.gif" height="20" align="center"><i><font face="Verdana, Tahoma, Arial, Helvetica" size="2" color="#7A3C9A"><%=AltTag3%></font></i></td>
</tr>
<tr>
<td width="33%" background="common/pg_banner_bg2.gif" height="66" align="center"><%=BannerString%></td>
<td width="33%" background="common/pg_banner_bg2.gif" height="66" align="center"><%=BannerString2%></td>
<td width="34%" background="common/pg_banner_bg2.gif" height="66" align="center"><%=BannerString3%></td>
</tr>
</table>
</td>
<td width="1"><img src="common/pg_banner_end.gif" width="1" height="86" border="0"></td>
</tr>
</table>
<!-- end of banners -->
</td>
</tr>
<tr>
<td width="580" valign="top" colspan="3"><font size="1">&nbsp;&nbsp; </font></td>
</tr>
<tr>
<td width="580" valign="top" colspan="3">
<!-- category name and category menu -->
<form name="choose_category">
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td width="60%" valign="top"><font size="1">&nbsp; </font><br><font face="Verdana, Tahoma, Arial, Helvetica" color="#7A3F99" size="4"><i><b><%=CName%></b></i></font></td>
<td width="40%" valign="top" align="right"><p align="right"><font size="1">&nbsp;<font><br>
<font size="2"><select name="categories" size="1" onchange="if (this.options[this.selectedIndex].value != '') {parent.location.href =  this.options[this.selectedIndex].value}">
<option value="#">Choose Another Category</option>
<%
' BEGIN LISTING CATEGORIES **************************************************************************************
CategoryString = ""
Set rsCategories = cnSupport.Execute("SELECT * FROM tblCategories WHERE CategoryName <> 'Distributors' ORDER BY CategoryName")
do while not rsCategories.eof
	if CInt(rsCategories("CategoryID")) = CInt(CID) then
		CatSel = " selected"
	else
		CatSel = ""
	end if
	CatName = rsCategories("CategoryName")
	CatName = Replace(CatName," ","_")
	CatName = Replace(CatName,"&","and")
	CatName = Replace(CatName,"/",",")
	CatName = Replace(CatName,"Limos_","Limousines_")

CategoryString = CategoryString & "<option" & CatSel & " value=""professionals.asp?MARKETID=" & MARKETID & "&amp;CID=" & rsCategories("CategoryID") & "&amp;CAT=" & CatName & """>" & rsCategories("CategoryName") & "</option>" & vbcrlf

rsCategories.movenext
loop
rsCategories.close

%>
<%=CategoryString%>
<option value="distributors.asp?MARKETID=<%=MARKETID%>">-------------------</option>
<option value="distributors.asp?MARKETID=<%=MARKETID%>">DISTRIBUTION LOCATIONS</option>
<option value="distributors.asp?MARKETID=<%=MARKETID%>">-------------------</option>
</select>
</font></td>
</tr>
</table>
</form>
<!-- end of category name and menu -->
</td>
</tr>
<tr>
<td width="580" valign="top" colspan="3">
<font size="1">&nbsp;&nbsp; </font>
</td>
</tr>
<tr>
<td width="420" valign="top">
<!-- Begin Listings -->
<table border="0" width="100%" cellpadding="2">
<%
'ESTABLISH NEW/UPDATE DATES ***********************************************************************************
intInterval = -7
NewDate = DateAdd("D", intInterval, sDate)

'ESTABLISH COUNT ***********************************************************************************************
cntDirectory = 0
cntDirectory2 = 0

'ESTABLISH MATCH VALUE *****************************************************************************************
sMatch = ""

'RETREIVE LISTINGS *********************************************************************************************
SQL="SELECT tblAdvertiser_Locations.*, tblAdvertiser_Locations_Markets.*, tblAdvertiser_Locations_Categories.* "
SQL=SQL & "FROM tblAdvertiser_Locations, tblAdvertiser_Locations_Markets, tblAdvertiser_Locations_Categories "
SQL=SQL & "WHERE tblAdvertiser_Locations_Markets.MarketID = " & MARKETID & " "
SQL=SQL & "AND tblAdvertiser_Locations_Categories.CategoryID = " & CID & " "
SQL=SQL & "AND tblAdvertiser_Locations_Markets.LocationID = tblAdvertiser_Locations_Categories.LocationID "
SQL=SQL & "AND tblAdvertiser_Locations_Categories.LocationID = tblAdvertiser_Locations.LocationID "
SQL=SQL & "ORDER BY tblAdvertiser_Locations.EP DESC, tblAdvertiser_Locations.Name"
Set rsDirectory = cnSupport.Execute(SQL)
do while not rsDirectory.eof

sMatch2 = rsDirectory("Name") & rsDirectory("Address") & rsDirectory("Contact") & rsDirectory("Email")

if sMatch2 <> sMatch then

'BEGIN LOOPING LISTINGS ****************************************************************************************
Set rsDisable = cnSupport.Execute("SELECT * FROM tblAdvertisers WHERE AdvertiserID = " & rsDirectory("AdvertiserID") & " ORDER BY AdvertiserID")
while not rsDisable.eof
	sDisabled = rsDisable("Late")
	sSTATUS = rsDisable("STATUS")
rsDisable.movenext
Wend
rsDisable.close

if sDisabled <> "Yes" then
	if sSTATUS <> "Deactive" then
		if rsDirectory("Name") <> "" then
	
			'IF CITY IS ENTERED IN DATABASE, LIST THE CITY NEXT TO THE COMPANY NAME **********************************
			if rsDirectory("City") <> "" then
				sCity = ", <i>" & rsDirectory("City") & "</i>"
			else
				sCity = ""
			end if
			
			'IF LISTING IS LESS THAN 30 DAYS OLD, DISPLAY A NEW BANNER NEXT TO NAME **********************************		
			if rsDirectory("LocationDate") >= NewDate then
				if rsDirectory("UpdateDate") >= NewDate then
					sNew = ""
					sUpdate = "&nbsp;<img src='common/icon_updated.gif' border='0' width='62' height='13' alt='UPDATED Listing'>"
				else
					sUpdate = ""
					sNew = "&nbsp;<img src='common/icon_new.gif' border='0' width='62' height='13' alt='JUST Listed'>"
				end if
			else
				sNew = ""
				if rsDirectory("UpdateDate") >= NewDate then
					sUpdate = "&nbsp;<img src='common/icon_updated.gif' border='0' width='62' height='13' alt='UPDATED Listing'>"
				else
					sUpdate = ""
				end if
			end if
			
			if rsDirectory("Slogan") <> "" then
				sSlogan = "<br><font face=""Verdana, Tahoma, Arial, Helvetica"" color=""#000000"" size=""2""><i>" & rsDirectory("Slogan") & "</i></font>"
			else
				sSlogan = ""
			end if
			
			if rsDirectory("EP") = 3 then
				if cntDirectory = 0 then
%>
<tr>
<td width="100%" valign="middle" colspan="2"><p style="margin-left: 3"><img src="common/hdr_featuredpros.gif" width="155" height="25" border="0"></p>
</td>
</tr>
<%
				end if
			else
				if cntDirectory2 = 0 then
%>
<tr>
<td width="100%" valign="middle" colspan="2"><p style="margin-left: 3"><img src="common/hdr_pros.gif" width="125" height="25" border="0"></p>
</td>
<%
				end if
			end if
			
			if rsDirectory("EP") = 3 then
				strBullet = "blt_vendor_star.gif"
				strCellColor = "#FFFFFF"
			else
				strBullet = "blt_vendor.gif"
				if rsDirectory("EP") = 2 then
					strCellColor = "#FFFFD7"
				else
					if rsDirectory("EP") = 1 then
						strCellColor = "#ECEDFD"
					else
						strCellColor = "#DAF2D7"
					end if
				end if
			end if
%>
<tr>
<td width="7%" valign="middle">
<p align="center"><img border="0" src="common/<%=strBullet%>"></td>
<td width="93%" bgcolor="<%=strCellColor%>" valign="top">
<p style="margin-left: 3"><font face="Verdana, Tahoma, Arial, Helvetica" color="#081697" size="2"><A HREF="javascript:void(0)" onclick="N = window.open('popups/profilecnt.asp?MARKETID=<%=MARKETID%>&LID=<%=rsDirectory("LocationID")%>','N','toolbar=1,status=1,menubar=1,resizable=yes,scrollbars=yes,width=620,height=480'); if(!(N.closed)) {N.focus();} TimedDelay = setTimeout('if(!(N.closed)) {N.focus();}', 1500);"><%=rsDirectory("Name")%><%=sCity%></a></font><%=sNew%><%=sUpdate%><%=sSlogan%></p>
</td>
</tr>
<tr>
<td width="100%" colspan="2"><img src="common/blk_vendors.gif" border="0" height="6" width="25"></td>
</tr>
<%
		end if
	end if

	cntDirectory = cntDirectory + 1
	
	if rsDirectory("EP") = 3 then
		cntDirectory2 = cntDirectory2
	else
		cntDirectory2 = cntDirectory2 + 1
	end if

end if

sMatch = sMatch2
end if
rsDirectory.MoveNext
Loop
rsDirectory.close



'RETREIVE DISTRIBUTORS *********************************************************************************************
SQL="SELECT tblDistributors.*, tblDistributor_Markets.*, tblDistributor_Categories.* "
SQL=SQL & "FROM tblDistributors, tblDistributor_Markets, tblDistributor_Categories "
SQL=SQL & "WHERE tblDistributor_Markets.MarketID = " & MARKETID & " "
SQL=SQL & "AND tblDistributor_Categories.CategoryID = " & CID & " "
SQL=SQL & "AND tblDistributor_Markets.DistributorID = tblDistributor_Categories.DistributorID "
SQL=SQL & "AND tblDistributor_Categories.DistributorID = tblDistributors.DistributorID "
SQL=SQL & "ORDER BY tblDistributors.Name"
Set rsDirectory = cnSupport.Execute(SQL)
do while not rsDirectory.eof


	if rsDirectory("Name") <> "" then
	
		'IF CITY IS ENTERED IN DATABASE, LIST THE CITY NEXT TO THE COMPANY NAME **********************************
		if rsDirectory("City") <> "" then
		sCity = ", <i>" & rsDirectory("City") & "</i>"
		else
			sCity = ""
		end if
		
		'IF LISTING IS LESS THAN 30 DAYS OLD, DISPLAY A NEW BANNER NEXT TO NAME ***********************************	
		if rsDirectory("DistributorDate") >= NewDate then
			if rsDirectory("UpdateDate") >= NewDate then
				sNew = ""
				sUpdate = "&nbsp;<img src='common/icon_updated.gif' border='0' width='62' height='13' alt='UPDATED Listing'>"
			else
				sUpdate = ""
				sNew = "&nbsp;<img src='common/icon_new.gif' border='0' width='62' height='13' alt='JUST Listed'>"
			end if
		else
			sNew = ""
			if rsDirectory("UpdateDate") >= NewDate then
				sUpdate = "&nbsp;<img src='common/icon_updated.gif' border='0' width='62' height='13' alt='UPDATED Listing'>"
			else
				sUpdate = ""
			end if
		end if
		
		if rsDirectory("Slogan") <> "" then
			sSlogan = "<br><font face=""Verdana, Tahoma, Arial, Helvetica"" color=""#000000"" size=""2""><i>" & rsDirectory("Slogan") & "</i></font>"
		else
			sSlogan = ""
		end if

%>
<tr>
<td width="7%" valign="middle">
<p align="center"><img border="0" src="common/blt_vendor.gif"></td>
<td width="93%" bgcolor="#FFFFFF" valign="top">
<p style="margin-left: 3"><font face="Verdana, Tahoma, Arial, Helvetica" size="2"><A HREF="javascript:void(0)" onclick="N = window.open('popups/distprofilecnt.asp?DbID=<%=rsDirectory("DistributorID")%>','N','toolbar=1,status=1,menubar=1,resizable=yes,scrollbars=yes,width=620,height=480'); if(!(N.closed)) {N.focus();} TimedDelay = setTimeout('if(!(N.closed)) {N.focus();}', 1500);"><%=rsDirectory("Name")%><%=sCity%></a></font><%=sNew%><%=sUpdate%><%=sSlogan%></p>
</td>
</tr>
<tr>
<td width="100%" colspan="2"><img src="common/blk_vendors.gif" border="0" height="6" width="25"></td>
</tr>
<%

		cntDirectory = cntDirectory + 1

	end if

rsDirectory.movenext
loop
rsDirectory.close



if cntDirectory = 0 then
	strCount = "No"
else
	strCount = cntDirectory
end if

if cntDirectory = 1 then
	strEntries = "listing"
else
	strEntries = "listings"
end if

%>
<!-- Record Count -->
<tr>
<td width="100%" colspan="2"><p style="margin-left: 23"><font face="Verdana, Tahoma, Arial, Helvetica" color="#081697" size="2">&nbsp;<br><i><b><%=strCount%></b> <%=strEntries%> in <%=FName%> for &quot;<%=CName%>&quot;</i></font> </p>
<p style="margin-left: 23"><form><p style="margin-left: 23"><img border="0" src="common/hdr_choosecategory.gif"><br>
<select name="categories" size="1" onchange="if (this.options[this.selectedIndex].value != '') {parent.location.href =  this.options[this.selectedIndex].value}">
<option value="#">Choose Another Category</option>
<%=CategoryString%>
<option value="distributors.asp?MARKETID=<%=MARKETID%>">-------------------</option>
<option value="distributors.asp?MARKETID=<%=MARKETID%>">DISTRIBUTION LOCATIONS</option>
<option value="distributors.asp?MARKETID=<%=MARKETID%>">-------------------</option>
</select></p>
</form></td>
</tr>
<!-- End of Count -->
</table>
<!-- End Contents ---->
</td>
<td width="14" valign="top">&nbsp; </td>
<td width="146" valign="top">
<!-- info boxes -->
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="100%"><img border="0" src="common/pg_header_wordfromdoug.gif"></td>
</tr>
<tr>
<td width="100%" background="common/pg_right_bg2.gif"><p style="margin-left: 5; margin-right: 5"><font face="Verdana, Tahoma, Arial, Helvetica" size="2">Coming Soon, so stay tuned...</font></td>
</tr>
<tr>
<td width="100%"><img border="0" src="common/pg_right_bottom2.gif"></td>
</tr>
<tr>
<td width="100%"><font size="1">&nbsp; </font></td>
</tr>
<tr>
<td width="100%"><img border="0" src="common/pg_header_askdoug.gif"></td>
</tr>
<tr>
<td width="100%" background="common/pg_right_bg2.gif"><p style="margin-left: 5; margin-right: 5"><font face="Verdana, Tahoma, Arial, Helvetica" size="2">Have a question? Email Us at <a href="mailto:admin@mysite.com">admin@mysite.com</a>. If we 
publish your  email and your reply, we'll omit your name!<br>
</font><font face="Verdana, Tahoma, Arial, Helvetica" size="1">&nbsp; <br>
</font><font face="Verdana, Tahoma, Arial, Helvetica" size="2">Get the answers to your home and garden questions with &quot;Dear Doug&quot;!</font></td>
</tr>
<tr>
<td width="100%"><img border="0" src="common/pg_right_bottom2.gif"></td>
</tr>
<tr>
<td width="100%"><font size="1">&nbsp;&nbsp; </font></td>
</tr>
</table>
<!-- end of info boxes -->
</td>
</tr>
<tr>
<td width="420">&nbsp;</td>
<td width="14">&nbsp;</td>
<td width="146">&nbsp;</td>
</tr>
</table>
<!-- end of content -->
</td>
<!-- end of column for page content -->
</tr>
<tr>
<td width="739" colspan="3"><img border="0" src="common/pg_badge_bg_bottom.gif"></td>
</tr>
<tr>
<td width="145">&nbsp;</td>
<td width="14">&nbsp;</td>
<td width="580">&nbsp;</td>
</tr>
</table>
</div>

<!-- #include file="footer.asp" -->

<p align="center">&nbsp;</p>

</body>

</html>

rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

baddos · Mar 3, 2004

Try this code out... It basically scales down your Professionals.asp page. Make sure you try it on a test web server before uploading to your live site though

.

In SQL Server, create a stored proceedure in your database with this code:

Code:

CREATE PROCEDURE [upd_Professionals]
	@marketid int,
	@catid int,
	@countdate datetime,
	@countMarket bit,
	@Market varchar(50) OUTPUT,
	@IntOnly int OUTPUT,
	@Bundle varchar(50) OUTPUT,
	@CategoryName varchar(50) OUTPUT,
	@mkthitcount int OUTPUT,
	@mktcathitcount int OUTPUT
AS

/* Get Franchise / Market Name */

Set @Market = (SELECT Market FROM tblMarkets WHERE MarketID = @marketid)
Set @IntOnly = (SELECT IntOnly FROM tblMarkets WHERE MarketID = @marketid)
Set @Bundle = (SELECT Bundle FROM tblMarkets WHERE MarketID = @marketid)

/* Get CategoryName */
Set @CategoryName = (SELECT CategoryName FROM tblCategories WHERE CategoryID = @catid)

/* Update Market_Hits Table with it's new hitcount */
Set @mkthitcount = (SELECT Count(*) FROM tblMarket_Hits WHERE MarketID = @marketid AND CountDate = @countdate)
IF @mkthitcount <> 0
         UPDATE tblMarket_Hits SET FCount = FCount + 1
Else
     INSERT INTO tblMarket_Hits (MarketID, FCount, CountDate) VALUES (@marketid, 1, @countdate)
     SET @mkthitcount = 1

/* Update tblMarket_Hits_Categories Table with it's new hitcount */

Set @mktcathitcount = (SELECT Count(*) FROM tblMarket_Hits_Categories WHERE MarketID = @marketid AND CountDate = @countdate AND CategoryID = @catid)
IF @mktcathitcount <> 0
         UPDATE tblMarket_Hits_Categories SET CCount = CCount + 1
Else
     INSERT INTO tblMarket_Hits_Categories (MarketID, CCount, CountDate, CategoryID) VALUES (@marketid, 1, @countdate, @catid)
     SET @mktcathitcount = 1
GO

Then replace your professionals.asp page with this...

Code:

<%
'DIM VARIABLES ************************************************************************************************
Dim FID, MARKETID, CID, rsName, FName, IntOnly, Bundle, rsName2, CName, sDate, strDate
Dim rsCheck, sAction, FCount, RS, CmdUpdateRecord, SQL, sCAction, CCount
Dim objCmd, blnUpdateMarketCount

'GET TODAYS DATE **********************************************************************************************
sDate = Now()
strDate = FormatDateTime(sDate, 2)

'Find out if need to update Market Count **********************************************************************
If Session("MYSITEVISIT") = MARKETID Then
    blnUpdateMarketCount = True
Else
	blnUpdateMarketCount = False
End If


'RETRIEVE FRANCHISE ID AND CATEGORY ID *************************************************************************
MARKETID = Request.Querystring("MARKETID")
CID = Request.Querystring("CID")

'Retrieve Franchise/MarketName, Category Name, Market Hits
cnSupport.Open
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = cnSupport
objCmd.CommandType =adCmdStoredProc
objCmd.CommandText = "upd_Professionals"
'Build our stored proceedure's paramaeters
objCmd.Parameters.Append objCmd.CreateParameter("@marketid", adInteger, adParamInput, 4, MARKETID)
objCmd.Parameters.Append objCmd.CreateParameter("@catid", adInteger, adParamInput, 4, CID)
objCmd.Parameters.Append objCmd.CreateParameter("@countdate", adVarChar, adParamInput, 50, strDate)
objCmd.Parameters.Append objCmd.CreateParameter("@countMarket", adBoolean, adParamInput, 1, blnUpdateMarketCount)
objCmd.Parameters.Append objCmd.CreateParameter("@Market", adVarChar, adParamOutput, 50)
objCmd.Parameters.Append objCmd.CreateParameter("@IntOnly", adInteger, adParamOutput, 4)
objCmd.Parameters.Append objCmd.CreateParameter("@Bundle", adVarChar, adParamOutput, 50)
objCmd.Parameters.Append objCmd.CreateParameter("@CategoryName", adVarChar, adParamOutput, 50)
objCmd.Parameters.Append objCmd.CreateParameter("@mkthitcount", adInteger, adParamOutput, 4)
objCmd.Parameters.Append objCmd.CreateParameter("@mktcathitcount", adInteger, adParamOutput, 4)
objCmd.Execute 'Execute the stored proceedure
FName = objCmd.Parameters.Item("@Market")
IntOnly = objCmd.Parameters.Item("@IntOnly")
FName = objCmd.Parameters.Item("@Bundle")
CName = objCmd.Parameters.Item("@CategoryName")
FCount = objCmd.Parameters.Item("@mkthitcount")
CCount = objCmd.Parameters.Item("@mktcathitcount")
cnSupport.Close
Set objCmd = Nothing

'IF FRANCHISE DOES NOT PAY FOR MYSITE.COM, REDIRECT THEM TO MAP PAGE ******************************
if Bundle <> "Plat" then
    Response.Redirect ("index_choose.asp?SEL=NONE")
    Response.end
end if

'Make sure to set the VISIT Session Variant
Session("MYSITEVISIT") = MARKETID
%>

Rexolio · Mar 3, 2004

Baddos,

You're the Man/Woman!

I'm trying it, but I'm getting the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'No' to a column of data type int.

/stored_procedures/Scripts_Professionals.asp, line 39

That line (39) is the last line here:

Code:

objCmd.Parameters.Append objCmd.CreateParameter("@marketid", adInteger, adParamInput, 4, MKTID)
objCmd.Parameters.Append objCmd.CreateParameter("@catid", adInteger, adParamInput, 4, CID)
objCmd.Parameters.Append objCmd.CreateParameter("@countdate", adVarChar, adParamInput, 50, strDate)
objCmd.Parameters.Append objCmd.CreateParameter("@countMarket", adBoolean, adParamInput, 1, blnUpdateMarketCount)
objCmd.Parameters.Append objCmd.CreateParameter("@Market", adVarChar, adParamOutput, 50)
objCmd.Parameters.Append objCmd.CreateParameter("@IntOnly", adInteger, adParamOutput, 4)
objCmd.Parameters.Append objCmd.CreateParameter("@Bundle", adVarChar, adParamOutput, 50)
objCmd.Parameters.Append objCmd.CreateParameter("@CategoryName", adVarChar, adParamOutput, 50)
objCmd.Parameters.Append objCmd.CreateParameter("@mkthitcount", adInteger, adParamOutput, 4)
objCmd.Parameters.Append objCmd.CreateParameter("@mktcathitcount", adInteger, adParamOutput, 4)
objCmd.Execute 'Execute the stored proceedure

I think the problem is with the line about @countMarket, but I'm not sure.

[bugeyed]

rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

baddos · Mar 4, 2004

Yes... the value for that param need to be (true or false) or (0 or -1).

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!

Too Many ASP and DB errors to name off!!!

Rexolio

Technical User

YNOTU

Technical User

baddos

MIS

Rexolio

Technical User

baddos

MIS

baddos

MIS

Rexolio

Technical User

baddos

MIS

Rexolio

Technical User

Rexolio

Technical User

baddos

MIS

Rexolio

Technical User

baddos

MIS

Similar threads

Part and Inventory Search

Sponsor