Why not create an account for them? it can be something as simple as stu00001 with 00001 being their ID or pass number or whatever you see fit.
External entities can have their own "account" format.

if you set them both as not null, then you need to fill both. Instead use a constraint to verify that you get either one, but I doubt you need it. Like you said yourself, keep it simple. You are not building the accounting super-system, so just cover your needs.

Usually you debit the cash account and credit the proper account when you receive money.
Since you are not really building an accounting system I suggest you debit your cashier (keep track of the money you've collected) and credit the student.
You can keep track of what the student has paid by a reason (code) or a transaction type.
If you need to check if a particular item has been paid, you sum based on that reason or transaction type.
Additional info (like month for tuition) can come along in separate fields, but it's not your primary focus.

You might want to also build payment transactions, for when you pay money and you need to credit your cashier.

@pritaeas: Aren't you making this too complicated?

LEFT(LEN - PATINDEX(REVERSE))

So what is the problem?

You use a timer to track time and run the next thing when you want it to.
But... you don't want to "pause" your program or have the timer loop for a period, becuase if I am not mistaken that will freeze your main thread and no animation will show.
Use multi-threading or background worker for the timer and the track of time.
I'm sure you'll find lots of tutorials in here.

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
    values 
    ('2013-01-01')
    insert into test
    values 
    ('2013-01-10')
    insert into test
    values 
    ('2013-01-15')
    insert into test
    values 
    ('2013-01-20')
    insert into test
    values 
    ('2013-01-31')
    insert into test
    values 
    ('2013-02-01')
    insert into test
    values 
    ('2013-02-10')

    insert into test
    values 
    ('2013-02-20')
    insert into test
    values 
    ('2013-03-20')

**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
    union
    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
    union 
    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

**[Results][2]**:

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

It was really late last night and I wasn't thinking straight, of course round was working, but it still returned 2 decimal points, nobody said that round would return an integer, only that it would change the value.
For clarification, there is no roundup in SQL. The command is ceiling for rounding up and floor to round down:
http://msdn.microsoft.com/en-us/library/ms189818.aspx
http://msdn.microsoft.com/en-us/library/ms178531.aspx

They both wouldn't work because they return the same data type they receive in.

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 also write it as:

SELECT DISTINCT 
                      b.vSeries_Table_Number, b.vSeries_Number, b.vSeries_Geography, b.vSeries_Type, b.vSeries_Unit_Type, a.Variable_Date, a.Variable_Value
FROM         dbo.tblPopulationSC AS a INNER JOIN
                      dbo.tblVSeriesList AS b ON a.vSeries_ID = b.vSeries_ID AND b.vSeries_Geography = 'Manitoba' AND b.vSeries_Type = ' Both sexes' AND 
                      b.vSeries_Unit_Type in ( ' 15 to 64 years', ' 65 years and over')
Stuugie commented: Thanks, that will help clean up my statements. +2

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.

I think it's the same with SQL:

select field1 ,count(*) from table
group by field1
having count(*) > 1 

or

select * from table
where field1 in 
(select field1 from table
 group by field1
 having count(*) > 1 )

You are missing the handles part. So far you've declared a sub and give it some actions to perform, but it wouldn't make a difference if you've called it "abcd". The handles part of the declaration is what ties it to an event and make the event run that sub.
Change Private Sub btn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
to Private Sub btn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) handles btn1.Click

Haven't we had this conversation already?
Anyway, I agree with parameterized queries but use them with stored procedures as G_Waddell is saying. Also do use user security to limit everybody to just these procedures, but I disagree with SQL user account. For me it's always Windows Security - provided you have active directory. No password will be transmitted or saved as plain text, it will be much easier to change it and you will know who has access where if you use AD groups in SQL and add/remove members as you see fit.

I'd like to broaden up this thread a bit and say that you may avoid SQL injection following the above - it's relative and as always security is an illusion- but you may give away your data by other flaws in your design.
And I'll give an example, but it's not the only one: People use datasets to retrieve all accounts in their app and then match user entry against that, because that's what they are familiar with. This is a bad practise.
Not only you are transmiting the whole security of your app over the network, you are using .Net to hold everything. It doesn't have to be SQL injection, you gave away username and password for anybody with admin rights.
It's same if you select * with criteria in the server. Instead either only select what you care for (if something is joined in your query, you will still return only what you wanted ...

Broken name, perfect service.

relu69 commented: Not bad at all. Me likes it .. +0

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 

next

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.

There are 2 ways you can calculate the new total after removing an item from your "order".
1st you deduct the value you've removed from the total
2nd you add all the remaining values to get the new total.

I'm guessing you went for both ways at the same time.
Since you are declaring Sum for each record removed, it will be 0. Then from 0 you deduct all the prices currently found in your DGV2 (For I = 0 to DGV2.Rows.Count - 1 )
Change Sum -= DGV2.Rows(I).Cells(5).Value to Sum += DGV2.Rows(I).Cells(5).Value and you should be fine.

It's not just another column, but a table with slots for each of your resources (if this is a hotel for each of your rooms).

I suggest you show us what you've done already or at least post the relationships, so that we can understand the structure you are using.

If you are getting the conversion error with the select convert(datetime, '2013-02-01',100) statement (or whatever format you are passing date in) , then SQL server can't determine the format of your date and you need to either tell it the format or change the format you are using.

A good way to see what format it is expecting would be to select an existing one or select getdate().

If you can't change the date format you are passing your procedure, then use DATEFORMAT to tell the server what format you are using for your date. Read here for SET DATEFORMAT : http://msdn.microsoft.com/en-us/library/ms189491.aspx

Shouldn't create_date be greater than or equal to now - 24 hours?

The problem is ) as x. It needs to go before the where and just after b:
I've tested this in sqlfiddle.com:

SELECT month(date),count(*)
FROM (
    SELECT CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END AS date
    FROM contact_concedii
CROSS JOIN
(SELECT a.id + b.id AS num FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS a
CROSS JOIN (SELECT 0 AS id UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) AS b ) AS x
    WHERE CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END IS NOT NULL
) AS c
GROUP BY month(date)

The results are:

| MONTH(DATE) | COUNT(*) |
--------------------------
|           1 |        4 |
|           2 |       21 |

So I'm guessing you are really after something like:

| ID | MONTH(DATE) | COUNT(*) |
-------------------------------
|  1 |           1 |        1 |
|  2 |           1 |        1 |
|  3 |           1 |        2 |
|  3 |           2 |       21 |

For this you'll need this:

SELECT id, month(date),count(*)
FROM (
    SELECT id, CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END AS date
    FROM contact_concedii ...

What I don't like is "shared" and "intervals". Even in small scale shared workbooks can cause user frustration and problems. Running a job at intervals reading a shared workbook, that users might be still editing - but haven't saved or have saved half-way sounds like bad idea.

May I propose an Excel macro using ADODB to insert records directly in SQL ?
I've used this in past to read & write to SQL and you simply give a template to the user to fill in and when done he/she has to press a button and the magic happens.

The code required should be :

Public Sub create_request()
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Your connection string here"
conn.Open

comm.ActiveConnection = conn
comm.CommandText = "insert into table (column1, column2, column3) values ('" & 
Sheets("Sheet1").Range("A2").Value & "','" & Sheets("Sheet1").Range("B2").Value & "','" & Sheets("Sheet1").Range("C2").Value & "')" 

comm.Execute
End If

If you have multiple records to insert, you can either loop through records or union them in a select.

I prefer it this way, because users know instantly if their insert succeeded and the template can be nice, with vlookups, data validation, calculations and whatever Excel supports. You also can save with data from several worksheets and can validate before the insert.

I've checked the above and it did contain errors, so below is the corrected script (but I haven't tested this one either):

select month(date),count(*) 
from (
    select DATEADD(dd, num , @start_date ) as date from 
(select a.id + b.id as num from 
(select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) a cross join 
(select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90 ) b ) c
) d
where date < @end_date
group by month(date)

What you do with it is:

select month(date),count(*) 
from (
    select case when DATEADD(dd, num , data1 ) <= data2 
    then DATEADD(dd, num , data1 )
    else null end as date
    from tablename 
cross join 
(select a.id + b.id as num from (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 
union select 7 union select 8 union select 9 union select 0) a 
cross join (select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 
union select 60 union select 70 union select 80 union select 90 ) b 
    where case when DATEADD(dd, num , data1 ) <= data2 
    then DATEADD(dd, num , data1 )
    else null end is not ...

On the top of my head (and with MS SQL in mind) it is possible, but a) it won't be nice and b) I don't know how much use it'll be to anybody.

If you create a set of days from beginning until end of period, you can count(*) group by month :

select month(date),count(*) 
from (
    select DATEADD(dd, num , @start_date ) as date 
(select a.id + b.id as num from 
(select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) a full join 
(select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90 ) b )
) 
where date < @end_date

Please note a couple things on the above query:
1) I'm using a series of numbers from 0 to 99 to calculate the dates in the period. So this query is limited to 99 days between start date and end date. If you need more days you need to add a "table" like a and b with values 0 to 900.
2) I prefer calculating the dates over using a calendar table, as this method is maintenance free and will work for any given period - if SQL supports the date.
3) I haven't tested it, so it might contain errors.

I agree with JorgeM, the symptoms described show that explorer.exe is not responding.
This can be due to a virus or a driver misbehaving or some other software "glitch".
If it was overheating then mouse would be also frozen and it wouldn't only cause explorer.exe to stop responding (browsers tend to freeze when windows explorer does.)

My advice to the OP: Next time everything turns white, press Alt+ Ctrl + Del, select Task Manager. In the Task Manager window, select Processes tab, click Show processes from all users, find and select explorer.exe. Note CPU usage and memory and post them here.
To try to recover your PC -if possible - click the End process button and confirm in the following prompt. You'll probably see taskbar and start menu disappear, but that's normal. In Task Manager select File/ New Task (Run...) and type explorer.exe
After you've clicked OK it might take a couple of secs, but everything should change back to normal.

xXExtrikerXx commented: like I said though, when it freezes, I'm not able to open the task manager or even do ctrl + alt + delete +0

I agree with Ancient Dragon, always make it resizeable.
I've have the bad luck to work with a program designed around a standard resolution (as Begginnerdev) suggests and it's always causing me troubles because the programmer disabled the scroll bars (!) for some reason and if your resolution isn't at least the one he was working with, you get all the controls of the form, except the ones that actually make things happen.

Begginnerdev commented: Always* and yes - I make sure to design for the smaller resolutions. The smallest I have worked with being 640x480. +7

You need to go with a separate thread (or background worker) for your upload. This will keep your main thread free to update your progress bar and make your form responsive.

Try this:

SELECT A.AccountID,
        A.LastName,
        B.Paid
 FROM A, (SELECT AccountID, SUM(InvCurrency) as 'Paid' FROM B
 WHERE B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000' AND 
  StatusID = 1 AND
 (ServiceCode=13 OR ServiceCode=14) AND
 (ServiceCode=15 OR ServiceCode=16) AND
 (ServiceCode=17 OR ServiceCode=18) AND
 (ServiceCode=19 OR ServiceCode=20) AND
 (ServiceCode=21 OR ServiceCode=26)
Group By AccountID ) B 
 on A.AccountID = B.AccountID 
 order by LastName

Isn't this the same as right([ID] + 10000;4) ?