Setting up PCSX2 for Socom 2 and Xlink Kai

 Updates

The following has updated:

  • Uploaded a new pcsx2.zip file because I forgot to include the memory card. If you download it again all you need to do is take the socomCard.ps2 from the memory cards folder and add it into your memorycards folder in your pcsx2 folder.
  • Added a trouble shooting section with some known fixes from the community that have had some issues.

Why should I use PCSX2 over a Playstation 2?

Well if you already have a PS2 setup and you don’t care about better looking visuals then PCSX2 really isn’t for you. However if you don’t wish to use a PS2 anymore or can’t be bothered but would like to play Socom2 again this this would be for you. You can expect a cleaner looking game, smoother visuals and the ability to easily use custom patches / content (Harry62’s r0005 patch for instance).

pcsx2_dgseal

pcsx2_dg

pcsx2_deathTrap

Things to keep in mind

  1. Emulating the PS2 requires a decent computer.
  2. Expect to be playing with settings. As stated before you will need a decent computer but since not everyone has the same hardware the settings you may have to use may not be the same as someone else.
  3. The community is small, don’t expect to be playing at any hour in the day.

What works

Currently everything that the base game offered works. There may be some hiccups with frame drops but that’s the nature of the beast.
Map packs can work and there are a few ways to go about with using them. That will also be covered later on at a later date.

Getting started

The following files will be required
PCSX2
CLR DEV9  or his thread on the pcsx2 forums
Tap Driver
Xlink Kai Click the download button at the top of their page. While you are here also create an xlink account if you do not already have one because it will be needed later on in the guide.

Optional Files
Anything listed here is optional but will also have a section in the post that will go over how to install them as well as setting them up.

Harry’s r0005 Patch
Harry’s r0005 Patch (PAL)
The above files will always be the most current version. If you wish to follow his development and read up on the patch notes, you can do that here.
DvD Decrypter You can get the download from their website. You would only need this if you wish to make an ISO image of your copy of socom 2 to use with the emulator.

SCP DS Driver Package (PS3 Controllers)

The following is only for people on windows 7 as anyone on windows 8 and newer should already have the xbox 360 controller drivers by default. If you are unsure if you have windows 7 32 bit or 64 bit you can follow this link and it will tell you how.

Pick a driver based on your version of windows 7
Xbox 360 Controller Software / Drivers Windows 7 32Bit
Xbox 360 Controller Software / Drivers Windows 7 64Bit

Setting up PCSX2

First time start up

  1. Extract the pcsx2 folder from the pcsx2.zip file to a location of your choice.
  2. Extract the CLR_DEV9.dll file from the CLR_DEV9.zip to the Plugins folder in the pcsx2 folder.
  3. Extract TAP Driver.exe from the Tap Driver.zip file to the folder of your choosing. Run the TAP Driver.exe and follow through the steps.
    1. On the “Choose Components” step, select TAP Vritual Ethernet Adaptor and click next. This is a default option but if it for any reason is not checked make sure it is.
    2. Allow it to install to the default location. In my case it is “C:\Program Files\TAP-Windows” and click install.
    3. Once it finishes installing restart your PC.
  4. After your PC is rebooted go to your PCSX2 folder and launch it by double clicking on the pcsx2.exe file. You will be presented with the “PCSX2 First Time Configuration” wizard.
  5. One the first screen select your language or stick with the System Default and click next. You will then be presented with the Plugins window.pcsx2_PluginsSetupFromWizard
  6. Select the plugins to match the screenshot. We will be configuring the plugins in a few steps later so for now just click next and you will be brought to the Bios Selection screen.
  7. If you do not see a BIOS listed in the list, uncheck “Use default setting and then click Browse. A new window will pop up and you will go to your PCSX2 folder and select the bios folder. Once you do that click the select folder button. You should now see a bios appear in the list “USA v2.30(20/02/2008) Console”. Click on the bios in the list and click finish. If you do not see that verify that there are files in the bios folder. The folder should appear like this:
    ps2biosPCSX2 will now open and you will be presented to a screen like this:pcsx2_pcsx2window

Emulation Settings

  1. Click on Config on the top menu bar and then click on emulation settings.
  2. At the bottom of the window uncheck the checkbox that says Preset and then click on SpeedHacks in the left hand column.
    pcsx2_emulationSettings
  3. On the EE Cyclerate set it to -1 and on VU Cycle Stealing set it to 2. If you have a quad core processor make sure to check MTVU (Multi-Threaded MicroVU1).  If you plan on using your socom 2 disk you can also check Enable fast CDVD.
  4. After making the changes to Speedhacks, head on down to Game fixes on the left hand column and check Enable manual game Fixes. Check Delay VIF1 Stalls.
    pcsx2_gamefixes
  5. Click Apply and then OK and you will be brought back to the pcsx2 window.

