473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
9 3972
Killer42
8,435 Expert 8TB
What version of VB, and where does the error occur?
Aug 1 '07 #2
kazper
15
I'm using Visual Basic 6 the database is SQL Server 2005
Aug 2 '07 #3
Killer42
8,435 Expert 8TB
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
kazper
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
Killer42
8,435 Expert 8TB
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
kazper
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
kazper
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
kazper
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
1,977 1GB
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

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

Similar topics

5
by: Bob Bamberg | last post by:
Hello All, I have been trying without luck to get some information on debugging the Runtime Error R6025 - Pure Virtual Function Call. I am working in C++ and have only one class that is derived...
1
by: Nachi | last post by:
I am getting above error, could pls anyone help me........? Soruce Code : Sub updateOfferCategoryAmounts(ByVal Transid As Long) Dim spComm As Command Set spComm = New Command Dim intCatID...
4
by: neutralm | last post by:
Hi, I am getting the following error: OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server Column 'tags.id' is invalid in the select list because it is not contained in either...
8
by: g_man | last post by:
I am trying trap Runtime error 3022 (duplicates) in the click event of a command button that closes the form. I have code in the Form_Error event that does a good job of providing a more meaningful...
1
by: renukayuvaraj | last post by:
When i run the report its showing runtime error in this line as.... (Syntax error - missing operator in query expression) DataEnvironment1.rsCommand1.Open ("select * from GRNTrans where SUPPLIER...
1
by: Gorkamail2 | last post by:
Hi, I'm getting the following error when a program generates a query to a AS400 database. Product=ZWWVI-AUNO Platform=Other Table=IIM Error -2147217900 SQL0104 - Token 99 was not valid. Valid...
1
by: ALaurie10 | last post by:
Hi I am trying to write a select query that prompts a user for an application number that will call a recordset from an already existing table that will be used to export XML file data that will...
3
by: Jim Armstrong | last post by:
Hello all - This is driving me crazy. I have a table called tblClients - very simple, has the following fields: taxID (PK) ClientName SalesName The main form of my application allows a...
2
by: rahul more | last post by:
Hiiii I am making my project in VB6.0 and my back end is Access 2003. I hav one problem..... When I going to save data into database, at at cmdSave_Click event, VB giving me error(Error mentioned...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.