Cursors in MS SQL

I'm always forgetting now to do a simple cursor in MS SQL, so here is the basic structure

DECLARE @field1 VARCHAR(10)
DECLARE @field2 VARCHAR(10)

DECLARE name_cursor CURSOR
FOR
SELECT field1, field2 FROM table WHERE this = 'that'

OPEN name_cursor

FETCH NEXT FROM name_cursor INTO @field1, @field2

WHILE @@FETCH_STATUS = 0
BEGIN
--DO WORK HERE

FETCH NEXT FROM name_cursor INTO @field1, @field2

END

CLOSE name_cursor
DEALLOCATE name_cursor

An alternate would be
WHILE @@FETCH_STATUS <> -1 -- -1 indicates beyond result set
BEGIN
IF @@FETCH_STATUS <> -2 -- -2 indicates row is missing
BEGIN
--DO WORK
FETCH NEXT
END
END

Effective Way to Remove CR LF in MySQL Query

I had a need to replace new lines in a query that was causing issues elsewhere in a process flow that I was working on. While removing the new lines in code would be easy, the problem was that they were needed to designate the end of a line in a file. So, I decided to remove them at the earliest possible step, and that is at the query. So, to do so, here is the statement...

REPLACE(REPLACE(userEnteredField, CHAR(10), ''), CHAR(13), '')

Since we are not sure if the input is inputing the new line as Carriage Return, Line Feed, or both, you first replace one, then replace the other. Most solutions I see posted have REPLACE(userEnteredField, CHAR(10) + CHAR(13)) which works if they are both present. The way done replaces, one or the other or both.

I'm always searching for this...

To get the datakey on rowdatabound event for gridviews.


if (e.Row.RowType == DataControlRowType.DataRow)
{
string strY = GridView2.DataKeys[e.Row.RowIndex].Values[1].ToString();
}

The number 1 could be replaced with "KeyName" also.

Getting Buffalo NFiniti wireless adapter working in linux

Recently, in getting the home dvr setup and working, it needed to be moved into another room. The best solution would be to run cat5 cable to that room, but to do it properly was going to be cumbersome, getting into the attic and running wires is not fun. The easy solution was to get wireless setup on the pc.

I searched and found a reasonably priced wireless-n card on Amazon. It was a Buffalo NFinity Wireless-N USB 2.0 adapter. This will hopefully give me the needed speed when viewing HD content on my PS3 in another room.

Well, the product arrives, and the process of getting installed under Linux began. Unfortunately it did not work "out of the box" so I started searching for what the card really was.... and in the end, it turns out to be a Ralink chipset card, and uses the RT3070USB drivers. They are downloadable from their website. This was like 3 or 4 hour search.

But the fun did not end there. Come to find out, the card may or may not have its "id" in the files provided from Ralink, so I had to modify the source to get it to work. Finally, after that, it worked like a charm.

Here are the steps I took to get this card working in Myth/Ubuntu 9.10...

1. Obtain drivers from Ralink (Ralink Driver Site).
2. Download the RT3070USB(RT307x) driver. In my case it was 2009_1110_RT3070_Linux_STA_v2.1.2.0.tar.bz2
3. Uncompress the resulting file with tar -xvzf 2009_1110_RT3070_Linux_STA_v2.1.2.0.tar (change as needed for the most current driver)
4. CD to the directory created
5. CD to the os/linux directory
6. Open the config.mk file in your favorite editor
7. In the config.mx file, change the HAS_WPA_SUPPLICANT=n and the HAS_NATIVE_WPA_SUPPLICANT_SUPPORT=n lines to have =y on the end instead of =n.
8. Save the file.
9. In a terminal window, type in lsusb. You should get a line that ends in Melco, Inc.
10. Notate the ID for the device, in my case it was 0411:015d
11. Open the usb_main_dev.c in your favorite editor.
12. Search for the id earlier in the form of 0x0411,0x15d.
13. If it is found, you are good to go, else add a line to the end of the list that looks like this.... {USB_DEVICE(0x0411,0x015d)}, /* Baffulo WIFI N USB */ If this line is not present, it will not detect and associate the device to the driver.
14. Save and exit out of your editor.
15. CD back to the main directory.
16. Make the driver, sudo make
17. Install the driver, sudo make install
18. To be on the safe side, at this point I restarted. When the pc came back up, I just used the wireless manager in GNome to setup the network connection.

Some futher issues I ran into was that for whatever reason, traffic was still trying to go out the eth0 and not the new wireless connection. I had to edit my /etc/network/interfaces file and comment out the auto eth0 line. At this point, when the pc starts, the wireless connects and works just fine. There were some other things that were specific to my setup with MythTV, but I'll leave those out unless someone asks.

Hopefully this helps someone.

Showing gridview when no records present

Here is an easy solution to get a GridView to show headers when there are no records to show.

Code snippet:

DataTable dt = getResults(); //getResults would query your datasource and return a datatable

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.Databind();
}
else //part that generates the Gridview with headers only
{
dt.Rows.Add(); //adds blank row to table
GridView1.DataSource = dt;
GridView1.DataBind();
GridView1.Rows[0].Visible = false; //makes blank row invisible
GridView1.Rows[0].Controls.Clear(); //removes any controls for row
}

Weird Results with datatable.select

Ok,

Was working on a project today and need to get information from the database based off of an already completed query. I first did it by making a routine to run the query and build the select with a customer where statement, but hitting the db 300-400 times per incident was being slow and ineffective. So, I decided to just cut that down to one query and use a select on the resulting datatable.

I would get the results in a datatable, and make my expression.

My criteria was for the account, which was a number, and SQL syntax would be account = 1234567

That kind of worked, but would throw this error...

Min (274) must be less than or equal to max (-1) in a Range object.

After searching Google, I came across this post, Simillar error.

That post said to single quote the qualifier. So, I rewrote the expression to be account = '1234567' and it works.

Custom Link Buttons In Gridview

There have been times in my job, where a customized link button in a gridview was needed. While there are probably several ways to do this, the following is the best way to do it that I have found. In my case, I needed the link button to change text, and also change command name, based off of data from a query.

What I did was when creating the binding of the datasource to the gridview in codebehind, I added a datakey that I wanted to key off of.

gv.DataKeyNames = new string[] { "excluded" };

Then in the RowDataBound event, I have the following code within it. Basically it looks at the value I'm keying off of, and if it is a 1, then does one routine, else it does another. In the routine, it gets the LinkButton control from the cell, and then sets the text, forecolor, and command name.

if (Convert.ToString(gvEvents.DataKeys[e.Row.RowIndex].Values["excluded"].ToString()) == "1")
{
LinkButton lb = (LinkButton)e.Row.Cells[10].Controls[0];
lb.Text = "Include";
lb.ForeColor = System.Drawing.Color.Black;
lb.CommandName = "include";
}
else
{
LinkButton lb = (LinkButton)e.Row.Cells[10].Controls[0];
lb.Text = "Exclude";
lb.ForeColor = System.Drawing.Color.Black;
lb.CommandName = "exclude";
}