How to Insert Value into an Identity Column

In database tables, normally identity columns are used as primary keys that automatically generates numeric values for every new inserted row.

For this identity (AutoNumber) column, you are not allow to insert your own value. So what if you really want to insert your own value into the column?

A very easy way to do this. I will show you how to do this.

Firstly, create a table Sample by using the script below into your database.

CREATE TABLE Sample (
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50) NOT NULL )
GO

Normally if we want to insert a new row into this table, you will have to only insert the value of Name, as script below:

INSERT INTO Sample(Name) VALUES ('July')

Now, we try to insert a value into the Id column.

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

When this query is executed, an error will be generated:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Sample' when IDENTITY_INSERT is set to OFF.

To insert this record into table without any error, you just need to enable the IDENTITY_INSERT. It should look like this:

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

But there are some points that you need to be aware of about IDENTITY_INSERT.

1.   Only 1 table can have the IDENTITY_INSERT property set to ON at a time. If you try to enable IDENTITY_INSERT to ON for a second table while a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server will return an error message.
2.    If the value that you have inserted is greater than the current identity seed in your table, the new inserted value will be set as the current identity seed.

For example, table Sample has its current identity value 1 and you want to insert a new row with the identity value 11. When you insert another new row, your identity value will start from 12, instead of 1.

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

INSERT INTO Sample(Name) VALUES ('August')

Result:

Id Name
11 July
12 August

Cheers.

How to Call Remote Web Services from Client Side JavaScript

Web Services is a collection of universally available functions, which allows different applications from different sources to share among each other without time-consuming custom coding by using the XML, SOAP, WSDL and UDDI open standards across the Internet. You can go to here for more detail explanation on it.

Recently, I have been doing some research into how to call a web service from client side JavaScript. ASP .NET AJAX enables you to call a web service from the browser by using client script, namely the page can call server-based methods without a postback and refreshing the whole web page.

Here, I will show you how to call Web Services within the same project and also how to call Web Services remotely.

Consuming a Local Web Service
Simple example of Web Service
I assume that you had installed the ASP .NET AJAX framework. So let us start with create a new ASP .NET AJAX-Enabled Web Application created.

I will firstly show you how to call a web service within the same project, so let’s add new Web Service called webService.asmx to the project (Right-click your project on your solution explorer | Add New Item | Select Web Service and name it | Click Add.).

Let us create a very simple web service that returns current date and time.

In order to make a web service accessible from script, the web service class must be qualified with the ScriptServiceAttribute. See the example below:

...
using System.Web.Script.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class WebService : System.Web.Services.WebService
{
  ...
  [WebMethod]
  public string GetCurrentDateTime() {
    string curTime = String.Format("Current datetime is {0}.", DateTime.Now);
    return curTime;
  }
}

And then save it.

Exposing Web Services to Client Script in .aspx Web Page
To enable a web service to be called from client script in your .aspx file, make sure that we have one instance of ScriptManager object.

Then add the Services collection to the ScriptManager object, reference the web service by adding asp:ServiceReference child element to the Services collection and sets its Path attribute to point to the web service. Below is how it is looks like:

<body>
  <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
      <Services>
      <asp:ServiceReference Path="WebService.asmx" />
      </Services>
    </asp:ScriptManager>
  </form>
</body>

Client-side Function, Button and Label
Next we need to add in a client-side function to call server-side web service, a button to trigger the web service, and a label to display the returned result. Below is the sample of the web page:

<html>
<head runat="server">
<title>Calling Web Services</title>
<script language="javascript" type="text/javascript">
function GetTime() {
  WebService.GetCurrentDateTime(ShowResult);
}

function ShowResult(result) {
  document.getElementById("lblResult").innerHTML = result.value;
}
</script>
</head>
<body>
  <form id="form1" runat="server">
    ...
    <asp:Label id="lblResult" runat="server">Result will be displayed here.</asp:Label>
    <br/>
    <button id="btn" name="btn" onclick="GetTime()">Get Time</button&gt;
    <br/>
  </form>
</body>
</html>

TIPS: If you want to call the JavaScript function from a separate .js file, copy the functions into the .js file, and the add in Scripts collection to the ScriptManager object, reference the JavaScript function by adding asp:ScriptReference child element to the Scripts collection and sets its Path attribute to point to the JavaScript, as below:

<Scripts>
<asp:ScriptReference Path="CallWebServiceMethods.js" />
</Scripts>

That’s it for consuming web service, which located in the same project.


Consuming Web Services Remotely
WebService Behavior (HTC file)
The first thing that you need to do is to create a separate ASP .NET Web Service application and copy the web service that we created as above into it. I will add another method here to show you how to call a web service and pass parameters to its method through JavaScript.

[WebMethod]
public string EchoYourWord(string yourword) {
  return "You have said that: " + yourword;
}

Save and run it. So now you got your web service ready to test.

