Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Insert on table After moving from one select box

Hi, I have this form where a person can add players to a Given Team. Now I am confussed how to do the INSERT part. I am using dreamweaver and the default wizard doesnt seem to work . Currently I have and error :

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][SQL Native Client][SQL Server]The parameterized query '(@P1 text)INSERT INTO dbo.LeagueTeams (TeamName) VALUES (@P1)' expects the parameter '@P1', which was not supplied.

/Leagues/quickTeamAdd.asp, line 25
Many Thanks
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../../Connections/sqlConn1.asp" -->
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
 
' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_sqlConn1_STRING
    MM_editCmd.CommandText = "INSERT INTO dbo.LeagueTeams (TeamName) VALUES (?)" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 201, 1, 50, Request.Form("_ctl0_ContentPlaceHolder_TeamName")) ' adLongVarChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
  End If
End If
%>
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("League") <> "") Then 
  Recordset1__MMColParam = Request.QueryString("League")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
 
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_sqlConn1_STRING
Recordset1_cmd.CommandText = "SELECT GolferID, LeagueID, FirstName, LastName FROM dbo.LeagueGolfers WHERE LeagueID = ?" 
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 5, 1, -1, Recordset1__MMColParam) ' adDouble
 
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Documento sin t&iacute;tulo</title>
<link href="../Template/style.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.Estilo2 {font-size: 16px}
-->
</style>
<script type="text/javascript">
function list(){
	var optionList = document.getElementById('_ctl0_ContentPlaceHolder_TeamGolfers').options;
	var data = '';
	var len = optionList.length;  
	for(i=0; i<len; i++){
		data += optionList.item(i).value+',';
	}	
	document.getElementById('HiddenList').value = data			
}
 
 
 
 
 
function MoveOption(objSourceElement, objTargetElement){ 
 
	if(objSourceElement.value==''){
		alert('Please select an option to move');
		return false;	
	}
 
	var aryTempSourceOptions = new Array();        
	var x = 0;               
	//looping through source element to find selected options        
	for (var i = 0; i < objSourceElement.length; i++){            
		if (objSourceElement.options[i].selected){          	
			//need to move this option to target element                
			var intTargetLen = objTargetElement.length++;                
			objTargetElement.options[intTargetLen].text = objSourceElement.options[i].text;                
			objTargetElement.options[intTargetLen].value = objSourceElement.options[i].value;            
		}else{                
			//storing options that stay to recreate select element                
			var objTempValues = new Object();                
			objTempValues.text = objSourceElement.options[i].text;                
			objTempValues.value = objSourceElement.options[i].value;                
			aryTempSourceOptions[x] = objTempValues;               
			x++;           
		}       
	}              
	//resetting length of source        
	objSourceElement.length = aryTempSourceOptions.length;       
	//looping through temp array to recreate source select element      
	for (var i = 0; i < aryTempSourceOptions.length; i++){            
		objSourceElement.options[i].text = aryTempSourceOptions[i].text;           
		objSourceElement.options[i].value = aryTempSourceOptions[i].value;           
		objSourceElement.options[i].selected = false;        
	}    
}
function MM_callJS(jsStr) { //v2.0
  return eval(jsStr)
}
</script>
</head>
 
<body>
<div id="content2">
  <p> </p>
  <p> </p>
  <p><br />
  </p>
<div id="Div1"><strong><strong></strong><strong></strong><strong></strong><strong></strong></strong>
        <div>
          <p><span class="Estilo2" id="_ctl0_ContentPlaceHolder_TeamTitle">Quick Team   Add</span></p>
          <p>&nbsp;</p>
          <form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
            <div id="content">
              <div id="Div2">
                <div>
                  <table id="_ctl0_ContentPlaceHolder_TeamTable" cellspacing="0" cellpadding="4" width="100%" border="0">
                    <tbody>
                      <tr>
                        <td colspan="3"></td>
                      </tr>
                      <tr>
                        <td colspan="3">Team Name<span id="_ctl0_ContentPlaceHolder_TeamNameValidator" title="Team Name is required." controltovalidate="_ctl0_ContentPlaceHolder_TeamName" errormessage="Team Name is required." initialvalue="" isvalid="true" display="Dynamic"> (Required)</span>
                            <input id="_ctl0_ContentPlaceHolder_TeamName" tabindex="5" maxlength="20" name="_ctl0:ContentPlaceHolder:TeamName" />
                          (Maximum of 20   characters)</td>
                      </tr>
                      <tr>
                        <td colspan="3"></td>
                      </tr>
                      <tr>
                        <td colspan="3">Add/Remove golfers for this team.</td>
                      </tr>
                      <tr>
                        <td width="25%" align="middle" style="width: 12%"><span id="_ctl0_ContentPlaceHolder_OnTheTeamLabel"><strong>On   the Team</strong></span> </td>
                        <td width="16%" align="middle" valign="center" style="width: 7%"></td>
                        <td align="middle" width="59%"><div align="left"><span id="_ctl0_ContentPlaceHolder_AvailableGolfersLabel"><strong>Available Golfers</strong></span> </div></td>
                      </tr>
                      <tr>
                        <td valign="top" style="width: 12%"><select id="_ctl0_ContentPlaceHolder_TeamGolfers" multiple="multiple" size="10" name="_ctl0:ContentPlaceHolder:TeamGolfers" style="width: 133px">
                          </select>
                        </td>
                        <td valign="center" align="middle" style="width: 7%"><input id="_ctl0_ContentPlaceHolder_AddToTeam" type="submit" value="&lt;-- Add to Team" name="_ctl0:ContentPlaceHolder:AddToTeam" onclick="MoveOption(document.getElementById('_ctl0_ContentPlaceHolder_AvailableGolfers'), document.getElementById('_ctl0_ContentPlaceHolder_TeamGolfers'))"/>
                            <br />
                            <br />
                            <input id="_ctl0_ContentPlaceHolder_RemoveFromTeam" type="submit" value="Remove from Team --&gt;" name="_ctl0:ContentPlaceHolder:RemoveFromTeam" onclick="MoveOption(document.getElementById('_ctl0_ContentPlaceHolder_TeamGolfers'), document.getElementById('_ctl0_ContentPlaceHolder_AvailableGolfers'))"/>
                        </td>
                        <td valign="top"><div align="left">
                            <select id="_ctl0_ContentPlaceHolder_AvailableGolfers" multiple="multiple" size="10" name="_ctl0:ContentPlaceHolder:AvailableGolfers">
                              <%
