Category Archives: Programming

Linq Update FK – Operation is not valid due to the current state of the object

sql serverTo update the entity in question you need to get a new entity for it from the context rather than setting it directly.

Wrong Way

OrderRecord.customerId = 105;

Throws an error!
“Operation is not valid due to the current state of the object”

Solution

OrderRecord.tblCustomer = db.tblCustomers.Single(t => t.customerID == 105);

Returns no error!

MySQL Import Data From EXCEL (csv file)

mysql logo

1. Save as excel document in CSV format. (found as CSV Comma Delimited file)

2. Connect to Mysql through command line
Start Cmd
cd c:\mysql\bin\ (or wherever the mysql directory is installed…)

3. Connect to MySQL command console
use

mysql -u root

to connect as root user to be able to do changes to your db

4. Remember to Save the CSV File in the mysql\bin directory

5. Enter the following commands
SELECT THE DATABASE TO USE

use yourdbname;

ENTER THE INSERT COMMAND

LOAD DATA LOCAL INFILE '/yourCSVfilename.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3); 

For Greek or non-unicode Remember 3 Things
1.  mysql> set character_set_database =utf8;
2. CSV File saved as UTF8 encoding
3. database Table character set to greek_unicode_ci;

Vista 4gb Problem – Solution


First of all i d like to remind that In most new pc configurations Vista cant be installed in 4gb systems. You have to uninstal 2gb and then when Windows setup is complete you can use the other 2gb.

How to Enable More Than 3.5 GB of Memory in 32-Bit Windows Vista
You’re probably wondering why I picked a number as horrible as 3.5GB? Why not 4GB? Well, there’s a really technical explanation for this but to make a long explanation short, 32-bit operating systems can only handle up to 4GB of memory. In fact, this number may vary but I stuck with 3.5 GB. Along with the RAM, you also have other memory-mapped devices such as your video cards. The memory used counts towards the 4GB virtual memory address capacity of a 32-bit operating system. But actually, the world does not end at 4,096 megabytes for 32-bit Windows Vista users anymore because the Arsgeek is here to save the day.

If you’ve accidentally bought 4GB of RAM, this may be a good idea. And I say may because many 32-bit operating systems begin to lose efficiency as they surpass the 4GB mark. For that reason, if you have less than 3GB RAM, it’s not necessarily the best idea to go out and buy some more RAM. If you want to utilize more RAM (between 8GB and 128GB), you can always switch to 64-bit Windows Vista. Alright, enough babbling. Let’s get started.

Steps:

1) Access cmd: Click on the Start Pearl > type cmd in the Search Bar > and press Ctrl + Shift + Enter (this allows you to run cmd in administrative mode)

2) Type BCDEdit /set PAE forceenable

“BCDEdit is a boot configuration editor for the command line. Using the above command you’ve just enabled Physical Address Extension (PAE) which can address memory larger than 4 GB. ” – Arsgeek

Source: Arsgeek

MySQL Command List


This is a list of the most common used mySQL Commands

General Commands

USE database_name
Change to this database. You need to change to some database when you first connect to MySQL.
SHOW DATABASES
Lists all MySQL databases on the system.
SHOW TABLES [FROM database_name]
Lists all tables from the current database or from the database given in the command.
DESCRIBE table_name
SHOW FIELDS FROM table_name
SHOW COLUMNS FROM table_name
These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name
Lists all indexes from this tables.
SET PASSWORD=PASSWORD(‘new_password’)
Allows the user to set his/her own password.
Table Commands
CREATE TABLE table_name (create_clause1, create_clause2, …)
Creates a table with columns as indicated in the create clauses.
create_clause
column name followed by column type, followed optionally by modifiers. For example, “gene_id INT AUTO_INCREMENT PRIMARY KEY” (without the quotes) creates a column of type integer with the modifiers described below.
create_clause modifiers
  • AUTO_INCREMENT : each data record is assigned the next sequential number when it is given a NULL value.
  • PRIMARY KEY : Items in this column have unique names, and the table is indexed automatically based on this column. One column must be the PRIMARY KEY, and only one column may be the PRIMARY KEY. This column should also be NOT NULL.
  • NOT NULL : No NULL values are allowed in this column: a NULL generates an error message as the data is inserted into the table.
  • DEFAULT value : If a NULL value is used in the data for this column, the default value is entered instead.
