Run-time error '-2147217900 (80040e14)'

[kazper]

Good day,

I'm currently trying to print the data in my database using Visual Basic but I have a little problem with the codes, need your help about figuring out what I might be missing. I keep on getting the same error

Run-time error '-2147217900 (80040e14)'


[CODE=vb]Dim adoHistoryPrint As New ADODB.Connectio n
Dim recHistoryPrint As New ADODB.Recordset
Dim cmdHistoryPrint As New ADODB.Command
Dim rsHistoryPrint As New ADODB.Recordset


Private Sub cmdPrintReceivi ngHistory_Click ()
Dim i As Integer

adoHistoryPrint .Open "Provider=SQLOL EDB.1;Password= Pword;Persist Security Info=True;User ID=User;Initial Catalog=DBase;D ata Source=DATABASE "

With cmdHistoryPrint
.ActiveConnecti on = adoHistoryPrint
.CommandType = adCmdText
.CommandText = " SHAPE {" & adoReceivingHis tory.RecordSour ce & "} AS Command1 COMPUTE Command1 BY 'ItemType'"
.Execute
End With

With recHistoryPrint
.ActiveConnecti on = adoHistoryPrint
.CursorLocation = adUseClient
.Open cmdHistoryPrint
End With

With rptReceivingHis tory
Set .DataSource = Nothing
.DataMember = ""
Set .DataSource = rsHistoryPrint

With .Sections("Item Type").Controls
For i = 1 To .Count
If TypeOf .Item(i) Is RptTextBox Then
.Item(i).DataMe mber = ""
.Item(i).DataFi eld = "ItemType"
End If
Next i
End With

With .Sections("Rece ivingDetails"). Controls
For i = 1 To .Count
If TypeOf .Item(i) Is RptTextBox Then
.Item(i).DataMe mber = ""
End If
Next i
End With

.Show

End With

End Sub[/CODE]

[Killer42]

What version of VB, and where does the error occur?

[kazper]

I'm using Visual Basic 6 the database is SQL Server 2005

[Killer42]

I'm using Visual Basic 6 the database is SQL Server 2005

By "where" I meant "at what line in the code".

[kazper]

sorry i forgot to paste the code :D

[CODE=vb] With cmdHistoryPrint
.ActiveConnecti on = adoHistoryPrint
.CommandType = adCmdText
.CommandText = " SHAPE {" & adoReceivingHis tory.RecordSour ce & "} AS Command1 COMPUTE Command1 BY 'ItemType'"
.Execute[/CODE]

[Killer42]

I've never heard of a SHAPE command before, so don't know whether I'm likely to be any help with this. I did wonder when reading it, whether perhaps ItemType should not have quotes around it, in line 4.

Presumably it was the Execute which returned the error?

[kazper]

the Shape command is used for grouped reports. I'm also new with this command. As I have done some research regarding the error. It might have caused either by my

.CommandText = "SHAPE {SELECT * FROM ReceivingHistor y} AS ReceivingDetail s COMPUTE ReceivingDetail s BY ItemType"

or by

adoHistoryPrint .Open "Provider=SQLOL EDB.1;Password= Pword;Persist Security Info=True;User ID=User;Initial Catalog=DBase;D ata Source=DATABASE "

I have tried doing the same thing with Just 1 Form, 1 Command Button, and 1 Data Report with these codes using NWIND.MDB
[CODE=vb]
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Private Sub Command1_Click( )
Dim q As Integer
Dim intCtrl As Integer
Dim x As Integer
Dim z As Integer
x = 0
q = 0
z = 0

With DataReport1
.Hide
Set .DataSource = rs
.DataMember = ""

With .Sections("sect ion4").Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptLabel Then
.Item(intCtrl). Caption = "City" & " :"
q = q + 1
End If
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl). DataMember = ""
.Item(intCtrl). DataField = "City"
End If
Next
End With

q = 0
With .Sections("Sect ion1").Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptLabel Then
.Item(intCtrl). Caption = rs1.Fields(q).N ame & " :"
q = q + 1
End If
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl). DataMember = "Command1"
.Item(intCtrl). DataField = rs1(z).Name
z = z + 1
End If
Next intCtrl
End With
.Refresh
.Show
End With
End Sub

Private Sub Form_Load()

Command1.Captio n = "Show Report"

