CREATE TABLE [dbo].[tblBorrowers](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](80) NULL,
[address] [nvarchar](80) NULL,
[phone] [nvarchar](30) NULL,
[contact] [nvarchar](30) NULL,
[email] [nvarchar](40) NULL,
[website] [nvarchar](255) NULL,
[tic_borrower] [nvarchar](20) NULL,
CONSTRAINT [PK_tblBorrowers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[Sp_GetBorrowsers]
(
@term NVARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT name label, name value, *
FROM tblBorrowers
WHERE [name] LIKE '%' + @term + '%'
ORDER BY [name];
END
GO
SET IDENTITY_INSERT [dbo].[tblBorrowers] ON
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (1, N'Name 1', N'Address 1', N'Phone 1', N'Contact 1', N'Email 1', N'Website 1', N'A')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (2, N'Name 2', N'Address 2', N'Phone 2', N'Contact 2', N'Email 2', N'Website 2', N'B')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (3, N'Name 3', N'Address 3', N'Phone 3', N'Contact 3', N'Email 3', N'Website 3', N'C')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (4, N'Tan', N'dummy'' addr'' xx', N'phone 4', N'91234567', N'test@here.com', N'web 4', N'D')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (5, N'Andy', N'du<b>mmy " \ > addr DGs dd', N'phone 5', N'61234567', N'gtst@h.com', N'web 5', N'A')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (6, N'David', N'dummy addr dgo', N'phone 6', N'11111111', N'em@here.com', N'web 6', N'C')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (7, N'Dan', N'dummy addr Hds', N'Phone 7', N'22222222', N'sgd@h.com', N'web 7', N'B')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (8, N'Ant', N'dummy addr sdds', N'Phone 8', N'33333333', N'dgd@GS', N'web 8', N'C')
GO
SET IDENTITY_INSERT [dbo].[tblBorrowers] OFF
GO
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/json";
string term = context.Request.Form["term"];
try
{
if (term == null)
{
context.Response.Write("Invalid Parameter(s)");
}
else
{
//Trim what to search
term = term.Trim();
//Read connection string
String connstring = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
//Establish connection and grab data
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("Sp_GetBorrowsers", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
cmd.Parameters.Add("@term", System.Data.SqlDbType.NVarChar).Value = term;
using (SqlDataReader r = cmd.ExecuteReader())
{
//Convert data reader to json
var obj = Serialize(r);
string json = JsonConvert.SerializeObject(obj, Formatting.Indented);
context.Response.Write(json);
}
}
}
}
}
catch (Exception)
{
context.Response.Write("Error Processing Request");
}
}
public IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
{
var results = new List<Dictionary<string, object>>();
var cols = new List<string>();
for (var i = 0; i < reader.FieldCount; i++)
cols.Add(reader.GetName(i));
while (reader.Read())
results.Add(SerializeRow(cols, reader));
return results;
}
private Dictionary<string, object> SerializeRow(IEnumerable<string> cols,
SqlDataReader reader)
{
var result = new Dictionary<string, object>();
foreach (var col in cols)
result.Add(col, reader[col]);
return result;
}
<table>
<tr>
<th colspan="3" class="auto-style4">Borrower Detail</th>
</tr>
<tr>
<td class="auto-style3">
<label for="name">Name</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<input id="name"></td>
</tr>
<tr>
<td class="auto-style3">
<label for="address">Address</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<input id="address"></td>
</tr>
<tr>
<td class="auto-style3">
<label for="phone">Phone</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<input id="phone"></td>
</tr>
<tr>
<td class="auto-style3">
<label for="contact">Contact</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<input id="contact"></td>
</tr>
<tr>
<td class="auto-style3">
<label for="email">Email</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<input id="email"></td>
</tr>
<tr>
<td class="auto-style3">
<label for="website">Website</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<input id="website"></td>
</tr>
<tr>
<td class="auto-style3">
<label for="tic_borrower">TIC Borrower</label></td>
<td class="auto-style2">:</td>
<td class="auto-style1">
<select id="tic_borrower">
<option value=""></option>
<option value="A">A</option>
<option value="B">B</option>
<option value="C">C</option>
<option value="D">D</option>
</select></td>
</tr>
<tr>
<td class="auto-style3"> </td>
<td class="auto-style2"> </td>
<td class="auto-style1">
<input id="reset" type="reset" value="reset" /></td>
</tr>
</table>
and then we need to include some style sheets and JavaScript links in the head section:
<head runat="server">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Test</title>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<style>
.ui-autocomplete-loading {
background: url(http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/themes/smoothness/images/ui-anim_basic_16x16.gif) no-repeat right center
}
table, th, td {
border: 1px solid black;
width: 500px;
height: 50px;
}
.auto-style1 {
width: 100px;
}
.auto-style2 {
width: 16px;
}
.auto-style3 {
width: 136px;
}
.auto-style4 {
text-align: left;
}
</style>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$(function () {
function populate(item) {
$('#address').val(item.address);
$('#phone').val(item.phone);
$('#contact').val(item.contact);
$('#email').val(item.email);
$('#website').val(item.website);
$('#tic_borrower').val(item.tic_borrower);
}
$("#name").autocomplete({
source: function (request, response) {
$.ajax({
url: "posting.ashx",
method: "POST",
dataType: "json",
data: {
term: request.term
},
success: function (data) {
response(data);
}
});
},
minLength: 2,
select: function (event, ui) {
populate(ui.item);
}
});
});
$(document).ready(function () {
$("#name").focus();
$("#reset").click(function () {
$("#name").focus();
});
});
</script>
</head>
.ui-autocomplete-loading {
background: url(http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/themes/smoothness/images/ui-anim_basic_16x16.gif) no-repeat right center
}
We use it so that it will generate a small loading icon while the data is loading.
$("#name").autocomplete({
source: function (request, response) {
$.ajax({
url: "posting.ashx",
method: "POST",
dataType: "json",
data: {
term: request.term
},
success: function (data) {
response(data);
}
});
},
minLength: 2,
select: function (event, ui) {
populate(ui.item);
}
});
[
{
"label": "Name 1",
"value": "Name 1",
"id": 1,
"name": "Name 1",
"address": "Address 1",
"phone": "Phone 1",
"contact": "Contact 1",
"email": "Email 1",
"website": "Website 1",
"tic_borrower": "A"
},
{
"label": "Name 2",
"value": "Name 2",
"id": 2,
"name": "Name 2",
"address": "Address 2",
"phone": "Phone 2",
"contact": "Contact 2",
"email": "Email 2",
"website": "Website 2",
"tic_borrower": "B"
},
{
"label": "Name 3",
"value": "Name 3",
"id": 3,
"name": "Name 3",
"address": "Address 3",
"phone": "Phone 3",
"contact": "Contact 3",
"email": "Email 3",
"website": "Website 3",
"tic_borrower": "C"
}
]
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
https://www.experts-exchange.com/questions/29209841/change-sa-password-for-SQL-job-created-in-maintenance-paln.html#questionAdd