DROP TABLE table_name
Removes the table from the database. Permanently! So be careful with this command!
ALTER TABLE table_name ADD (create_clause1, create_clause2, …)
Adds the listed columns to the table.
ALTER TABLE table_name DROP column_name
Drops the listed columns from the table.
ALTER TABLE table_name MODIFY create_clause
Changes the type or modifiers to a column. Using MODIFY means that the column keeps the same name even though its type is altered. MySQL attempts to convert the data to match the new type: this can cause problems.
ALTER TABLE table_name CHANGE column_name create_clause
Changes the name and type or modifiers of a column. Using CHANGE (instead of MODIFY) implies that the column is getting a new name.
ALTER TABLE table_name ADD INDEX [index_name] (column_name1, column_name2, …)
CREATE INDEX index_name ON table_name (column_name1, column_name2, …)
Adds an index to this table, based on the listed columns. Note that the order of the columns is important, because additional indexes are created from all subsets of the listed columns reading from left to write. The index name is optional if you use ALTER TABLE, but it is necesary if you use CREATE INDEX. Rarely is the name of an index useful (in my experience).
Data Commands
INSERT [INTO] table_name VALUES (value1, value2, …)
Insert a complete row of data, giving a value (or NULL) for every column in the proper order.
INSERT [INTO] table_name (column_name1, column_name2, …) VALUES (value1, value2, …)
INSERT [INTO] table_name SET column_name1=value1, column_name2=value2, …
Insert data into the listed columns only. Alternate forms, with the SET form showing column assignments more explicitly.
INSERT [INTO] table_name (column_name1, column_name2, …) SELECT list_of_fields_from_another_table FROM other_table_name WHERE where_clause
Inserts the data resulting from a SELECT statement into the listed columns. Be sure the number of items taken from the old table match the number of columns they are put into!
DELETE FROM table_name WHERE where_clause
Delete rows that meet the conditions of the where_clause. If the WHERE statement is omitted, the table is emptied, although its structure remains intact.
UPDATE table_name SET column_name1=value1, column_name2=value2, … [WHERE where_clause]
Alters the data within a column based on the conditions in the where_clause.
LOAD DATA LOCAL INFILE ‘path to external file’ INTO TABLE table_name
Loads data from the listed file into the table. The default assumption is that fields in the file are separated by tabs, and each data record is separated from the others by a newline. It also assumes that nothing is quoted: quote marks are considered to be part of the data. Also, it assumes that the number of data fields matches the number of table columns. Columns that are AUTO_INCREMENT should have NULL as their value in the file.
LOAD DATA LOCAL INFILE ‘path to external file’ [FIELDS TERMINATED BY ‘termination_character’] [FIELDS ENCLOSED BY ‘quoting character’] [LINES TERMINATED BY ‘line termination character’] FROM table_name
Loads data from the listed file into the table, using the field termination character listed (default is tab \t), and/or the listed quoting character (default is nothing), and/or the listed line termination chacracter (default is a newline \n).
SELECT column_name1, column_name2, … INTO OUTFILE ‘path to external file’ [FIELDS TERMINATED BY ‘termination_character’] [FIELDS ENCLOSED BY ‘quoting character’] [LINES TERMINATED BY ‘line termination character’] FROM table_name [WHERE where_clause]
Allows you to move data from a table into an external file. The field and line termination clauses are the same as for LOAD above. Several tricky features:

  1. Note the positions of the table_name and where_clause, after the external file is given.
  2. You must use a complete path, not just a file name. Otherwise MySQL attempts to write to the directory where the database is stored, where you don’t have permission to write.
  3. The user who is writing the file is ‘mysql’, not you! This means that user ‘mysql’ needs permission to write to the directory you specify. The best way to do that is to creat a new directory under your home directory, then change the directory’s permission to 777, then write to it. For example: mkdir mysql_output, chmod 777 mysql_output.

