001.<% Option Explicit %>
002.<html>
003.<head>
004.<title></title>
005.</head>
006.<body>
007.<form name="frmMain" method="get" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
008. <input type="hidden" name="hdnCmd" value="">
009. <table width="599" border="1">
010. <tr><th>Keyword
011. <input name="txtKeyword" type="text" id="txtKeyword" value="<%=Request.QueryString("txtKeyword")%>">
012. <input type="submit" value="Search"></th>
013. </tr>
014. </table>
015.
016.<%
017.if Request.QueryString("txtKeyword") <> "" Then
018. Dim Conn,strSQL,objRec,strKeyword,objExec,i
019. strKeyword = Request.QueryString("txtKeyword")
020. Set Conn = Server.Createobject("ADODB.Connection")
021. Conn.Open "Driver={SQL Server};Server=.\SQL2008;Database=STOCK2015;UID=sa;PWD=12349789;"
022.
023.
024. If Request.Form("hdnCmd") = "Add" Then
025. strSQL = "INSERT INTO CSBARCODE "
026. strSQL = strSQL & "(BARCODE,PRODUCTCODE,BARCODETEXT,UNITCODE,PRICE,STATUS) "
027. strSQL = strSQL & "VALUES "
028. strSQL = strSQL & "('"& Request.Form("txtAddBARCODE") &"','"& Request.Form("txtAddBARCODE_ID") &"', '"& Request.Form("txtAddBARCODE_NAME") &"' "
029. strSQL = strSQL & ",'"& Request.Form("txtAddUNIT") &"','"& Request.Form("txtAddPRICE") &"', '"& Request.Form("txtAddSTATUS") &"') "
030. Set objExec = Conn.Execute(strSQL)
031. End IF
032.
033.
034. If Request.Form("hdnCmd") = "Update" Then
035. strSQL = "UPDATE CSBARCODE SET "
036. strSQL = strSQL & "BARCODE= '"& Request.Form("txtEditBARCODE") &"' "
037. strSQL = strSQL & ",BARCODETEXT = '"& Request.Form("txtEditBARCODE_NAME") &"' "
038. strSQL = strSQL & ",UNITCODE ='"& Request.Form("txtEditUNIT") &"' "
039. strSQL = strSQL & ",PRICE = '"& Request.Form("txtEditPRICE") &"' "
040. strSQL = strSQL & ",STATUS = '"& Request.Form("txtEditSTATUS") &"' "
041. strSQL = strSQL & "WHERE (CSBARCODE.PRODUCTCODE = '"& Request.Form("hdnEditID") &"')"
042. Set objExec = Conn.Execute(strSQL)
043. End If
044.
045.
046. If Request.QueryString("Action") = "Del" Then
047. strSQL = "DELETE FROM CSBARCODE "
048. strSQL = strSQL &"WHERE CSBARCODE = '"& Request.QueryString("CusID") &"' "
049. Set objExec = Conn.Execute(strSQL)
050. End IF
051.
052.
053. strSQL = "SELECT CSPRODUCT.CODE AS ID "
054. strSQL = strSQL & ",CSPRODUCT.NAMETH AS NAME "
055. strSQL = strSQL & ",CSBARCODE.BARCODETEXT AS BARCODETEXT "
056. strSQL = strSQL & ",CSBARCODE.PRODUCTCODE AS PRODUCTCODE "
057. strSQL = strSQL & ",CSPRODUCT.MAINGROUP AS MAINGROUP "
058. strSQL = strSQL & ",CSPRODUCT.SUBGROUP1 AS SUBGROUP "
059. strSQL = strSQL & ",CSPRODUCT.SUBGROUP2 AS BRAND "
060. strSQL = strSQL & ",CSBARCODE.UNITCODE "
061. strSQL = strSQL & ",CSBARCODE.PRICE "
062. strSQL = strSQL & ",CSBARCODE.STATUS "
063. strSQL = strSQL & ",CSBARCODE.BARCODE "
064. strSQL = strSQL & "FROM CSPRODUCT "
065. strSQL = strSQL & "LEFT JOIN CSBARCODE ON CSPRODUCT.CODE = CSBARCODE.PRODUCTCODE "
066. strSQL = strSQL & "WHERE (BARCODE LIKE '%"& Request.QueryString("txtKeyword") &"%')"
067. strSQL = strSQL & "ORDER BY CSPRODUCT.NAMETH,CSPRODUCT.MAINGROUP,SUBGROUP1,SUBGROUP2 ASC"
068. Set objRec = Server.CreateObject("ADODB.Recordset")
069. objRec.Open strSQL, Conn, 1,3
070.
071. If objRec.EOF Then
072. Response.write (" Not found record.")
073. Else
074.
075. Dim PageLen,PageNo,TotalRecord,TotalPage,No,intID
076. PageLen = 10
077. PageNo = Request.QueryString("Page")
078. if PageNo = "" Then PageNo = 1
079. TotalRecord = objRec.RecordCount
080. objRec.PageSize = PageLen
081. TotalPage = objRec.PageCount
082. objRec.AbsolutePage = PageNo
083.
084. %>
085. <table width="100%" border="1" align="center">
086. <tr bgcolor="#FFFF00">
087. <th width="169"> <div align="center">ID</div></th>
088. <th width="317"> <div align="center">NAME</div></th>
089. <th width="297"> <div align="center">BARCODE NAME</div></th>
090. <th width="215"> <div align="center">BARCODE ID</div></th>
091. <th width="105"> <div align="center">MAINGROUP</div></th>
092. <th width="87"> <div align="center">SUBGROUP</div></th>
093. <th width="109"> BRAND</th>
094. <th width="42"> <div align="center">UNIT</div></th>
095. <th width="119"> <div align="center">PRICE</div></th>
096. <th width="64"> <div align="center">STATUS</div></th>
097. <th width="83"> <div align="center">BARCODE</div></th>
098. <th width="31"> <div align="center">Edit</div></th>
099. <th width="49"> <div align="center">Delete</div></th>
100. </tr>
101.<%
102. NO=1
103. Do While Not objRec.EOF And NO <= PageLen
104. IF objRec.Fields("BARCODE").Value = Request.QueryString("CusID") and Request.QueryString("Action") = "Edit" Then
105.%>
106. <tr bgcolor="#99CC33">
107. <td><DIV><input name="txtEditID" type="text" id="txtEditID" value="<%=objRec.Fields("ID").Value%>" size="15">
108. <input type="hidden" name="hdnEditID" size="5" value="<%=objRec.Fields("ID").Value%>"></DIV></td>
109. <td><DIV><label for="txtNAME"></label><input name="txtNAME" type="text" id="txtNAME" value="<%=objRec.Fields("NAME").Value%>" size="20"></DIV></td><td><div>
110. <label for="txtEditBARCODE_NAME"></label><input name="txtEditBARCODE_NAME" type="text" id="txtEditBARCODE_NAME" value="<%=objRec.Fields("BARCODETEXT").Value%>"></div></td>
111. <td><div><label for="txtEditBARCODE_ID"></label><input name="txtEditBARCODE_ID" type="text" id="txtEditBARCODE_ID" value="<%=objRec.Fields("PRODUCTCODE").Value%>"></div></td>
112. <td><DIV><label for="txtMAINGROUP"></label><input name="txtMAINGROUP" type="text" id="txtMAINGROUP" value="<%=objRec.Fields("MAINGROUP").Value%>" size="15"></DIV></td><td><div align="center">
113. <input type="text" name="txtEditSUBGROUP" size="10" value="<%=objRec.Fields("SUBGROUP").Value%>"></div></td>
114. <td><input type="text" name="txtEditBRAND" size="10" value="<%=objRec.Fields("BRAND").Value%>"></td>
115. <td><DIV><input type="text" name="txtEditUNIT" size="7" value="<%=objRec.Fields("UNITCODE").Value%>"></DIV></td>
116. <td><DIV align="center"><input type="text" name="txtEditPRICE" size="7" value="<%=objRec.Fields("PRICE").Value%>"></div></td>
117. <td><DIV><input type="text" name="txtEditSTATUS" size="7" value="<%=objRec.Fields("STATUS").Value%>"></DIV></td>
118. <td><DIV><input type="text" name="txtEditBARCODE" size="7" value="<%=objRec.Fields("BARCODE").Value%>"></div></td>
119. <td colspan="2" align="right"><DIV align="center">
120. <input name="btnAdd" type="button" id="btnUpdate" value="Update" OnClick="frmMain.hdnCmd.value='Update';frmMain.submit();">
121. <input name="btnAdd" type="button" id="btnCancel" value="Cancel" OnClick="window.location='<%=Request.ServerVariables("SCRIPT_NAME")%>?&Page=<%=PageNo%>&txtKeyword=<%=strKeyword%>';">
122. </div></td>
123. </tr>
124.
125.<%
126.
127. Else
128.%>
129. <tr bgcolor="#66FFCC"><td><DIV> <%=objRec.Fields("ID").Value%> </DIV></td>
130. <td><DIV> <%=objRec.Fields("NAME").Value%></DIV></td>
131. <td><DIV> <%=objRec.Fields("BARCODETEXT").Value%><br></DIV></td>
132. <td><DIV> <%=objRec.Fields("PRODUCTCODE").Value%></DIV></td>
133. <td><DIV> <%=objRec.Fields("MAINGROUP").Value%></DIV></td>
134. <td><DIV> <%=objRec.Fields("SUBGROUP").Value%></DIV></td>
135. <td><DIV> <%=objRec.Fields("BRAND").Value%></DIV></td>
136. <td><DIV> <%=objRec.Fields("UNITCODE").Value%></DIV></td>
137. <td><DIV> <%=objRec.Fields("PRICE").Value%></DIV></td>
138. <td><DIV> <%=objRec.Fields("STATUS").Value%></DIV></td>
139. <td><DIV> <%=objRec.Fields("BARCODE").Value%></DIV></td>
140. <td align="center"><a href="<%=Request.ServerVariables("SCRIPT_NAME")%>?Action=Edit&Page=<%=PageNo%>&txtKeyword=<%=strKeyword%>&CusID=<%=objRec.Fields("BARCODE").Value%>">Edit</a></td>
141. <td align="center"><a href="JavaScript:if(confirm('Confirm Delete?')==true){window.location='<%=Request.ServerVariables("SCRIPT_NAME")%>?&txtKeyword=<%=strKeyword%>&Page=<%=PageNo%>&Action=Del&CusID=<%=objRec.Fields("BARCODE").Value%>';}">Delete</a></td>
142. </tr>
143.<%
144.
145. End IF
146.
147. NO = NO + 1
148. objRec.MoveNext
149. Loop
150.%>
151. <tr bgcolor="#66FF66">
152. <td> </td>
153. <td> </td>
154. <td><div>
155. <label for="txtAddBARCODE_NAME"></label>
156. <input name="txtAddBARCODE_NAME" type="text" id="txtAddBARCODE_NAME" size="20">
157. </div></td>
158. .<td><div>
159. <label for="txtAddBARCODE_ID"></label>
160. <input name="txtAddBARCODE_ID" type="text" id="txtAddBARCODE_ID" size="20">
161. </div></td>
162. <td> </td>
163. <td> </td>
164. <td> </td>
165. <td><DIV><input type="text" name="txtAddUNIT" size="7" id="txtAddUNIT"></div></td>
166. <td><div align="center"><input type="text" name="txtAddPRICE" size="7"></div></td>
167. <td><DIV><input type="text" name="txtAddSTATUS" size="7"></DIV></td>
168. <td><DIV><input type="text" name="txtAddBARCODE" size="7" id="txtAddBARCODE"></DIV></td>
169. <td colspan="2" align="right"><div align="center">
170. <input name="btnAdd" type="button" id="btnAdd" value="Add" OnClick="frmMain.hdnCmd.value='Add';frmMain.submit();">
171. </div></td>
172. </tr>
173. </table>
174. Total : <%=TotalRecord%> Page <%=PageNo%> All Page <%=TotalPage%>
175. <% IF Cint(PageNo) > 1 then %>
176. <a href="<%Request.ServerVariables("SCRIPT_NAME")%>?txtKeyword=<%=strKeyword%>&Page=1"><< First</a>
177. <a href="<%Request.ServerVariables("SCRIPT_NAME")%>?txtKeyword=<%=strKeyword%>&Page=<%=PageNo-1%>">< Back</a>
178. <% End IF%>
179. <% IF Cint(PageNo) < TotalPage Then %>
180. <a href="<%Request.ServerVariables("SCRIPT_NAME")%>?txtKeyword=<%=strKeyword%>&Page=<%=PageNo+1%>">Next ></a>
181. <a href="<%Request.ServerVariables("SCRIPT_NAME")%>?txtKeyword=<%=strKeyword%>&Page=<%=TotalPage%>">Last >></a>
182. <% End IF%>
183. <br>
184. Go to
185. <% For intID = 1 To TotalPage%>
186. <% if intID = Cint(PageNo) Then%>
187. <b><%=intID%></b>
188. <%Else%>
189. <a href="<%Request.ServerVariables("SCRIPT_NAME")%>?txtKeyword=<%=strKeyword%>&Page=<%=intID%>"><%=intID%></a>
190. <%End IF%>
191. <%Next%>
192. <%
193. objRec.Close()
194. Conn.Close()
195. Set objRec = Nothing
196. Set Conn = Nothing
197.
198. End IF
199. End IF
200.%>
201.</form>
202.</body>
203.</html>
