Problem in VB SQL Query

**QVeen72** · Jan 9 '08, 06:37 AM

Hi,

How many fields you are trying to Display in the datareport...?

Regards
Veena

**MeeMee** · Jan 9 '08, 07:23 AM

Originally posted by QVeen72

Hi,

How many fields you are trying to Display in the datareport...?

Regards
Veena

four fields ... It works when I move the fields and make them so close to each other and when I make the font small so that the width of the report is reduced.

**QVeen72** · Jan 9 '08, 07:42 AM

Hi,

Before showing the report, give this :

DataReport1.Ori entation = rptOrientLandsc ape
DataReport1.Sho w

Regards
Veena

**MeeMee** · Jan 9 '08, 08:23 AM

Originally posted by QVeen72

Hi,

Before showing the report, give this :

DataReport1.Ori entation = rptOrientLandsc ape
DataReport1.Sho w

Regards
Veena

Ya, I already tried this and it worked ... but some reports don't need to be in landscape so I'll try to reduce the width until it works ...

thanks alot Veena :)

**MeeMee** · Jan 13 '08, 09:11 AM

Hi again,

I have a problem in the data reports, sometimes it shows all required records except one .. however, when I run the code in debug mode and go through each line until the report is generated, I can see all records in the report including the missing one ..

**QVeen72** · Jan 13 '08, 10:36 AM

Originally posted by MeeMee

Hi again,

I have a problem in the data reports, sometimes it shows all required records except one .. however, when I run the code in debug mode and go through each line until the report is generated, I can see all records in the report including the missing one ..

Hi,

May be Some of the Fields of that Record are null,
Or there is no Proper Linking.. Try to run the query in Query Designer and find how many records are returned..

Regards
Veena

**MeeMee** · Jan 13 '08, 10:53 AM

Originally posted by QVeen72

Hi,

May be Some of the Fields of that Record are null,
Or there is no Proper Linking.. Try to run the query in Query Designer and find how many records are returned..

Regards
Veena

I checked the records returned by the query , and it is correct, only one record isn't showing in the report , also all the fields have values..

However, when I go step by step in debug mode until I am done wit hthe code and the report is generated, all records are shown in the report .. Why is this happening only in debug mode ??

**QVeen72** · Jan 13 '08, 10:56 AM

Hi,

May be some time is needed, in between..
After Passing parameters to command object, and before showing the report, use "DoEvents" or use "Sleep" for few seconds.. give a Mesage box in between like "Generating Report"..

Regards
Veena

**MeeMee** · Jan 13 '08, 11:05 AM

Originally posted by QVeen72

Hi,

May be some time is needed, in between..
After Passing parameters to command object, and before showing the report, use "DoEvents" or use "Sleep" for few seconds.. give a Mesage box in between like "Generating Report"..

Regards
Veena

It worked , thaaanks alooot :D

**MeeMee** · Jan 14 '08, 06:10 AM

Originally posted by MeeMee

It worked , thaaanks alooot :D

After testing it a few times, I found out that using Sleep solves the problem except when the query returns 1 record, It is not shown ?? I tried increasing the Sleep time but it didn't work ??

**QVeen72** · Jan 14 '08, 06:15 AM

Hi,

Can you post the code here...?

Regards
Veena

**MeeMee** · Jan 14 '08, 06:40 AM

Originally posted by QVeen72

Hi,

Can you post the code here...?

Regards
Veena

OK..

I am using the designer to add data environment, data report and command .. I set the command text with query that worked ..

The main code calls a function "Record5" which searches table "Requests" and find records which has a specific type of request and directorate name entered by the user from the form. If the records are found another function "AddRecord" is called to add records to a temporary table "TRequests" .

//Main Code

Code:

fromm = (fromMonthCmb.ListIndex) + 1
tom = (toMonthCmb.ListIndex) + 1
FromDate = CDate(fromm & "/" & fromDayCmb.Text & "/" & fromYearCmb.Text)
ToDate = CDate(tom & "/" & toDayCmb.Text & "/" & toYearCmb.Text)
                 
If IsDate(FromDate) = True And IsDate(ToDate) = True Then
   Call Records5

   Sleep 600 //works fine with records > 1

  Load DataEnvironment4
  With DataEnvironment4
      If .rsCommand1.State <> 0 Then .rsCommand1.Close
      .Command1 CDate(FromDate), CDate(ToDate)
  End With

  answer = MsgBox("Show Report ??", vbYesNo + vbQuestion, "Confirm")
  If answer = vbYes Then
   s2 = FromDate
   s3 = ToDate
   s4 = "To"

DataReport4.Sections("Section4").Controls("rptHeader").Caption = ss
DataReport4.Sections("Section4").Controls("direclbl").Caption = direcCmb.Text
DataReport4.Sections("Section4").Controls("from").Caption = s2
DataReport4.Sections("Section4").Controls("tolbl").Caption = s3
DataReport4.Sections("Section4").Controls("min").Caption = s4
DataReport4.Refresh
                        

If DataReport4.Visible = False Then DataReport4.Show

End If

Code:

Private Sub Records5()
Requests.Recordset.MoveFirst
Do While (Requests.Recordset.EOF = False)
If Requests.Recordset!ReqSubType = ReqTypeCmb.Text And       Requests.Recordset!ReqType = rtype And Requests.Recordset!Directorate =  direcCmb.Text Then
            Call AddRecord
End If
Requests.Recordset.MoveNext
Loop

Code:

Private Sub AddRecord()
If Adodc1.Recordset.RecordCount > 1 Then
Adodc1.Recordset.MoveFirst
End If
Adodc1.Recordset.AddNew
Adodc1.Recordset!cpr = Requests.Recordset!cpr
If Requests.Recordset!Name <> "" Or Requests.Recordset!Name <> Null Then
    Adodc1.Recordset!Name = Requests.Recordset!Name
    reqName = Requests.Recordset!Name
End If
If Requests.Recordset!jobtitle <> "" Or Requests.Recordset!jobtitle <> Null Then
    Adodc1.Recordset!jobtitle = Requests.Recordset!jobtitle
End If
If Requests.Recordset!grade <> "" Or Requests.Recordset!grade <> Null Then
    Adodc1.Recordset!grade = Requests.Recordset!grade
End If
If Requests.Recordset!ByEmployee <> "" Or Requests.Recordset!ByEmployee <> Null Then
    Adodc1.Recordset!ByEmployee = Requests.Recordset!ByEmployee
End If
If Requests.Recordset!status <> "" Or Requests.Recordset!status <> Null Then
    Adodc1.Recordset!status = Requests.Recordset!status
End If
If Requests.Recordset!ReqType <> "" Or Requests.Recordset!ReqType <> Null Then
    Adodc1.Recordset!ReqType = Requests.Recordset!ReqType
End If
If Requests.Recordset!ReqSubType <> "" Or Requests.Recordset!ReqSubType <> "-" Then
    Adodc1.Recordset!ReqSubType = Requests.Recordset!ReqSubType
End If
If Requests.Recordset!Directorate <> "" Then
    Adodc1.Recordset!Directorate = Requests.Recordset!Directorate
End If
If Requests.Recordset!section <> "" Or Requests.Recordset!section <> Null Then
    Adodc1.Recordset!section = Requests.Recordset!section
End If
If Requests.Recordset!Employmentdate <> "" Or Requests.Recordset!Employmentdate <> Null Then
    Adodc1.Recordset!Employmentdate = Requests.Recordset!Employmentdate
End If
If Requests.Recordset!reqdate <> "" Or Requests.Recordset!reqdate <> Null Then
    Adodc1.Recordset!reqdate = Requests.Recordset!reqdate
End If
If Requests.Recordset!Comment <> "" Or Requests.Recordset!Comment <> Null Then
    Adodc1.Recordset!Comment = Requests.Recordset!Comment
End If

If Adodc1.Recordset.RecordCount > 1 Then
Adodc1.Recordset.MoveNext
End If
End Sub

**QVeen72** · Jan 14 '08, 06:57 AM

Hi,

Why are you looping through all the Records and adding ..?
Use Connection Object and Execute Insert Query for the selected conditions..
and this approach will be much faster than looping through..

Some thing like this :

[code=vb]
Dim AConn As New ADODB.Connectio n
Dim sSQL As String
AConn.Connectio nString = "<Your Connection String>"
'Conn string can be copied from the Properties of Connection obj in dataenv..

AConn.Open
sSQL = "Delete From TempTable"
AConn.Execute sSQL
sSQL = "Insrt Into TempTable(Field 1,Field2...) Select F1,F2.. From MainTable Where Category='XYZ' "
AConn.Execute sSQL
'
'After this show the datareport
'
[/code]

Regards
Veena

**MeeMee** · Jan 14 '08, 07:42 AM

Originally posted by QVeen72

Hi,

Why are you looping through all the Records and adding ..?
Use Connection Object and Execute Insert Query for the selected conditions..
and this approach will be much faster than looping through..

Some thing like this :

[code=vb]
Dim AConn As New ADODB.Connectio n
Dim sSQL As String
AConn.Connectio nString = "<Your Connection String>"
'Conn string can be copied from the Properties of Connection obj in dataenv..

AConn.Open
sSQL = "Delete From TempTable"
AConn.Execute sSQL
sSQL = "Insrt Into TempTable(Field 1,Field2...) Select F1,F2.. From MainTable Where Category='XYZ' "
AConn.Execute sSQL
'
'After this show the datareport
'
[/code]

Regards
Veena

I keep getting syntax error

Code:

sSQL = " Insert Into TRequests(CPR,Name,JobTitle,Directorate,Section,Grade,EmploymentDate,ReqDate,ReqType,ReqSubType,ByEmployee,Status,Comment,Seen) Select CPR,Name,JobTitle,Directorate,Section,Grade,EmploymentDate,ReqDate,ReqType,ReqSubType,ByEmployee,Status,Comment,Seen From Requests Where ReqType = rtype And ReqSubType = ReqTypeCmb.Text And Directorate = 'Direc1'"

rtype is a variable

**QVeen72** · Jan 14 '08, 07:47 AM

Hi,

Check this :

Code:

sSQL = " Insert Into TRequests " _  
& " (CPR,Name,JobTitle,Directorate,Section,Grade, " _
& " EmploymentDate,ReqDate,ReqType,ReqSubType, " _
& " ByEmployee,Status,Comment,Seen) Select " _
& " CPR,Name,JobTitle,Directorate,Section,Grade, " _
& " EmploymentDate,ReqDate,ReqType,ReqSubType, " _
& " ByEmployee,Status,Comment,Seen From Requests "  _
& " Where ReqType = '" & rtype & "'" _
& " And ReqSubType = '" & ReqTypeCmb.Text & "'" _ 
& " And Directorate = 'Direc1'"

Regards
Veena