Connect to SQL 2005 with ASP is not working

**dude9er** · 06-27-2007, 09:48 PM

My hosting service just updated their SQL Server from 2000 to 2005. I have been trying to update my code and keep getting this error:

Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "//"]'

Can someone please have a look at my connecting code and see what needs to be updated for the 2005 version. I would appreciate it as I have not been able to figure this out. Here is my ASP:

Code:

<%
Dim connectstr, fieldname, tablename
Dim db_name, db_username, db_userpassword
Dim db_server
Dim checkConn,countrec,check_qry,check_connectstr,checkRS

Dim sConnString, connection, sSQL, oRS, oConn
'Dim lname, fname, address1, address2, city, state, zip, phone, email, nranumber, nraexpires, gifter, mag, cc_typev, cc_numv, cc_expiresv, seccodev, cc_addr1v, cityv, statev, zipcodev, phonev, emailv

team_name = left(Trim(Replace(Request.Form("team_name"),"'","''")),40)
capf_name = left(Trim(Replace(Request.Form("capfname"),"'","''")),40)
capl_name = left(Trim(Replace(Request.Form("caplname"),"'","''")),40)
address1 = left(Trim(Replace(Request.Form("address1"),"'","''")),40)
address2 = left(Trim(Replace(Request.Form("address2"),"'","''")),40)
city = left(Trim(Replace(Request.Form("city"),"'","''")),40)
state = left(Trim(Replace(Request.Form("state"),"'","''")),40)
zip = left(Trim(Replace(Request.Form("zip"),"'","''")),40)
phone = left(Trim(Replace(Request.Form("phone"),"'","''")),40)
email = left(Trim(Replace(Request.Form("email"),"'","''")),40)
yardage = left(Trim(Replace(Request.Form("yardage"),"'","''")),40)
ataNum = left(Trim(Replace(Request.Form("ataNum"),"'","''")),40)
ataAvg = left(Trim(Replace(Request.Form("ataAvg"),"'","''")),40)
ataYard = left(Trim(Replace(Request.Form("ataYard"),"'","''")),40)
photo = left(Trim(Replace(Request.Form("photo"),"'","''")),40)
ccNAME = left(Trim(Replace(Request.Form("CCNAME"),"'","''")),40)
CC_TYPE = left(Trim(Replace(Request.Form("CC_TYPE"),"'","''")),40)
CC_NUM = left(Trim(Replace(Request.Form("CC_NUM"),"'","''")),40)
CC_EXPIRES = left(Trim(Request.Form("CC_expmonth") & " " & Request.Form("CC_expyear")),40)
SecCode = left(Trim(Replace(Request.Form("SecCode"),"'","''")),40)
CC_ADDR1 = left(Trim(Replace(Request.Form("CC_ADDR1"),"'","''")),40)
CC_CITY = left(Trim(Replace(Request.Form("CC_CITY"),"'","''")),40)
CC_STATE = left(Trim(Replace(Request.Form("CC_STATE"),"'","''")),40)
CC_ZIPCODE = left(Trim(Replace(Request.Form("CC_ZIPCODE"),"'","''")),40)
CC_PHONE = left(Trim(Replace(Request.Form("CC_PHONE"),"'","''")),40)
CC_EMAIL = left(Trim(Replace(Request.Form("CC_EMAIL"),"'","''")),40)

DBConnectionTwunk()
	check_connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
Set checkConn = Server.CreateObject("ADODB.Connection")
	checkConn.Open check_connectstr
	countrec=0
	
'================================================================
dim InputError,Reason,Reason2,Reason3
InputError=0
'check for numeric vals in str
'if no pass test, no need to continue proc
If Not IsNumeric(CC_NUM) Then
	Reason = "<strong>ERROR:</strong> Card Number contains invalid characters. Use Numbers ONLY without hyphens or spaces."
	InputError=InputError+1
