[RESOLVED] connection between SQL Server 2. and dreamweaver??

Printable View

Show 60 post(s) from this thread on one page

Jul 21st, 2005, 08:41 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Ok, thanks !!

Re: connection between SQL Server 2. and dreamweaver??

This is the page TEST.ASP, just copy all and save it. Then run the page again. You can add new records as you like.

VB Code:

<!--#include File="conn.asp"-->
 
<%'this is the save to database code when somebody enters a new data
if Request.ServerVariables("REQUEST_METHOD") = "POST"  then'already submitted
sSkillName = request("skillname") 'here we can take what the user entered as skill name, put to a variable
Set RS=CreateObject("ADODB.Recordset")
sql="select * from Skills where SkillName = '" & sSkillname & "' or SkillNum = " & request("skillnum")
Rs.Open sql,conn,1,3
If RS.eof then 'Just want to check if this skill name already exists in db before saving it
rs.addnew 'make a new record
rs("Skillname") = sSkillName 'the name entered by user
rs("Skillnum") = request("skillnum")
rs("CreatedDate") = Date 'that puts today's date
rs("LastUpdate") = Date 'that puts today's date
rs.update 'save the record
else' it is already here, don't submit to db
response.write "<p align='center'><font face='verdana' size='2' color='red'>The skill you entered, " & sSkillName & ", is already in the database, so it was not added.</font></p>"
end if'if the skill is not already in db
end if'submit data to db
%>
 
<html>
<head>
<script language="javascript">
function checkinput(){
//use this Javascript function to see what the user entered before submitting.
if (newskill.skillname.value == "")
{alert("You have to enter a skill name.");
newskill.skillname.focus();
return false;}
if (newskill.skillnumber.value == "")
{alert("You have to enter a skill number.");
newskill.skillnumber.focus();
return false;}
if (IsNumeric(newskill.skillnumber.value) == false)
{alert("The skill number must be a number.");
newskill.skillnumber.focus();
return false;}
 
}
function IsNumeric(sText)
{
//this function checks if some text is a number or not
if (sText.length > 1 && sText.charAt(0)=="0")
{  IsNumber = false;
   return IsNumber}
   var ValidChars = "0123456789";
   var IsNumber=true;
   var Char; 
   for (i = 0; i < sText.length && IsNumber == true; i++) 
      { 
      Char = sText.charAt(i); 
      if (ValidChars.indexOf(Char) == -1) 
         {
         IsNumber = false;
         }   
      }
   return IsNumber;   
   }
</script>
</head>
<body>
 
 
 
<%'here is the form to add new records!%>
<form name="newskill" id="newskill" Method="Post" onSubmit="return checkinput();">
  <table cellpadding="4" cellspacing="1" border="0"  width="75%" align="center" bgcolor="000082">
    <tr> 
      <td height="37" colspan="2" bgcolor="#000082"><font face="verdana, arial, helvetica" size="2"  color="#F0F0F0"><b>Adding 
        a New Skill</b></font><font face="verdana,arial,helvetica" size="1"  color="#F0f0f0">Here 
        you add new skill records to the database.</font></td>
    </tr>
     <tr> 
      <td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>Skill Number</b><br>
        </font> <font face="verdana,arial,helvetica" size="1" >Enter a skill number (no decimals).</font></td>
      <td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" > 
<input type="text" name="skillnum" id="skillnum" maxlength="10" size="10">
        </font></td>
    </tr>
    <tr> 
      <td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>Skill Name</b><br>
        </font> <font face="verdana,arial,helvetica" size="1" >Enter a skill name (up to 30 characters).</font></td>
      <td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" > 
<input type="text" name="skillname" id="skillname" maxlength="30" size="30">
        </font></td>
    </tr>
    
    <tr bgcolor="#000082"> 
      <td colspan="2"><font face="verdana, arial, helvetica" size="2" >&nbsp; 
        </font></td>
    </tr>
  </table>
