|
 |
asp_databases thread: sending the code
Message #1 by "sushma roopa kadambari" <sushmark1@r...> on 15 Oct 2001 05:17:06 -0000
|
|
hello..
this is class in vb which will be calling sql
Public Function validate(userid As String, userpwd As String, role As String)
As String 'passing userid ,password and role from asp's page.. 'this module helps
in logging into the application.. Dim cnn As New Connection Dim cmd As New
Command Dim rs1 As New ADODB.Recordset Dim r As Integer Dim ssql As
String 'setting the connection Set cnn = New Connection
cnn.ConnectionString = "provider=sqloledb;server=212.162.196.114;
database=employee;uid=sushma;pwd=roopa;"
cnn.Open Set rs1 = New ADODB.Recordse
t rs1.CursorType = adOpenStatic 'retriving the data from database "employee"
ssql = "select * from pwdtbl where id='" & userid & "'" rs1.Open ssql, cnn,
adOpenStatic, adLockReadOnly, adCmdText 'checking the user's validity
If rs1.RecordCount = 0 Then 'user dosnot exist
validate = "sorry user doesnot exist" rs1.Close Set rs1 = Nothing
Exit Function Else ' checking for password..and his role....
If (userpwd = rs1.Fields("pwd")) Then
If (role = "manager") Then
If role = rs1.Fields("role") Then
validate = "true"
Else
validate = "sorry you are not authorised to login"
End If
Else
validate = "true"
End If
Else
validate = "login failed"
End If
rs1.Close Set rs1 = Nothing
End If
cnn.Close Set cnn = Nothing End Function
and this is the vb script... dim userid,userpwd,userrole
dim str1 userid=Request.Form("text1") userpwd=Request.Form("text2")
userrole=Request.Form("s1")
straction=Request.Form ("ok") if straction="ok" then
set t1=server.CreateObject("vacation.initial")
str1=t1.validate (userid,userpwd,userrole) if (str1="true")then
select case userrole case manager gotopage=emp.asp
case yourself gotopage=emp.asp else Response.Write (str1) end if %>
Message #2 by David Cameron <dcameron@i...> on Mon, 15 Oct 2001 17:07:38 +1000
|
|
<warning SarcasmLevel="Low"><!-- Thanks Peter Foti -->
Thanks for passing on your code. I really enjoyed reading it. Is there a
problem with it? If so it would be good to let us know what the problem is.
</warning>
Also, please consider the effect of line wrapping in emails. At the moment
your code is, to me, illegible. See a sample of what I received below:
<snip>
New
Command Dim rs1 As New ADODB.Recordset Dim r As Integer Dim ssql As
String 'setting the connection Set cnn = New Connection
cnn.ConnectionString = "provider=sqloledb;server=212.162.196.114;
database=employee;uid=sushma;pwd=roopa;"
cnn.Open Set rs1 = New ADODB.Recordset rs1.CursorType = adOpenStatic
</snip>
regards
David Cameron
nOw.b2b
dcameron@i...
Message #3 by "Ken Schaefer" <ken@a...> on Mon, 15 Oct 2001 18:49:47 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "David Cameron" <dcameron@i...>
Subject: [asp_databases] RE: sending the code
: <warning SarcasmLevel="Low"><!-- Thanks Peter Foti -->
: Thanks for passing on your code. I really enjoyed reading it. Is there a
: problem with it? If so it would be good to let us know what
: the problem is.
: </warning>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I believe the code is related to the thread "Accessing SQL from components
in ASP". Someone asked the original poster to supply their code.
Cheers
Ken
Message #4 by "Iyappan P" <iyappan_p@h...> on Mon, 15 Oct 2001 14:02:45
|
|
Public Function
validate(userid As String, userpwd As String, role As String)
change this line in your vb dll to
Public Function
validate(ByVal userid As String,ByVal userpwd As String,ByVal role As
String)
In ASP
str1=t1.validate (userid,userpwd,userrole)
change this line to
str1=t1.validate (cstr(userid),cstr(userpwd),cstr(userrole))
ByRef is the default in Visual Basic. But in MTS 2, you can pass it byref
only if u declare the argument as variant. but in this case u have declared
it as string. that is why u r getting this error.
and also make sure that u r converting the argument to the exact type
before passing it from asp(it is only applicable to arguments that is
declared as Byval). if u pass it byref, u always declare it as variant, so
u don't need to convert it.
oneway u can avoid these errors easily is declaring all the arguments as
variant. but that is not a good way of programming.
please let me know if u have any further problems
Regards,
Iyappan
>From: "sushma roopa kadambari" <sushmark1@r...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] sending the code
>Date: 15 Oct 2001 05:17:06 -0000
>
> hello..
>
> this is class in vb which will be calling sql Public Function
>validate(userid As String, userpwd As String, role As String)
>As String 'passing userid ,password and role from asp's page.. 'this
>module helps
>in logging into the application.. Dim cnn As New Connection Dim cmd As
>New
>Command Dim rs1 As New ADODB.Recordset Dim r As Integer Dim ssql As
>String 'setting the connection Set cnn = New Connection
>cnn.ConnectionString = "provider=sqloledb;server!2.162.196.114;
>database=employee;uid=sushma;pwd=roopa;"
>cnn.Open Set rs1 = New ADODB.Recordset rs1.CursorType = adOpenStatic
>'retriving the data from database "employee"
> ssql = "select * from pwdtbl where id='" & userid & "'" rs1.Open ssql,
>cnn,
>adOpenStatic, adLockReadOnly, adCmdText 'checking the user's validity
>If rs1.RecordCount = 0 Then 'user dosnot exist
>validate = "sorry user doesnot exist" rs1.Close Set rs1 =
>Nothing
> Exit Function Else ' checking for password..and his role....
> If (userpwd = rs1.Fields("pwd")) Then
> If (role = "manager") Then
> If role = rs1.Fields("role") Then
> validate = "true"
> Else
> validate = "sorry you are not authorised to login"
> End If
>Else
>validate = "true"
> End If
> Else
>validate = "login failed"
> End If rs1.Close Set rs1 = Nothing
> End If
> cnn.Close Set cnn = Nothing End Function
> and this is the vb script... dim userid,userpwd,userrole
>dim str1 userid=Request.Form("text1") userpwd=Request.Form("text2")
> userrole=Request.Form("s1")
> straction=Request.Form ("ok") if straction="ok" then
> set t1=server.CreateObject("vacation.initial")
>str1=t1.validate (userid,userpwd,userrole) if (str1="true")then
> select case userrole case manager gotopage=emp.asp
>case yourself gotopage=emp.asp else Response.Write (str1) end
>if %>
>
>
Message #5 by David Cameron <dcameron@i...> on Tue, 16 Oct 2001 08:48:52 +1000
|
|
My abject apologies. The lack of a 'RE' or 'SV' misled me. I will be more
careful in future.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Monday, 15 October 2001 6:50 PM
To: ASP Databases
Subject: [asp_databases] RE: sending the code
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "David Cameron" <dcameron@i...>
Subject: [asp_databases] RE: sending the code
: <warning SarcasmLevel="Low"><!-- Thanks Peter Foti -->
: Thanks for passing on your code. I really enjoyed reading it. Is there a
: problem with it? If so it would be good to let us know what
: the problem is.
: </warning>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I believe the code is related to the thread "Accessing SQL from components
in ASP". Someone asked the original poster to supply their code.
Cheers
Ken
Message #6 by David Cameron <dcameron@i...> on Tue, 16 Oct 2001 09:40:41 +1000
|
|
I reformatted your code and had a look at it. Somehow you seemed to lose 2
out of 3 single carriage returns. I removed your comments to save space.
There may be some line wrapping in the following code but I will try to
minimise it.
The error IIRC is that you are getting type mismatch errors when calling the
function. Try altering the function to take in variant data parameters
rather than string or explicitly convert the parameters to string eg.
Cstr(MyVar) when you call the function.
In case you are interested by other comments they are marked by <<
Public Function validate(userid As String, userpwd As String, role As
String) As String
Dim cnn As New Connection
Dim cmd As New Command
Dim rs1 As New ADODB.Recordset
Dim r As Integer
Dim ssql As String 'setting the connection
Set cnn = New Connection
cnn.ConnectionString = "provider=sqloledb;server=" & _
"212.162.196.114;database=employee;uid=sushma;pwd=roopa;"
cnn.Open
Set rs1 = New ADODB.Recordset
rs1.CursorType = adOpenStatic << not needed, specified in rs1.open
ssql = "select * from pwdtbl where id='" & userid & "'"
<< If the id in the database is a int you should not enclose it in ''
rs1.Open ssql, cnn, adOpenStatic, adLockReadOnly, adCmdText
If rs1.RecordCount = 0 Then
<< Do not use .Recordcount see
<< http://www.adopenstatic.com/faq/recordcounterror.asp
<< instead use "If rs1.EOF Then" in place of your current statement
validate = "sorry user doesnot exist"
rs1.Close
Set rs1 = Nothing
Exit Function
Else
If (userpwd = rs1.Fields("pwd")) Then
If (role = "manager") Then
If role = rs1.Fields("role") Then
validate = "true"
Else
validate = "sorry you are not" & _
" authorised to login"
End If
Else
validate = "true"
End If
Else
validate = "login failed"
End If
rs1.Close
Set rs1 = Nothing
End If
cnn.Close
Set cnn = Nothing
End Function
<%
and this is the vb script...
dim userid, userpwd, userrole
dim str1
userid = Request.Form("text1")
userpwd = Request.Form("text2")
userrole = Request.Form("s1")
straction = Request.Form("ok")
if straction = "ok" then
set t1 = server.CreateObject("vacation.initial")
str1 = t1.validate (userid,userpwd,userrole)
if (str1 = "true") then
select case userrole
case manager << I take it you mean "manager", not manager
gotopage=emp.asp
case yourself << I take it you mean "yourself", not
yourself
gotopage=emp.asp
else
Response.Write (str1)
end if%>
<< you are missing an end if
regards
David Cameron
nOw.b2b
dcameron@i...
Message #7 by "sushma roopa kadambari" <sushmark1@r...> on 16 Oct 2001 05:54:27 -0000
|
|
=0D=0A=0D=0Ahi...=0D=0A please take a look at the following code..=0D=0A in
this i used dll from vb=0D=0Aand registered with mts..=0D=0A...to which i
didnot pass any parameters...and which neither=0D=0Areturned any values..
=0D=0A=0D=0A-----------------------------------------------------=0D=0Apubl
ic sub conn()=0D=0A =0D=0ADim cnn As Connection=0D=0ASet cnn =3D New Connec
tion=0D=0Acnn.Provider =3D "sqloledb"=0D=0Acnn.ConnectionString =3D "server
=3D;database=3Demployee;uid=3D;pwd=3D;"=0D=0Acnn.Open=0D=0Acmd.ActiveConnec
tion =3D cnn=0D=0Acmd.CommandType =3D adCmdText=0D=0Acmd.CommandText =3D ..
.UPDATE SOME TABLE ... =0D=0A cnn.Close=0D=0A Set cnn =3D Nothing=0D=0A EN
D SUB=0D=0A---------------------------------------------------------=0D=0A
ASP PAGE...=0D=0AStraction=3DRequest.Form ("ok")=0D=0Aif straction
=3D"ok" then=0D=0A SET t1=3Dserver.createobject("project.class")=0D=0At1.co
nn()=0D=0Aend if=0D=0A....=0D=0A-------------------------------------------
--------------=0D=0A this piece of code is also giving me errors..=0D=0A ru
ntime error8000a000d=0D=0A type mismatch error..=0D=0A---------------------
------------------------------------=0D=0A so i came to conclusion that the
re is some problem with=0D=0Amts to sql...=0D=0A the problem may be with .
.mts..=0D=0Ai am using sql server registration to login into sql server..
=0D=0A but i doubt that mts uses windows nt authentication..=0D=0A so ....
=0D=0A please help me to correct this..=0D=0Ai am trying with the suggesti
ons you guys gave..=0D=0A thanks..=0D=0A=0D=0A =0A
Message #8 by "Iyappan P" <iyappan_p@h...> on Tue, 16 Oct 2001 14:39:06
|
|
try avoid using parenthesis when calling a sub.
So change ur asp code from
t1.conn()
to
t1.conn
it should work now.
When u use MTS make sure that u r making use of context object. i have
written some code, please try to use this and let me know whether u get any
error. I am working on windows 2000 and i don't have NT. so i cannot test
this code. please try the following
--------------------------------------------------------
public sub conn()
On Error GoTo conn_Error
Dim context As ObjectContext
Set context = GetObjectContext
Dim cnn As ADODB.Connection
Dim cmd as ADODB.Command
If Not (context Is Nothing) Then
Set cnn = context.CreateInstance("ADODB.connection")
Set cmd= context.CreateInstance("ADODB.Command")
Else
Set cnn = CreateObject("ADODB.connection")
Set cmd = CreateObject("ADODB.Command")
End If
'now do ur stuff
cnn.Provider = "sqloledb"
cnn.ConnectionString = "server=;database=employee;uid=;pwd=;"
cnn.Open
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = ...UPDATE SOME TABLE ...
cmd.ActiveConnection = nothing
cnn.close
'complete the transaction
If Not (context Is Nothing) Then
context.SetComplete
End If
conn_exit:
If Not (context Is Nothing) Then
Set context = Nothing
End If
If Not (cnn Is Nothing) Then
Set cnn = Nothing
End If
If Not (cmd Is Nothing) Then
Set cmd = Nothing
End If
Exit Function
conn_Error:
If Not (context Is Nothing) Then
context.SetAbort
End If
Resume conn_Exit
end sub
-----------------------------------
if it doesn't solve ur problem, please use err.raise and get the exact error
from the vb dll and let us know.
Regards,
Iyappan
>From: "sushma roopa kadambari" <sushmark1@r...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: sending the code
>Date: 16 Oct 2001 05:54:27 -0000
>
>
>
>
>hi...
> please take a look at the following code..
> in this i used dll from vb
>and registered with mts..
>...to which i didnot pass any parameters...and which neither
>returned any values..
>
>-----------------------------------------------------
>public sub conn()
>
>Dim cnn As Connection
>Set cnn = New Connection
>cnn.Provider = "sqloledb"
>cnn.ConnectionString = "server=;database=employee;uid=;pwd=;"
>cnn.Open
>cmd.ActiveConnection = cnn
>cmd.CommandType = adCmdText
>cmd.CommandText = ...UPDATE SOME TABLE ...
> cnn.Close
> Set cnn = Nothing
> END SUB
>---------------------------------------------------------
> ASP PAGE...
>Straction=Request.Form ("ok")
>if straction="ok" then
> SET t1=server.createobject("project.class")
>t1.conn()
>end if
>....
>---------------------------------------------------------
> this piece of code is also giving me errors..
> runtime error8000a000d
> type mismatch error..
>---------------------------------------------------------
> so i came to conclusion that there is some problem with
>mts to sql...
> the problem may be with ..mts..
>i am using sql server registration to login into sql server..
> but i doubt that mts uses windows nt authentication..
> so ....
> please help me to correct this..
>i am trying with the suggestions you guys gave..
> thanks..
|
|
|
View Cart
