Data environment question, incrementing a recordset [*Not Resolved*]

[steve_rm]

Hello

I am using the datagrid and data environment. But my sql query does not work. Can anyone tell me what l am doing wrong from the code below.

Private Sub cmdNext_Click() 
Dim strSQL As String 
Dim monthName As String 

'increment the months to display each month in the year 
deSalaries.rscmdIncrementMonths.MoveNext 
If deSalaries.rscmdIncrementMonths.EOF = True Then 
    deSalaries.rscmdIncrementMonths.MoveLast 
End If 
                                                                                                                                                                    
monthName = txtMonths 'Get the current month that is display and convert it to a date format 
monthName = (CDate(monthName)) 

'Write out the sql query to find all the employees under the current month 
strSQL = "SELECT `Month`.`MonthName`, " & _ 
            "LinkMonthEmployee.EmployeeNo, " & _ 
            "LinkMonthEmployee.Surname, " & _ 
            "LinkMonthEmployee.AccountNo, " & _ 
            "LinkMonthEmployee.EmployeeSalary, " & _ 
            "LinkMonthEmployee.Position, " & _ 
            "LinkMonthEmployee.Certificate, " & _ 
            "LinkMonthEmployee.GrossSalary, " & _ 
            "LinkMonthEmployee.Tax, " & _ 
            "LinkMonthEmployee.SocialSecurity, " & _ 
            "LinkMonthEmployee.NetSalary " & _ 
        "FROM Month, LinkMonthEmployee " & _ 
        "WHERE `Month`.MonthID = LinkMonthEmployee.MonthID AND Month`.MonthID = '" & monthName & "' " 

'Set all the properties for the data environment 
deSalaries.Commands("cmdAllDetails").CommandType = adCmdText 
deSalaries.Commands("cmdAllDetails").CommandText = strSQL 
dgrSalaries.DataMember = "cmdAllDetails" 'Set the datamember to cmdAddDetails 
dgrSalaries.ReBind 'Rebind the datagrid to display all the employees under the current month that has been incremented 

end sub

Not sure where l am going wrong with the above code, but it will not work but compiles ok. Not sure if l have set all the properties in the data environment. Any help will be most gratefull in solving this problem for me.

Many thanks in advance

Steve

[ARPRINCE]

What is a sample value for your txtMonth and monthName?

[steve_rm]

Hello

I think l know what the problem is.

MonthName in the database is a text database, so l can display the date like JAN 04, FEB 04 etc. The monthID is a date/time datatype, 1/01/04, 1/02/04.

So when the SQL statement compares both these dates, they are false.

How can l change the format of the monthName to display like JAN 04 to 1/01/04, as what it is in the database.

Many thanks for your help

Steve

[ARPRINCE]

How can l change the format of the monthName to display like JAN 04 to 1/01/04, as what it is in the database.

For example, if your txtMonth = "JAN 04", you need to tweak this so that VB would know that the "04" is the year and not the day.

Dim monthName as String
monthName = FORMAT(CDATE(LEFT(txtMonth,4) & "20" & RIGHT(txtMonth,2)),"M/dd/yyyy")
 
'LEFT(txtMonth,4) & "20" & RIGHT(txtMonth,2)                             will give you "JAN 2004"
'CDATE(LEFT(txtMonth,4) & "20" & RIGHT(txtMonth,2))                      will give you "1/1/2004"
'FORMAT(CDATE(LEFT(txtMonth,4) & "20" & RIGHT(txtMonth,2)),"M/dd/yyyy")  will give you "1/01/2004"

Since you're data type in the database is date/time, the value can have something like this "2004-01-01 09:01:00.000". So even if your date is a match, when you try to look for it in the database, you will not get a record match because of the "time" especially in SQL server.

What I do is I format my date to a "MM/dd/yyyy" so in this case, it would be like:

monthName = FORMAT(CDATE(LEFT(txtMonth,4) & "20" & RIGHT(txtMonth,2)),"MM/dd/yyyy")   'Will give me 01/01/2004

Then on the databse side I will convert my datetime type into the same format.

... AND CONVERT(CHAR(10),[Month].MonthID,101) = '" & monthName & "' "

The query will the give you an exact match.

On a side note, you should stay away from this type of coding.

Dim monthName As String 
.
.
monthName = txtMonths 'Get the current month that is display and convert it to a date format 
monthName = (CDate(monthName))  '<--- NOTE

Your monthName is a string variable and you are passing CDATE into it. CDATE converts a string into a date type so it would not make sense to pass this back to a string variable.

Although VB6 would accept this, VB.NET would crap this out. These are some of the issues VB.NET addressed with the old VBs hence, trouble shooting error in the code was minimized because they catch it before it happens.

[steve_rm]

Hello

Many thanks for all your help with helping me solve this problem, but l am still experiencing problems with this. i have followed all your instructions, and still can't understand where l am going wrong with it.

I have sent a small version of my program. I hope someone can understand it and finally solve the problem that l am having with it.

Many thanks for your help, it is most grateful

Steve

[ARPRINCE]

I have sent a small version of my program

I'm again assuming that this is a small part of your whole project. I browsed through it and the database you sent with it. The monthID field of your table is of a "text" type and not date/time as you impled. I guess you are reading and displaying the recordset during the form_load and the grid is a binded grid to your database coz I couldn't see initialization of "deEmployees" object .

I debugged the cmdNext_Click routine and the only thing I see that needs to be replaced is the one below.

Replace this....

"WHERE Month.MonthID = LinkMonthEmployee.MonthID AND Month.MonthID = #" & monthName & "# "

With this...

"WHERE Month.MonthID = LinkMonthEmployee.MonthID AND Month.MonthID = '" & monthName & "' "

Also, this should be on the "VB6" forum and not on the "Reports". There would be a lot more people who would be able to help you troubleshoot your problems if posted correctly.

Hope this helps.