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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Type Mismatch

Status
Not open for further replies.
coachdan

coachdan

MIS
Mar 1, 2002
269
US
I am having trouble with a connection string that I use to connect to several different db's. It is strange because I use the same string on numerous pages and it works on some, but not on a couple. I get a type mismatch error within the following code:

Code: 
If varBusName = "Citi" Then
If varYrA = "2002" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2003" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data03.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2004" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data04.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

  Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2005" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data05.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2006" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data06.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2007" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data07.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2008" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data08.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2009" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data09.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2010" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data10.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   		Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

Else

If varYrA = "2002" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_DataSears.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2003" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data03Sears.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2004" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data04Sears.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2005" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data05Sears.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2006" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data06Sears.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2007" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data07Sears.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2008" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data08Sears.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2009" Then

	Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
			& "Data Source=" & Server.Mappath("Training_Delivery_Data09Sears.mdb") & ";"

	SQLAdd = " SELECT * FROM TrainersAU "

	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

If varYrA = "2010" Then

   Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath("Training_Delivery_Data10Sears.mdb") & ";"

   	SQLAdd = " SELECT * FROM TrainersAU "

   	Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

End If

coachdan32

 
Sort by date Sort by votes
Wow, a lot of code there to wallow through.

Is there authentication happening here? Try changing input from 'type' to 'varchar'.

When in doubt, deny all terms and defnitions.
 
Upvote 0 Downvote
So, which bit doesn't work? What values do varBusName and varYrA have?

It won't solve your problem, but it will make your code a lot more concise, if you code it like this:
[tt]
If varBusName = "Citi" Then
If varYrA = "2002" Then
varDB = "Training_Delivery_Data.mdb"
ElsIf VarYrA = "2003" Then
varDB = "Training_Delivery_Data03.mdb"
' ... More elsif clauses in here ...
End If
End If;

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath(varDB) & ";"

SQLAdd = " SELECT * FROM TrainersAU "

Set uRS = Server.CreateObject("ADODB.Recordset")
uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic
[/tt]
Not sure what language this code is in (some flavour of VB?), if it has a Case statement you could use it instead of some of the if/elsifs.

-- Chris Hunt
 
Upvote 0 Downvote
ChrisHunt,

It is written in vbscript. I am a newby, so I may have some mistakes. But I don't understand why it works on some and not on others. I thought about the case statement, but was not real sure how to go about it. I will give the above suggestion a try, should it be "elseif" or is "elsif" correct?

coachdan32

 
Upvote 0 Downvote
Chris,

It would probably help you if I answered all of your questions, I submitted before I was ready. I am not sure where exactly it breaks down. The error line returned is 522, which is the SQL statement on year 2007 with in the Training_Delivery_Data07Sears.mdb string. All I am given to work with is FrontPage, so the only assistance I get with debugging is the error messages returned by the browser. VarBusName has a value stored in it by a session variable chosen from a dropdown (either "Citi" or "Sears") and varYrA has the year value selected from a dropdown (2003, 2004, etc...)

coachdan32

 
Upvote 0 Downvote
I tried the following code, as an attempt to try your idea, but have something wrong within the if nesting. I get this error message:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/Training_Dept/KCOCP.asp, line 299

Elsif varYrA = "2003" Then
----------------------^

The code used:

Code: 
If varBusName = "Citi" Then
	If varYrA = "2002" Then
		varDB = "Training_Delivery_Data.mdb"
	Elsif varYrA = "2003" Then
		varDB = "Training_Delivery_Data03.mdb"
	Elsif varYrA = "2004" Then
		varDB = "Training_Delivery_Data04.mdb"
	Elsif varYrA = "2005" Then
		varDB = "Training_Delivery_Data05.mdb"
	Elsif varYrA = "2006" Then
		varDB = "Training_Delivery_Data06.mdb"
	Elsif varYrA = "2007" Then
		varDB = "Training_Delivery_Data07.mdb"
	Elsif varYrA = "2008" Then
		varDB = "Training_Delivery_Data08.mdb"
	Elsif varYrA = "2009" Then
		varDB = "Training_Delivery_Data09.mdb"
	Elsif varYrA = "2010" Then
		varDB = "Training_Delivery_Data10.mdb"
	End If
	End If
	End If
	End If
	End If
	End If
	End If
	End If
	End If