<p align="center"><input type="submit" class="bginput" value="Submit"></p>
         </form>
<%'end of add records form%>
 
 
 
 
<%'here is where you show the records%>
<table width="75%" align="center" bgcolor="#c0c0c0" cellspacing="1">
<tr>
<td width="25%" align="center" bgcolor="#ffffff"><b>Number</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Name</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Created</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Updated</b></td>
</tr>
<%
SET rs=CreateObject("ADODB.Recordset")
sql="select * from Skills Order by CreatedDate ASC"
rs.Open sql,Conn,1,2 
do until rs.eof
%>
<tr>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("SkillNum")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("SkillName")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("createdDate")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("lastupdate")%></td>
</tr>
<%rs.movenext
loop%>
</table>
 
 
</body>
</html>

Jul 21st, 2005, 08:58 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

let me know if it has errors, I can not run this without the database.

I think you are making a mistake in your db structure. If you want to use skillnum, enter the number yourself, and change it later, you should add a new Column, ID, and make that the Primary Key, auto increment. That way, you have a real, unique, permanent ID for each record, and you can change anything else.
Otherwise, later, maybe SQL Server will make an error when you want to edit one record, since it cannot confirm which record is being edited.
Jul 21st, 2005, 09:03 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Ooohhh
Then i guess i'll have to do it to the rest of my tables. :-( I'll be here i while.

I did the copy/paste, and it worked perfect. !!!!
No errors :-)

Thank you again,
Edith
Jul 21st, 2005, 09:11 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

Be sure to read and understand all the code. The javascript will not allow the user to submit unless they enter the name and number. Then number must be numeric.
Then after submit, the query checks to see if the same name or number exists in the database, so it can't be added again.

The basic structure is <form method="post"><inputs></form>
then the other half is after the page reloads <% if method="post" %>

That's basically it for adding records.

When you feel comfortable with this code, you can go on to editing data. But like I said, you should have a permanent, increment ID for each record, so that SQL Server can find it. Remember, when you bring a recordset back to your page, you disconnect from the database. To save changes to the record, SQL Server has to find the same record in the database again. But if you change the number, which is the primary key, probably SQL Server cannot find that record again and you will get the error:
'this recordset cannot be updated'

So, add the ID field to your table, make it INT, set as Autoincrement, and set to Primary Key. Then you should change SkillNum to an INT field.
Also, if your Skill Name has a limited size, you should not use TEXT field. That is too big (2E16 !!). You should use VARCHAR and fix a limit (for example 50 characters)
Also, never use NVARCHAR. You don't need that. It is for unicode when you use languages like Chinese, Japanese. If you use English, just use varchar.
Jul 21st, 2005, 09:15 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

by the way, if you add the ID field, I think it is easier to add it just as INT first, not autoincrement. Then save the table design. Then open the table all records, and add the numbers 1,2,3, whatever to each record under ID. When every record has a unique ID, save that (re-execute the !). Then go back to table design and switch it to AUTOINcREMENT. This way will be no errors.
Jul 21st, 2005, 09:24 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Wow, so i'll probably have to redo my tables, must of the field are text.
I'll do the ID, INT and re-execute it to put it in auto increment.

This is all new to me.

Thanks !!
Jul 21st, 2005, 09:30 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

OK. well, edit code is not difficult. When you are ready to do it, let me know. I'm going offline now, since it's pretty late here. Good luck.
Jul 21st, 2005, 09:35 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

ok, good night
Jul 26th, 2005, 09:07 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Hello

I don't know if you check here first or some other place but anyway I put the msg here.

I have this syntax error and i can't see the data in my page.

<%
SET rs=CreateObject("ADODB.Recordset")
sql="select * from User Order by CreatedDate ASC"
rs.Open sql,Conn,1,2
do until rs.eof
%>

But i see no error

Plus in a new ASP i created when i enter the data and press submit it doesn't show the name, but everything else is visible. Weird.

