SQL RANK() equivalent for VBA ADODB.Recordset
strangely, standard SQL functions such as RANK() or BETWEEN() do not work in VBA/ADODB record strings. Instead, use of existing VBA functions are necessary and work the same.
Is there a more efficient method of using the SQL count mutliple times?
You could combine it all into a single query: SELECT (SELECT COUNT(*) FROM tbl_imts WHERE [REQUEST TYPE]='MINISTERIAL DOCKET' AND [OPI]='ASFBA' AND [DATE RECEIVED] BETWEEN #"+date1+"# AND #"+date1_2+"#") AS FirstColumn, (SELECT COUNT(*) FROM tbl_imts WHERE [REQUEST TYPE]='MINISTERIAL DOCKET' AND [OPI]='ASFBD' AND [DATE RECEIVED] BETWEEN #"+date1+"# AND #"+date1_2+"#") AS SecondColumn, Add other...
Delphi ADO : Locate with dataset filter on bug
The problem with TCustomADODataSet.Locate is that it's internally using Recordset.Clone and trying to locate a record in the cloned recordset, without setting the filter to the cloned recordset (see ADODB TCustomADODataSet.LocateRecord). From the Remarks in the docs: The Filter property of the original Recordset, if any, will not be applied...
Indexed MySQL field not saving value when creating/editing record through ADO
mysql,database,vba,ms-access,ado
I was able to recreate your issue with the 64-bit version of the MySQL ODBC 5.3 Unicode Driver (5.03.04.00). It appears to be an issue with ADO Recordset updates when the last column in the table is of type TEXT. I did not even have an index on userid and...
SQL multiple queries response to variables
You can't do this with a single command because it would need to return 4 separate datasets - 1 command = 1 dataset returned (even if empty). There's no reason however to close your database connection between queries; Open the connection, execute a query to get all IDs, then a...
DELPHI Fill a listbox with the results of ADO Query
You only see one field because you are only reading out one field (adoqMenu.Fields[0]). Simply read out the other fields as well: adoqMenu.Close; adoqMenu.SQL.Text := 'SELECT StoryID, Story Description, Completion Date FROM Story'; adoqMenu.Open; try ListBox1.Items.Clear; while not adoqMenu.Eof do begin Value1 := adoqMenu.Fields[0].AsString; Value2 := adoqMenu.Fields[1].AsString; Value3 := adoqMenu.Fields[2].AsString;...
How to GROUP BY in groups of N rows?
SQL tables are inherently unordered, so I assume that you want to order by mydate descending. You can enumerate the rows using variables, use arithmetic to define the groups, and then get the average: select avg(age) from (select t.*, (@rn := @rn + 1) as seqnum from table t cross...
Insert raw file data into BLOB (“OLE Object”) field of Access table using ADO
c++,ado,ole,ms-access-2013,variant
Since you are already using ADODB.Connection and ADODB.Recordset objects you should be able to use a binary ADODB.Stream object to manipulate the file contents with .LoadFromFile to fill the Stream with the file contents, and .Read to pull it back out of the Stream and store it in the database...
“Identity cannot be determined for newly inserted rows” after ADO RecordSet AddNew and Update
vbscript,asp-classic,ado,recordset
The best solution I was able to come up with was adding the following line of code right after the first Update is called on the RecordSet: rsTaskLog.AbsolutePosition = rsTaskLog.AbsolutePosition For some reason moving the cursor position puts the RecordSet back into a state where Update can be called again...
ADO stream failes to save BLOB loaded from database
c++,stream,ms-access-2007,blob,ado
Normally, when I solve the problem on my own, and there are no answers, I simply delete the question. However, the code posted could be great working example for future generations. The problem was in my construction of file path. After rewriting my code as below, everything works. Here is...
Delphi Database Connection Using ACCESS and ADO connections
database,delphi,input,connection,ado
You're doing far too much open, do something, close, open. Don't do that, because it's almost certain that is the cause of your problem. If the data is already being displayed, the database is open already. If you want it to keep being displayed, the database has to remain open....
Excel vba ADODB.Connection Sql Count distincts
Solved, every column in a SELECT that is not aggregated needs to be in the GROUP BY clause sql "SELECT `P3`, `P2`, `P1`, COUNT(`P3`) FROM [QQ$] GROUP BY `P3`, `P2`, `P1` ", Worksheets("QQQQ").Range("a3") ...
Server.ScriptTimeout = 0 Not working
sql,vbscript,asp-classic,timeout,ado
0 is an invalid value for Server.ScriptTimeout for 2 reasons: ASP pages have to have a timeout. They cannot run indefinitely. ScriptTimeout can never be lower than the AspScriptTimeout set in IIS according to this statement from the MSDN docs: For example, if NumSeconds is set to 10, and the...
explain differences between two different updates
To sum it up: The first approach (parameterized query/prepared statement) is best, because it is easy: automagical quoting and formatting of the parameters can be more efficient: the DBMS may optimize avoids fetching and storing the resultset (@Ansgar) scales better: would work for many records without change guards against SQL-Injection...
Connecting to 32 bit Oracle client with 64 bit Excel
Yes, when you use 64-bit Excel you must install also 64-bit Oracle Client and 64-bit OLE DB Provider. OLE DB Provider from Microsoft (i.e. MSDAORA) does not exist for 64-bit. It exists only for 32-bit and has been deprecated for very long time already. I don't know whether you need...
Running SQL Files with Variety of DDL commands and INSERT operations from a VB6 Code
sql,sql-server,sql-server-2008,vb6,ado
You can run SQL scripts from the command line using sqlcmd -S myServer\instanceName -i C:\myScript.sql (see https://msdn.microsoft.com/en-us/library/ms170572.aspx) In VB6 you can execute command line strings using the Shell command, for example to start the Windows Calculator: Shell "C:\WINDOWS\System32\calc.exe", vbNormalFocus For more on the Shell command see: https://msdn.microsoft.com/en-us/library/aa242087.aspx and How do...
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
Wouldn't it be easier simply to get the next or previous orders directly from the database using SQL: sSQLGetPrevOrder = "SELECT top(1) PREV.* " _ & " FROM [Order] PREV " _ & " JOIN ( " _ & " SELECT " & strOrderBy & " sortvalue, orderno " _...
ASP ADO recordset.NextRecordset() breaks when there is no next recordset
javascript,asp-classic,ado,recordset
Proper syntax would be: while (record) { //Some Code Stuff record = record.NextRecordset(); } This way the loop would stop when NextRecordset() will return null....
VBA, Excel ODBC driver - Type Mismatch error while using sql query with parameter from cell
You may have suffered from a little too much copy and paste. It seems that you are wrapping the `Range("U17") value in ticks while using it within a mathematical calculation. sSQLSting = "SELECT SUM(Sicherkoef*LG/KFM/" & Range("U17").value & ") AS MAXLG From [DATABASE$] WHERE Platzierung ='" & Range("S11").value & "' AND...
Delphi ADO: save DBEdit changes
Post is automatically called when the user navigates to another record. The only time you need to call Post is if the currently selected record has pending changes at the time you close the form. But even then, you don't have to call Post explicitly: there's a helper function CheckBrowseMode...
How to give default connection string for mysql database in app.config using c# [duplicate]
c#,mysql,.net,connection-string,ado
If you want to install a MySql Server in user machine, you will have to specify a User and Password as root for the server. Just then use the connection string: "server=127.0.0.1;user id=User;password=Password;database=Db" Where User is the Username you specified, Password is the Password you specified, and Db is your...
ADO Library References and Forward Compatibility
vba,excel-vba,versioning,ado,ms-jet-ace
In my opinion you should use late binding in your situation. In such situation you can rely on compiler to use the most appropriate library available on computer where your code will be used.
ADO equivalent for NZ function in MS Access?
c++,sql,ms-access,ms-access-2007,ado
Use an IIf expression which produces the same result as Nz. select ID, IIf(PaymentAmount Is Null, 0, PaymentAmount) As nz_PaymentAmount from Contracts; ...
ADO code no longer performing copy/paste after changing field to a drop down menu
Now that you are using a listbox, you need to reference the control properly. I assume you are not allowing multi-select, so you should reference as follows (assuming the key is Col 0, else use 1 or 2...: If rstTEAM!Team_name = Me.txtTEam.Column(0) Then If allowing multi-select, you would use something...
What is the difference between the field lookup and the function lookup?
The Lookup() function is a virtual function of TDataSet. Being a virtual function means that the implementation can change from class to class. Because of this, the documentation has different comments that varies from each TDataSet descendant. Let's take a closer look: TDataSet's Lookup(): Implements a virtual method to retrieve...
Print recordset if value exists in array
Add an extra for loop inside to compare every value in the range to the array values For Each rsFilter In Range("A1:A10").Cells For i=1 To 3 If rsFilter.Value = payid(i) Then Debug.Print rs(1) rs.MoveNext End If Next i Next ...
What's the appropriate place to call CoInitialize/CoUninitialize across DLL's?
You should not be doing this in the DLL. Make it part of the contract between the DLL and the host that the host is responsible for initializing COM. The DLL cannot be expected to initialize COM because the host may already have done so. And use a different threading...
DataReader index outside bounds of Array
What is in the CustomerTableColumns, could you show us the code? At first guess, I would say that the first value of your enum CustomerTableColumns is 1 to 8 and the datareader would expect to be 0 to 7...
How to read data from Excel starting from given row and getting proper type inference
.net,excel,datatable,ado,type-inference
This is a dark road you have started down. I've been down it recently and after trying several options, I ended up going with ExcelDataReader. This is a nice .NET library that does a decent job importing data from .xls and .xlsx files. Basic importing is below: IExcelDataReader excelReader =...
list records from sql server UDF that returns Table in classic asp
sql-server,asp-classic,ado,user-defined-functions
We tend to create stored procedure "wrappers" for UDFs on a one-to-one basis which can be called directly from web code. For your example, this might look like CREATE PROCEDURE [dbo].[pr_Alert] @I nvarchar (30), @L nvarchar (10) AS SELECT * FROM testDb.dbo.udf_Alert(@I, @L) RETURN Your commandText would then be simply...
Glimpse ADO “ExecuteReaderAsync()” does not respond
c#,.net,async-await,ado,glimpse
This: var result = cmd.ExecuteReaderAsync().Result; Is causing your code to deadlock. I'm assuming you're running an app which passes around a synchronization context (UI, ASP.NET, Universal App or such). This is why you shouldn't block on async code To get around this, you'll have to make your method async Task...
Linking two access databases in C#
If you use System.Data.Odbc then you can open an OdbcConnection to one database and refer to a table in another database within a query using [;Database=...].[TableName] like this: using (var cmd = new OdbcCommand()) { // con is an open OdbcConnection to C:\Users\Public\Database1.accdb cmd.Connection = con; cmd.CommandText = @"SELECT c.LastName,...
Recordset Closed After Stored Procedure Execution
sql-server,vba,stored-procedures,ado,recordset
Based on similar question: “Operation is not allowed when the object is closed” when executing stored procedure i recommended in comment: I suspect 2 reasons: 1) your sp does not contains: SET NOCOUNT ON; and 2) you're working on variable of type: table. The most common reason of Operation is...
c++ builder: getting values via ADOQuery using SELECT
sql-server,delphi,ado,c++builder
ExecSQL is only used for SQL statements that don't return a recordset, and to determine the results you use RowsAffected. For SELECT statements (which return a recordset), you use Open or set Active to true. Also, count is a reserved word in most SQL dialects (as in SELECT Count(*) FROM...
retrieve resultset from MSSQL stored procedure in classic asp
So I figured this out on my own. After digging though some logs I saw an error which I hadn't spotted previously: Object_doesn't_support_this_property_or_method:_'Close' Turns out I was incorrectly trying to close the command object: objCommand.Close My working solution is: Dim fpart, objCommand, objRecordset fpart = "SMF10320BRNU12" Set objCommand = Server.CreateObject("ADODB.Command")...
Populate treeview with recordset
I suspect that the GetItem method is returning a temporary value which is being lost once the function returns; so the string that pszText ends up pointing to has been freed/overwritten. Try the following and see if it makes a difference: // load contract values into treeview _variant_t varText =...
Sorting Database Records in Descending Order
database,vb.net,sorting,listbox,ado
You can use the desc keyword to sort the data descending: .Sort = "RunningSpeed desc" Consider using an SQL query to fetch the data instead of opening the table. The database is generally much faster at sorting the data than the Recordset object....
Converting ADODB Loop into DAO
DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...
excel vba: recordset joining and performance
Using Application.Index is possibly the slowest way to combine your arrays: use a regular nested loop instead and you won't even notice any hit - Sub TT() Dim a(1 To 2000, 1 To 10) Dim b(1 To 2000, 1 To 10) Dim cc(1 To 2000) Dim r, c, t t...
How to pass String Parameters to Command objects in ADO with VBScript
I think what you're trying to do is this: Function GetYQConditions(command, startYQ, endYQ) Dim strSql strSql = " AND yq>=? and yq<=?" startYQ = "'" & startYQ & "'" endYQ = "'" & endYQ & "'" command.Parameters.Append(command.CreateParameter(undefined, 200, 1, 61, startYQ)) command.Parameters.Append(command.CreateParameter(undefined, 200, 1, 61, endYQ)) GetYQConditions = strSql End...
Why does saving data result in an invalid value for inexistent field 'ERROR_CODE'?
There was a bug in DataSetProvider in Delphi XE which was fixed with one of its hotfixes http://edn.embarcadero.com/article/41312#3RADStudioXEHotfixforRAIDQC The same bug has also been seen in Delphi XE2 and was fixed with Update 3 http://qc.embarcadero.com/wc/qcmain.aspx?d=88928 Here is another QC report of this bug for Delphi XE http://qc.embarcadero.com/wc/qcmain.aspx?d=100723...
ADOQuery1 parameter 'card' not found
There are some issues in your code. SQL-Statement If you want to execute more than one statement, then you have to use the statement delimiter (most times ;). You missed that in your statements. INSERT INTO STUDENT (CARD_ID,NAMA,MATRIC_ID,SUBJEK,KURSUS,FAKULTI,Seksyen,TAHUN) VALUES (card,nama,matric,subjek,kursus,fakulti,seksyen,tahun); -- missed ; INSERT INTO subjek2 (CARD_ID, MATRIC_ID,NAMA,SUBJEK) VALUES (card,matric,nama,subjek);...
Datatype mismatch when querying DBase Date field via a Delphi ADO Query
Firstly, thanks to all that posted suggestions. Alas, I tried them all but without success :( thankfully, I found the solution while searching for something unrelated. SELECT * FROM [QPERFSAL.DBF] QPERFSAL WHERE PERFDATE = Format('2014/12/06',"YYYY/MM/DD") I'm not sure what effect this will have on localization but at least I can...
How to use TAdoQuery to ALTER IB stored procedure
delphi,stored-procedures,ado,alter,interbase
When assigning SQL to an TAdoQuery in code, make sure you have set ParamCheck := False before assigning the SQL statements. Otherwise, the Params list will still be populated.
ADO Repeated SQL Parameters
You should use named parameters: Try using named parameters for example: WHERE a.JobState = @JobDate and AND a.JobDate = @JobDate Then, when you add your parameters you will only need to add them once. It will look something like this: SqlParameter param = new SqlParameter(); param.ParameterName = "JobDate"; param.Value =...
Indy TCP Server Freezes, no idea why
If a client receives a "Connection closed gracefully" error, it means the server closed that client's connection on the server side. If your server code is not explicitly doing that, then it usually means an uncaught exception was raised in one of the server's event handlers, which would cause the...
Using SQL to copy data from worksheets to other worksheets in the same workbook?
Below is the solution i created: I got sheet1, sheet2 as described as below images: To copy the by using the sql, the following code is been used: Note: You need go to Tools > Reference > check Microsoft ActiveX Data Objects 2.8 Library and Microsoft Forms 2.0 Object Library...
Paramater Not Supplied Error when Executing Stored Procedure
sql-server-2008,excel-vba,stored-procedures,parameters,ado
You need to add: oCM.NamedParameters = True before assigning the parameters, otherwise they're actually being passed by position....
Javascript ADO recordset open method not working. Parametrized queries
javascript,html,sql,ms-access,ado
The following query will produce an error in Access, because it's trying to compare two field names: "EmployeeName" and "kevin": Select * From reportForm Where EmployeeName = kevin The following query should not produce an error, because it's comparing "EmployeeName" against a string value of "kevin": Select * From reportForm...
Prevent DBLookupComboBox from moving ADOTable cursor
It is not possible to prevent the DBLookupCombobox from moving the cursor in the underlying dataset. It needs to move along the records it lists to retrieve the value to be assigned to the main dataset field. The usual approach is to have an auxiliar dataset that only holds the...
Using Max Database Size option in ADO causes the Open command to throw
You must use: Provider= Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=test.sdf;ssce:Max Database Size=512 (same applies to password and other SQL Compact specific keywords)...
Zeros rows returned from ADO Recordset
sql-server,vba,excel-vba,ado,recordset
As i mentioned in a comment to the question, replace adOpenDynamic with adOpenStatic and you should get proper recordcount. The use of the ADO Recordset's .RecordCount property requires either the use of: 1. Static or Keyset server-side cursors or 2. A client-side cursor (which returns a Static cursor) The RecordCount...
Can't write accented characters to MySQL using ADO/ODBC
I eventually solved my problem: I use ADO prepared statements. The problem was the in the CreateParameter() call upon setup of the prepared statement PCommand->CreateParameter( LPCTSTR(NULL), ADODB::adVarChar, ADODB::adParamInput, 50); In there, simply replacing adVarChar by adVarWChar solved the problem. adVarChar seems to be considered an ANSI character without any regard...
Why isn't the ADO Extended Properties “HDR=No” flag being obeyed here?
I wanted to post the answer to my own question in case someone else runs into a similar situation in the future. For the purposes of the post I had oversimplified my code, and in so doing I had removed the thing that was making it break. The code was...
F2051 Unit ADODB was compiled with a different version of ADOInt._Command
Check the project source (DPR) and/or settings of the migrated application project. You may find explicit entries in the uses list that reference ADO units that are normally part of the VCL/RTL. Or, you may find the relevant ADO*.pas files have been placed in the same location as the DPR...
Import rows from delimited file (.cvs) into MS-Access table
Your issue with methods not existing may be that it's not properly opening the CSV using that provider. To my knowledge, SQLOLEDB.1 doesn't have this text reader functionality. Try this, change: objconnection.Open "Provider=SQLOLEDB.1;Data Source=strpathtotextfile;Extended Properties=;HDR = YES;FMT = Delimited" To this, objconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=strpathtotextfile;Extended Properties=""Text;HDR=YES;FMT=Delimited""" Update: noticed an issue with...
Ado multiple range select
Something like this, if you don't have column headers: szSQL = "SELECT [F1], [F2], [F30] FROM [" SheetName & "$F1:AI1000];" where F1, F2 etc indicates Field1, Field2 and so on. If you do have column headers use those in place of the F1 and F2....
SQL Server Native Client 11 ADO connection string for MS SQL Server 2014 with integrated login
sql-server,iis,asp-classic,database-connection,ado
Got the following connectionstring to work: strConn = "Provider=SQLOLEDB;DataTypeCompatibility=80;Integrated Security=SSPI;MARS Connection=False;Initial Catalog=myDB;Data Source=myServer" ...
ADO parameter query is inserting the same data into each row
sql,excel,vba,ms-access-2010,ado
Instead of addind all parameters again and again in the for-loop try to create the named parameter only once and change its value only before Excecute() is called. Example for 'SaleNumber': With adoComm Set .ActiveConnection = adoConn .CommandText = "INSERT INTO Sales([SaleNo]) VALUES(?)" .Parameters.Append adoComm.CreateParameter("SaleNumber", Type:=adInteger) For RecordRow = 2...
VBA - Using Typeof … Is ADODB.Recordset Results in Compile Error
ms-access,access-vba,ado,late-binding,adox
Just to recap, without an ADO reference included in your project, you get a compile error at this line: If TypeOf adoRsColumns Is ADODB.Recordset Then Without the reference, VBA doesn't recognize ADODB.Recordset The situation is basically the same as if you tried to declare Dim rs As ADODB.Recordset without the...
Data type mismatch when calling ADO Open Method with a Session variable
The error was in the string I used as SQL query. I had ' around numbers and the engine didnt read them correctly. I just had to remove them and the problem's gone.
Executing msdb.dbo.sp_send_dbmail via ADO from within Excel's vba
As an example if you want to avoid using a command object: Dim sSQL as String sSQL = "EXEC msdb.dbo.sp_send_dbmail " & _ @recipients='[email protected]'," & _ @subject='xxx'," & _ @body='yyy'" Call r.Open(sSQL,c) People will huff and puff about SQL injection but thats the least of your worries....