Reading from Access

kamsabeti · Jul 23, 2008

Recetly we update our database on access. we addedd more cloumns of data on one of the tables. I have five tables on the access " active,master,customer,user,...)The table I added more cloumn is "master" table. before we had: part-no., descrption,price" now we have "part-no.,descption,price,MVC,NRC,NPN". the ASP program it will upload an excel file with part-no. and qty then it will display the data from these two tables"active and master" by active being the first choice the master the second. can you please look at this ASP code to see how can i add other column to be printed with new data. Thanks. Kamran



<html>
<head>
<title>new page</title>
<link rel="stylesheet" type="text/css" href="css.css">
<meta http-equiv="Content-Type" content="text/html;">
</head>
<body>
<form name="form" method="post" action="savexls.asp" enctype="multipart/form-data" >
<input type="file" name="file1" size="20">
<input type="submit" name="Submit" value="UPLOAD">
<p>
</p>
<%
set rst = server.createobject("adodb.recordset")
sql="select top 1 * from active"
rst.open sql,conn,1,1
%>
<table style="border-collapse: collapse" bordercolor="#C0C0C0" width="100%" id="AutoNumber3" cellspacing="1" height="0" border="1">
<tr>
<% j=0
for each x in rst.fields %>
<% fieldname=trim(x.name)
if fieldname="pn" then
fieldname="Part-No."
end if
if fieldname="price" then
fieldname="Cost"
end if

if j=2 then %>
<td width="7%" height="50" align="center" bgcolor="#E1E1E1"><b><font face="Arial" style="font-size: 11pt">Qty</font></b></td>

<td height="50" align="center" bgcolor="#E1E1E1"><b><font face="Arial" style="font-size: 11pt"><%=ucase(left(fieldname,1))&right(fieldname,len(fieldname)-1)%></font></b></td>

<% else
%>
<td height="50" align="center" bgcolor="#E1E1E1"><b><font face="Arial" style="font-size: 11pt"><%=ucase(left(fieldname,1))&right(fieldname,len(fieldname)-1)%></font></b></td>
<%
end if
j=j+1
next
%>
<td align="center" bgcolor="#E1E1E1" height="50"><b><font face="Arial" style="font-size: 11pt">Source</font></b></td>
<td align="center" bgcolor="#E1E1E1" height="50"><b><font face="Arial" style="font-size: 11pt">MVC&NRC</font></b></td>
<td align="center" bgcolor="#E1E1E1" height="50"><b><font face="Arial" style="font-size: 11pt">NPN</font></b></td>
</tr>

<%
rst.close
set rst=nothing
if request("id")=1 then
set conn1 = server.createobject("adodb.connection")
name=trim(request("name"))
FileName="xls\"&name
ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source ="&Server.MapPath(FileName)&";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Conn1.Open ConnStr

set rs = server.createobject("adodb.recordset")
set rs1 = server.createobject("adodb.recordset")
sql = "select * from [sheet1$]"
rs.open sql,conn1,1,1
do While Not Rs.EOF
i=1
for each x in rs.fields
if i=1 then
text1=x.name
end if
if i=2 then
text2=x.name
end if
i=i+1
next
'strpn=trim(rs("pn"))
strpn=trim(rs(text1))
'strquantity=trim(rs("quantity"))
strquantity=trim(rs(text2))
datafrom="None"
description=""
price=""
sql1="select * from active where pn='"&strpn&"'"
rs1.open sql1,conn,1,1
IF Rs1.Eof And Rs1.Bof Then
rs1.close
sql2="select * from master where pn='"&strpn&"'"
rs1.open sql2,conn,1,1

if Rs1.Eof And Rs1.Bof Then
datafrom="None" %>
<tr><td><font face="Arial" style="font-size: 10pt"> <%=strpn%></font></td><td> </td><td><font face="Arial" style="font-size: 10pt"><%=strquantity%></font></td>
<% for diffn=1 to j-2 %><td> </td><% next %><td><font face="Arial" style="font-size: 10pt"><%=datafrom%></font></td> </tr>
<% else
datafrom="Master"
do While Not Rs1.EOF
m=0 %><tr>
<%

for each x in rs1.fields
if m=2 then
xvalue="$"&trim(x.value) %>
<td><%=strquantity%></td>
<% else
xvalue=trim(x.value)
end if

%>
<td><font face="Arial" style="font-size: 10pt"><%=xvalue%></font></td>

<% m=m+1
next

if m<j then
for dif=1 to j-m
%><td> </td>
<%
next
end if
%>
<td><font face="Arial" style="font-size: 10pt"><%=datafrom%></font></td>
</tr>
<% rs1.MoveNext
loop

end if
else
datafrom="Active"
do While Not Rs1.EOF
m=0
%> <tr>
<% for each x in rs1.fields
if m=2 then
xvalue="$"&trim(x.value)
%> <td><%=strquantity%></td>
<% else
xvalue=trim(x.value)
end if

%>
<td><font face="Arial" style="font-size: 10pt"><%=xvalue%></font></td>

<% m=m+1
next %>
<td><font face="Arial" style="font-size: 10pt"><b><font color=#ff0000><%=datafrom%></font></b></font></td>
</tr>
<% rs1.MoveNext
loop

end if

Rs1.Close
rs.MoveNext

Loop

%>
</table>

</form>
<form name="form1" method="post" action="quote.asp?id=1&name=<%=name%>">
<b><font face="Arial" style="font-size: 11pt">Please Input Customer's NAME:</font></b>
<input type="text" name="email" size="20">
<input type="submit" name="continue" value="CONTINUE">
</form>
<form name="form2" method="post" action="quote.asp?id=1&name=<%=name%>">
<b><font face="Arial" style="font-size: 11pt">Select A Customer's Name:</font></b>
<p> </p>
<select name="email">

<%
sql3="select * from customer"
set rs3 = server.createobject("adodb.recordset")
rs3.open sql3,conn,1,1
if rs3.eof and rs3.bof then
response.write "Not record."
else
do while not rs3.eof
response.write "<option " & " value='" + Cstr(rs3("name")) + "'>" + rs3("name") + "</option>"
rs3.MoveNext
Loop
end if
rs3.close
set rs3=nothing
%>
</select>
<input type="submit" name="continue" value="CONTINUE">

</form>

<%
Rs.Close
Set Rs=nothing
Conn1.Close
Set Conn1=Nothing

Set Rs1=nothing
Conn.Close
Set Conn=Nothing
end if
%>
</body>
</html>

jmeckley · Jul 24, 2008

you need forum333 not the asp.net forum

Jason Meckley
Programmer
Specialty Bakers, Inc.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Reading from Access

kamsabeti

IS-IT--Management

jmeckley

Programmer

Similar threads

Part and Inventory Search

Sponsor