Hope u answer :-)
Jul 26th, 2005, 09:10 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

This is the code of Comp - the one that i can't see the name:

<%
if Request.ServerVariables("REQUEST_METHOD") = "POST" then
sCompName = request("sCompName")
Set RS=CreateObject("ADODB.Recordset")
sql="select * from Competency where CompName = '" & sCompName & "' or CompNum = " & request("CompNum")
Rs.Open sql,conn,1,3
If RS.eof then
rs.addnew
rs("CompName") = sCompName
rs("CompNum") = request("CompNum")
rs("CompName") = request("CompName")
rs("CompDesc") = request("CompDesc")
rs("CompProcowner") = request("CompProcowner")
rs("CompSystowner") = request("CompSystowner")
rs("CreatedDate") = Date
rs("LastUpdate") = Date
rs.update
else
response.write "<p align='center'><font face='verdana' size='2' color='red'>The comp you entered, " & sCompName & ", is already in the database, so it was not added.</font></p>"
end if
end if
%>

<html>
<head>
<script language="javascript">
function checkinput(){
//use this Javascript function to see what the user entered before submitting.
if (newcomp.CompName.value == "")
{alert("You have to enter a Comp name.");
newcomp.CompName.focus();
return false;}
if (newcomp.CompDesc.value == "")
{alert("You have to enter a Comp desc.");
newcomp.CompDesc.focus();
return false;}
if (newcomp.CompProcowner.value == "")
{alert("You have to enter a Comp proc name.");
newcomp.CompProcowner.focus();
return false;}
if (newcomp.CompSystowner.value == "")
{alert("You have to enter a Comp system name.");
newcomp.CompSystowner.focus();
return false;}
if (newcomp.compnumber.value == "")
{alert("You have to enter a skill number.");
newcomp.compnumber.focus();
return false;}
if (IsNumeric(newcomp.compnumber.value) == false)
{alert("The skill number must be a number.");
newcomp.compnumber.focus();
return false;}

}
function IsNumeric(sText)
{
//this function checks if some text is a number or not
if (sText.length > 1 && sText.charAt(0)=="0")
{ IsNumber = false;
return IsNumber}
var ValidChars = "0123456789";
var IsNumber=true;
var Char;
for (i = 0; i < sText.length && IsNumber == true; i++)
{
Char = sText.charAt(i);
if (ValidChars.indexOf(Char) == -1)
{
IsNumber = false;
}
}
return IsNumber;
}
</script>
</head>
<body>
Jul 26th, 2005, 09:30 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Don't know how but i fixed the code and now i can see the name.

But the syntax error it's still not visible.

<table width="75%" align="center" bgcolor="#c0c0c0" cellspacing="1">
<tr>
<td width="25%" align="center" bgcolor="#ffffff"><b>Number</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Name</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Last Name</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Roles</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Email</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Created</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Updated</b></td>
</tr>
<%
SET rs=CreateObject("ADODB.Recordset")
sql="select * from User Order by CreatedDate ASC"
rs.Open sql,Conn,1,2
do until rs.eof
%>
<tr>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("NtID")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserFirstName")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserLastName")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserRoles")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserEmail")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("CreatedDate")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("LastUpdate")%></td>
</tr>
<%rs.movenext
loop%>
</table>
Jul 27th, 2005, 02:51 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

so what is the error? is there an error message? do you mean your data is not visible?

by the way, your table has 7 25% widths, that means your table is 175% wide.

Maybe adjust each width according to which data is larger, but usually, the total width of the text divisions <td> should not be more than 100%.
Of course this is not the reason for any code problem, but just an aesthetic tip.
Jul 27th, 2005, 07:07 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Good morning,

Ooohh, right, change the number to a 25%.

The page user.asp gives me an incorrect syntax error near the keyword "User". The page doesn't show the data, not visible.

