try to do it with selection instead of content. Make sure you start with selection.collapse if you don't want the search to be within the selected area only.

Try this:

update Users set NW ='1' 
from Contacts left join Users
on Users.code = Contacts.code 
inner join Contacts.Country = Location.Country 
where Users.NW = Location.City

But be warned that it might contain errors or other typos, as it was written in the reply box and hasn't been tested.

Good luck

Edit: Just saw that this is in the MySQL subforum. The update supports from in MS SQL, but don't know what MySQL will do.

I'll be honest, I didn't read everything. But what I did read, makes me think that you are replacing null with 0 in your columns but not in your formulas.

Also it would be easier to read and create to use isnull() instead of case.

To make it easier for you, I would replace NULLs (either with case or isnull()) in the T derived table. This way you don't need to change your formulas and add additional complexity there.

Inside the triggers you get access to 2 "special" tables. Read here: for info and how to use them.

I was considering this INV-JAN-UNITS-SOLD as sales statistics.

How do you store multiple shades in only 4 bin-locations?
Anyway, yes in my mind a separate table is used to hold inventory by item number, by lot number (batch/run/whatever number), by location.

Can I also say that giving access to your db on the internet, is a huge risk? You have described what seems a lot of work to populate with data. It would be terrible if somebody could wipe it all with a single statement. Of course you could restore a backup, but the on hand quantities per lot wouldn't be correct.
I would use a second db for the internet and push info in it. This way if somebody SQL injects into my db, it wouldn't matter as my data would be safe.

In my opinion you need to break it down to smaller tables, not per warehouse but per info usage.
You don't want to search 30 misc fields and the qties sold all year when you are looking for a bin location.
This setup is too expensive (in resources and time) to have for 74000 part numbers in 10 locations (740,000 SKUs). Separate the item info, from the inventory info, from the sales info, from the sales statistics.

Also keep in mind that if you have to store an item in more than 4 bin locations, then you'll need to change both the table and your app. Use rows instead of columns for this kind of info.

In a typical WMS things are like this:
Order info goes to order tables, header and details. In details you probably need 2 qties, 1 ordered and 1 remaining.
Price info goes to item master, together with part number, description, product category (Finished goods, raw material, packaging supply), lot and expiration date requirements, possibly owner if dealing with a 3PL warehouse, inventory unit (the unit that the quantities are expressed in) and possibly the rules for storing and picking it and hazardous classification.
Item master is usually accompanied by a couple other tables, that don't have part number as single identity field, as they are required to repeat it a couple times. These would be the UOMs (units of measure) and you would store there qty per box, ctn, pallet or other containers like drums. With that you probably store the dimensions for each UOM and sometimes the UPC assosiated with it.
The other table would be the UPCs table. You might not find it everywhere, especially if the system has the UPC associated with a particular level.

You'd also have a locations table, where you store info about your storage locations and their status, capacity and how you want them to react to the pick and putaway rules (picking path, machinery requyired,etc). If your warehouse is zoned to certain uoms per area, then you need to add this info here.

Finally you've got the stored items table, where you store location, part number, quantity, UOM (for zoned warehouses), owner, ...

I have never used this and haven't tested it, but you should be able to get MRN in your app with OUTPUT clause:

     [dbo].[Generate_MRN]('00001', '00001')
      , @FIRSTNAME


Each and every one of us has his/hers way of doing things and for various reasons. Others be because that feels right, others because they carry habits from one language to another and others because it helps us read our code now or it will help us understand our code when we revisit it a few months/years in the future.

I've met this guy that writes SQL pretty much identical with C++. He can't help it but press tab where it feels necessary. To me it's both useless effort and brilliant code to read.
Then there was this other guy that would simply press enter to word wrap (in SQL), no matter how complex his code got. It was next to impossible to read, sometimes even for him.

I agree with AleMonteiro if the code is readable its ok, but sometimes that extra few characters can make a lot of difference.

You are looking for a shell replacement. Google for a tutorial.

I'll use SQLfiddle -hope that's OK - to prove my point about setting day part of the date to '01' and also give you the answer how to set it to '01' or '16' depending on the date stored in the field.

[SQL Fiddle][1]

**MS SQL Server 2008 Schema Setup**:

    create table test (date datetime)

    insert into test
    insert into test
    insert into test
    insert into test
    insert into test
    insert into test
    insert into test

    insert into test
    insert into test

