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!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reading from MS Access

Status
Not open for further replies.
kamsabeti

kamsabeti

IS-IT--Management
Feb 26, 2008
21
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


<!--#include file="conn.asp"-->
<!--#include file="verify.asp"-->
<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>&nbsp;</td><td><font face="Arial" style="font-size: 10pt"><%=strquantity%></font></td>
<% for diffn=1 to j-2 %><td>&nbsp;</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>&nbsp;</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>
 
Sort by date Sort by votes
This is not going to be a complete answer to your question, but assuming your sql2 statement pulls the data you need from the "master" table you would need to grab values for MVC,NRC,NPN and assign them to variables inside this [red]red[/red] loop (or in a similar loop for that recordset). You would probably have to expand the If statement or add a new one to get the values for your three fields. Right now, you are just testing it for 2 values, the quantity and some other field.
[red]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 [/red]

Then once you capture the values for those fields, you would need to assign them to a variable and put them in the table. The [blue]blue[/blue] line in the table below assigns a value to the Source box in your table. In this case it would be "Master". The next html line (that I created right under that), in [green]green[/green] is where you would put the variable that has your recordset value for the MVC & NRC fields in it.

[blue] <td><font face="Arial" style="font-size: 10pt"><%=datafrom%></font></td>[/blue]
[green]<td><font face="Arial" style="font-size: 10pt"><%=rs.(MVC&NRCfields)%></font></td>[/green]
<td><font face="Arial" style="font-size: 10pt"><%=rs.(NPN field)%></font></td>

</tr>
Then you would have to repeat the process for the third, NPN field which would go in the last line. If you give it a try and post back with specific problems, we might be able to work this out.

Paul

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
148
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
SMTP emailing from localhost
Replies
1
Views
113
JScannell
JScannell
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
497
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Classic ASP multiple record update with Access
Replies
7
Views
212
dhookom
dhookom
Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
339
Krus1972
Krus1972

Part and Inventory Search

Sponsor

Back
Top