WebService behavior enables us to make a method call to a remote web service using a scripted language. It is a reusable DHTML component that uses web services by handling the communication of the SOAP data packets between the browser and the web services.

The following are 3 important methods supported by the web service behavior:

• createUseOptions – Allows us to preserve authentication information across remote method invocations. Can be very useful when using SSL to communicate with the remote web service

• callService – Allows us to invoke the remote web service method asynchronously

• useService – Allows us to establish a friendly name for the web service that can be used while invoking the web service

You can download the HTC file at here. Copy into the same folder as your project.

How to use Web Service Behavior in .aspx Page
I will recommend you that to create another new .aspx page to test on consuming remote web service.

To invoke the GetCurrentDateTime() method, we have to attach the HTC file to a valid element, such as DIV or BODY.

<div id="service" style="BEHAVIOR:url(webservice.htc)" ></div>

Once this behavior is attached, we may need to add some code to the web page to enable use of WebService behavior and make calls to the web service.

Add in this JavaScript function, as below:

function Init() {
  // initialize and create a name "webService" for Web service
  // Note: the "service.use..." is the DIV or BODY element’s id as created just now.
  service.useService("http://localhost/My_WebServices/WebService.asmx?wsdl", "webService");
}

Next, we will need to invoke the useService method in the onload event to map the web service before any methods on the web service are invoked.

After that, we can add in another JavaScript function and use the callService method to invoke the remote web service method asynchronously.

Your web page should look like this when you are done:

<html>
<head runat="server">
<title>Calling Web Services</title>
<script language="javascript" type="text/javascript">
function Init() {
  // initialize and create a shortcut name "webService" for Web service
  // Note: the "service.use..." is the DIV or BODY element’s id as created just now.
  service.useService("http://localhost/My_WebServices/WebService.asmx?wsdl", "webService");
}

function GetTime() {
  // Note: the "service.webService..." is the shortcut name of your web service that you had initialize in the Init().
  service.webService.callService(ShowResult, "GetCurrentDateTime");
}

// sample of passing a parameter to the web service
var myword;
function Echo() {
  myword = document.form1.SayOutLoud.value;
  service.webService.callService(ShowResult, "EchoYourWord", myword);
}

function ShowResult(result) {
  document.getElementById("lblResult").innerHTML = result.value;
}
</script>
</head>
<body onload="Init()">
  <form id="form1" runat="server">
    <div id="service" style="BEHAVIOR:url(webservice.htc)" ></div>
    <asp:Label id="lblResult" runat="server">Result will be displayed here.</asp:Label>
    <br/><br/>
    Your words : <input type="text" name="SayOutLoud"/><br/><br/>
    <button id="btn" name="btn" onclick="GetTime()">Get Time</button><br />
    <button id="btn1" name="btn1" onclick="Echo()">Echo Your Word</button><br/>
    <br/>
  </form>
</body>
</html>

Note: Of course, if you are using a separate .js file, you still need to add a reference in a ScriptManager object, as I had mentioned above.

That’s it for calling a remote web service. Any question please feels free to ask.

Cheers!

Displaying Date and time in VB Script with ASP Classic

To display the Date and Time in your ASP page with certain format is very simple indeed. Take a look at the examples as below:

1. <%=time()%>

   - Get current time

   - e.g. 9:52:30 AM

   
   
2. <%=date()%>

   - Get current date

   - e.g. 7/11/2008

   
   
3. <%=now()%>

   - Get current date and time

   - e.g. 7/11/2008 9:54:03 AM

   
   
4. <%=FormatDateTime(now,0)%>

   - e.g. 7/11/2008 9:55:41 AM

   
   
5. <%=FormatDateTime(now,1)%>

   - e.g. Friday, July 11, 2008

   
   
6. <%=FormatDateTime(now,2)%>

   - e.g. 7/11/2008

   
   
7. <%=FormatDateTime(now,3)%>

   - e.g. 9:57:49 AM

   
   
8. <%=FormatDateTime(now,4)%>

   - e.g. 09:58

   
   
9. <%=WeekDay(now)%>

   - Day of the week

   - e.g. 6

   
   
10. <%=WeekDayName(WeekDay(now))%>

    - e.g. Friday

    
    
11. <%=Day(date)%>

    - Day of the month

    - e.g. 11

    
    
12. <%=Month(date)%>

    - Month of the year

    - e.g. 7

    
    
13. <%=MonthName(Month(date))%>

    - e.g. July

    
    
14. <%=Year(date)%>

    - Current year

    - e.g. 2008

    
    
15. <%=Right(Year(date),2)%>

    - Current year

    e.g. 08

    
    
16. <%=Hour(now)%>

    - Hour part

    - e.g. 10

    
    
17. <%=Minute(now)%>

    - Minute part

    - e.g. 11

    
    
18. <%=Second(now)%>

    - Second part

    - e.g. 2

Hope you will find this helpful.