PDA

View Full Version : Connect to SQL 2005 with ASP is not working



dude9er
06-27-2007, 10: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:





<%
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,C C_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, 10: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 (http://www.SURFThru.com)

Regards,
Patrick