But i checked the whole code and i wrote the "keyword User" correct. I use the Find
and replace.

Thanks for your help ...
Jul 27th, 2005, 08:25 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

so, I'm not clear. do you still have a problem? if so, what is it?
Jul 27th, 2005, 08:44 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

:-)

Yes, the problem is that i can't see the user.asp
I use the code you gave me and modify it so i could see the user info. But when i open the page is not visible, there is an error, an Incorrect syntax near the keyword 'User'.
So i tried to use the Find and Replace to see if i really wrote it wrong but its right,. so i don't know what's his problem (the PC i mean)

I now added this columns:
rs("NtId") = sNtID
rs("UserFirstName") = request("UserFirstName")
rs("UserLastName") = request("UserLastName")
rs("UserRoles") = request("UserRoles")
rs("UserEmail") = request("UserEmail")
rs("CreatedDate") = Date
rs("LastUpdate") = Date

The problem line is this one:

<%
SET rs=CreateObject("ADODB.Recordset")
sql="select * from UserTbl Order by CreatedDate ASC"
rs.Open sql,Conn,1,2
do until rs.eof
%>

Ok, ???
Jul 27th, 2005, 08:46 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

You know i re-did it and put it in a new table and know its visible... i think that the word "User" can't be used.
Jul 27th, 2005, 08:49 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

can you paste your whole page code and also the error exactly?
Jul 27th, 2005, 08:50 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Before u go

What if a have UserTbl join to UserTraining (both tables) With the same primary key NtID. Do i have to change one of the primary keys?
Jul 27th, 2005, 08:50 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

The one with the word User??
Jul 27th, 2005, 08:52 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

well, is the problem solved?
by the way, if some field name is a reserved word, like "ORDER" you can still use it, just have to write like select [order] from tblWhatever where....
Jul 27th, 2005, 08:53 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

well, if problem is solved, don't need to paste
Jul 27th, 2005, 08:59 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

OOohh, ok

Thank you :-)

Another question. Why am i getting this other error after i fill the balnks and press submit.?? It says Invalid column name..
Jul 27th, 2005, 09:00 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

his is the error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Edith'.
/user.asp, line 8

This is the code:



<%
if Request.ServerVariables("REQUEST_METHOD") = "POST" then
sNtID = request("NtID")
Set RS=CreateObject("ADODB.Recordset")
sql="select * from UserTbl where NtId = '" & sNtID & "' or UserFirstName = " & request("UserFirstName")
Rs.Open sql,conn,1,3
If RS.eof then
rs.addnew
rs("NtId") = sNtID
rs("UserFirstName") = request("UserFirstName")
rs("UserLastName") = request("UserLastName")
rs("UserRoles") = request("UserRoles")
rs("UserEmail") = request("UserEmail")
rs("CreatedDate") = Date
rs("LastUpdate") = Date
rs.update
else
response.write "<p align='center'><font face='verdana' size='2' color='red'>The user you entered, " & sNtID & ", is already in the database, so it was not added.</font></p>"
end if
end if
%>

<% if err.number <>0 then
response.write err.description
end if %>

<html>
<head>
<script language="javascript">
function checkinput(){
//use this Javascript function to see what the user entered before submitting.
if (newuser.UserFirstName.value == "")
{alert("You have to enter a user name.");
newuser.UserFirstName.focus();
return false;}
if (newuser.UserLastName.value == "")
{alert("You have to enter a user Last name.");
newuser.UserLastName.focus();
return false;}
if (newuser.NtID.value == "")
{alert("You have to enter a user number.");
newuser.NtID.focus();
return false;}
if (newuser.UserRoles.value == "")
{alert("You have to enter a user roles name.");
newuser.UserRoles.focus();
return false;}
if (newuser.UserEmail.value == "")
{alert("You have to enter a user email.");
newuser.UserEmail.focus();
return false;}
if (IsNumeric(newuser.NtID.value) == false)
{alert("The user ID must be a number.");
newuser.NtID.focus();
return false;}

}
function IsNumeric(sText)
{
//this function checks if some text is a number or not
if (sText.length > 1 && sText.charAt(0)=="0")
{ IsNumber = false;
return IsNumber}
var ValidChars = "0123456789";
var IsNumber=true;
var Char;
for (i = 0; i < sText.length && IsNumber == true; i++)
{
Char = sText.charAt(i);
if (ValidChars.indexOf(Char) == -1)
{
IsNumber = false;
}
}
return IsNumber;
}
</script>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body>

