FAQ Database Discussion Community


Delphi Database Connection Using ACCESS and ADO connections

database,delphi,input,connection,ado
Okay so basically I've been working on my computing project for a while now and I've got 90% of it working however I'm having a problem with Delphi where is says that my database is not connected/ there is a problem connecting however I've already tried writing the information to...

VBA, Excel ODBC driver - Type Mismatch error while using sql query with parameter from cell

sql,excel,vba,excel-vba,ado
I have problem with Type Mismatch error while I do a select with getting parameter from excels cell. I am using excel like a database. My code is: Sub maxlg() Dim sSQLQry As String Dim ReturnArray Dim Conn As New ADODB.Connection Dim mrs As New ADODB.Recordset Dim DBPath As String,...

ADO Library References and Forward Compatibility

vba,excel-vba,versioning,ado,ms-jet-ace
I'm looking to add some Access database functionality in a customer's Excel workbook app. Okay Excel & VBA aren't my first choice, but that is what we're working with. Normally I use ADO through ADO.NET, so we always know the version because we've built for a specific version of .NET....

Running SQL Files with Variety of DDL commands and INSERT operations from a VB6 Code

sql,sql-server,sql-server-2008,vb6,ado
I have a VB6 application, which restores a BAK file to the Database and runs through some scripts. As there are large volume of SQL Scripts to run, I decided to move all in to a SQL File and then to run it from the Application. The SQL File is...

Linking two access databases in C#

c#,ms-access,ado.net,ado
I have a c# winforms app that will be connecting to 2 access databases. One is a reporting database which I wont be amending, and the other database is where the app will make updates etc. There will be a need for queries which join tables in the two databases....

VBA - Using Typeof … Is ADODB.Recordset Results in Compile Error

ms-access,access-vba,ado,late-binding,adox
I am building a function with a set of supporting sub-functions to create ADOX.Catalog objects to help me build automation for Access database generation. I like to use late-binding for my applications because my user base doesn't always have the same version of office applications, so I can't always rely...

aClassic ASP : How to locate an specific order in array, or record-set. Then move Next or Previous, starting from that order

arrays,vbscript,asp-classic,ado,recordset
I have done a bit of work on my code, and still unsure about how some of the code needs to be done in order to work. So far I got a function named FunctionUp' coded, this is the code that will go in the following sequence: If array of...

ADO equivalent for NZ function in MS Access?

c++,sql,ms-access,ms-access-2007,ado
I have the following command object: ADODB::_CommandPtr pCmd("ADODB.Command"); pCmd->ActiveConnection = pConn; pCmd->CommandType = ADODB::adCmdText; pCmd->CommandText = L" select ID, NZ(PaymentAmount, 0) from Contracts;"; ADODB::_RecordsetPtr pRS = pCmd->Execute(NULL, NULL, ADODB::adCmdText); When I run it, it reports error that NZ function does not exists. Researching on my own, I have found out...

Javascript ADO recordset open method not working. Parametrized queries

javascript,html,sql,ms-access,ado
I am creating a web page and have some javascript code to insert some information from the webpage to MS access database. I am using ADO connections and record set but I am having a problem with the open method. This is a snippet of the code I am having...

DELPHI Fill a listbox with the results of ADO Query

mysql,delphi,ado
have been reading through all associated threads but no solution posted to help me with Delphi. Quite simply, i have a MySQL table called Story and i want to extract specific fields from this - and hence populate list box. From other posts, i've used the following... adoqMenu.Close; adoqMenu.SQL.Text :=...

ASP ADO recordset.NextRecordset() breaks when there is no next recordset

javascript,asp-classic,ado,recordset
I'm writing a script in ASP with Javascript, and I need to loop through an indeterminate number of records AND recordsets. I have no problem looping through the individual records. The problem arises when I need to move through recordsets. As long as there is another recordset it works fine....

Delphi ADO: save DBEdit changes

