When it comes to handling datetime on SQL server it can get quite error prone when you are dealing with different regions and end up getting an out or range date exception if you are trying to add a date that is in EU date format to a server that is expecting a US date format.
Rather than having to worry about generating the string for the timestamp you can make SQL Server generate the timestamp for the datetime field automatically when you insert the row. This can be done by setting the default value for the field to a method called “getdate()”.
If you are using SQL Server Management Studio you can set the default value of datetime to the current time using the following. Make sure you set “Allow Nulls” to no and then add the method to the default value field.
SQL server default date time
When using Sql Server to insert records into a DB its a common requirement to be able to pull back the auto incremented Id that was generated for this row. Most solutions for this i have seen involve making a second query to the database in and along the lines of “SELECT @@IDENTITY”, but this method seems quite wasteful. It seems like a simple request that the database would return the value automatically without you having to ask for it with a second wasteful query. Well, it turns out that it’s incredibly simple to do the auto increment ID from SQL Server after an insert is performed.
The auto incremented value can be easily returned by using the OUTPUT Inserted.<auto id col name>. To put this in a real life example, the following query will insert a user into the Users database and will return the auto ID. In this case there is a column called “id” and this is what i am asking the DB to return in the query.
INSERT INTO Users(name, age) OUTPUT Inserted.id VALUES ('Tom', '50');
Using this query from whatever language you use will make the DB return the auto incremented ID from an SQL Server database. Here is an example of how you would use the above query in C# to get the auto incremented ID back from the database.
using(OleDbCommand cmd = new OleDbCommand("INSERT INTO Users(name, age) OUTPUT Inserted.id VALUES ('Tom', '50')"))
cmd.Connection = myCon;
int scheduleid = (int)cmd.ExecuteScalar();
Most standard PDO tutorials from around the web are going to demonstrate using a MySQL database with apache as a web host. The PDO connection string for this setup is not going to work if you want to connect to a SQL Server database using Microsofts IIS server.
The first step is to make sure PHP is setup to connect to SQL Server. PHP is going to use the sqlsrv driver on IIS in order to connect to SQL Server. This will probably be installed already, but to make sure you will have to go to your php.ini file. If you are unsure where this file is located you can use phpInfo() to find out the file path of the php.ini file.
You need to open the file and check for the following line. If its commented out, uncomment it. If its missing then add it. If it’s not present then there is a chance that the driver is not installed at all, if so then you will need to install it.
If you don’t have the driver installed, you can get it here.
Once you are all setup with the driver and php is good to go, restart IIS to make sure that the changes with the php.ini are picked up by IIS.
When making a query the code stays the same, but the PDO connection string is going to be different. Use the following format and add the connection details for your server into the string.
$db = new PDO ("sqlsrv:server=localhost;database=Database","username","password");
This should be all you need to connect to an SQL Server database using PHP with IIS web host.