While (NOT Recordset1.EOF)
%>
                              <option value="<%=(Recordset1.Fields.Item("FirstName").Value)%><%=(Recordset1.Fields.Item("LastName").Value)%>"><%=(Recordset1.Fields.Item("FirstName").Value)%>,<%=(Recordset1.Fields.Item("LastName").Value)%></option>
                              <%
  Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
  Recordset1.MoveFirst
Else
  Recordset1.Requery
End If
%>
                            </select>
                        </div></td>
                      </tr>
                      <tr>
                        <td colspan="3"><hr />
                        </td>
                      </tr>
                      <tr>
                        <td align="right" colspan="3"><div align="left">
                            <input name="Save" type="submit" id="_ctl0_ContentPlaceHolder_SaveAndClose" tabindex="50" onclick="list();" value="Save and Close" language="javascript" />
                            <input language="javascript" id="_ctl0_ContentPlaceHolder_Cancel" tabindex="55" type="submit" value="Close" name="Close" />
                            <input type="hidden" name="hiddenList" id="hiddenList" />
                        </div></td>
                      </tr>
                    </tbody>
                  </table>
                </div>
                <strong><strong></strong><strong></strong><strong></strong><strong></strong></strong></div>
            </div>
                    
            <input type="hidden" name="MM_insert" value="form1" />
          </form>
          <p>&nbsp;</p>
          <p> </p>
          <p> </p>
        </div>
<div></div>
      </div>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Open in new window

0
RickyGtz
Asked:
RickyGtz
  • 20
  • 9
  • 7
  • +1
3 Solutions
 
Eric AKA NetminderCommented:
Simply put, the parameter isn't being passed to the insert query.

It looks like you've named the parameter to be passed as param1, but the query is expecting a parameter named P1. Try fixing that.

As I recall, you can test the query before trying to actually incorporate it into the page (I haven't used Dreamweaver for several years) -- that's where your problem is.

ep
0
 
RickyGtzAuthor Commented:
Hi I dont get tat error anymore however there still something wrong on the logi. It does not take the argument I want to Insert .
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../../Connections/sqlConn1.asp" -->
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
 
' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_sqlConn1_STRING
    MM_editCmd.CommandText = "INSERT INTO dbo.LeagueTeams (TeamName) VALUES (?)" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("p1", 201, 1, 50, Request.Form("TeamName")) ' adLongVarChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
  End If
End If
%>
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("League") <> "") Then 
  Recordset1__MMColParam = Request.QueryString("League")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
 
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_sqlConn1_STRING
Recordset1_cmd.CommandText = "SELECT GolferID, LeagueID, FirstName, LastName FROM dbo.LeagueGolfers WHERE LeagueID = ?" 
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("p1", 5, 1, -1, Recordset1__MMColParam) ' adDouble
 
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Documento sin t&iacute;tulo</title>
<link href="../Template/style.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.Estilo2 {font-size: 16px}
-->
</style>
<script type="text/javascript">
 
 
 
 
function MoveOption(objSourceElement, objTargetElement){ 
 
	if(objSourceElement.value==''){
		alert('Please select an option to move');
		return false;	
	}
 
	var aryTempSourceOptions = new Array();        
	var x = 0;               
	//looping through source element to find selected options        
	for (var i = 0; i < objSourceElement.length; i++){            
		if (objSourceElement.options[i].selected){          	
			//need to move this option to target element                
			var intTargetLen = objTargetElement.length++;                
			objTargetElement.options[intTargetLen].text = objSourceElement.options[i].text;                
			objTargetElement.options[intTargetLen].value = objSourceElement.options[i].value;            
		}else{                
			//storing options that stay to recreate select element                
			var objTempValues = new Object();                
			objTempValues.text = objSourceElement.options[i].text;                
			objTempValues.value = objSourceElement.options[i].value;                
			aryTempSourceOptions[x] = objTempValues;               
			x++;           
		}       
	}              
	//resetting length of source        
	objSourceElement.length = aryTempSourceOptions.length;       
	//looping through temp array to recreate source select element      
	for (var i = 0; i < aryTempSourceOptions.length; i++){            
		objSourceElement.options[i].text = aryTempSourceOptions[i].text;           
		objSourceElement.options[i].value = aryTempSourceOptions[i].value;           
		objSourceElement.options[i].selected = false;        
	}    
}
function MM_callJS(jsStr) { //v2.0
  return eval(jsStr)
}
 
 
 