excel,delphi,ado,delphi-xe5
I opened Excel table using ADO (though it is probably the same as any other database: adoconnection, adotable, datasource) and display it in ReadOnly DBGrid. And I have DBEdit to edit some value in currently selected row. But how to save it? I tried adotable.Post on FormClose but it throws...

Glimpse ADO “ExecuteReaderAsync()” does not respond

c#,.net,async-await,ado,glimpse
When use Glimpse ADO ExecuteReaderAsync() did not return any response forever: var factory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlClient"); var conn = factory.CreateConnection(); conn.ConnectionString = "YourConnectionString"; conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = "Select 1"; // Correct SQL is not required. If use SQL "Foo" brings same result. var result = cmd.ExecuteReaderAsync().Result; // NG....

Executing msdb.dbo.sp_send_dbmail via ADO from within Excel's vba

sql-server,vba,excel-vba,ado
I'm trying to run the following: Sub sendMailViaADO() Dim cmdStoredFunct As ADODB.Command Dim r As ADODB.Recordset Dim strConn As String strConn = _ "PROVIDER=SQLOLEDB.1;" & _ "P-----D=xxxxx;" & _ "PERSIST SECURITY INFO=True;" & _ "USER ID=yyyyyy;" & _ "INITIAL CATALOG=xxxxx;" & _ "DATA SOURCE=xxxxx;" & _ "USE PROCEDURE FOR PREPARE=1;"...

Using SQL to copy data from worksheets to other worksheets in the same workbook?

sql,vba,excel-vba,ado
I would like to use SQL to move data around from sheet to sheet within the same workbook. My sheets are large (width & depth) and coping with VBA is slow. The scenario is that I have sheets 1 to 5. I want to create sheet(6) with a column or...

Connecting to 32 bit Oracle client with 64 bit Excel

excel,oracle,vba,oledb,ado
I'm trying to connect my Excel to Oracle with this connection string in VBA: dim cn As New ADODB.Connection cn.Open "Provider=OraOLEDB.Oracle;Data Source=source;User Id=userid;Password=pwd;" I'm getting this error: Run-time error '3706': Provider cannot be found. It may not be properly installed. Relevant references: Microsoft ActiveX Data Objects 6.1 Library OraOLEDB 1.0...

How to GROUP BY in groups of N rows?

mysql,sql,group-by,ado
I have a table called PEOPLE with the following data: MYNAME AGE MYDATE ========================== MARIO 20 2015/02/03 MARIA 10 2015/02/02 PEDRO 40 2015/02/01 JUAN 15 2015/01/03 PEPE 20 2015/01/02 JULIA 30 2015/01/01 JUANI 50 2014/02/03 MARTIN 10 2014/02/03 NASH 21 2014/02/03 Then I want to get the average of age...

Server.ScriptTimeout = 0 Not working

sql,vbscript,asp-classic,timeout,ado
I have an ASP Classic script that has to do a lot of things (admin only) including running a stored proc that takes 4 mins to execute. However even though I have Server.ScriptTimeout = 0 Right at the top of the page it seems to ignore it - 2003 and...

Delphi ADO : Locate with dataset filter on bug

delphi,filter,ado,locate
I have as simple query, which returns following rows: Name Value Peter 1 Peter 2 Peter 3 John 1 John 2 Applying filter: ADO.Filter := 'Name="John"'; ADO.Filtered := True; // Now, its only 2 rows in dataset ADO.Locate('Value', 2); Cursor should point to "John 2", but it points to "Peter...

SQL Server Native Client 11 ADO connection string for MS SQL Server 2014 with integrated login

sql-server,iis,asp-classic,database-connection,ado
I'm currently trying to migrate a working web solution from an environment on IIS 7.5 and MS SQL Server 2008 R2 (both on Windows Server 2008 R2 x64) to IIS 8.5 and MS SQL Server 2014 (both on Windows Server 2012 R2). I can't quite get my ADODB connectionstring working...

