SW Dev. Notes: 2017

Sunday 12 November 2017

SQL Update Table From Table

update ORIGINAL
set Field=wt.Field
from WHRETable wt
where ORIGINAL.PrimaryField= wt.PrimaryField

Tuesday 31 October 2017

How to search in sql for arabic names

When you create table contains name and you want to search about 'إبراهيم', or 'أحمد', no result will return,
to overcome this problem create another field and update it by no Hamza as the following, search in the new field and the original field

update StudentInfo
set FullNameWithoutHamza= REPLACE(REPLACE(FullName,N'إ',N'ا'),N'أ',N'ا')

In select
where FullName like N'%'+@StudentName+'%' or FullNameWithoutHamza like N'%'+@StudentName+'%'

Wednesday 25 October 2017

asp connect to sql and access

<html dir="rtl">
<head runat="server">
<meta charset="utf-8" />
<title>المعادلة والتصديق | الرئيسية
</title>


<link rel="stylesheet" href="resources/css/reset.css" type="text/css" />

<link rel="stylesheet" href="resources/css/style.css" type="text/css" />

<link rel="stylesheet" href="resources/css/invalid.css" type="text/css" />

</head>
<body>

<h1 id="sidebar-title"><a href="#">Simpla Admin</a></h1>

<a href="#">
<img id="logo" src="resources/images/logo.png" alt="Simpla Admin logo" /></a>

<div id="profile-links">

</div>

</div>
</div>

</div>

<h2>برنامج المعادلة والتصديق</h2>
<p id="page-intro">ابحث باستخدام الاسم</p>

<ul class="content-box-tabs">
<li><a href="#tab1" class="default-tab" style="font-size: 20pt;">النتائج
</a></li>
</ul>

</div>

<div class="tab-content default-tab" id="tab1">
<table border="1">
<tr>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$seat_id')">رقم الجلوس</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$f_name')">الاسم الأول</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$s_name')">اسم الأب</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$m_name')">اسم الجد</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$l_name')">العائلة</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$branch')">فرع التوجيهي</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$total')">المجموع</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$avg')">المعدل</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$school')">المدرسة</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$place')">السكن</a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$year')">العام </a></th>
<th scope="col"><a href="javascript:__doPostBack('GridView1','Sort$type')">قاعدة البيانات </a></th>
</tr>
<%
dim txt_fName, txt_sName, txt_gfName, txt_lName,selectSQL
txt_fName=request.querystring("txt_fName")
txt_sName=request.querystring("txt_sName")
txt_gfName=request.querystring("txt_gfName")
txt_lName=request.querystring("txt_lName")

if txt_fName <> "" or txt_sName <> "" or txt_gfName <> "" or txt_lName <> "" then
if txt_fName ="" then
txt_fName ="'%'"
else
txt_fName ="'%"+txt_fName+"%'"
end if
if txt_sName ="" then
txt_sName ="'%'"
else
txt_sName ="'%"+txt_sName+"%'"
end if
if txt_gfName ="" then
txt_gfName ="'%'"
else
txt_gfName ="'%"+txt_gfName+"%'"
end if
if txt_lName ="" then
txt_lName ="'%'"
else
txt_lName ="'%"+txt_lName+"%'"
end if
selectSQL="select seat_id, f_name, s_name, m_name, l_name, branch, total, avg, school, place, year FROM students where f_name like "+txt_fName+" and s_name like "+txt_sName+" and m_name like "+txt_gfName+" and l_name like "+txt_lName+""

Dim ConnectionString
Dim ADO
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/tawjihi.mdb")
Set ADO=Server.CreateObject("ADODB.Connection")
ADO.ConnectionString =ConnectionString
ADO.Open
set rs=ADO.execute(selectSQL)
do while not rs.eof
%>
<tr>
<td>
<%
response.write(rs("seat_id"))
%>
</td>
<td>
<%
response.write(rs("f_name"))
%>
</td>
<td>
<%
response.write(rs("s_name"))
%>
</td>
<td>
<%
response.write(rs("m_name"))
%>
</td>
<td>
<%
response.write(rs("l_name"))
%>
</td>
<td>
<%
response.write(rs("branch"))
%>
</td>
<td>
<%
response.write(rs("total"))
%>
</td>
<td>
<%
response.write(rs("avg"))
%>
</td>
<td>
<%
response.write(rs("school"))
%>
</td>
<td>
<%
response.write(rs("place"))
%>
</td>
<td>
<%
response.write(rs("year"))
%>
</td>
<td>Access DB
</td>
</tr>