ELSE
if CC_TYPE = "AMEX" AND Len(CC_NUM) <> 15 THEN
	Reason = "<strong>ERROR:</strong> AMEX Number is " & Len(CC_NUM) & " Digits Long instead of 15."
		InputError=InputError+1
	      else
	if CC_TYPE = "VISA" AND Len(CC_NUM) = 13 Or Len(CC_NUM) = 16 THEN
	      Else
	InputError=InputError+1
	Reason = "<strong>ERROR:</strong> Visa Card Number is " & Len(CC_NUM) & " Digits Long instead of 13 or 16."
	InputError=InputError+1
		end if	
	if CC_TYPE = "MasterCard" AND Len(CC_NUM) <> 16 THEN
	Reason = "<strong>ERROR:</strong> MasterCard Number is " & Len(CC_NUM) & " Digits Long instead of 16."
	InputError=InputError+1
		end if
		'===============================================
if CC_TYPE = "AMEX"  AND Len(SecCode) <> 4 THEN
	Reason3 = "<strong>ERROR:</strong> AMEX Security Code should contain <strong>4 </strong>Digits."
	InputError=InputError+1
end if

if CC_TYPE = "VISA"  AND Len(SecCode) <> 3 THEN
	Reason3 = "<strong>ERROR:</strong> Visa Security Code should contain <strong>3 </strong>Digits."
	InputError=InputError+1
end if

if CC_TYPE = "MasterCard"  AND Len(SecCode) <> 3 THEN
	Reason3 = "<strong>ERROR:</strong> MasterCard Security Code should contain <strong>3</strong> Digits."
	InputError=InputError+1
end if
end if	
end if
		
'===================================================		
dim CCExpDate,LLDate,ConcDate
'Concatenate date str for compare with CDate vals, if this is needed
dim DaysInMos,DateDiffTest

'Select CASE based on card month input from usr
Select Case CC_expmonth
	Case "1"
			DaysInMos="31"
	Case "2"
		'forgot to calc for leap yr for day diff in Feb
		'Every year divisible by 4 IS a leap year.
		'But every year divisible by 100 is NOT a leap year
		'Unless the year IS ALSO divisible by 400, then it is still a leap year.
		'i.e., 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years, year 2000 and 2400 are.

		'establish generic days-in-mos for Feb
		DaysInMos=28

		'check for Leap Year and chg days to 29 instead
		right_year_divided=cc_expyear/4
			if right_year_divided = cint(right_year_divided) then
				DaysInMos=29
			end if

			if right(cc_expyear,2)="00" then
				right_year_divided=cc_expyear/400

					if right_year_divided = cint(right_year_divided) then
						DaysInMos=29
					end if ' I checked of year values
			end if ' I checked for new century

	Case "3"
			DaysInMos="31"
	Case "4"
			DaysInMos="30"
	Case "5"
			DaysInMos="31"
	Case "6"
			DaysInMos="30"
	Case "7"
			DaysInMos="31"
	Case "8"
			DaysInMos="31"
	Case "9"
			DaysInMos="30"
	Case "10"
			DaysInMos="31"
	Case "11"
			DaysInMos="30"
	Case "12"
			DaysInMos="31"
End Select

'Concantenate cc exp str for compare
ConcDate = CC_expmonth & "/" & DaysInMos & "/" & CC_expyear

'Check differences in Card Date and Now
'Complain if diff is 0 or neg num
DateDiffTest=DateDiff("D", Date, ConcDate)
if DateDiffTest =< 0 then
	Reason2 = "<strong>ERROR:</strong> Credit Card Expired. "
	InputError=InputError+1
end if


'===================================================


'Last Chance to Check Contact/Billing Info
'CC_NAMEv CC_ADDR1v CC_CITYv CC_STATEv CC_ZIPCODEv CC_PHONEv CC_EMAILv
if ccNAME = "" then
	Reason = " Contact Information is Missing a Name."
	InputError=InputError+1
End if
if CC_ADDR1 = "" then
	Reason = " Contact Information is Missing an Address."
	InputError=InputError+1
