Noob Flash

Archive for the ‘SQL’ Category

The Problem

I recently formatted my hard drive and reinstalled Windows 7.  As a developer, I use Microsoft Visual Studio 2010 RC as well as Microsoft SQL Server 2008.  I installed VS 2010 RC first, then SQL Server 2008.  Ever since I have had one error after another when using SQL Server.  The most obvious error was when opening SQL Server.  I would get a dialog box that said:

SQL Error

Could not load file or assembly ‘Microsoft.SqlServer.Diagnostics.STrace, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified. (Microsoft.SqlServer.SqlTDiagM)

This error did not prevent me from using SQL, but it seems that other problems might be related to it.

The Solution

The fix for this error (at least in my instance) was to uninstall Microsoft Visual Studio 2010 Beta or RC completely, then reinstall it.  It’s a pain, but it does seem to take care of the problem.

A request has been made for me to cover how to create a SQL If / Else statement. In this example, I am declaring a variable (@myVar), and using a select statement to set it equal to the value of table.columnName. Once you have a value, you can use that value in your If / Else statement. This example is checking to see if @myVar is greater than 10. If so, then the first SQL select statement is executed. If @myVar is not greater than 10, then the second SQL statement is executed.

DECLARE @myVar AS INTEGER

SELECT
@myVar = table.columnName
FROM table
WHERE columnName2 = condition

IF ( @myVar > 10 )
BEGIN
   SELECT *
   FROM table
   WHERE columnName = @myVar
END
ELSE
BEGIN
   SELECT *
   FROM table
   WHERE columnName < @myVar
END

Searching for some characters in MySQL / SQL can be a challenge, especially when those characters can be used in the MySQL / SQL query itself. The following code is the correct way to search for an apostrophe in MySQL / SQL data.

SELECT * FROM tableName WHERE columnName LIKE '%''%'

If you simply type one apostraphe inside the LIKE statement, it will end your query and cause an error. Using a double apostrophe actually equals one.

In MySQL and SQL, you can use the following syntax to insert an item into a database table. The example below assumes that the table you are inserting data into has three columns. The values in the query must exist in the same order as columns in the table.

INSERT INTO tableName
VALUES (firstValue, secondValue, thirdValue)

You can use the following code to connect to a MySQL database with PHP.

<?php
$host = "localhost"; //Address or IP of MySQL Database
$username="username"; //Username for MySQL Database
$password="password"; //Password for MySQL Database
$database="dbName"; //MySQL Database Name
mysql_connect($host,$username,$password);
mysql_select_db($database) or die( "Failed to select database");
?>