function list(){
	var optionList = document.getElementById('TeamGolfers').options;
	var data = '';
	var len = optionList.length;  
	for(i=0; i<len; i++){
		data += optionList.item(i).value+',';
	}	
	document.getElementById('HiddenList').value = data			
}
 
 
 
</script>
</head>
 
<body>
<div id="content2">
  <p> </p>
  <p> </p>
  <p><br />
  </p>
<div id="Div1"><strong><strong></strong><strong></strong><strong></strong><strong></strong></strong>
        <div>
          <p><span class="Estilo2" id="TeamTitle">Quick Team   Add</span></p>
          <p>&nbsp;</p>
          <form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
            <div id="content">
              <div id="Div2">
                <div>
                  <table id="TeamTable" cellspacing="0" cellpadding="4" width="100%" border="0">
                    <tbody>
                      <tr>
                        <td colspan="3"></td>
                      </tr>
                      <tr>
                        <td colspan="3">Team Name<span id="TeamNameValidator" title="Team Name is required." controltovalidate="TeamName" errormessage="Team Name is required." initialvalue="" isvalid="true" display="Dynamic"> (Required)</span>
                            <input id="TeamName" tabindex="5" maxlength="20" name="TeamName" />
                          (Maximum of 20   characters)</td>
                      </tr>
                      <tr>
                        <td colspan="3"></td>
                      </tr>
                      <tr>
                        <td colspan="3">Add/Remove golfers for this team.</td>
                      </tr>
                      <tr>
                        <td width="25%" align="middle" style="width: 12%"><span id="OnTheTeamLabel"><strong>On the Team</strong></span> </td>
                        <td width="16%" align="middle" valign="center" style="width: 7%"></td>
                        <td align="middle" width="59%"><div align="left"><span id="AvailableGolfersLabel"><strong>Available Golfers</strong></span> </div></td>
                      </tr>
                      <tr>
                        <td valign="top" style="width: 12%"><select id="TeamGolfers" multiple="multiple" size="10" name="TeamGolfers" style="width: 130px">
                          </select>
                        </td>
                        <td valign="center" align="middle" style="width: 7%"><input id="AddToTeam" type="submit" value="&lt;-- Add to Team" name="AddToTeam" onclick="MoveOption(document.getElementById('AvailableGolfers'), document.getElementById('TeamGolfers'))"/>
                            <br />
                            <br />
                            <input id="RemoveFromTeam" type="submit" value="Remove from Team --&gt;" name="RemoveFromTeam" onclick="MoveOption(document.getElementById('TeamGolfers'), document.getElementById('AvailableGolfers'))"/>
                        </td>
                        <td valign="top"><div align="left">
                            <select id="AvailableGolfers" multiple="multiple" size="10" name="AvailableGolfers">
                              <%
While (NOT Recordset1.EOF)
%>
                              <option value="<%=(Recordset1.Fields.Item("FirstName").Value)%><%=(Recordset1.Fields.Item("LastName").Value)%>"><%=(Recordset1.Fields.Item("FirstName").Value)%>,<%=(Recordset1.Fields.Item("LastName").Value)%></option>
                              <%
  Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
  Recordset1.MoveFirst
Else
  Recordset1.Requery
End If
%>
                            </select>
                        </div></td>
                      </tr>
                      <tr>
                        <td colspan="3"><hr />
                        </td>
                      </tr>
                      <tr>
                        <td align="right" colspan="3"><div align="left">
                            <input name="Save" type="submit" id="SaveAndClose" tabindex="50" onclick="list();" value="Save and Close" language="javascript" />
                            <input language="javascript" id="Cancel" tabindex="55" type="submit" value="Close" name="Close" />
                            <input type="hidden" name="hiddenList" id="hiddenList" />
                        </div></td>
                      </tr>
                    </tbody>
                  </table>
                </div>
                <strong><strong></strong><strong></strong><strong></strong><strong></strong></strong></div>
            </div>
                    
            <input type="hidden" name="MM_insert" value="form1" />
          </form>
          <p>&nbsp;</p>
          <p> </p>
          <p> </p>
        </div>
<div></div>
      </div>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Open in new window

0
 
Eric AKA NetminderCommented:
This is a wild guess based only on dim memories...

But it looks like you're trying to insert the parameter BEFORE you know what it is. Lines 30-36 define the parameter; lines 14-29 run the query based on the parameter. In using Dreamweaver's wizard, you have set the coding up in the wrong order.