Privilege Commands
Most of the commands below require MySQL root access
GRANT USAGE ON *.* TO user_name@localhost [IDENTIFIED BY ‘password’]
Creates a new user on MySQL, with no rights to do anything. The IDENTIFED BY clause creates or changes the MySQL password, which is not necessarily the same as the user’s system password. The @localhost after the user name allows usage on the local system, which is usually what we do; leaving this off allows the user to access the database from another system. User name NOT in quotes.
GRANT SELECT ON *.* TO user_name@localhost
In general, unless data is supposed to be kept private, all users should be able to view it. A debatable point, and most databases will only grant SELECT privileges on particular databases. There is no way to grant privileges on all databses EXCEPT specifically enumerated ones.
GRANT ALL ON database_name.* TO user_name@localhost
Grants permissions on all tables for a specific database (database_name.*) to a user. Permissions are for: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE.
FLUSH PRIVILEGES
Needed to get updated privileges to work immediately. You need RELOAD privileges to get this to work.
SET PASSWORD=PASSWORD(‘new_password’)
Allows the user to set his/her own password.
REVOKE ALL ON [database_name.]* FROM user_name@localhost
Revokes all permissions for the user, but leaves the user in the MySQL database. This can be done for all databases using “ON *”, or for all tables within a specific databse, using “ON database_name.*”.
DELETE FROM mysql.user WHERE user=’user_name@localhost’
Removes the user from the database, which revokes all privileges. Note that the user name is in quotes here.
UPDATE mysql.user SET password=PASSWORD(‘my_password’) WHERE user=’user_name’
Sets the user’s password. The PASSWORD function encrypts it; otherwise it will be in plain text.
SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user
A good view of all users and their approximate privileges. If there is a password, it will by an encrytped string; if not, this field is blank. Select is a very general privlege; insert allows table manipulation within a database; shutdown allows major system changes, and should only be usable by root; the ability to grant permissions is separate from the others.
SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db
View permissions for individual databases.

Ajax Autocomplete Tutorial

This a solution i used in many autocomplete input fields. I have implemented it to php and C# web application.
Dont be scared to use it, its really simple to use. The tutorial is fully explanatory.

Part 1: Auto Complete

Start by creating a new PHP page and placing the agent.php (Ajax Agent) file in the same directory. Open your new PHP page in an editor and start hacking away.

First create the HTML Form controls we will be using:
txtArtists = TextBox to type the ArtistName in.
matches = A SelectBox with an onclick event which will be used to send the ArtistName to the AlbumSearch method we will be creating.
txtArtistID = Hidden to hold the selected ArtistName (js cannot see the select box because it is hidden?)
htmlOutput = An empty DIV when we will be creating a list of Albums
htmlOutputTracks = An empty DIV when we will be creating a list of Tracks

<form>
<input name=“artistName” id=“artistName” size=“20″ type=“text”
onkeyup=“GetArtist();return false;” autocomplete=“off”>
<br>
<select id=“matches” style=“VISIBILITY: hidden”
onclick=“MatchSelected(this);” ></select>
<hr>
<div id=“htmlOutput”></div>
<hr>
<div id=“htmlOutputTracks”></div>
</form>

Now at the top of the page add a new PHP script tag with an include for agent.php and a new $agent object. Any other PHP code that we must be placed before (above) this code.

/* my functions go here
and
here
*/
include_once(“agent.php”);
$agent->();

Next we’ll add the first function that search for Artists by name.
GetArtist function works by taking a string parameter and uses it to search for Artists who’s name begin with it using a Regular Expression. The list of Artists are stored in an Array array and we will use a foreach loop to search the Artis Name. If we find a match we add it to a second $results array and them move on to the next item in the array. Once we have reached the end of the $Artist Array we re-sort the $results Array. Lastly we only want to return a list of Artist Name so we’ll use the array_values($results) function.