Elsif varBusName = "Sears" Then
	If varYrA = "2002" Then
		varDB = "Training_Delivery_DataSears.mdb"
	Elsif varYrA = "2003" Then
		varDB = "Training_Delivery_Data03Sears.mdb"
	Elsif varYrA = "2004" Then
		varDB = "Training_Delivery_Data04Sears.mdb"
	Elsif varYrA = "2005" Then
		varDB = "Training_Delivery_Data05Sears.mdb"
	Elsif varYrA = "2006" Then
		varDB = "Training_Delivery_Data06Sears.mdb"
	Elsif varYrA = "2007" Then
		varDB = "Training_Delivery_Data07Sears.mdb"
	Elsif varYrA = "2008" Then
		varDB = "Training_Delivery_Data08Sears.mdb"
	Elsif varYrA = "2009" Then
		varDB = "Training_Delivery_Data09Sears.mdb"
	Elsif varYrA = "2010" Then
		varDB = "Training_Delivery_Data10Sears.mdb"
	End If
	End If
	End If
	End If
	End If
	End If
	End If
	End If
	End If

End If
End If

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   			& "Data Source=" & Server.Mappath(varDB) & ";"	

SQLAdd = " SELECT * FROM KCO "

Set uRS = Server.CreateObject("ADODB.Recordset")
	uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

coachdan32

 
Upvote 0 Downvote
You don't need all those [tt]End If[/tt]s, each Els(e)If clause forms part of the same If statement (Some languages spell it [tt]Elsif[/tt], others use [tt]Elseif[/tt]; I don't know which one vbscript uses - try both!). So your code should be something like
[tt]
If varBusName = "Citi" Then
If varYrA = "2002" Then
varDB = "Training_Delivery_Data.mdb"
Elsif varYrA = "2003" Then
varDB = "Training_Delivery_Data03.mdb"
Elsif varYrA = "2004" Then
varDB = "Training_Delivery_Data04.mdb"
Elsif varYrA = "2005" Then
varDB = "Training_Delivery_Data05.mdb"
Elsif varYrA = "2006" Then
varDB = "Training_Delivery_Data06.mdb"
Elsif varYrA = "2007" Then
varDB = "Training_Delivery_Data07.mdb"
Elsif varYrA = "2008" Then
varDB = "Training_Delivery_Data08.mdb"
Elsif varYrA = "2009" Then
varDB = "Training_Delivery_Data09.mdb"
Elsif varYrA = "2010" Then
varDB = "Training_Delivery_Data10.mdb"
End If
Elsif varBusName = "Sears" Then
If varYrA = "2002" Then
varDB = "Training_Delivery_DataSears.mdb"
Elsif varYrA = "2003" Then
varDB = "Training_Delivery_Data03Sears.mdb"
Elsif varYrA = "2004" Then
varDB = "Training_Delivery_Data04Sears.mdb"
Elsif varYrA = "2005" Then
varDB = "Training_Delivery_Data05Sears.mdb"
Elsif varYrA = "2006" Then
varDB = "Training_Delivery_Data06Sears.mdb"
Elsif varYrA = "2007" Then
varDB = "Training_Delivery_Data07Sears.mdb"
Elsif varYrA = "2008" Then
varDB = "Training_Delivery_Data08Sears.mdb"
Elsif varYrA = "2009" Then
varDB = "Training_Delivery_Data09Sears.mdb"
Elsif varYrA = "2010" Then
varDB = "Training_Delivery_Data10Sears.mdb"
End If
End If

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath(varDB) & ";"

SQLAdd = " SELECT * FROM KCO "

Set uRS = Server.CreateObject("ADODB.Recordset")
uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic
[/tt]
As noted above, you could replace the inner If statements (the year checks) with a [tt]Case[/tt] statement, but you'd have to look up the syntax.

-- Chris Hunt
 
Upvote 0 Downvote
ChrisHunt,

I got past that OK - it was Elseif in vbscript. I agree with you that your code is much more efficient. However, getting past that only got me back to a Type Mismatch error. The line number changed to a point just past the connection string, within the uRS.AddNew. It's very strange that the original connection works in other pages. I'm starting to think that the way the pages were originally setup is causing the problem. Most of the pages in the site use an include file to determine the connection information, but some of them (this one included) uses both the include file and the connection statement we have been working on. There are other pages though that use both without error.

coachdan32

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dmacster
  • Locked
  • Question
Moveable Type shows parent directory listing
Replies
6
Views
91
dmacster
dmacster
skiflyer
  • Locked
  • Question
input type image 1
Replies
4
Views
21
skiflyer
skiflyer
BabyJeffy
  • Locked
  • Helpful tip
Typetester - free online typography tool
Replies
2
Views
42
BabyJeffy
BabyJeffy
mrnoisy
  • Locked
  • Question
Setting the default value for <input type=file>
Replies
1
Views
48
VBRookie
VBRookie
J1mbo
  • Locked
  • Question
How to force file download of a known file type
Replies
7
Views
38
lebisol
lebisol

Part and Inventory Search

Sponsor

Back
Top