ep
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RickyGtzAuthor Commented:
Yes you are right I am getting rid of DW insert code and trying to build it from scratch. So this is my code, but not sure how Do I get Vlues passed to the left textbox and Insert them on submit.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
 
<!--#include file="../../Connections/sqlConn1.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 
 
 
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("League") <> "") Then 
  Recordset1__MMColParam = Request.QueryString("League")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
 
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_sqlConn1_STRING
Recordset1_cmd.CommandText = "SELECT GolferID, LeagueID, FirstName, LastName FROM dbo.LeagueGolfers WHERE LeagueID = ?" 
Recordset1_cmd.Prepared = true
Recordset1_cmd.Par
ameters.Append Recordset1_cmd.CreateParameter("p1", 5, 1, -1, Recordset1__MMColParam) ' adDouble
 
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
 
 
<%
		
' To make Members be part of the Team 
 
 
	if (request("Submit")<>"") then 
 
	Users =request.form("_ctl0:ContentPlaceHolder:AvailableGolfers")
	League_N =request.form(" _ctl0:ContentPlaceHolder:TeamName")
 
	UserArr = split(Users,",")
	TotVal = Ubound(UserArr)
	conSQL.execute("update LeagueTeams set TeamName= '"&League_N&"' ")
	for Indexval =0 to Totval
		conSQL.execute("update LeagueTeams set TeamMember = '"&Users&"'  where ssn ='"&trim(UserArr(Indexval))&"'")
	Next
	
 
	end if 
 
 
%>
 
 
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Documento sin t&iacute;tulo</title>
<link href="../Template/style.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.Estilo4 {font-size: 18px}
-->
</style>
<script type="text/javascript">
function MoveOption(objSourceElement, objTargetElement){ 
 
	if(objSourceElement.value==''){
		alert('Please select an option to move');
		return false;	
	}
 
	var aryTempSourceOptions = new Array();        
	var x = 0;               
	//looping through source element to find selected options        
	for (var i = 0; i < objSourceElement.length; i++){            
		if (objSourceElement.options[i].selected){          	
			//need to move this option to target element                
			var intTargetLen = objTargetElement.length++;                
			objTargetElement.options[intTargetLen].text = objSourceElement.options[i].text;                
			objTargetElement.options[intTargetLen].value = objSourceElement.options[i].value;            
		}else{                
			//storing options that stay to recreate select element                
			var objTempValues = new Object();                
			objTempValues.text = objSourceElement.options[i].text;                
			objTempValues.value = objSourceElement.options[i].value;                
			aryTempSourceOptions[x] = objTempValues;               
			x++;           
		}       
	}              
	//resetting length of source        
	objSourceElement.length = aryTempSourceOptions.length;       
	//looping through temp array to recreate source select element      
	for (var i = 0; i < aryTempSourceOptions.length; i++){            
		objSourceElement.options[i].text = aryTempSourceOptions[i].text;           
		objSourceElement.options[i].value = aryTempSourceOptions[i].value;           
		objSourceElement.options[i].selected = false;        
	}    
} 
</script>
</head>
 
<body>
<div id="content2">
  <p></p>
  <p></p>
  <p></p>
<div id="Div1"><strong><strong></strong><strong></strong><strong></strong><strong></strong></strong>
        <div>
          <p>&nbsp;</p>
          <p>&nbsp;</p>
          <p>&nbsp;</p>
          <p><span class="Estilo4">Quick Team   Add</span></p>
          <p>&nbsp;</p>
          <p>Add your league members to a team. </p>
          <form id="form1" name="form1" method="post" action="">
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <table id="_ctl0_ContentPlaceHolder_TeamTable" cellspacing="0" cellpadding="4" width="100%" border="0">
              <tbody>
                <tr>
                  <td colspan="3"></td>
                </tr>
                <tr>
                  <td colspan="3">Team Name<span id="_ctl0_ContentPlaceHolder_TeamNameValidator" title="Team Name is required." controltovalidate="_ctl0_ContentPlaceHolder_TeamName" errormessage="Team Name is required." initialvalue="" isvalid="true" display="Dynamic"> (Required)</span>
                      <input id="_ctl0_ContentPlaceHolder_TeamName" tabindex="5" maxlength="20" name="_ctl0:ContentPlaceHolder:TeamName" />
                    (Maximum of 20   characters)</td>
                </tr>
                <tr>
                  <td colspan="3"></td>
                </tr>
                <tr>
                  <td colspan="3">Add/Remove golfers for this team.</td>
                </tr>
                <tr>
                  <td width="17%" align="middle" style="width: 12%"><span id="_ctl0_ContentPlaceHolder_OnTheTeamLabel"><strong>On   the Team</strong></span> </td>
                  <td width="16%" align="middle" valign="center" style="width: 7%"></td>
                  <td align="middle" width="67%"><div align="left"><span id="_ctl0_ContentPlaceHolder_AvailableGolfersLabel"><strong>Available Golfers</strong></span> </div></td>
                </tr>
                <tr>
                  <td valign="top" style="width: 12%"><select id="_ctl0_ContentPlaceHolder_TeamGolfers" multiple="multiple" size="10" name="_ctl0:ContentPlaceHolder:TeamGolfers" style="width: 133px">
                    </select>
                  </td>
                  <td valign="center" align="middle" style="width: 7%"><input id="_ctl0_ContentPlaceHolder_AddToTeam" type="submit" value="&lt;-- Add to Team" name="_ctl0:ContentPlaceHolder:AddToTeam" onclick="MoveOption(document.getElementById('_ctl0_ContentPlaceHolder_AvailableGolfers'), document.getElementById('_ctl0_ContentPlaceHolder_TeamGolfers'))"/>
                      <br />
                      <br />
                      <input id="_ctl0_ContentPlaceHolder_RemoveFromTeam" type="submit" value="Remove from Team --&gt;" name="_ctl0:ContentPlaceHolder:RemoveFromTeam" onclick="MoveOption(document.getElementById('_ctl0_ContentPlaceHolder_TeamGolfers'), document.getElementById('_ctl0_ContentPlaceHolder_AvailableGolfers'))"/>
                  </td>
                  <td valign="top"><div align="left">
                      <select id="_ctl0_ContentPlaceHolder_AvailableGolfers"  multiple="multiple" size="10" name="_ctl0:ContentPlaceHolder:AvailableGolfers">
                        <%