function GetArtist( $text ){
include(“dbconn.inc.php”);
$strSQL = “SELECT * FROM artists WHERE artist_name LIKE ‘$text%’”;
$db= mysql_connect($dbHost, $dbUser,$dbPwd);
mysql_select_db($dbName,$db);
$result = mysql_query($strSQL,$db);
$num = mysql_num_rows($result);
$listArray = array();
$i=0;
while ($i<$num)
{
$artist_name = mysql_result($result,$i,“artist_name”);
$listArray&#91;$i&#93; = $artist_name;
$i++;
}
asort( $listArray );
mysql_close($db);
return $listArray;
}
&#91;/sourcecode&#93;
So far we have a form and a PHP function now we need to wire up some ajax to make this work.

There are some visibility issues with JS and the HTML Controls so the block needs to be below the form elements.

We need to create 2 JS functions to get this to work.The first function GetArtist is what we will call from our OnKey Event iun the search text box and the second function is the Callback method we will send our search results to.

In the GetArtist function we are creating a temp variable to get the letters from the search box and then we are using the agent.call function do define the PHP function, the JS Callback and the parameters we are sending.

&#91;sourcecode lang="C"&#93;
var matchList = document.getElementById(“matches”);
function GetArtist() {
var artistName = document.getElementById(‘artistName’).value;
agent.call(”,‘GetArtist’,‘GetArtist_Callback’,artistName);
}
&#91;/sourcecode&#93;
In the GetArtist_Callback we are setting the Select Box to visible and then giving it a display size which is equal to the number of items returned from the search. Then we loop over the items and add them to the select box.


&#91;sourcecode lang="C"&#93;
function GetArtist_Callback(obj) {
matchList.style.visibility = “visible”;
matchList.options.length = 0; //reset the states dropdown
matchList.size = obj.length;
for (var i = 0; i < obj.length; i++)
{
matchList.options&#91;matchList.options.length&#93; =new Option(obj&#91;i&#93;);
}
}
&#91;/sourcecode&#93;


And lastly we have the MatchSelected JS function which is called by the OnClick event of the SelectBox:
&#91;sourcecode lang="C"&#93;
function MatchSelected(matches) {
var artistName = document.getElementById(“artistName”);
artistName.value = matches.options&#91;matches.selectedIndex&#93;.text;
GetAlbumByArtist(artistName.value);
}
&#91;/sourcecode&#93;

We will cover this function in the next section but for now just know that it invokes the Ajax function that gets a list of Albums by the selected Artist.

If all has gone well you should be able to start typing in a name and some results should show as a select box. Once you have this part working we will move on to the second function of GetAlbumsByArtist.


<h3>Part 2: Posting Back</h3>
This next part is some very cool stuff. What we are going to do is select an item from the SelectBox which will return a list of Albums by the selected Artist. Since the list of Albums is being held in a server-side PHP Array getting the data back requires a round-trip to the server. In any normal case this is done in a Form Post which casues a page refresh but we are going to use Ajax to fire-off the server-side request for the data and avoid refreshing the page all together. The Get Albums task is made up of 2 parts: a server-side PHP function that searchs for the Albums and a client-side Ajax function that invokes the server-side function and hadles the response.The PHP function GetAlbumByArtist works like the GetArtist PHP function but instead of a regex we’ll just use a string comparison usiug the Artist Name.


function GetAlbumByArtist( $text )
{
include("dbconn.inc.php");
$strSQL = "SELECT albums.album_name FROM albums INNER JOIN artists ON 
albums.artist_id = artists.artist_id where artists.artist_name = '$text'";
$db = mysql_connect($dbHost, $dbUser,$dbPwd);
mysql_select_db($dbName,$db);
$result = mysql_query($strSQL,$db);
$num = mysql_num_rows($result);
$listArray = array();
$i=0;
while ($i<$num)
{
$listArray&#91;$i&#93; = mysql_result($result,$i,"album_name");
$i++;
}
asort( $listArray );
mysql_close($db);
return array_values($listArray);
}
&#91;/sourcecode&#93;

In the FORM we created a SelectBox that has an OnClick(“MatchSelected(this)”) function defined. The MatchSelected function calls the GetAlbumByArtist and passes it the selected Artist Name. GetAlbumByArtist then invokes the agent.call method which has the Server-side PHP function defined, the client-side callback handler and the parameters we want to send to the PHP function. The agent.call has an optional first parameter of URL which can be used if your PHP code in in a seprate file.

