Parametrized query

<i><b>Originally posted by : BalaKrishnan (ramaswamy.balakrishnan@dupontpharma.com)</b></i><br /><br />I have an Access database and I want to get the user id and using the following code; <br />but it does not work. I need some help.<br /><br /><%<br />Dim name<br />Dim password<br />name=Request.Form("UserName")<br />password=Request.Form("Password1")<br /><br />Set cn = Server.CreateObject("ADODB.Connection")<br />set rs = Server.CreateObject("ADODB.Recordset")<br />strconn="DSN=caco; UID=; Password=;"<br /><br />strSQL="SELECT USERtbl.UserName, USERtbl.Password," <br />strSQL=strSQL & "GROUPtbl.GroupID FROM USERtbl, USER_GROUP_LISTtbl,"<br />strSQL=strSQL & "GROUPtbl WHERE USERtbl.UserID = USER_GROUP_LISTtbl.UserID "<br />strSQL=strSQL & "AND USER_GROUP_LISTtbl.GroupID = GROUPtbl.GroupID "<br />strSQL=strSQL & "AND USERtbl.UserName= '" & Request.Form("UserName") & "' "<br />strSQL=strSQL & "AND USERtbl.Password= '" & Request.Form("Password1") & "' "<br /><br />rs.Open strSQL, strconn,2,2<br />Response.Write Request.Form("Password1")<br />Do while not rs.EOF<br />Response.Write rs("UserName") & "<BR>"<br />Response.Write rs("GroupID") & "<BR>"<br />rs.MoveNext<br />Loop<br /><br />rs.Close<br />cn.Close<br />%><br /><br />

<i><b>Originally posted by : jh</b></i><br />here is some sample code I found<br /><br />Here is a sample for a parameterized stored procedure in MS Access. The SQL in Access is: PARAMETERS [inid] Text; SELECT ... and on. <br /><br />And the source is: <br /><br /><br /><%@ Language=VBScript %><br /><%<br /> Dim cnn1<br /> Dim cmdNyttSvarID<br /> Dim prmNyttSvarID<br /> Dim rstNyttSvarID<br /> Dim strID<br /> Dim strCnn<br /> Dim strSize<br /> Dim i<br /> Dim tmpFields<br /> Dim strName<br /> Dim strTable<br /> <br /> ' Open connection.<br /> Set cnn1 = Server.CreateObject ("ADODB.Connection")<br /> strCnn = "DSN=agenda21"<br /> cnn1.Open strCnn<br /> cnn1.CursorLocation = adUseClient<br /> <br /> ' Open command object with one parameter.<br /> Set cmdNyttSvarID = Server.CreateObject ("ADODB.Command")<br /> cmdNyttSvarID.CommandText = "nyttsvarid"<br /> cmdNyttSvarID.CommandType = adCmdStoredProc<br /> <br /> ' Get parameter value and append parameter.<br /> ' The value for the one parameter in this example<br /> strID = "00001"<br /> strSize = Len(strID)<br /> <br /> ' Parametername<br /> strName = "inid"<br /> <br /> Set prmNyttSvarID = cmdNyttSvarID.CreateParameter(strName, adVarChar, adParamInput,strSize,strID)<br /> cmdNyttSvarID.Parameters.Append prmNyttSvarID<br /> prmNyttSvarID.Value = strID<br /> <br /> ' Create recordset by executing the command.<br /> Set cmdNyttSvarID.ActiveConnection = cnn1<br /> Set rstNyttSvarID = cmdNyttSvarID.Execute<br /> i = 1<br /> <br /> ' Dump the returned recordset to the client<br /> 'set rstNyttSvarID = Server.CreateObject ("ADODB.RECORDSET")<br /> 'set tmpField = rstNyttSvarID.Fields.Item (1).Value <br /> <br /> Do While Not rstNyttSvarID.EOF<br /> Response.Write ("<BR>")<br /> <br /> For Each tmpField In rstNyttSvarID.Fields<br /> Response.Write (tmpField.Name & ":" & tmpField.Value & ",")<br /> Next<br /> <br /> rstNyttSvarID.MoveNext<br /> Loop<br /> <br /> rstNyttSvarID.Close<br /> cnn1.Close<br />%><br /><br /><br /><br /><br />------------<br />BalaKrishnan at 2/1/00 3:25:55 PM<br /><br /><br />I have an Access database and I want to get the user id and using the following code; <br />but it does not work. I need some help.<br /><br /><%<br />Dim name<br />Dim password<br />name=Request.Form("UserName")<br />password=Request.Form("Password1")<br /><br />Set cn = Server.CreateObject("ADODB.Connection")<br />set rs = Server.CreateObject("ADODB.Recordset")<br />strconn="DSN=caco; UID=; Password=;"<br /><br />strSQL="SELECT USERtbl.UserName, USERtbl.Password," <br />strSQL=strSQL & "GROUPtbl.GroupID FROM USERtbl, USER_GROUP_LISTtbl,"<br />strSQL=strSQL & "GROUPtbl WHERE USERtbl.UserID = USER_GROUP_LISTtbl.UserID "<br />strSQL=strSQL & "AND USER_GROUP_LISTtbl.GroupID = GROUPtbl.GroupID "<br />strSQL=strSQL & "AND USERtbl.UserName= '" & Request.Form("UserName") & "' "<br />strSQL=strSQL & "AND USERtbl.Password= '" & Request.Form("Password1") & "' "<br /><br />rs.Open strSQL, strconn,2,2<br />Response.Write Request.Form("Password1")<br />Do while not rs.EOF<br />Response.Write rs("UserName") & "<BR>"<br />Response.Write rs("GroupID") & "<BR>"<br />rs.MoveNext<br />Loop<br /><br />rs.Close<br />cn.Close<br />%><br /><br />