**Query 1**:

    select 'a',DATEDIFF(MONTH, (CONVERT(Datetime, case when day(date) <= 15 then convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '01')
    else convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '16')
    end ,101)), '2013-03-01') AS [Depreciated Months], date
    from test
    select 'b', DATEDIFF(MONTH, (CONVERT(Datetime, case when day(date) <= 15 then convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '01')
    else convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '16')
    end ,101)), '2013-02-10') AS [Depreciated Months], date
    from test
    select 'c', 
    DATEDIFF(MONTH, (CONVERT(Datetime, case when day(date) > 15 then convert(datetime, convert(char(4),year(dateadd(m,1,date))) + 
    '-' + convert(varchar(2), month(dateadd(m,1,date))) + '-' + '01')
    else convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '01')
    end ,101)), 

    convert(datetime, convert(char(4),year('2013-03-01')) + 
    '-' + convert(varchar(2), month('2013-03-01')) + '-' + '28')) 

    AS [Depreciated Months], date
    from test


    | COLUMN_0 | DEPRECIATED MONTHS |                            DATE |
    | ...

@LastMitch, I'm sorry to inform you that OP's statement is valid, just not what he is looking for.
on the other hand:

AND CONVERT(DATETIME, [Item].[Dpchsed],101) 
AS [Depreciated Months];

won't fly.
What OP is doing is convert Dpchsed from table Item to a datetime field with a particular format (101) and then compare it against today's date to get how many months apart the 2 days are.

Your query won't be processed at all, since AND is not valid in the select statement, DATETIME is a datatype and not a field (At least not one I can see in OPs post) and to convert Dpchsed to a datetime won't do much by itself.

No offense intended.

I'd try convert(int,a.Variable_Value) by definition the integer doesn't have decimal points, plus I'm suspecting that it's not rounding because the field is char and either includes spaces or something similar that can't be interpreted by the round (perhaps wrong decimal symbol? ) .

Stuugie commented: That did it, thanks so much +0
        cmd2 = New SqlCommand("select count(*) from ProgramDetails.Subjects where IDNumber = '" & txtIDNumber.Text & "'", cn)
        queryResult = cmd2.ExecuteScalar
        If queryResult > 0 Then

So basically you are telling your program that if count is greater that 0 (records have been found) do the insert. If it's less or equal to 0 (impossible to be less or no matches found) error the user.
change If queryResult > 0 Then to If queryResult = 0 Then

You could try something like this:

DATEDIFF(MONTH, (CONVERT(Datetime, case when day([Item].[Dpchsed]) <= 15 then [Item].[Dpchsed] else dateadd(m, 1, [Item].[Dpchsed]) end ,101)), getdate()) AS [Depreciated Months]

but... When you run the query the first 15 days of the month, you will be missing a month and same will happen for items bought with day(item.Dpchsed) > day(getdate()), but that's the limitation of the datediff. Also, since we are adding a month to the purchase date you should probably either check the dates in the case or in the where clause or datediff will return negative numbers.

My suggestion would be to drop the day part of the date and set all purchases to have occured in the 1st day of each month and instead of using getdate(), use the first day of the next month. This will solve the problem of datediff.

LastMitch commented: Nice! ... case when ... then ... else ... end +0

I think it's much easier to replace the where part with where date_format(date, '%Y/%m/%e') <> CURRENT_DATE
Basically strip the time from datetime.

Same goes if you want to select 1 visit per IP per day, use the date_format with a distinct and you are done.

If this has answered your question, please mark this thread as solved.

I was talking about the whole database. And I'll repeat it just to make it clear: "They are there to make sure that the user will enter valid data or that relative data won't be left behind when deleting or that an update will "destroy" the links between 2 tables."

The constraints you are adding are not part of the join itself. It's an added measure that ensures that your data will be "correct". There are other ways to achieve this, or in same cases there are no correct and wrong data.
In example table1 has column1 that holds the id available in table2, column2. You can join the 2 tables without constraint:

select * from table1 inner join table2 on column1 = column2 

What the constraint will do is not allow you to insert or update a value in column1 that doesn't exist in column2.
It won't join the 2 tables on its own and just because you've got a FK doesn't mean that your joins will be perfect.