While (NOT Recordset1.EOF)
%>
                        <option value="<%=(Recordset1.Fields.Item("FirstName").Value)%><%=(Recordset1.Fields.Item("LastName").Value)%>"><%=(Recordset1.Fields.Item("FirstName").Value)%>,<%=(Recordset1.Fields.Item("LastName").Value)%></option>
                        <%
  Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
  Recordset1.MoveFirst
Else
  Recordset1.Requery
End If
%>
                      </select>
                  </div></td>
                </tr>
                <tr>
                  <td colspan="3"><hr />
                  </td>
                </tr>
                <tr>
                  <td align="right" colspan="3"><div align="left">
                      <input language="javascript" id="_ctl0_ContentPlaceHolder_SaveAndClose" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions('_ctl0:ContentPlaceHolder:SaveAndClose', '', true, '', '', false, false))" tabindex="50" type="submit" value="Save and Close" name="Submit" />
                      <input language="javascript" id="_ctl0_ContentPlaceHolder_Cancel" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions('_ctl0:ContentPlaceHolder:Cancel', '', false, '', 'TeamManagement.aspx', false, false))" tabindex="55" type="submit" value="Close" name="_ctl0:ContentPlaceHolder:Cancel" />
                  </div></td>
                </tr>
              </tbody>
            </table>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
          </form>
          <p>&nbsp;</p>
          <p> </p>
          <p> </p>
        </div>
<div></div>
  <strong><strong></strong><strong></strong><strong></strong><strong></strong></strong></div>
</div>
</body>
</html>
 <%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Open in new window

0
 
Eric AKA NetminderCommented:
I don't think I'd get rid of Dreamweaver. But part of the problem with using a program like Dreamweaver is that it doesn't help you understand what you're doing (and frankly, I don't have enough experience in ASP, specifically, to REALLY help you other than in a theoretical way -- which means your specific issue will take me a little longer to fix, because you're going to have to do the work).

So... let's look at your code as it was and figure out what each part of it is doing.

Did you use a text editor to move the code that sets the parameter?

0
 
RickyGtzAuthor Commented:
What I meant is get rid of dreamweaver auto generated code asp.
0
 
Eric AKA NetminderCommented:
*smiling*...

You're going to have some problems unless you start getting a grounding in ASP pretty quickly. Dreamweaver uses a number of standard bits of code, and integrates them into the page with SSIs; note the second line of the page above:
<!--#include file="../../Connections/sqlConn1.asp" -->

That refers to the page that lists the connection string(s) to your database for your various recordsets.

Better, I think, to go back to the original page you posted, and to deal with that from within Dreamweaver. Let me see if I can get the Mods to get you some Dreamweaver help.
0
 
Jason C. LevineNo oneCommented:
>> however there still something wrong on the logi. It does not take the argument I want to Insert .

RickyGtz,

As far as I can see, your initial DW code is okay.  When I look at the form, I see multiple submit buttons and javascript events and I think that might be the problem.

Have you tested this form by doing the simplest action possible?   Load it, enter a team name, and hit the submit button.  Do not try to move players around from one list to the other...
0
 
RickyGtzAuthor Commented:
>>ericpete >>
<!--#include file="../../Connections/sqlConn1.asp" -->

That refers to the page that lists the connection string(s) to your database for your various recordsets.
>>

Yes I know that, I am not that far behind on asp programming, I am just kinda lazy and want to speed up the development by using dreamweaver tools.. which seems to make everything more complicated so far..
0
 
RickyGtzAuthor Commented:
>> jason1178>>>

When I test first code It does insert teamname field into database when I hit submit, now, how do Insert the players move to the left list??
0
 
