Random db record generating script

May 24, 2012 - Posted in Access, Classic ASP, SQL - Permalink

Generate random db record output. This is classic asp script. VB.NET version is coming soon


< % @ Language="VBScript" %>
< % Option Explicit %> 
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head runat="server">
    <title>
    <link href="Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <div class="page">
    <div class="main">
    <div style="width: 900px; text-align: right;">
    <input type="button" Value="Back to Home" 
    OnClick="window.location.href='http://my_website/home.aspx'" 
    class="button blue"/>
                
    <% 
    'declare your variables
    dim connection, recordset, sConnString, sql
    dim intRandomNumber, intTotalRecords, i
                
    'declare SQL statement that will query your database
     sql = "SELECT * FROM ob_feedback"
                
     'create ADO connection and recordset object
     Set connection = Server.CreateObject("ADODB.Connection")
    Set recordset = Server.CreateObject("ADODB.Recordset") 
                
   'define the connection string, specify database 
   ' driver and the location of database
   sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0; &_
    Data Source=" & Server.MapPath("App_Data\ob_db.accdb") 
                
   'Open the connection to the database
   connection.Open(sConnString)
                
   'Open the recordset object executing the SQL 
   recordset.Open sql, connection, 3, 1
   'count the number of records and hold this is the variable intTotalRecords
   intTotalRecords = recordset.RecordCount
   Randomize()
   intRandomNumber = Int(intTotalRecords * Rnd)
   'move to the random number returned
    recordset.Move intRandomNumber
   'open the table
   'header
    Response.Write("
Drawing a winner")
    'running a job
    'def var
    Dim job
    job = Request.QueryString("do")
	If job = "go" Then
	Response.write("<table style='border: gray 1px solid;' ><tr>")
        'loop through the total number of fields
        For i = 0 to recordset.Fields.Count - 1
        'write out the field value
        Response.write("<td>" & recordset(i) & "")
        Next
        'close the table
        response.write ""
        End If
        'close the recordset and connection objects and free up resources
        recordset.Close
        Set recordset=Nothing
        connection.close
         Set connection=Nothing
     %>
     <div style="min-height: 20px;"> 
     <div style="width: 900px; text-align: center;"> 
     <input type="button" Value="Generate"
     OnClick="window.location.href='http://my_website/drawing.asp?do=go'" 
     class="button blue"/>
     <div class="clear">
     <div class="footer">
       
</body>
</html>

Find missing record using query

April 20, 2012 - Posted in Access, MS SQL, SQL - Permalink

After running the query to update table records the record(s) is missing. Run this query to find the missing record.


SELECT Table1.column
FROM Table1
LEFT JOIN Table2 ON
Table1.column = Table2.Column
WHERE Table2.column is NULL;

* Replace Table1 and *2 with your table names

VB.NET – Login form – create user session

March 30, 2012 - Posted in Applications - Permalink

This post will guide you through designing the login form with SQL backend and creating a user session.

Learn more about new promotions!
login.aspx

login.aspx (markup code)

<%@ Page Title="" Language="VB" MasterPageFile="~/Default.Master" 
AutoEventWireup="false" CodeFile="login.aspx.vb" Inherits="_login" %>
<asp:Content ID="HeaderContent" runat="server" 
ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" 
ContentPlaceHolderID="MainContent" >

<table border="0" cellpadding="1" cellspacing="0">
   <tr>
      <td colspan="2">Login:</td>
   </tr>
   <tr>
      <td colspan="2>
     <br />
     <asp:label ID="ssError" runat="server"></asp:label></td>
     </tr>
     <tr>
      <td align="right">Username: </td>
      <td>
      <asp:textbox ID="usn" runat="server" ToolTip="username" 
      CssClass="loginBox" ></asp:textbox>
      <asp:requiredfieldvalidator ID="RVusn" runat="server"
      ErrorMessage="*" ControlToValidate="usn" >
      </asp:requiredfieldvalidator></td>
   </tr>
   <tr>
       <td align="right">Password: 
       <td>
       <asp:textbox ID="pwd" runat="server" TextMode="Password" 
       ToolTip="password" CssClass="loginBox" ></asp:textbox>
      <asp:requiredfieldvalidator ID="RVpwd" runat="server"
      ErrorMessage="*" ControlToValidate="pwd">
      </asp:requiredfieldvalidator></td>
   </tr>
   <tr>
      <td colspan="2"> </td>
   </tr>
   <tr>
      <td colspan="2" class="text">
      <asp:imagebutton ID="Login" runat="server" 
      ImageURL="/images/buttons/btn_signin.gif" />
      </td>
   </tr>
 </table>
</div>
</asp:Content>

login.aspx.vb (code behind)

Imports System.IO
Imports System.Data.OleDb
Imports System.Web.UI.WebControls.ImageButton
Imports System.Web.UI.WebControls.TextBox
Imports System.Web.SessionState
Imports System.Web.Security
Imports System.Web
Partial Class _ss_login
  Inherits System.Web.UI.Page
   Protected Sub Login_Click(ByVal sender As Object, _
     _ByVal e As System.Web.UI.ImageClickEventArgs)
              Handles Login.Click
     '/ defining variables/controls
      Dim usn As TextBox
      Dim pwd As TextBox

      'binding controls
       usn = Login.FindControl("usn")
       pwd = Login.FindControl("pwd")

       Dim user As String
       Dim key As String
       user = usn.Text.ToString()
       key = pwd.Text.ToString()
       
   'encrypt password
    Dim sha1Obj As _
    New System.Security.Cryptography.SHA1CryptoServiceProvider
     Dim bytesToHash() As _
       _Byte = System.Text.Encoding.ASCII.GetBytes(key)
    bytesToHash = sha1Obj.ComputeHash(bytesToHash)

    Dim keyCode As String = ""
    For Each b As Byte In bytesToHash
        keyCode += b.ToString("x2")
     Next

     '/ sql oledb connection * you can define your own sql connection
     Dim ConnDB As_
       _New OleDbConnection("Provider=sqloledb;Data Source=mySqlServer;
     Initial Catalog=mySqlDB;Persist Security Info=True;_
       _User ID=myUsr;Password=myPwd")
     Dim cmd As New OleDbCommand("SELECT * FROM ss_users WHERE 
                                (u_user_name = '" & usn.Text.ToString() & "')
                                AND
                                (u_password = '" & keyCode & "');", ConnDB)
     Dim reader As OleDbDataReader

     Try
       '/ open connection
       cmd.Connection.Open()
       reader = cmd.ExecuteReader()
       If reader.Read() Then
         'create sessions
          Session("Name") = reader(6).ToString()
          Session("ID") = reader(0).ToString()
          Session("LastLogIn") = reader(11).ToString()
          Response.Redirect("default.aspx")  'page of your choice
       Else
          ssError.Text = ("Error - please try again.")
       End If
       cmd.Connection.Close()
       Catch ex As Exception
          ssError.Text = "Error: " & ex.Message.ToString()
       End Try
    End Sub
End Class

Related posts:
End User Session

« Older Entries
Newer Entries »