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

[Rexolio]

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:

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.



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

 

[YNOTU]

I think you would find this post thread333-721855 helpful

​

 

[baddos]

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]

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:

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:

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

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

SELECT TOP 10 * FROM dbo.tblNames ORDER BY NameID

or

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.


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

 

[baddos]

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]

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]

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.


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

 

[baddos]

There is plenty of stuff to laught at on the net, but not in this forum. Go ahead and post away.

 

[Rexolio]

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.

<%
'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]

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:

<!-- #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]

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:

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...

<%
'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]

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:

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.



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

 

[baddos]

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