Setting up memory cards

  1. Click on Config on the top menu bar and then click on Memory Cards.
  2. If you do not see a memory card under Unused cards then click the browse button which will open a new window. Navigate to the memory card folder in your pcsx2 folder and click the select folder button. You should see a memory card show up called socomCard.ps2 under Unused cards.pcsx2_memoryCard
  3. Right click socomCard and click on Insert card. From there make sure that Port-1 is selected and click Ok. It will now show that socomCard.ps2 is in slot one. Click Apply and then Ok. You will be brought back to the pcsx2 main window.

Setting up the Video Plugin

  1. Click on Config on the top menu bar and select video (GS). A new menu will appear and from that menu select plugin settings.pcsx2_gssettingsmenu
  2. A new window will appear.
    1. Under Adapter, either select your video card or leave it as Default Hardware Device. (This is mainly directed to people on laptops that may have more than 1 GPU for based on power states).
    2. Set renderer to Direct3d9(Hardware). Direct3d11Hardware (and software) tend to have problems with some maps in Socom 2. While Direct3d11(Software) can make them playable, it’s not ideal. Direct3d9 seems to give you a little more performance for the time being.
    3. Set Interlacing to Auto
    4. Uncheck Allow 8-bit Textures
    5. If you wish to up the internal resolution I suggest either 2x or 3x. If you have a powerful GPU you could go higher but it’s not really that noticeable. If you are wondering what this will look like, refer to the screenshots at the top of this post.
    6. Keep Texture Filtering to Bilinear(PS2)
    7. Keep Anisotropic Filtering off.
    8. Set CRC Hack Level to Full(Safest)
    9. Uncheck Enable Hardware Hacks.
    10. Check Logarithmic Z and Alpha Correction.
    11. Once you do all this, click OK and you will be brought back to the main pcsx2 window.pcsx2_GSSettingsdWindow

Setting up the Audio Plugin

  1. Click on Config on the top menu bar and click on Audio(SPU2) and click on Plugin Settings.
  2. Keeping everything as the default is fine. However if you experience audio problems later on, this is where you will go to tweak those settings.
  3. Click OK and you will be brought back to the pcsx2 main window.

Setting up the USB Plugin

  1. Click on Config on the top menu bar and click on USB and then plugin settings. This will open up the Qemu USB Configuration window.
    1. Under Device Type select Logitech USB Headset under port 1. It doesn’t matter what your actual headset is this is just how the plugin works.
    2. Set port 2 to None.
    3. Under Device API  port 1 select WASAPI and then click the Configure button next to it. From here we can set up our headset audio in and out to whatever headset device you have.
      1. For Audio Input Player 1 select your headset speaks.
      2. Keep Player 2 set to none.
      3. For Audio Output set it to whatever device is your microphone.
      4. Keep the Input Buffering and Output buffering to their defaults. These can be tweak if you need to later on.
      5. Click Ok to close out of the WASAPI settings window
    4. Click Ok to close out of the Qemu USB Configuration window. You will be brought back to the pcsx2 main window.pcsx2_usbSettingsWindow

Setting up the controller plugin

Notes: If you want to have pressure sensitivity (being able to crouch is the main reason to want this) then I suggest using a PlayStation 3 controller. There are additional steps for using this and a different plugin. I will be going over using a generic controller and the PlayStation 3 controller in this section.

Generic Controller Instructions
  1. If your controller is not already plugged in to your computer do so now.
  2. Click on Config on the top menu bar and click Controllers (Pad) and then in the new menu list click plugin settings. The LilyPad Configuration window will open up.
  3. Under Input API select Windows messaging (Recommended)
  4. Under Game Device API’s check the following
    1. DirectInput
    2. XInput (Xbox 360 Controllers only) (If this what you are going to be using for your controller)
    3. Monitor when in the background
  5. Under Mouse API
    1. Set to Disabled
    2. Check Start without mouse focuspcsx2_lilypadMainWindow
  6. Click on the Pad 1 tab and you will be brought to the controller mapping window.
  7. Click on a button (Square for instance) and then press the button on the controller that you want square to be. You will know if it bound because there will b e a list on the left hand side saying what controller button does what.
  8. If your controller supports rumble you can go down to the Add Force Feedback Effect and select your controller. You can adjust the motors by clicking on Big and Small motor.
  9. Once you are finished click apply and then OK to be taken back to the pcsx2 main window.pcsx2_LilypadControllerConfig