cn.Open "Provider=MSDAT ASHAPE; Data Provider=Micros oft.JET.OLEDB.4 .0;" & _
"Data Source=D:\DSVFi les\Visual Studio\VB98\Nwi nd.mdb;"

With cmd
.ActiveConnecti on = cn
.CommandType = adCmdText
.CommandText = " SHAPE {SELECT FirstName,Lastn ame,City FROM `Employees`} AS Command1 COMPUTE Command1 BY 'City'"
.Execute
End With

With rs
.ActiveConnecti on = cn
.CursorLocation = adUseClient
.Open cmd
End With
Set rs1 = rs(0).Value

End Sub[/CODE]

This codes work fine. But when I start using SQL Database I get Run-time error '-2147217900 (80040e14)'. I'm currently trying to find some other ways to print Grouped reports, hope you can give me some ideas :)

TIA

[kazper]

I've never heard of a SHAPE command before, so don't know whether I'm likely to be any help with this. I did wonder when reading it, whether perhaps ItemType should not have quotes around it, in line 4.

Presumably it was the Execute which returned the error?

I have tried removing the quotes around the ItemType, it still doesnt work.

Try using the codes that I have provided above using 1 form, 1 command button, 1 data report.

remove the Report header of the data report, add a group header and footer. at the group header add a rptLabel and a rptText.

on the details section add 2 rptLabel and 2 rptText.

just change the path of the database for your northwind database location.

[kazper]

I think I have solved my problem ^_^

I just have to modify the command of the hierarchicy of the data report.

[CODE=vb]Option Explicit

Const preSHAPE = "SHAPE {"
Const SQL = "SELECT dbo.ReceivingHe ader.RRNo, dbo.ReceivingHi story.DateRecei ved, dbo.ReceivingHi story.ReceivedF rom, dbo.ReceivingHi story.Address, dbo.ReceivingHi story.Receiving Type, dbo.ReceivingHi story.Amount, dbo.ReceivingHi story.ItemType FROM dbo.ReceivingHe ader INNER JOIN dbo.ReceivingHi story ON dbo.ReceivingHe ader.RRNo = dbo.ReceivingHi story.RRNo "
Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'R eceivingType') AS Company BY 'ItemType'"


Private Sub Command1_Click( )
With DataEnvironment 1
If .rsItemTypes.St ate Then .rsItemTypes.Cl ose
.Commands!ItemT ypes.CommandTex t = preSHAPE & SQL & " where ReceivingHistor y.ItemType = 'CHEMICAL'" & postSHAPE
.ItemTypes
End With
DataReport1.Ref resh
If DataReport1.Vis ible = False Then DataReport1.Sho w
End Sub[/CODE]

^_^

[hariharanmca]

I think I have solved my problem ^_^

I just have to modify the command of the hierarchicy of the data report.

Option Explicit

Const preSHAPE = "SHAPE {"
Const SQL = "SELECT dbo.ReceivingHe ader.RRNo, dbo.ReceivingHi story.DateRecei ved, dbo.ReceivingHi story.ReceivedF rom, dbo.ReceivingHi story.Address, dbo.ReceivingHi story.Receiving Type, dbo.ReceivingHi story.Amount, dbo.ReceivingHi story.ItemType FROM dbo.ReceivingHe ader INNER JOIN dbo.ReceivingHi story ON dbo.ReceivingHe ader.RRNo = dbo.ReceivingHi story.RRNo "
Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'R eceivingType') AS Company BY 'ItemType'"


Private Sub Command1_Click( )
With DataEnvironment 1
If .rsItemTypes.St ate Then .rsItemTypes.Cl ose
.Commands!ItemT ypes.CommandTex t = preSHAPE & SQL & " where ReceivingHistor y.ItemType = 'CHEMICAL'" & postSHAPE
.ItemTypes
End With
DataReport1.Ref resh
If DataReport1.Vis ible = False Then DataReport1.Sho w
End Sub

^_^

I don’t know for what purpose you are using, but we can use


[Code=VB]Dim adoHistoryPrint As New ADODB.Connectio n
Dim recHistoryPrint As New ADODB.Recordset

Set recHistoryPrint = adoHistoryPrint .Execute strSqlQuery
‘strSqlQuery should be Select query to fill the record set.[/code]


If you want the same structure, then you have to explain this line.

[code=vb]" SHAPE {" & adoReceivingHis tory.RecordSour ce & "} AS Command1 COMPUTE Command1 BY 'ItemType'"[/code]