RickyGtzAuthor Commented:
I am thinking I might need create somehow a hidden field , and somehow grab each value displayed on left list by looping through this list and enter them into the Database . Just not sure what logic to follow.
0
 
Jason C. LevineNo oneCommented:
>> how do Insert the players move to the left list??

You've reached a limitation in the DW auto-code.  It cannot handle multi-select list options (or checkboxes for that matter) on its own.

>> somehow grab each value displayed on left list by looping through this list and enter them into the Database

Which table and how do you want the data to go in?  Do you expect to have one row per team member:

team name  member1
team name  member2
etc.

or will each team take up one row:

team name  member1, member2, member3, etc.

or is there a subtable that handles the team members?

No matter what you do, you will end up looping, but depending on your answer you have some options to work with the Developers Toolkit here...
0
 
RickyGtzAuthor Commented:
Hi, i expect to have The Golfer Name and the ID, Now you got me thinking, I have to use another table that I created called League Golfers, and Insert the information from that tabble into the selected users for that team.

Hope you can understand what the goal is, you can kind off see it live here:
http://www.playerscores.net/Leagues/quickTeamAdd.asp?League=57

Also, you mention developer toolbox, which I happen to have, So, how is this done using that tool??
0
 
Jason C. LevineNo oneCommented:
>> So, how is this done using that tool??

If you don't mind storing the names in a single text field, the Toolbox has a widget to convert groups of lists or checkboxes to a CSV string.  It's not the best way to handle things, but it works in the short term.

>> Hope you can understand what the goal is,

No, I see what you are trying to do.  I'm just not convinced you have gone about it in the best manner.  The underlying weakness in your approach is that you are committing transactions with Javascript (moving names from one list to another) but trying to rely upon a single, final SQL statement to post the result.  This would seem to be a perfect opportunity to integrate AJAX into your site and really make those Add and Remove buttons DO something.  All you would need is an insert behavior onblur from the team name (that creates the initial record) and then have the buttons actually fire insert, update, or delete queries depending on how exactly the form is structured.  So when I add a name to the list, I'm REALLY adding that name to the list and not performing some pseudoaction via javascript.  This scenario removes the need to have the overall submit button and sets the stage for the next step which would be allowing drag and drop from one list to the other instead of highlight, move mouse, click.

If you want to stay with the current architecture, you can use the ADDT widget I described above or write custom ASP code to handle to the insert that does loop through each value in the list and perform some action.  
0
 
RouchieCommented:
Hi there.

I've been asked by Jason1178 to take a look at your problem.

Currently as has been suggested, the form will post a list of names that you have to separate in some way before you can actually save them in a database table.

Now, ASP can do this, but there are also other solutions based on database code, that are much faster and much more reliable.  Please can you tell me what database software you are using?

Thanks.
0
 
RickyGtzAuthor Commented:
I assume you mean this, I am using sql server 2005, and sql management studio 2005. And for editing I am using dreamweaver cs3 + developer toolbox.
0
 
Jason C. LevineNo oneCommented:
Thanks for checking this, Rouchie.
0
 
RouchieCommented:
Okay if you're using SQL then the job just got a lot easier.   What I recommend though is performing this database action using a stored procedure instead of crafting SQL commands within ASP.  This way its more reliable, and a lot more secure.

What I need to do is write you a stored procedure that takes the values from ASP and inserts them as required.  There is a way within SQL to take a list of comma separated values and insert them into a table as separate records.

I am presuming the logic will work something like this... (Correct me if I'm wrong please!)

 1. Create team name in database
 2. For that newly-created team, add each player separately

If this is correct, please let me have the following details so I can create some code for you:

 1. The two database table names
 2. The database fields being used, and the data types of each one (e.g. varchar(20))

Also, one problem that you have currently is that items in a select box don't get submitted unless they are highlighted.  So even if I choose both players for my new team, their names won't get submitted because they are not highlighted when I press Save.  There are two methods to fix this:

 1) Add more JavaScript to the "Add to Team" button so all values become highlighted after the names are moved
 2) Use a check box list instead and just have one list of names that the user selects
0
 
RickyGtzAuthor Commented:
Hi, yes thats the purpose
1. Create team name in database
 2. For that newly-created team, add each player separately

Table LeagueGolfers

GolferID      int      Unchecked
LeagueID      int      Checked
FirstName      varchar(50)      Checked
LastName      varchar(50)      Checked
Gender      varchar(50)      Checked
Title      varchar(50)      Checked
City      varchar(50)      Checked
State      varchar(50)      Checked
ZipCode      varchar(50)      Checked
Email      varchar(50)      Checked
HomePhone      varchar(50)      Checked
WorkPhone      varchar(50)      Checked
LeagueAdministrator      bit      Checked
UserName      varchar(50)      Checked
Password      varchar(50)      Checked
CurrentIndex      int      Checked
EventsPlayed      nchar(10)      Checked
TotalPoints      nchar(10)      Checked
AvgGross      nchar(10)      Checked
AvgNet      nchar(10)      Checked
BestGross      nchar(10)      Checked
BestNet      nchar(10)      Checked
Table LeagueTeams
 
