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)...
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 =...
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-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.
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);...
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") ...
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...
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...
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...
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...
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...
.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 =...
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...
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-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...
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...
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.
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;...
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...
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 =...
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...
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...
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...
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....
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.
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...
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...
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...
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...
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....
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...
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...
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...
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]...
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...
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...
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...
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")...
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; ...
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...
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...
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...
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....
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...
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" ...
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...
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....
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...
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.
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...
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 ...
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...
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...
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...
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....
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...
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...
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...
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...
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...
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 " _...
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,...