<%'here is the form to add new records!%>
<form name="newuser" id="newuser" Method="Post" onSubmit="return checkinput();">
<table cellpadding="4" cellspacing="1" border="0" width="75%" align="center" bgcolor="000082">
<tr>
<td height="37" colspan="2" bgcolor="#000082"><font face="verdana, arial, helvetica" size="2" color="#F0F0F0"><b>Adding
a New User</b></font><font face="verdana,arial,helvetica" size="1" color="#F0f0f0">  Here
you add new user records to the database.</font></td>
</tr>
<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User ID Number</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user number (no decimals).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="NtID" id="NtID" maxlength="10" size="10">
</font></td>
</tr>
<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Name</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user name (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserFirstName" id="UserFirstName" maxlength="30" size="30">
</font></td>
</tr>

<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Last Name</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user last name (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserLastName" id="UserLastName" maxlength="30" size="30">
</font></td>
</tr>

<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Role Name</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user role name (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserRoles" id="UserRoles" maxlength="30" size="30">
</font></td>
</tr>
<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Email</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user email (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserEmail" id="UserEmail" maxlength="30" size="30">
</font></td>
</tr>

<tr bgcolor="#000082">
<td colspan="2"><font face="verdana, arial, helvetica" size="2" > 
</font></td>
</tr>
</table>
<p align="center"><input type="submit" class="bginput" value="Submit"></p>
</form>
<%'end of add records form%>
<%'here is where you show the records%>
<table width="25%" align="center" bgcolor="#c0c0c0" cellspacing="1">
<tr>
<td width="25%" align="center" bgcolor="#ffffff"><b>Number</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Name</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Last Name</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Roles</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Email</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Created</b></td>
<td width="25%" align="center" bgcolor="#ffffff"><b>Updated</b></td>
</tr>
<%
SET rs=CreateObject("ADODB.Recordset")
sql="select * from UserTbl Order by CreatedDate ASC"
rs.Open sql,Conn,1,2
do until rs.eof
%>
<tr>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("NtID")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserFirstName")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserLastName")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserRoles")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("UserEmail")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("CreatedDate")%></td>
<td width="25%" align="center" bgcolor="#ffffff"><%=rs("LastUpdate")%></td>
</tr>
<%rs.movenext
loop%>
</table>
</body>
</html>
Jul 27th, 2005, 09:01 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

This is line 8: Rs.Open sql,conn,1,3
Jul 27th, 2005, 09:02 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

should be

or UserFirstName = '" & request("UserFirstName") & "'"
Jul 27th, 2005, 09:03 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

when you get line error RS.Open, that means your SQL statement has error.

since you didn't use ' ', the SQL thinks EDITH is a field name
Jul 27th, 2005, 09:07 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

also, your second table is now only 25%. I guess it must be very narrow!
what i said before is this:
<table width="50%" or "80%" or "100%" or whatever,
but the <TD>'s should add up to 100

for example, if you have 5 td's, they should be <width="20%"> (average of course)

notice my first table has 2 columns, one at 55% of the table width, one at 45%

in my other table, there were 4 columns, all 25%, in both tables 100% is used

table width doesn't matter.
Jul 27th, 2005, 09:09 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Like this:
sql="select * from UserTbl where NtId = '" & sNtID & "' or UserFirstName = " & request("UserFirstName")

Cause it still sending me an error
Jul 27th, 2005, 09:10 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

sql="select * from UserTbl where NtId = '" & sNtID & "' or UserFirstName = " & request("UserFirstName")

should be:

sql="select * from UserTbl where NtId = '" & sNtID & "' or UserFirstName = '" & request("UserFirstName") & "'"
Jul 27th, 2005, 09:12 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

here is the basic rule in sql

where sometext = 'Edith'

where somenumber = 555

when using number fields you can do this way:
where mynum=" & tNumber

but when doing text, must be:
where mytext = '" & sText & "'"

And that's the rule
Jul 27th, 2005, 09:18 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Right did the columns but, does it still mean, the invalid column name, that the width of my table is not adding up?

The table is too big? or too small?

I did the change and the table looks big enough :-)
Jul 27th, 2005, 09:19 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

ok, i'll check rigth now
Jul 27th, 2005, 09:24 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

New error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E2F)
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_User_User-Training'. The conflict occurred in database 'copla-bi3dc-stage', table 'User-Training', column 'NtID'.
/user.asp, line 18
Jul 27th, 2005, 09:28 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

table width not related to code problem.

html is not code (not really, it's just formatting)
code is ASP here, anything inside <% %>

html code cannot make errors, it can only make strange looking pages

the invalid column name is because you didn't put ' ' around your request("userfirstname")

OK.
imagine you are sql and you are trying to read this sentence:

select * from tblWhatever where username = password
and this sentence:
select * from tblWhatever where username = 'password'

see, they are different.
in the first, it means, username and password are the same
in the second it means my username is "password"

so when you are comparing in SQL with "WHERE" you should put string variables inside ''
like:
where name = ' " & request("something") & " ' "
but no spaces:
where name = '" & request("something") & "'"

OK?
Jul 27th, 2005, 09:32 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

hmm..
I guess your NTID column is set as auto-increment, so that you cannot assign this number.

Check that it is not set as auto-increment.
Jul 27th, 2005, 09:37 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Yes, i guess

Thanks
Jul 27th, 2005, 09:41 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

OK. is it solved?
Jul 27th, 2005, 09:43 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

if NTID is set as IDENTITY YES in SQL Server, you should change it to IDENTITY NO
or just remove this line:
rs("NtId") = sNtID
and your page will work. but if you do that, you cannot control this number.
up to you.
Jul 27th, 2005, 09:44 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

No the NtID is Int

but now the html is visible. The problem seems to be solve, thanks to u.

Ok what's the next step !!
How do i modify my data? After being submitted
Jul 27th, 2005, 09:46 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

no problem. just follow the instruction like this:
Jul 27th, 2005, 09:46 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

No, i cant remove it... it's not up to me (my boss)

But it's solve, i change the code to the way u told me.
Jul 27th, 2005, 09:48 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Like what?

u there?
Jul 27th, 2005, 09:54 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

sorry, I have to stop first. I think your code is in danger because your table has no unique IDENTITY Field. This will make it very difficult to control the data. For example, when somebody edits the data and change the NTID number or username, maybe two or three will have the same number. But SQL Server cannot distinguish one from another.

I suggest you have an ID field in the table of Users that is set as INT, IDENTITY YES (NOT FOR REPLICATION)

then you can proceed with edit data. It is for stability of your database that you have to take this step.
Jul 27th, 2005, 09:57 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

Add the ID field to your table and then post the whole user.asp page code to me. I will modify it to be an edit data page and send it back to you.
But it's late here. I'm going offline. Send the page up again and I will send it back to you tomorrow when I am at the office.
Jul 27th, 2005, 09:59 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Ok, done
Jul 27th, 2005, 10:04 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Ok, thank u.

This is the new ID: UserID
And here is the code:


<%
if Request.ServerVariables("REQUEST_METHOD") = "POST" then
sNtID = request("NtID")
Set RS=CreateObject("ADODB.Recordset")
sql="select * from UserTbl where NtId = '" & sNtID & "' or UserFirstName = '" & request("UserFirstName") & "'"
Rs.Open sql,conn,1,3
If RS.eof then
rs.addnew
rs("NtId") = sNtID
rs("UserFirstName") = request("UserFirstName")
rs("UserLastName") = request("UserLastName")
rs("UserRoles") = request("UserRoles")
rs("UserEmail") = request("UserEmail")
rs("CreatedDate") = Date
rs("LastUpdate") = Date
rs.update
else
response.write "<p align='center'><font face='verdana' size='2' color='red'>The user you entered, " & sNtID & ", is already in the database, so it was not added.</font></p>"
end if
end if
%>
<% if err.number <>0 then
response.write err.description
end if %>

<html>
<head>
<script language="javascript">
function checkinput(){
//use this Javascript function to see what the user entered before submitting.
if (newuser.UserFirstName.value == "")
{alert("You have to enter a user name.");
newuser.UserFirstName.focus();
return false;}
if (newuser.UserLastName.value == "")
{alert("You have to enter a user Last name.");
newuser.UserLastName.focus();
return false;}
if (newuser.NtID.value == "")
{alert("You have to enter a user number.");
newuser.NtID.focus();
return false;}
if (newuser.UserRoles.value == "")
{alert("You have to enter a user roles name.");
newuser.UserRoles.focus();
return false;}
if (newuser.UserEmail.value == "")
{alert("You have to enter a user email.");
newuser.UserEmail.focus();
return false;}
if (IsNumeric(newuser.NtID.value) == false)
{alert("The user ID must be a number.");
newuser.NtID.focus();
return false;}

}
function IsNumeric(sText)
{
//this function checks if some text is a number or not
if (sText.length > 1 && sText.charAt(0)=="0")
{ IsNumber = false;
return IsNumber}
var ValidChars = "0123456789";
var IsNumber=true;
var Char;
for (i = 0; i < sText.length && IsNumber == true; i++)
{
Char = sText.charAt(i);
if (ValidChars.indexOf(Char) == -1)
{
IsNumber = false;
}
}
return IsNumber;
}
</script>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body>

<%'here is the form to add new records!%>
<form name="newuser" id="newuser" Method="Post" onSubmit="return checkinput();">
<table cellpadding="4" cellspacing="1" border="0" width="75%" align="center" bgcolor="000082">
<tr>
<td height="37" colspan="2" bgcolor="#000082"><font face="verdana, arial, helvetica" size="2" color="#F0F0F0"><b>Adding
a New User</b></font><font face="verdana,arial,helvetica" size="1" color="#F0f0f0">  Here
you add new user records to the database.</font></td>
</tr>
<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User ID Number</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user number (no decimals).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="NtID" id="NtID" maxlength="10" size="10">
</font></td>
</tr>
<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Name</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user name (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserFirstName" id="UserFirstName" maxlength="30" size="30">
</font></td>
</tr>

<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Last Name</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user last name (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserLastName" id="UserLastName" maxlength="30" size="30">
</font></td>
</tr>

<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Role Name</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user role name (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserRoles" id="UserRoles" maxlength="30" size="30">
</font></td>
</tr>
<tr>
<td width="55%" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" ><b>User Email</b><br>
</font> <font face="verdana,arial,helvetica" size="1" >Enter a user email (up to 30 characters).</font></td>
<td width="45%" align="center" bgcolor="#DFDFDF"><font face="verdana, arial, helvetica" size="2" >
<input type="text" name="UserEmail" id="UserEmail" maxlength="30" size="30">
</font></td>
</tr>

<tr bgcolor="#000082">
<td colspan="2"><font face="verdana, arial, helvetica" size="2" > 
</font></td>
</tr>
</table>
<p align="center"><input type="submit" class="bginput" value="Submit">
<input type="reset" name="Reset" value="Clear">
</p>
</form>
<%'end of add records form%>

<%'here is where you show the records%>
<table width="100%" align="center" bgcolor="#c0c0c0" cellspacing="1">
<tr>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Number</b></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Name</b></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Last Name</b></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Roles</b></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Email</b></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Created</b></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><b>Updated</b></td>
</tr>
<%
SET rs=CreateObject("ADODB.Recordset")
sql="select * from UserTbl Order by CreatedDate ASC"
rs.Open sql,Conn,1,2
do until rs.eof
%>
<tr>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("NtID")%></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("UserFirstName")%></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("UserLastName")%></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("UserRoles")%></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("UserEmail")%></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("CreatedDate")%></td>
<td width="14.2%" align="center" bgcolor="#ffffff"><%=rs("LastUpdate")%></td>
</tr>
<%rs.movenext
loop%>
</table>
</body>
</html>
Jul 27th, 2005, 08:33 PM
wengang

Re: connection between SQL Server 2. and dreamweaver??

ok. got it. I'll edit the page and send it up later.
Jul 27th, 2005, 09:15 PM
wengang

1 Attachment(s)

Re: connection between SQL Server 2. and dreamweaver??

OK.

I have zipped the two files and attached to this post.

If you see any code you don't understand, ask me.

This is a basic model of add and edit data.

If you can understand all the code, you should have no problem accomplishing other tasks with SQL Server.
Jul 28th, 2005, 07:53 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Hello,

thank you for the zipped files.

I read both codes and i'm still studying it . Then i run the page, but there's an error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
/edituser.asp, line 35

And line 35 is this:
Rs.Open sql,conn,1,3

In the edituser.asp

And in the user.asp, there's another error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/user.asp, line 157

line 157:
<td width="10%" align="left" bgcolor="#ffffff"><a href="edituser.asp?id=<%=rs("UserID")%>"><%=rs("NtID")%></a></td>

And to be honest I don't understand it, why did you write both ID's? (user and NT)

Thank you again,
Jul 28th, 2005, 08:14 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

hmm.
i am confused too. Yesterday my meaning about add a new ID column is to add one that is autoincrement. I guessed you added that in ADDITION To your NTID.

The point is that there should be one column in the table whose value is not set by you, it is set automatically by sql server. Without this, it is very easy to make errors in modifying data.

So, do you have an int column set as IDENTITY YES (NOT FOR REPLICATION)?
Jul 28th, 2005, 08:17 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Yes, it's UserID.

Ok, so i have two UserID and NtID. Do i delete NtID?

:-)
Jul 28th, 2005, 08:19 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

I thought you wanted to keep one which is set by you, called NTID, and one that is set by SQL Server, called USERID. If you have that, my pages should work correctly.
Jul 28th, 2005, 08:20 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

if you have already deleted NTID and now you add it again, you should add the number values directly in SQL SERVER the first time before you run the pages.
Jul 28th, 2005, 08:22 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Yes, that's what i want.
NtId is set by me, and UserID is the unique number.
But what's the meaning of the errror ?

Now i'm more confused...
Jul 28th, 2005, 08:23 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

No, i haven't touch a thing.
Jul 28th, 2005, 08:26 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

ok.

so your db has one column
NTID set as INT, Identity NO
and
USERID set as INT, Identity Yes

is it right?

so try to run your user.asp page again and tell me the error.
Jul 28th, 2005, 08:40 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

Do you know about QuickBook?
Jul 28th, 2005, 08:41 AM
wengang

Re: connection between SQL Server 2. and dreamweaver??

sorry. it's my mistake

change line 7 to this:
sql="select * from UserTbl where NtId = " & sNtID
and you will be OK!
Jul 28th, 2005, 08:45 AM
edith

Re: connection between SQL Server 2. and dreamweaver??

I don't think is really keeping the data, is not in the db.

Show 60 post(s) from this thread on one page