Setting up a PlayStation 3 Controller
  1. Close out of PCSX2 for now since we are going to be adding in a new plugin and another file to the pcsx2 folder in just a few steps and we will need to re-open pcsx2 anyways.
  2. If you haven’t downloaded the SCP DS Driver Package (PS3 Controllers) I suggest doing this now since it will be required. If you are on windows 7 you will also need the xbox 360 controller drivers that I linked above. Make sure to get the correct one that matches what version of windows 7 you are using bit wise.
    1. If you are on windows 7 then you will need to install the xbox 360 software first.
  3. Extract the SCP-DS-Driver-Package-1.2.0.160.zip to a location of your choosing. Where ever you put this it will need to remain there so put it somewhere where you won’t delete it.
  4. Inside the SCP-DS-Driver-Package-1.2.0.160 folder is another folder called ScpServer. Open that up and then open the bin folder. You can delete the source folder since it is not needed. However if you wanted to compile this yourself or make changes, the code is there to do so. There are newer versions of the software however I have had some issues with it and what I have posted is what has worked for myself and others. If you would like more information I suggest going to the pcsx2 thread and reading up on it there.
  5.  Plug in your Playstation 3 controller and launch the ScpDriver.exe file.
    1. Check Configure Service and Bluetooth driver (if you plan on using this over bluetooth. Keep in mind if you do not have a blue tooth dongle this option won’t apply to you.
    2. Click Install.
    3. If it all suceeded then you will see a screen like this:pcs2_SCPDriverInstall
    4. Click Exit
  6. Launch ScpMonitor.exe by double clicking on it. You will not see a window open however you should see an icon in your task tray.pcsx2_SCPMonitor
  7. Double click that icon and you will be presented with a window that looks like the following:pcsx2_scpmonitorYou should see a mac address of your controller and since it is plugged in may say that it is charging.
  8. You can close this window by clicking on the X.
  9. In the bin folder open the Win32 folder and move the LilyPad-Scp-r5875.dll file to the plugins folder in your pcsx2 foldder.
  10. In the Win32 folder take the XInput1_3.dll file and move it to the root of your pcsx2 folder. The root of the folder is the location where pcsx2 exists or refer to the screenshot.pcsx2_pcsx2root
  11. Re-open pcsx2 by double clicking on pcsx2.exe.
  12. Click on Config on the top menu bar and click Plugin/Bios Selector.
  13. Click on Plugins on the left hand column and for Pad select lilyPad svn(r5875) 0.11.0 [LilyPad-Scp-r5865].
  14. Click Apply.
  15. From here we can click configure next to the Pad selection and the LilyPad svn Configuration window will appear.
  16. Under Input API select Windows messaging (Recommended)
  17. Under Game Device API’s check the following
    1. DualShock 3 native mode (Requires SCP XInput DLL)
  18. Under Mouse API
    1. Set to Disabled
    2. Check Start without mouse focuspcsx2_lilypadPS3
  19. Click on the Pad 1 tab and you will be brought to the controller mapping window.
  20. Click on a button (Square for instance) and then press the button on the controller that you want square to be. You will know if it bound because there will b e a list on the left hand side saying what controller button does what.
  21. If your controller supports rumble you can go down to the Add Force Feedback Effect and select your controller. You can adjust the motors by clicking on Big and Small motor.
  22. Once you are finished click apply and then OK to be taken back to the pcsx2 main window.pcsx2_lilyPadPS3Buttons
  23. Click Ok and you will be brought back to the Components Selectors Window. Click Ok again to go back to the pcsx2 main window.

Configuring the network plugin

  1. Click on Config on the top menu bar and click Dev9 and then click Plugin Settings.
  2. You will be presented with the Configuration window for the CLRDev9 plugin.
  3. Check the Enable Ethernet Checkbox and then click Options.
    1. For Connection Method select TAP
    2. For Adapter select Ethernet # Tap-Windows Adapter V9 (# will be a number, it may not always be the same from user to user)
    3. Click Apply and the window will Close. Click apply on the ConfigForm window to go back to the pcsx2 main window. We will be coming back here in a few steps to setup the IPAddress information but first we need to acquire our PS2 mac address.
  4. Click on CDVD on the top menu bar and select No Disk.
  5. Click on System on the top menu bar and click Boot CD/DVD Fast. pcsx2 will start and you will be brought to the PS2 main menu.pcsx2_PS2Main
  6. Press the button that you bound for Triangle on your controller and you will be brought to the Version information screen. At the very bottom you will see your mac address. Note the last 4 characters (the characters will be either Numbers or letters, not the : that seperates them) and head on over to the xlink page to generate a ps2 IP address.
  7. On the xlink page make sure Playstation 2 is selected and enter in your 4 characters that you got from the mac address.
  8. Click Calculate IP address
  9. Note the IP address they gave you and head back over to pcsx2.
  10. Close out of the window that is currently running (the image above) and you will be back to the pcsx2 main window.
  11. Click on Config on the top menu bar and click Dev9 and then click Plugin Settings.
  12. Click Options next to enable Ethernet.
    1. Click on the check box named Intercept DHCP. The options below so become editable.
    2. For ps2 IP address enter the IP address that the xlink website gave you.
    3. For Subnet Mask enter 255.255.255.0.
    4. For Gateway IP set it as 192.168.0.1
    5. Leave both DNS1 and 2 set to auto.
  13. Click Apply and then Apply again to go back to the pcsx2 main window. You can close out of PCSX2 for the time being, we will be coming back to it shortly.

Choosing your Socom 2 media

If you want to use your socom 2 disk then no further steps are needed other than a plugin change for pcsx2 which will be covered later on.
If you want to create a backup of socom 2 to an ISO image and use that instead then I suggest downloading DVD Decrypter which I provided to where you can get that software.

  1. Open DVD Decrypter. If you installed it and selected the option to create a short cut on your desktop then use that to open it.
  2. Insert your Socom 2 disk into your DVD drive in your PC and give if a second or two to load the disk.
  3. With DVD Decrypter open click Mode on the top menu bar and click ISO and click on READpcsx2_IsoMode
  4. Where it says destination on the main program, click on the Folder Icon with the Magnifying glass and select where you want the ISO to be saved. Once you do that Click the DVD > Drive button at the bottom. This can take some time for the ISO to be written.

Setting up Xlink Kai

If you have  not already downloaded and create an account do so now.

  1. Install Xlink Kia.
  2. Open Xlink Kai and by default your web browser will open to a configuration page.
  3. Set the following options:
    1. Under User Interface select the UI Program option. Don’t change what is in the textbox and check Auto Launch UI
    2. Under Network Settings change the Network Adaptor to TAP-Windows Adapter V9.
    3. Under Engine Settings enter you xlink Kai Username and password and choose if you want it to auto log you in or not when the program starts.pcsx2_XlinkKaiConfig
    4. Click save to save your settings and you can close your web browser.
    5. Start Xlink and the program should launch.pcsx2_XlinkMainWindow
    6. At the top of the program you will see that it says Messenger Mode and has some icons off to the right. Click on the Globe Icon. A list will generate on the left hand side that will show various consoles. Click the green arrow on the PlayStation 2. A new list will populate for all the games that xlink supports. Scroll down till you see Socom 2 and click on the green arrow. Once again another list will populate with NTSC and PAL. Click the green arrow for NTSC.
    7. Xlink for the most part is all set up. You can leave this window open or you can minimize it.

Playing Socom 2

  1. Launch pcsx2 by double clicking on the pcsx2.exe file. You will then be brought to the main window.
  2. If you plan on using your Socom 2 Disk then do the following:
    1. Click on CDVD on the top menu bar and select Plugin. If you have more than 1 Disk Drive you may have to configure the plugin to look at the correct drive. To do this do the following:
      1. Click on CDVD on the top menu bar and select Plugin Menu and then click  Plugin Settings.
      2. From the drop down select whatever drive is the disk drive you are currently using.
  3. If you are using the ISO you created then do the following:
    1. Click on CDVD on the top menu bar and select ISO.
    2. Click on CDVD on the top menu bar and click ISO Selector and then click browse. Select your Socom 2 ISO.pcsx2_Cdvd
Running harry62’s r0005 patch

Now you have a slight decision to make, you can either play the game vanilla in that you aren’t using harry’s patch or you can load harry’s patch. If you choose to use his patch  then do the following.

  1. Click System on the top menu bar and click Run ELF. A file selection window will come up, select harry’s patch and it will load and automatically start the game. Nothing else is needed after this. You will need to do this every time you want to play. There are methods to get “embedded” into the game however this guide will not be covering that. You can read harry’s patch thread about things of that nature. The link is above.
Running the vanilla game
  1. Click System on the top menu bar and click Boot CDVD (Fast)

In either instance the game will load. Once you get to the main menu select LAN and click Login. Create your player name and Click connectpcsx2_pocsx2Live

 

Trouble Shooting

If you are unable to see games on Socom 2 do the following:

  1. Go back to the Configuring network plugin section and drop down to step 12.
  2. Set PS2 IP, subnet and gateway to Auto by checking the checkbox next to those fields and hit Apply.

How to get in contact with me

  • Discord 1UP#6405
  • 1UP@TRS
  • If you have any questions or comments please leave then in the TRS thread.

Discord Communities to Join for Socom

 

 

Ps2Dis: Extending and relocating a string

Programs and files needed:

PS2dis
Any Hex editor. In this example I am using HxD though.
HACK_00.ELF (This is found on your .hack.//Fragment disk.) (This is also the online ELF for the game, the single player version is HACK_01.ELF

Optional

Socom 2 Imposter Maker by Dark Killer ( I used this to convert text to hexadecimal. It’s not needed but it makes it quicker.)

Problem

When translating this game from Japanese to English you tend to run into space constraints when dealing with how many letters you can cram in a memory location.  The problem came up where a translation wasn’t correct and since there just wasn’t room to actually have the correct text it instead was made to be something else. As a quick example the Twinblade spell “Tiger Claws” is currently just “Claws”.

A way to fix this issue

In the game .hack//fragment strings are loaded in by pointers.
For this example I am going to show how we can extend a string past the limit it currently has.
Currently I have a level 15 Blademaster that knows the spell Vak Slash and I want to call it something else, something longer.

So we open up PS2Dis and take the HACK_00.ELF and drag it over to PS2Dis. Press Control G and type the following “Vak Slash”, you should see something like this:
ps2Dis Label List

Double click on “Vak Slash” and you will be brought to the place in the file where it exists.
ps21Dis ValSlash Label

As you can see, there isn’t much room after the “h” to really add anything more to this string. So what we need to do now is find the pointer that checks this address “0060a940” and loads this string.

Press Control F and type in the following: 40a96000. Check as hex string as shown below:
ps2Dis Find Hex String

Click ok and you will be brought to the address “004cac80”. This is the pointer. If you press W while on this line and then enter twice you will see that it shows “Vak Slash”. Take note of the data of this address since we will need this later when we go to hex edit our changes in. The pattern is 40a96000.
ps2Dis Vak Slash Pointer

Take note of the pointer address “004cac80” because we will need to come back here later.

So now we need to find an area in this file that is empty. The best way to do this is to Press Control F again and enter a bunch of zeros. So something like 000000000000000000000000000000000000000000000000000000000000000000000000 should be just fine. Once you press enter you should end up at the address “004cfca0”. If you look a few addresses below it you will see that there is a decent amount of space to work with so this is where we will be adding in our new text.
ps2Dis Empty Memory

Take note however of the data in address “004cfc9c” because this will be important when it comes to hex editing this file later. I will give you the hex pattern here so you won’t have to worry about it later. The pattern is b0437000.

Creating our Text

To create our new string I am going to use the program S2 Imposter maker that I mentioned above. Once you open the program you will be presented with this:
socom2 Imposter Maker Main Screen

You can ignore everything on here in terms of options. The only thing we care about is the Text box. The address in the second textbox can also be ignored since thats for a different game, all we care about is the data that this generates. So I want to name my spell to Smashy McSmash. I do understand that this string really isn’t that long and probably could fit into the original area in the file but for the sake of keeping this rather simple this will do just fine. I type it into the textbox and the second textbox will update in real time. So you should see something like:
socom2 Imposter Maker Filled In Text

All we care about is the data however what we do not need is the data 20202020. Again this is part of the code that this was originally generating and does not apply to what we are doing. So you can skip the first line and move onto the second.

In PS2Dis copy and paste the data from S2 Imposter Maker in the the lines starting at address “004cfca4”. You should end up with something like this:
ps2Dis Updated Text

You will know if you did this right because in the grey text area in PS2Dis you will see your text:
ps2Dis Grey Window

While this next step is not super important, it could help when it comes to understanding the change we are about to make. So the addresses you just changed, if you press B on every line you will end up turning them in “byte”. Once you do that go back to “004cfca4” and press enter twice. This will create a label. See below:
ps2Dis Byte View

Now that this is done we need to go back to our pointer and have it point to the address “004cfca4”. The pointer should now look like this:
ps2Dis Vak Slash Pointer Update

 

You will notice that it still says “Vak Slash”. However on the far right it will actually show what it’s pointing to. The reason it say’s “Vak Slash” Still is because when we hit enter on it twice it just created a label. You can remove it by hitting enter on it and deleting the text in the label but it’s really not that important. So as far as this is concerned we are done with our setup and we can now start writing this data to the file. Do not close PS2Dis since we will be needing this still to know what data to add into the HACK_00.ELF file.

Modifying the ELF File

Open HxD and drag the HACK_00.ELF into it. The first thing you may notice is that the addresses on PS2Dis and HxD don’t quite match up. That’s fine, its not important.

Now we want to write our changes and the first place to start is with the pointer. In HxD press Control F and enter the the hex pattern 40a96000 and select Hex-Values for DataType.
You will be taken to the place in the file where this exists and this would be our pointer. We need to change the 4 bytes to our new address. In PS2Dis on the make sure the pointer line is highlighted and in the grey textbox take the 4 bytes A4 FC 4C 00 and replace 40 A9 60 00 in HxD. When you make a change it will be marked in red in HxD.
hxd Opened Elf

Now we want to edit in our new text into HACK_00.ELF.  In PS2dis make sure your pointer address is selected and press spacebar. The line will now change from the dark blue to a grey. Press the right arrow key on your keyboard and it will take you to your new text that you entered in prior. Now since this area in the original file is nothing but zeros doing a Hex Value search for this would be a pain.

This is why I said to take note of the data in address “004cfc9c” since we are going to use that as what we search for in HxD. In HxD press Control F and enter the hex pattern b0437000 and press ok. You will be brought to a new location in the file. Now need to skip the next 4 bytes since in pcsx2 we did that as well. Just as we did with the pointer we are going to enter our text. In PS2dis go to the first line in your new text and in the grey text above take that and just enter it into HxD. You should end up with this:
hxd Updated Code

Once this is done you can save you changes in HxD. Now if you make an ISO of your game and copy the edited HACK_00.ELF you made and launch the game (granted you have the same spell) you will see your change in game.

MSSQL Sub Query

This is written using Microsoft SQL Server however a lot of this can carry over just fine to other SQL variants with little change. I am also using SQL Management Studio 2016 when writing this query. This can be found here.

Sub queries come handy when you need to return data from multiple tables but need that data Summed up or some form of calculation done on them. This isn’t the only scenario where these are useful but in this example that’s what I will be doing.

The example below does not require you to create any tables as everything is done in memory.

Creating our tables:

DECLARE @Users TABLE (
	UserID INT IDENTITY(1,1) PRIMARY KEY, 
	FirstName VARCHAR(25), 
	LastName VARCHAR(25)
 
); 
 
DECLARE @items TABLE (
	ItemID INT IDENTITY(1,1) PRIMARY KEY, 
	ItemName VARCHAR(150), 
	PricePerUnit NUMERIC(5,2)
 
); 
 
DECLARE @sales TABLE (
	SaleID INT IDENTITY(1,1) PRIMARY KEY,
	UserID INT, 
	ItemID INT, 
	AmountSold INT
 
);

 

The “@” tells SQL Server that we are creating a variable and in this case the data type for that variable is a table.

Notes:
IDENTITY(1,1) means that this field auto increments. This is great for Primary keys so you do not have to know what the last ID was, it will do that for you and insert it.

Now we want to insert data into these table so we have something to lookup later on.

Insert statements:

INSERT INTO @Users VALUES('Bill','Dozer');
INSERT INTO @Users VALUES('Bob','Smith');
 
INSERT INTO @items VALUES('Running Boards',52.79);
INSERT INTO @items VALUES('Pin Strip Decal',22.95);
INSERT INTO @items VALUES('Red paint that makes the car faster',133.70);
 
INSERT INTO @sales VALUES(1,1,30);
INSERT INTO @sales VALUES(1,2,25);
INSERT INTO @sales VALUES(1,3,45);
INSERT INTO @sales VALUES(2,1,92);
INSERT INTO @sales VALUES(2,2,100);
INSERT INTO @sales VALUES(2,3,3);

 

 

Nothing much to say here other than to make sure your data follows the constraints you set when you created the fields in the tables.
If you wish to see the data that was put in you can use these SELECT statements:

SELECT * FROM @Users
SELECT * FROM @Items
SELECT * FROM @sales

 

You will see this:

MS SQL Query Example

 

 

 

 

 

 

 

 

 

Now I want to know how many items each User sold and how much money they made each for those sales as totals.

So this is the data we want. We want the user’s name as well as the Total Stock sold (this would be a sum of the items they sold) and a total dollar amount that was sold per person.

So our resulting table should resemble something like this:

UserName,Total Stock Sold, Sales Total

Now that we know what we want we now need to build a query that gets us this information.

First off we need the user name:

SELECT 
	LastName + ', ' + FirstName AS 'Name'
FROM @Users AS usr

 

The above gives us every user in the Users table and displays their name like Smith, Bob.
We also gave the @Users table an Alias of usr. This becomes important in future steps since we will need to reference this again in our sub queries.

Now we want to get the Total Stock Sold per user. So we are going to add the following to the query we already started:

(
		SELECT
			SUM(AmountSold)
		FROM 
			@Sales AS Sa
		WHERE 
			Sa.UserID = usr.UserID
	) AS 'Total Stock Sold'

 

Sub queries are always encased in parentheses. So this query uses the SUM function on AmountSold From the @Sales table. We also give this table an Alias. SUM will take every row in that column (AmountSold) and add them all together. However since we want to only sum the AmountSold and have it displayed for the correct user we have to tell it to only sum it up for the current User we are on. So we add in a WHERE clause and tell it to do it on records that match. So in this case we want it to only SUM where Sa(@Sales table) UserID matches our usr(Users table) UserID. This is why I said the Alias prior was important since we have to use it to do the matching.

Now that this is done we want to now get the Sales Total. We will now add the following to there query we have been working on:

(
		SELECT 
			SUM(PricePerUnit * Sa.AmountSold)
		FROM 
			@items AS It
		JOIN 
			@sales Sa  ON Sa.ItemID = It.ItemID
		WHERE
			usr.UserID = Sa.UserID
	) AS 'Sales Total'

This query works much like the other however now we are doing some multiplication in the SUM function. We multiply those 2 fields together and them sum all those results together to get the total Sales for that user.

The key difference in this one is that we are also now a JOIN. JOINs are important when you are trying to select from more than one table. In this case we want to match records from the @Sales table and the @Items table. The reason we do this is because we need the PricePerUnit in order to do our calculations.

So now that this is all done the final statement will look like this:

SELECT 
	LastName + ', ' + FirstName AS 'Name',
	(
		SELECT
			SUM(AmountSold)
		FROM 
			@Sales AS Sa
		WHERE 
			Sa.UserID = usr.UserID
	) AS 'Total Stock Sold',
	(
		SELECT 
			SUM(PricePerUnit * Sa.AmountSold)
		FROM 
			@items AS It
		JOIN 
			@sales Sa  ON Sa.ItemID = It.ItemID
		WHERE
			usr.UserID = Sa.UserID
	) AS 'Sales Total'
FROM @Users AS usr

MS SQL Query Result

Wallpaper-Changer (source code)

This version of the wallpaper changer is based on my original code that was written in VB.NET. I chose to re-write this program mainly because the original code was horrible and I’ve been wanting to move a lot of my older programs over to C#.

The main purpose for this program was to be used in windows XP since this wasn’t a feature that it had. It never really worked in a way that I wanted it to (originally) so this re-write corrects a lot of the issues that the original had. I decided to post it on github with the hopes that someone may find it useful in some way.

The code as is isn’t complete. There are some changes I plan on making (currently it just does jpgs) but as the code is , it works.

The code can be found below:

Wallpaper-Changer Github

C# Allow only 1 instance of a form to be opened

This was a problem I ran into with using MDI parents /children forms. You could open the same form over and over and it soon became a mess. The code below will check to see if it already exists and if it doesn’t assign the new form as a child to the mdi parent form and open it.

 

//Code that handles Child forms. May be adding more code to this down the road for customization.
        //No need to write the same code for every form
        private void CreateMDIChild(Form childForm)
        {
            //Checks if child form already exists. Only open if it does not exist in the collection
            FormCollection allForms = Application.OpenForms;
            bool formOpened = false; //Assume that this form does not already exist
 
            foreach (Form frm in allForms)
            {
                if (frm.Name == childForm.Name)
                {
                    //Tried to open form here however it throws an error about the collection being modified. So we create a bool and if the form exists
                    //set it to true
                    formOpened = true;
                }
            }
            //As long as formOpened is false we can open the new form as a child form to the parent
            if (formOpened == false)
            {
                childForm.MdiParent = this;
                childForm.Show();
            }
        }

Using the method

private void loginRegisterToolStripMenuItem_Click(object sender, EventArgs e)
        {    
            CreateMDIChild(new frm_LoginRegister());
        }

Reflector Preventer

reflectorPreventer

This program makes it harder for someone to decompile your program with reflector.
How to use:
Select your .net compiled program and click patch.
To make it so you are able to open the binary with reflector simply select the file and click “Restore File”

This program may throw false positives with some anti virus software.

 

Download

[ASP.NET /VB.NET] Populate a Datagrid view without a database

I wanted to list out some data for a page that wasn’t being pulled from a database but I didn’t want to write out the table and all that. So I dropped a datagridview on to the webpage and wrote some code in the code behind.

ASP.NET Side of things

<asp:GridView ID="grd_Specs" CssClass="footable" runat="server" AutoGenerateColumns="False">
	<Columns>      
		<asp:BoundField DataField="item1" HeaderText="Pc Specs"   />
		<asp:BoundField DataField="itemstat" HeaderText="" />
	</Columns>
</asp:GridView>

 

 

VB.Net Code

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        PopulatePCGrid()
 
    End Sub
 
    Dim DT As New DataTable
    Dim DR As DataRow
 
    Private Sub PopulatePCGrid()
        Dim DS As New DataSet
 
        DT = New DataTable("items")
        DT.Columns.Add("item1")
        DT.Columns.Add("itemstat")
        DR = DT.NewRow
 
        AddRowToGrid("Operating System", My.Computer.Info.OSFullName.ToString)
        DR = DT.NewRow
        AddRowToGrid("Total Memory", ((My.Computer.Info.TotalPhysicalMemory / 1024) / 1024).ToString("N0") & " MB")
 
       grd_Specs.DataSource = DT
 
       grd_Specs.DataBind()
    End Sub
 
    Private Sub AddRowToGrid(ByVal description As String, ByVal data As Object)
        With DR
            .Item("item1") = description
            .Item("itemstat") = data
        End With
        DT.Rows.Add(DR)
    End Sub

 

I will explain this as best as I can.
We create two variables, 1 for the Data Table and 1 for the Data Row.
When we get into PopulatePCGrid we create a new data set and set up the data table to match the gridview layout. If these fields do not match you will get an error on page load. So make sure the fields you want exist in both the gridview and in the code behind.

I wrote a sub proceedure to add rows to the dataTable. This isn’t required but it’s best to break out code that you will be using over and over again and make them into functions /subs.

So we call the proceedure and pass in description and data. Data is an object simply because it could be anything. I didn’t want to limit it to a string or integer because that wouldn’t be true (and in fact on the actual page I wrote this for it wasn’t true).

We then add that row to the data table and go back to PopulatePCGrid to finish the rest of the code. We create a new Datarow and then continue to add more information to the Data table. After we finish that we bind the datagridview and we end up with something like this:

datagridviewExample

 

[VB.NET] MySQL Function to Insert/update/delete with parameters

I wrote this bit of code in a project that I am working on for taking screenshots and uploading them to a remote server.

Basically what this does is it accepts the following parameters:
SQLQuery as a string
Parameters and Values as a string array

I am sure there are probably better ways of doing this however this got the job done in a very little amount of code.

In order to use this you would need to include the MySql dll into your project and then refer to it.

Imports MySql.Data.MySqlClient
Dim connString As String = "server=server.com;database=db_Name;port=3306;user=db_user;password=db_password"
Public Sub SaveUpdateDelete(ByVal sql As String, ByVal parameters() As String, ByVal Values() As String)
	Dim con As MySqlConnection = New MySqlConnection(connString)
 
        con.Open()
        Dim cmd As MySqlCommand = New MySqlCommand(sql, con)
 
        For i = 0 To parameters.Count - 1
            cmd.Parameters.AddWithValue("@" & parameters(i).ToString, Values(i))
        Next
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
 
        con.Close()
End Sub

 

To make use of this function:

Private sub DoDBStuff
	Dim sql As String = "INSERT INTO db_table(userID,imageHash,notes,lastEditedDate) VALUES(@userID,@imageHash,@imageDescription,NOW())"
	Dim params() As String = {"userID", "imageHash", "imageDescription"}
	Dim Values() As String = {DataBase_UserID, DataBase_UserName & "/" & foldername & "/" & HashedFileName & "." & FileNameParts(FileNameParts.Count - 1).ToString, ""}
 
	SaveUpdateDelete(sql, params, Values)
End Sub

[VB.NET] Reading and Writing to the Registry

This is a piece from my Final fantasy 11 Configurator that I made back when I used to play the game.

  1. Imports Microsoft.Win32

We’ll now make a registrykey variable.

  1. Dim FF_key As RegistryKey

Now we will make sure the location want it stored in that variable.

  1. FF_key = Registry.LocalMachine.OpenSubKey("\Software\Microsoft\", True)

Now to read in the Keys that are already saved by the game. In this part I called a few keys that I wanted information from, so in order to do that I first declare my variables:

  1. Dim key_windowed_mode As String
  2. Dim key_res_1 As String
  3. Dim key_res_2 As String
  4. Dim key_3D_res_1 As String
  5. Dim key_3D_res_2 As String
  6. Dim key_texture_compress As String
  7. Dim key_on_screen_map As String
  8. key_res_1 = FF_key.GetValue("0001")
  9. key_res_2 = FF_key.GetValue("0002")
  10. key_3D_res_1 = FF_key.GetValue("0003")
  11. key_3D_res_2 = FF_key.GetValue("0004")
  12. key_texture_compress = FF_key.GetValue("0018")
  13. key_on_screen_map = FF_key.GetValue("0019")

The (“numbers”) is the key name which holds the information that I wanted.
Now when it comes to writing it’s just as easy:

  1. FF_key.SetValue("0034", 1, RegistryValueKind.DWord

Valuekind.dword is just the datatype, you don’t always needs to do this but for this to work for me in this case I had to.