End if
if CC_CITY = "" then
	Reason = " Contact Information is Missing a City."
	InputError=InputError+1
End if
if CC_STATE = "" then
	Reason = " Contact Information is Missing a State."
	InputError=InputError+1
End if
if CC_ZIPCODE = "" then
	Reason = " Contact Information is a ZipCode"
	InputError=InputError+1
End if
if CC_PHONE = "" then
	Reason = " Contact Information is Missing a Phone Number."
	InputError=InputError+1
End if
if CC_EMAIL = "" then
	Reason = " Contact Information is an Email Address"
	InputError=InputError+1
End if
if InputError = 0 then


AddToDatabase()
response.redirect "Team_ThankYou.asp"

End if

'Complain or agree about the data because of InputError proc

	'say you posted to server
	'send user to dbase submit routine HERE
'=======================

Function DBConnectionTwunk()
'Routine makes SQLServer DB Conn on GoDaddy Server
	db_server = "something.net"
	db_name = "DB_12345"
	db_username = "something"
	db_userpassword = "something"
	fieldname = "team_name,capfname,caplname,address1,address2,city,state,zip,phone,email,yardage,ataNum,ataAvg,ataYard,photo,ccNAME,CC_TYPE,CC_NUM,CC_EXPIRES,SecCode,CC_ADDR1,CC_CITY,CC_STATE,CC_ZIPCODE,CC_PHONE,CC_EMAIL"
	tablename = "teamtrap"
End Function
'====================
'FUNCTION LIB
'====================

'Call Request to Add Record to DB
function AddToDatabase()
	sSQL = "INSERT INTO teamtrap ([team_name], [capfname], [caplname], [address1], [address2], [city], [state], [zip], [phone], [email], [yardage], [ataNum], [ataAvg], [ataYard], [photo], [ccNAME], [CC_TYPE], [CC_NUM], [CC_EXPIRES], [SecCode], [CC_ADDR1], [CC_CITY], [CC_STATE], [CC_ZIPCODE], [CC_PHONE], [CC_EMAIL]) VALUES ('" & _
	team_name & "', '" & capfname & "', '" & caplname & "', '" & address1 & "', '" & address2 & "', '" & city & "', '" & state & "', '" & zip & "', '" & phone & "', '" & email & "', '" & yardage & "', '" & ataNum & "', '" & ataAvg & "', '" & ataYard & "', '" & photo & "', '" & ccNAME & "', '" & CC_TYPE & "', '" & CC_NUM & "', '" & CC_EXPIRES & "', '" & SecCode & "', '" & CC_ADDR1 & "', '" & CC_CITY & "', '" & CC_STATE & "', '" & CC_ZIPCODE & "', '" & CC_PHONE & "', '" & CC_EMAIL & "')" 

	'Call TALK to SQLServer DB
	DBConnectionTwunk()
	connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
	Set oConn = Server.CreateObject("ADODB.Connection")
	oConn.Open connectstr
	Set oRS = oConn.Execute(sSQL)

End Function
%>

THANKS EVERYONE!!

**SURFThru** · 06-30-2007, 09:31 PM

Hi Dude9er,

I develop in ASP.NET so I thought I would check the connection string format. Since you cannot use the native .NET provider I created a working ODBC string and pasted it below. You may also want to download SQL Server 2005 Express. Its free. I use this for my local development.

My site is hosted on 2005 as well.

DRIVER=SQL Native Client
UID=SqlTmp
PWD=tmp
DATABASE=Northwind
APP=Microsoft Data Access Components
SERVER=My2005Server
Description=SQL2005

SQL Server Express 2005 - Free Full Development Version
http://msdn.microsoft.com/vstudio/express/sql/download/

SURFThru.com - Better Search Results

Regards,
Patrick

Thread: Connect to SQL 2005 with ASP is not working

Thread Tools

Search Thread

Connect to SQL 2005 with ASP is not working

Bookmarks

Bookmarks

Posting Permissions