TeamID	int	Unchecked
TeamName	varchar(50)	Checked
GolferID	int	Checked
LeaderName	varchar(50)	Checked
LeaderLastName	varchar(50)	Checked
NoPlayers	int	Checked
FirstName	varchar(50)	Checked
LastName	varchar(50)	Checked
PlayerName	varchar(50)	Checked

Open in new window

0
 
RouchieCommented:
From your info above I think you are missing a database table.  The table would be a many-to-many database table showing which players are in which team.   The table would simply contain ID, TeamID and PlayerID columns.

How are you currently storing which players are in which team (just in case I missed something) ?

If possible can you create a Database Diagram in SQL (click on Database > Your Database > Diagrams > New Database Diagram then add all tables) to help illustrate what your explanation?
0
 
RickyGtzAuthor Commented:
No i have not started creating team players. Your are right I might need another table called LeagueTeamGolfers. I will try to look on  diagram. I am not really familiar with this tool, but i will igive a try.
0
 
RouchieCommented:
The reason I asked about the missing table is because of the following database table design:

TABLE: LeagueGolfers
COLUMN:  LeagueID

TABLE: LeagueTeams
COLUMN: GolferID

I don't know what the above two columns are used for.  Can you tell me?  If this is how you planned to store golfers in teams then using another table instead of this is a better idea, so you aren't duplicating data.
0
 
RouchieCommented:
Sorry forget the first column I mentioned above.  Obviously a golfer will be in just one league.

So I just need to know about GolferID in LeagueTeams.
0
 
RickyGtzAuthor Commented:
Ok, yes One golfer is just to be on one league for now.

TABLE: LeagueGolfers
COLUMN:  LeagueID
Column : GolferID

TABLE: LeagueTeams
Column : TeamID
Cloumn : LeagueID
COLUMN: GolferID
0
 
RouchieCommented:
I'm getting a bit confused.  We need to be absolutely clear how this will work before creating any stored procedures.  So...

I imagine that you track golfers, teams and leagues as you describe?  If so, you need four tables.  

