By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

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

P: 15
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)'


Expand|Select|Wrap|Line Numbers
  1. Dim adoHistoryPrint As New ADODB.Connection
  2. Dim recHistoryPrint As New ADODB.Recordset
  3. Dim cmdHistoryPrint As New ADODB.Command
  4. Dim rsHistoryPrint As New ADODB.Recordset
  5.  
  6.  
  7. Private Sub cmdPrintReceivingHistory_Click()
  8.     Dim i As Integer
  9.  
  10.     adoHistoryPrint.Open "Provider=SQLOLEDB.1;Password=Pword;Persist Security Info=True;User ID=User;Initial Catalog=DBase;Data Source=DATABASE"
  11.  
  12.     With cmdHistoryPrint
  13.         .ActiveConnection = adoHistoryPrint
  14.         .CommandType = adCmdText
  15.         .CommandText = " SHAPE {" & adoReceivingHistory.RecordSource & "}  AS Command1 COMPUTE Command1 BY 'ItemType'"
  16.         .Execute
  17.     End With
  18.  
  19.     With recHistoryPrint
  20.         .ActiveConnection = adoHistoryPrint
  21.         .CursorLocation = adUseClient
  22.         .Open cmdHistoryPrint
  23.     End With
  24.  
  25.     With rptReceivingHistory
  26.         Set .DataSource = Nothing
  27.             .DataMember = ""
  28.         Set .DataSource = rsHistoryPrint
  29.  
  30.             With .Sections("ItemType").Controls
  31.                 For i = 1 To .Count
  32.                     If TypeOf .Item(i) Is RptTextBox Then
  33.                         .Item(i).DataMember = ""
  34.                         .Item(i).DataField = "ItemType"
  35.                     End If
  36.                 Next i
  37.             End With
  38.  
  39.             With .Sections("ReceivingDetails").Controls
  40.                 For i = 1 To .Count
  41.                     If TypeOf .Item(i) Is RptTextBox Then
  42.                         .Item(i).DataMember = ""
  43.                     End If
  44.                 Next i
  45.             End With
  46.  
  47.             .Show
  48.  
  49.     End With
  50.  
  51. End Sub
Aug 1 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 5K+
P: 8,434
What version of VB, and where does the error occur?
Aug 1 '07 #2

P: 15
I'm using Visual Basic 6 the database is SQL Server 2005
Aug 2 '07 #3

Expert 5K+
P: 8,434
I'm using Visual Basic 6 the database is SQL Server 2005
By "where" I meant "at what line in the code".
Aug 2 '07 #4

P: 15
sorry i forgot to paste the code :D

Expand|Select|Wrap|Line Numbers
  1.     With cmdHistoryPrint
  2.         .ActiveConnection = adoHistoryPrint
  3.         .CommandType = adCmdText
  4.         .CommandText = " SHAPE {" & adoReceivingHistory.RecordSource & "}  AS Command1 COMPUTE Command1 BY 'ItemType'"
  5.     .Execute
Aug 2 '07 #5

Expert 5K+
P: 8,434
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?
Aug 2 '07 #6

P: 15
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 ReceivingHistory} AS ReceivingDetails COMPUTE ReceivingDetails BY ItemType"

or by