When I'm saying that you don't want a FK pointing to the answer, that is because you don't have answers yet, so it would be imposible to ask the questions (you can't insert a value in column1=question that doesn't exist in column2=answer).

You can skip the 2 FK and still join your tables. In fact you can omit all FKs and still be just fine. They are there to make sure that the user will enter valid data or that relative data won't be left behind when deleting or that an update will "destroy" the links between 2 tables.
In your case you really don't want to set a FK in table question pointing to table answer.

You mentioned nothing about average.
I guess that would make it:

SELECT avg(counter) FROM
(SELECT weekofyear(timeentry) AS 'week', count(*) AS 'counter' FROM history
    WHERE timeentry between "2012-02-03" and "2012-02-09"  
    and dayofweek(timeentry) = 3
GROUP BY weekofyear(timeentry)) a;

dayofweek will return values based on ODBC standard, as described here:
so Monday being 1st day of the week doesn't make a difference.

Good luck with that, and do let us know how we can help you.

PS: Read the rules and especially the part that says: "Do provide evidence of having done some work yourself if posting questions from school or work assignments"

Please mark this thread as solved then.

The space part is easy, remove if from tw.Write(", ").
As for the actual data, have you stepped through your code to see that the loop handling it works as planned?
A couple things I see wrong are:
Dim numRows As Integer = dgvData.RowCount - 1
For count As Integer = 0 To numRows - 1
you are deducting 1 twice when handling rows. If this is a datagridview with 1 row, then it won't fire.
For count2 As Integer = 0 To numCols - 1
If (count2 <> numCols) Then
The if will never return false, as by definition count2 will never equal to number of columns (since you are limiting this in the for statement) .

select count(*) from table_name
where date between "2013-01-31" and "2013-02-04" 
and dayofweek(date) = 3

Read this post on how to retrieve data in a combobox:

Although it's about separating what the user sees from what the db or the program will read, you can go ahead and modify it to suit your needs.

Alternatively you can use a bindingsource:

            myConnection = New SqlConnection(connectionstring)
            sqlcmd = New SqlCommand(" SELECT [S_Name]FROM [VBP].[dbo].[Purchase_Order] Where [O_N0]='" & Val(TextBox3.Text) & "'", myConnection)

            Dim bind as new bindingsource
            bind = sqlcmd.ExecuteReader
            ComboBox3.datasource = bind 

        Catch err As Exception
            MsgBox(err.Message, MsgBoxStyle.Exclamation)
        End Try             

You are declaring a dataset, initialize it and then proceed to use it as a datasource without ever filling it with data.
It shouldn't populate your combobox as it holds no data.

If finding the last used cell is required - which wasn't stated in OP - then my I suggest avoiding all the code and using this:

dim last_row as int = 1 
for i = 1 to 10 'place here the maximum number of txtboxes
xlSheet.Range("A65536").end(xlUp).offset(1,0).value = cobj("Textbox" & i ).text 
end if 


or can be used with Begginerdev's suggestion of looping through the objects of the form:

For Each t as TextBox in Me.Controls 
   xlSheet.xlSheet.Range("A65536").end(xlUp).offset(1,0).value = t.Text

This is guaranteed to write after the last row in a specified column (I'm using "A" ) - if the last cell is before row 65536. I'm checking for 65536 as that's the last row Office 2003 supported. If this is planned to be used in Office 2007 or later the row in range can be 1048576.

@OP: I was checking that the last row didn't exceed 65536 to avoid errors. Last column in Office 2007 can't be more than 16384. I don't have Office 2003 available now to see the limit for that.

If all your textboxes are named TextBox and a number, you can try:

dim last_row as int = 1 
for i = 1 to 10 'place here the maximum number of txtboxes
while (xlSheet.Cells(1,last_row).text <> "" and last_row < 65536 ) 'I 
    last_row = last_row + 1 
end while 
if last_row < 65536 then 
xlSheet.Cells(1,last_row).text = cobj("Textbox" & i ).text 
end if 


If your textboxes are not all numbered sequentially, you can loop through the objects on your form and check their type. I'm sure there are a couple of examples in VB.NET sub-forum.

Please note that I haven't tested the above code and might contain errors.

If this issue has been resolved, please mark this thread appropriately

Are you using a parameterized query, are you concatenating the value into the insert statement, do you use datatables ?
Please share the code that inserts this into your db.