TABLE 1: GOLERS (holds golfers' personal details and the league they are in)
GolferID
FirstName
Surname
LeagueID

TABLE 2: LEAGUEINFO (Optional, holds any league details for each league)
LeagueID
LeagueName
LeagueDescription

TABLE 3: GOLF TEAMS (Holds basic golf team information such as the team name)
TeamID
TeamName

TABLE 4: TEAM MEMBERS (Holds the data about which golfer is in while team.  This approach allows golfers to be multiple teams)
ID
TeamID
GolferID

Please let me know if I am incorrect here, or am making things too complicated?!
0
 
RickyGtzAuthor Commented:
It looks fine, I would prefer only two tables one for Golfers and one for TeamGolfers, But your are right I need one dedicated for team details.

0
 
RickyGtzAuthor Commented:
Looks current layout.


layout.jpg
0
 
RouchieCommented:
Your LEAGUE table here looks fine.  LEAGUETEAMS however contains a GOLFERID field. I presume this is how you want to add golfers into a team? If so, you are creating a problem for yourself in the form of repeated data. Look at how this table would get filled with data:

TEAMID       TEAMNAME         GOLFERID
1                 Red Team           3
1                 Red Team           4
1                 Red Team           7
2                 Blue Team          6
2                 Blue Team          34
2                 Blue Team          22

See above how the data for the team name is being repeated?  So if you wanted to change Red Team's teamname to Pink Team, you would have to go change all those records!

This is why you should separate out this data into different tables.  I might be totally wrong here, but if not, try removing the GolferID column from LeagueTeams.   This table will then just contain information about the teams, which is correct.

Then, remove TeamID from LeagueTeamGolfers and rename this table to be Golfers.  This table then just contains infomation about golfers.  

Finally, create another table called TeamsGolfers with the following columns:
ID, TeamID, GolferID

Then we can add any golfer to any team using this extra table, which makes things lightweight and MUCH easier to manage in three year's time when you have hundreds of golfers using your application!  :-)
0
 
RickyGtzAuthor Commented:
Thank you guys , your comments have been really helpful. Now back to the original question, can someone provide and example , or explain how do I insert the fields on this page to close this question.

>> jason1178>>

>>>>>All you would need is an insert behavior onblur from the team name (that creates the initial record) and then have the buttons actually fire insert, update, or delete queries depending on how exactly the form is structured.  So when I add a name to the list, I'm REALLY adding that name to the list and not performing some pseudoaction via javascript.  This scenario removes the need to have the overall submit button and sets the stage for the next step which would be allowing drag and drop from one list to the other instead of highlight, move mouse, click.

If you want to stay with the current architecture, you can use the ADDT widget I described above or write custom ASP code to handle to the insert that does loop through each value in the list and perform some action. >>>>>>>>>>>

can you tell me more details here this is the page so you look again


http://www.playerscores.net/Leagues/quickTeamAdd.asp?League=57
0
 
Jason C. LevineNo oneCommented:
>> can you tell me more details

On which approach?  
0
 
RickyGtzAuthor Commented:
Hi, using asp and loop
>> ASP code to handle to the insert that does loop >>
0
 
Jason C. LevineNo oneCommented:
Sorry, I don't do ASP :)

I've been answering this because Netminder asked me to look at it and the majority of the issue has been a discussion of what you are trying to do and better approaches for it.  Actual code manipulation in ASP is not my strong suit.
0
 
RickyGtzAuthor Commented:
dreamweaver widget ?
0
 
RickyGtzAuthor Commented:
Ok, Onthe insert transaction and how do I refer to the values inside left textbox and separate them to beinserted on dreamweaver developer toolbox
0
 
Jason C. LevineNo oneCommented:
>> dreamweaver widget ?

Server Behaviors | Developer Toolbox | Form Controls | Comma Separated Menu or Selector (I always get those mixed up)

>> On the insert transaction and how do I refer to the values inside left textbox

As Rouchie noted above, you need to have the values on the left side selected...it's not enough to just move them.  Once they are highlighted, the widget references above will convert the values to a comma-separated list.

If you are going with Rouchie's solution of a many-to-many table, this post is useless to you.  You would either write custom code that steps through the menu and peforms an insert for each item selected or attempt to do the same thing via the Toolkit and a custom transaction.

0
 
RickyGtzAuthor Commented:
>>Rouchi>>
>>What I need to do is write you a stored procedure that takes the values from ASP and inserts them as required.  >>

Have you make any sql store procedure to finally insert all fields
0
 
RickyGtzAuthor Commented:
I have the following error now, but seems I am in right path
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 'RobertPineda'.

/Leagues/quickTeamAdd.asp, line 76
0
 
RouchieCommented:
The stored procedure would be based around the database table structure, so its a bit hard to create this for you without seeing your final results.  Anyway, an example you could use to save players IDs into a team would be as follows:


USE [myDataBaseName]
GO
 
CREATE PROCEDURE golf_InsertPlayersIntoTeam
 
	@teamID int = NULL,
	@playerIDs varchar(2000) -- e.g. 1,2,3,4,5,6,7,8,9, (must end with a comma)
 
AS
 
	BEGIN TRAN
	
		-- 1. Put all Player ID's into a table variable
		DECLARE @separator_position INT
		DECLARE @array_value nvarchar(20)
		DECLARE @playerTable TABLE(ID INT IDENTITY(1,1), playerID INT)
		WHILE patindex('%' + @separator + '%' , @playerIDs) <> 0
			BEGIN
				SELECT @separator_position =  patindex('%' + @separator + '%' , @playerIDs)
				SELECT @array_value = LEFT(@playerIDs, @separator_position - 1)
				INSERT INTO @playerTable (playerID) VALUES (@array_value)
				SELECT @playerIDs = stuff(@playerIDs, 1, @separator_position, '')
			END
		
		-- We now have all the player IDs in their own table:
		--    SELECT * FROM @playerTable
		--       will return all these player IDs
		
		-- 2. Insert the players into the team (presuming we have a table with the following design:
		--         PlayersTeams (
		--             ID int, 
		--             PlayerID int, 
		--             TeamID int
		--             )
		
		INSERT INTO PlayersTeams
			(
			PlayerID,
			TeamID
			)
		SELECT
			playerID, -- playerID column from temporary table variable created in step 1
			@teamID -- TeamID passed as a parameter when the procedure is executed
		FROM
			@playerTable
			
		IF @@ERROR <> 0 -- If an error occurred when inserting, then rollback the whole procedure to cancel everything done
		BEGIN
			ROLLBACK TRAN
			RAISERROR('An error occurred while inserting players into the team.', 10, 1)
			RETURN
		END
		
	COMMIT TRAN -- No errors, so save our changes
 
GO

Open in new window

0
 
RouchieCommented:
As for ASP, the looping thing is quite simple.  The caveat is that you MUST have the players selected in the list when the form is submitted, otherwise the browser doesn't post the ID's.  So, you get something like this:


Dim chosenGolfers
Dim TeamName
 
' Get values posted from form
chosenGolfers = Request.Form("AvailableGolfers") ' we are presuming this would be something like "1,4,5,16"
TeamName = Request.Form("TeamName") ' the team name from your form
 
' Split the golfer IDs into an array so we can loop through them
chosenGolfers = Split(chosenGolfers, ",")
 
If UBound(chosenGolfers) > 0 Then -- if at least 1 golfer is chosen
 
  Dim myConn
  Dim mySQL
  Dim currentGolferID
  Set myConn = Server.CreateObject("ADODB.Connection")
 
  Dim i
  For i=0 to UBound(chosenGolfers) - 1 ' 
    currentGolferID = chosenGolfers(i)
    currentGolferID = Replace(currentGolferID, "'", "''") ' check to stop SQL injection attempts
    mySQL = "INSERT INTO myTableName (teamName, golferID) VALUES ('" & TeamName & "'," & currentGolferID & ")"
    myConn.Execute(mySQL)
  Next
 
  myConn.Close
  Set myConn = Nothing
 
End If

Open in new window

0
 
RickyGtzAuthor Commented:
thank you all
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 20
  • 9
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now