For example:
&#91;sourcecode lang="c"&#93;
agent.call('musicSearch.php','GetAlbumByArtist','GetAlbumByArtist_Callback',val);&#91;/sourcecode&#93;

If you use a seperate PHP file you’ll need to be sure to add the following lines to the end of the file:
&#91;sourcecode lang="c"&#93;
<?php
include_once("agent.php");
$agent->init();
?>

First we add the GetAlbumByArtist:

Javascript:

function GetAlbumByArtist(val) {
agent.call('','GetAlbumByArtist','GetAlbumByArtist_Callback',val);
}

The we add the the call back function but this time instead of placing our results in a Select Box we will be creating an Unordered list and placing the list inside a DIV.

function GetAlbumByArtist_Callback(obj) {
 
var htmlOutput = document.getElementById("htmlOutput");
var html = [];
for (var i in obj){
html[html.length] = '<li><a href=\"#\" 
onclick="GetTracksByAlbum(\''+obj&#91;i&#93;+'\')\">' + obj[i] + '</a></li>';
}
document.getElementById("htmlOutput").innerHTML = '<ul>' 
+html.join('')+'</ul>';
document.getElementById("htmlOutputTracks").innerHTML ='';
}

You can see that in the link that we create has an OnClick() event defined. This will fire off another Ajax function that returns a list of Tracks for a selected Album.

What you should have now is a search box that has an Ajax Auto-Complete function which return a list of Artist and a SelectBox that has a no-post back function which returns a list of Albums.

Part 3: On Your Own

Your last task is to use the OnClick() event in the Albums list to return a list of Tracks on an Album.

Remeber this is a 2 part process.

Part 1: Define a a PHP function that search for an Array of Tracks using the Album Name.

Part 2: On the client side:
A: define a JS function that invokes the agent.call function and passes it the Album Name.
B: Define an Callback function that handles the response from the server-side PHP function and then binds it to an html control.

It’s very easy to do so you should give it a shot before peaking at the source code.

IIS Server Error

iis-8

Every developer likes to take certain things for granted – like ASP.Net being configured correctly with IIS. However sometimes there will just be something non-obvious that is preventing ASP.Net pages from being served, returning an Http 500 error instead. The frustrating thing is that these are often caused by system configuration issues that most developers should never need to worry about. Below I offer some advice intended for IIS 5.1 on a local dev machine that resolves some of these errors. The problem is most likely:

  1. IIS or ASP.Net simply not installed correctly
  2. Security configuration

Continue reading IIS Server Error

Retain scroll position after browser refresh

Retain scroll position after browser refresh
As Joel Spolsky points out, updating web pages by getting just the bits of information that have changed instead of refreshing the whole page is the wave of the future. Whether we’re calling web services within a client-side page or using a gmail-like JavaScript technique (darn cool), it’s going to be a major architectural shift. It’s going to be hard.

In the meantime, there is a really easy way to fix the problem of having to scroll back down to the part of the page you were using when a refresh occurred. Because ASP.NET’s post-back model forces a trip to the server for anything interesting to happen, this technique is essential if you’re using ASP.NET. However, it works just as well in Perl, PHP, JSP – whatever your server-side technology. It requires client-side JavaScript to be enabled, but so do the fancier techniques we are looking forward to.
Note that you could also just turn “smart navigation” on in ASP.NET, but I’ve had issues where smart navigation messes up other JavaScript. I prefer not to use it.
I’ve implemented an example of the technique for this post separately in PHP and ASP.NET/C#. You can see the PHP example in action or download a zip file of the ASP.NET/C# example. For both examples, I use hidden fields and client-side script. They imply an http POST, and typically you will be posting to the same page. You could make it work with GET and/or with another page, but the problem itself gets more confusing in those scenarios.

Here is the complete PHP example:

Continue reading Retain scroll position after browser refresh

jQuery a great tool for any ServerSide Scripting Language

logo

One of the most used framework for making your web application more live, interactive. There is been a while since I run into it, but recently I decided to use it. Main reason is when i saw the sits that actually use the jQuery framework. Take a look here and you ll be suprised by the big company names.

Continue reading jQuery a great tool for any ServerSide Scripting Language