Recordset Closed After Stored Procedure Execution

sql-server,vba,stored-procedures,ado,recordset
I'm executing a stored procedure using ADO in VBA. I'm trying to populate the recordset with the results from a stored procedure in SQL Server 2008. Example of the VBA below: Public Function DoSomething() As Variant() Dim oDB As ADODB.Connection: Set oDB = New ADODB.Connection Dim oCM As ADODB.Command: Set...

How to use TAdoQuery to ALTER IB stored procedure

delphi,stored-procedures,ado,alter,interbase
We test migration of a D5 project (using IB 2009 Server and IBProvider) to XE6, keeping the same IB server an ADO provider. The project heavily uses stored procedures (SPs). At database creation (programmatically, with IB API), after having created (with TAdoQuery) the tables, exceptions and declaring (also with TAdoQuery)...

Zeros rows returned from ADO Recordset

sql-server,vba,excel-vba,ado,recordset
I'm trying to get data from a SQL Server table into an ADO Recordset using the below code. Everything works fine with no errors, but I always get a record count of -1. I've confirmed that the database name and table are correct. If I use SSMS I can see...

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao
Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

SQL multiple queries response to variables

sql,ms-access,ado,ole
I want to make multiple queries in single connection I do this like FileName = "d:\database.mdb" strCnn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & FileName strSQL = "SELECT ID FROM [Users];SELECT DISTINCT ID FROM [Users];SELECT COUNT(Name) FROM [Users];SELECT Phone FROM [Users]; " Set objXDatabase = Server.CreateObject("ADODB.Connection") objXDatabase.Cnn = strCnn objXDatabase.SQL = strSQL arrRst...

Sorting Database Records in Descending Order

database,vb.net,sorting,listbox,ado
I'm relatively New To programming and VB and Would like some help. I am trying to list runners in the order of their speed (Fastest First), the data is from a database, i am able to sort the data in ascending order but cannot get them in descending order, is...

What's the appropriate place to call CoInitialize/CoUninitialize across DLL's?

delphi,dll,com,ado
I'm implementing a DLL containing a shared ADO Connection by using the ConnectionObject property of TADOConnection and passing it across DLL boundaries into other instances of TADOConnection. I need to make sure COM is initialized, so I need to call CoInitialize / CoUninitialize. Currently it's in the context of a...

DataReader index outside bounds of Array

c#,sql,ado
I've the following method pulling information from a table; public Customer GetCustomerDetails(int customerID) { Customer currentCustomer = null; try { using (cxn = new SqlConnection(this.ConnectionString)) { using (cmd = new SqlCommand("spGetCustomerInformation", cxn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerID; cxn.Open(); SqlDataReader dataReader = cmd.ExecuteReader(); currentCustomer = new Customer(); currentCustomer.CustomerID...

How to pass String Parameters to Command objects in ADO with VBScript

vbscript,ado,query-parameters
I have a function which returns a part of a SQL query. Function GetYQConditions(command, startYQ, endYQ) Dim strSql strSql = " AND yq>=? and yq<=?" command.Parameters.Append(command.CreateParameter(undefined, 200, 1, 61, startYQ)) command.Parameters.Append(command.CreateParameter(undefined, 200, 1, 61, endYQ)) GetYQConditions = strSql End Function Problem: The issue is yq>=? and yq<=? needs to have...

How to read data from Excel starting from given row and getting proper type inference

.net,excel,datatable,ado,type-inference
I am importing data from .xls and .xlsx files using these connection strings "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\file.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=0;MaxScanRows=16;Empty Text Mode=NullAsEmpty;" or "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0;MaxScanRows=16;Empty Text Mode=NullAsEmpty;" I load data in a datatable. Using connection As New OleDbConnection(connString) connection.Open() wsName = "myWorksheet$" Dim SQL As [String] =...