adoHistoryPrint.Open "Provider=SQLOLEDB.1;Password=Pword;Persist Security Info=True;User ID=User;Initial Catalog=DBase;Data 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
Expand|Select|Wrap|Line Numbers
  1.       Dim cn As New ADODB.Connection
  2.       Dim rs As New ADODB.Recordset
  3.       Dim cmd As New ADODB.Command
  4.       Dim rs1 As New ADODB.Recordset
  5.       Private Sub Command1_Click()
  6.       Dim q As Integer
  7.       Dim intCtrl As Integer
  8.       Dim x As Integer
  9.       Dim z As Integer
  10.       x = 0
  11.       q = 0
  12.       z = 0
  13.  
  14.       With DataReport1
  15.       .Hide
  16.       Set .DataSource = rs
  17.       .DataMember = ""
  18.  
  19.       With .Sections("section4").Controls
  20.         For intCtrl = 1 To .Count
  21.           If TypeOf .Item(intCtrl) Is RptLabel Then
  22.              .Item(intCtrl).Caption = "City" & " :"
  23.               q = q + 1
  24.           End If
  25.           If TypeOf .Item(intCtrl) Is RptTextBox Then
  26.               .Item(intCtrl).DataMember = ""
  27.               .Item(intCtrl).DataField = "City"
  28.           End If
  29.         Next
  30.       End With
  31.  
  32.       q = 0
  33.       With .Sections("Section1").Controls
  34.           For intCtrl = 1 To .Count
  35.           If TypeOf .Item(intCtrl) Is RptLabel Then
  36.                    .Item(intCtrl).Caption = rs1.Fields(q).Name & " :"
  37.                     q = q + 1
  38.           End If
  39.           If TypeOf .Item(intCtrl) Is RptTextBox Then
  40.                    .Item(intCtrl).DataMember = "Command1"
  41.                    .Item(intCtrl).DataField = rs1(z).Name
  42.                    z = z + 1
  43.           End If
  44.       Next intCtrl
  45.       End With
  46.       .Refresh
  47.       .Show
  48.       End With
  49.       End Sub
  50.  
  51.       Private Sub Form_Load()
  52.  
  53.       Command1.Caption = "Show Report"
  54.  
  55.       cn.Open "Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0;" & _
  56.                "Data Source=D:\DSVFiles\Visual Studio\VB98\Nwind.mdb;"
  57.  
  58.       With cmd
  59.            .ActiveConnection = cn
  60.            .CommandType = adCmdText
  61.            .CommandText = " SHAPE {SELECT FirstName,Lastname,City FROM `Employees`}  AS Command1 COMPUTE Command1 BY 'City'"
  62.            .Execute
  63.       End With
  64.  
  65.       With rs
  66.            .ActiveConnection = cn
  67.            .CursorLocation = adUseClient
  68.            .Open cmd
  69.       End With
  70.       Set rs1 = rs(0).Value
  71.  
  72.       End Sub
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
Aug 2 '07 #7

P: 15
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.
Aug 2 '07 #8

P: 15
I think I have solved my problem ^_^

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

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Const preSHAPE = "SHAPE {"
  4. Const SQL = "SELECT dbo.ReceivingHeader.RRNo, dbo.ReceivingHistory.DateReceived, dbo.ReceivingHistory.ReceivedFrom, dbo.ReceivingHistory.Address, dbo.ReceivingHistory.ReceivingType, dbo.ReceivingHistory.Amount, dbo.ReceivingHistory.ItemType FROM dbo.ReceivingHeader INNER JOIN dbo.ReceivingHistory ON dbo.ReceivingHeader.RRNo = dbo.ReceivingHistory.RRNo "
  5. Const postSHAPE = "}  AS Command1 COMPUTE Command1, ANY(Command1.'ReceivingType') AS Company BY 'ItemType'"
  6.  
  7.  
  8. Private Sub Command1_Click()
  9.     With DataEnvironment1
  10.         If .rsItemTypes.State Then .rsItemTypes.Close
  11.         .Commands!ItemTypes.CommandText = preSHAPE & SQL & " where ReceivingHistory.ItemType = 'CHEMICAL'" & postSHAPE
  12.         .ItemTypes
  13.     End With
  14.     DataReport1.Refresh
  15.     If DataReport1.Visible = False Then DataReport1.Show
  16. End Sub
^_^
Aug 2 '07 #9

hariharanmca
100+
P: 1,977
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.ReceivingHeader.RRNo, dbo.ReceivingHistory.DateReceived, dbo.ReceivingHistory.ReceivedFrom, dbo.ReceivingHistory.Address, dbo.ReceivingHistory.ReceivingType, dbo.ReceivingHistory.Amount, dbo.ReceivingHistory.ItemType FROM dbo.ReceivingHeader INNER JOIN dbo.ReceivingHistory ON dbo.ReceivingHeader.RRNo = dbo.ReceivingHistory.RRNo "
Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'ReceivingType') AS Company BY 'ItemType'"


Private Sub Command1_Click()
With DataEnvironment1
If .rsItemTypes.State Then .rsItemTypes.Close
.Commands!ItemTypes.CommandText = preSHAPE & SQL & " where ReceivingHistory.ItemType = 'CHEMICAL'" & postSHAPE
.ItemTypes
End With
DataReport1.Refresh
If DataReport1.Visible = False Then DataReport1.Show
End Sub

^_^


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


Expand|Select|Wrap|Line Numbers
  1. Dim adoHistoryPrint As New ADODB.Connection
  2. Dim recHistoryPrint As New ADODB.Recordset
  3.  
  4. Set recHistoryPrint = adoHistoryPrint.Execute strSqlQuery 
  5. ‘strSqlQuery should be Select query to fill the record set.

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

Expand|Select|Wrap|Line Numbers
  1. " SHAPE {" & adoReceivingHistory.RecordSource & "}  AS Command1 COMPUTE Command1 BY 'ItemType'"
Aug 2 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.