<%
rs.movenext
loop
ADO.close

'SQL

selectSQL="select seat_id, f_name, s_name, m_name, l_name, branch, total, avg, school, place, year FROM students where f_name like N"+txt_fName+" and s_name like N"+txt_sName+" and m_name like N"+txt_gfName+" and l_name like N"+txt_lName+""

Dim ConnectionObj, Recordset,strConnectionString
strConnectionString= "Provider='sqloledb';Data Source='IHAB-PC'; User ID='tawjihi'; Password='tawjihi'; Initial Catalog='tawjihi';"
Set ConnectionObj = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
ConnectionObj.Open strConnectionString

'Open the recordset object executing the SQL statement and return records

set Recordset= ConnectionObj.Execute(selectSQL)

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
%>
<tr>
<td>
<%
response.write(Recordset("seat_id"))
%>
</td>
<td>
<%
response.write(Recordset("f_name"))
%>
</td>
<td>
<%
response.write(Recordset("s_name"))
%>
</td>
<td>
<%
response.write(Recordset("m_name"))
%>
</td>
<td>
<%
response.write(Recordset("l_name"))
%>
</td>
<td>
<%
response.write(Recordset("branch"))
%>
</td>
<td>
<%
response.write(Recordset("total"))
%>
</td>
<td>
<%
response.write(Recordset("avg"))
%>
</td>
<td>
<%
response.write(Recordset("school"))
%>
</td>
<td>
<%
response.write(Recordset("place"))
%>
</td>
<td>
<%
response.write(Recordset("year"))
%>
</td>
<td>SQL DB
</td>
</tr>

<%

Recordset.MoveNext
Loop
End If
Recordset.Close
Set Recordset=nothing
ConnectionObj.Close
Set ConnectionObj=nothing
end if
%>
</table>
</div>

</div>

</div>

<div class="clear"></div>

<div id="footer">
<small>

Copyright 2016
</small>
</div>

</form>
</div>

<style type="text/css">
body {
direction: rtl;
background: none;
color: black;
}

#sidebar {
right: 0;
left: auto;
background: #E8E8E8;
}

#main-content {
margin: 0 260px 0 30px;
}

#sidebar #logo {
width: 100%;
}

ul.shortcut-buttons-set li {
float: right;
}

#body-wrapper {
background: none;
}

ul.content-box-tabs li {
float: right;
}
</style>
</body>
</html>

Monday 27 February 2017

ASP Web Service

Sometimes you need to create a webservice .asmx, without use service reference and call this service via ajax request or other requests, you need to do the following

Right click on any folder you want to add the service to it and type the name of it.

After you hit OK, your code should locks like the following

namespace ...

{

///

/// Summary description for WebService1

///

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.ComponentModel.ToolboxItem(false)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

[System.Web.Script.Services.ScriptService]

public class WebService1 : System.Web.Services.WebService

{

[WebMethod(EnableSession = true)]// This to make this method accessible to public and access the web session in the link

[ScriptMethod(ResponseFormat = ResponseFormat.Json)]// to return the text as json

public string HelloWorld(string x1, int x2)

{

return "Hello World";

}

To call HelloWorld method from jquery use the following code snippet

var x1='xxx', x2=20;

$.ajax({

type: "POST",

url: "/WebService1.asmx/HelloWorld",

contentType: "application/json; charset=utf-8",

dataType: "json",

data: "{x1:'" + x1+ "', x2:'" + x2+ "'}",

beforeSend: function () {

$('#loadingImage').show();//to show a loading image while ajax call in progress

success: function (data) {

console.info(data.d);

complete: function () {

$('#loadingImage').hide();

error: function (XMLHttpRequest, result, errorThrown) {

console.info('error',errorThrown);

}

});