php,sql-server,stored-procedures,adodb,sqlsrv
As per my investigation it seems that ADODB driver for MSSQL does not support stored procedure with parameters. Use sqlsrv. $serverName = "XX.XX.XX.XXX"; $connectionInfo = array( "Database"=>"XXXXX", "UID"=>"XXXXX", "PWD"=>"XXX","ConnectionPooling" => "1","MultipleActiveResultSets"=>'0'); $conn = sqlsrv_connect( $serverName, $connectionInfo ); if( $conn === false ) { die( print_r( sqlsrv_errors(), true)); } $stmt =...
sql-server,vbscript,asp-classic,adodb
I added a comment earlier but seen as though everyone has jumped on the answer wagon here goes; SELECT Code FROM AS_Table WHERE NumAtend = 1234 AND LEN(Code) > 0 By adding the LEN(Code) > 0 will cause only results with Code with a length greater then 0 to be...
sql,excel,vbscript,asp-classic,adodb
So i finally found the problem...It had nohing to do do with my SQL or the cell having a list,the problem was that the cell was marked(in the excel sheet) as a DATE but the List was a String so whenever I tried to write my string on it gave...
Make sure you have the reference to "Microsoft ActiveX Data Objects Library" set in your project (Under the Project > References menu): If you do not have this set, the runtime environment doesn't know about the ADODB library so it throws a not defined error....
.net,datatable,vb6,adodb,recordset
.Net defaults to disconnected recordsets, but VB6 doesn't. The recordset that you have opened in your code is a Dynamic recordset, which is incapable of operating without an open connection. The dynamic cursortype means that all changes to your recordset are posted immediately to the database. When you closed your...
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...
Problem was in SQLRequst string : SQLRequest = "SELECT * FROM tblStack WHERE StackID='XXXXX'" It needed ''....
sql,vba,excel-vba,ms-access,adodb
You're using the ACE OleDb provider to interact with the spreadsheet. That means you can use only SQL features that provider supports for your data source. IF EXISTS is not supported in this situation. However, there may be an even more basic problem. I don't believe it's possible to alter...
sql,vbscript,asp-classic,adodb
Short answer to your question, yes, it is necessary if you want to specify the ADODBCommand.CommandTimeout to a value other than its default setting (30, I believe). From MSDN: The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same...
The problem here is that the Excel driver is converting the strings to ANSI symbols, for some reason. Some "clever" code is converting the Ă character (258) to A (65). If you have the JET drivers with the ISAM Excel driver installed, then the following connection string will use them:...
You need to execute asynchronously and wait for it to complete. Please note that it will slow you down if you wait for each command to execute until triggering the next. cn.Execute sqlString, , adAsyncExecute Do While cn.state = adStateOpen + adStateExecuting ' wait for command to finish ' perhaps...
I figured it out... I did not change the query to reflect the fact that I actually have field names now. So, F2 and F3 become [Fund ID] and [Fund Name]. (F1 is still not named in my data.) I am not bright. Thanks for knocking the screw loose StackOverflow!!...
excel-vba,connection,adodb,recordset
Is the "strConnect" parameter intentionally commented out in objConnection.Open 'strConnect Uncomment that parameter and things will hopefully work edit: also strSQL = "SELECT * FROM [Sheet$3] & ;" is wrong. It should be strSQL = "SELECT * FROM [Sheet3$];" (the $ sign was in the wrong place and there was...
excel,excel-vba,adodb,recordset
I haven't had this exact problem, but I've found that the recordcount property on an ADODB recordset is hit or miss. Your best bet is to rewrite the loops like: recordset.movefirst While Not recordset.eof <your stuff with your record> recordset.movenext Loop Also, to test that there are records in your...
sql,ms-access,asp-classic,adodb
Try changing to: Response.Write(Recordset.Fields("Song")) ...
excel,vba,excel-vba,database-connection,adodb
The answer is no... and yes. There is nothing built in to keep track of how many ADODB connections you have open to a particular database. You would have to keep track of this yourself if it is a requirement. You could do this by adding each of your connections...
The Long data type can hold a 32-bit integer. It isn't an object reference, so don't use Set. Dim userId As Long userId = RS.Fields(0).value For more on what is and isn't an object, see Data Type Summary for VBA....
I performed some tests and found that ADODB does in fact still run SQL Update statements. No voodoo as feared.
Here is a basic example, the magic line is; Set rs = rs.NextRecordSet Pseudo coded so apologies if there are some typos. Dim cmd, rs, sql, connstring Set cmd = Server.CreateObject("ADODB.Command") connstring = "your connection string here" sql = "" sql = sql & "SELECT col1, col2 FROM table1 WHERE...
if i dont remember wrongly, use Affected_Rows( ) ...
...CreateParameter(NAME,TYPE,DIRECTION,SIZE,VALUE) NAME: name of the parameter TYPE: must be a data type. 200, for example, is a varchar. DIRECTION: Input, Output, Retrurn Value,... 1 is Input. SIZE: Size of type. In your first example, 50 characters VALUE: The data. You can use more friendly values if include adovbs.inc If so,...
It looks like you are not specifying the fields/columns in the join. Both currAddress and curAddress2 look like tables. The SQL should be something like: strSQL = "SELECT * FROM [Table1] " & _ "LEFT JOIN [Table2] ON [Table1].[Field] = [Table2].[Field];" Are Indeks and Indeks2 your field names? If so,...
sql,sql-server,excel,vba,adodb
Sounds like this should be its own Subroutine. You can call the Sub whenever you need it by passing in the procedure to execute, and the worksheet to put the results on. Public Sub Macro1(byval storedProc as string, byval ws as worksheet) ' Create a connection object. Dim cnPubs As...
sql,vba,connection-string,adodb,recordset
The connection string is blank because a wrong parameter is being sent to GetData. Take a look at these code snips: SourceSheet is the second parameter in the call, but you pass it as the first parameter here: ' Here's the call to GetData GetData("export.csv" ,"A1:BE", ... Filename goes in...
State Property (ADO) indicates for all applicable objects whether the state of the object is open or closed. If the object is executing an asynchronous method, indicates whether the current state of the object is connecting, executing, or retrieving. Returns a Long value that can be an ObjectStateEnum value. The...
vba,ms-access,database-connection,adodb
Change my con.open from con.Open _ "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _ "Dbq=" & Loc & _ "Exclusive=1;" & _ "Uid=admin;" & _ "Pwd=;" to con.Open _ "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _ "Dbq=" & Loc & ";" And it seems to work now. ...
sql-server,excel-vba,combobox,adodb,recordset
There isn't any need to loop: If Not rst.EOF then Me.ComboBox2.Column = rst.getrows() should be enough to put the whole recordset into the control. GetRows returns an array transposed from the way you might expect, which is why I used .Column and not .List...
WHERE goes before GROUP BY <> is an equivalent to != so you should pass value to compare your [report$].column with. Probably the reason for No value given for one or more required parameters. is a misspelled field name. For example : WHERE [report$].column <> '' GROUP BY [report$].column,[report$].columnIndex...
mysql,sql,postgresql,join,adodb
Since you can't CAST to INT in the same way for each database due to data types, you can cast your numeric field to CHAR: CAST(a.numeric_Field AS CHAR(5))` = b.stringfield That will work on Postgresql, MySQL, SQL Server, not sure about others....
You need to concatenate the filename into the string using & to concatenate so for example: strQuery = "SELECT * FROM [" & filename & "$C2:C100]" ...
Put square brackets around position (seems to be a reserved word). Remove the IMEX=1 from the connection string, or you get an error "operation must use an updateable query" Revised code: Sub Tester() update "update [Regional Personnel$] set name='Ada Lovelace'," & _ " [Phone]='(303) 555-1337', [lan id]='ADL3', [position]='Engineer' where...
asp.net,vb.net,asp-classic,migration,adodb
You do not use ADODB in DotNet. Technically, you can, but that's not the way to do. You use ADO.Net, IDataReaders, DataSets (loose or strongly-typed, I prefer strongly-typed). ASP.NET is not ASP. Don't feel bad, I was trying the same thing you are (albeit, back in 2002). Until someone told...
c#,visual-studio,ms-access-2010,adodb,adox
You are declaring the text fields as ADOX.DataTypeEnum.adVarChar but all Access Text fields are capable of storing Unicode so you need to declare them as ADOX.DataTypeEnum.adVarWChar.
This might do the trick: Just use an array of connection strings. From this you create an array of connections. Then you can iterate over this array and send your commands to the separate databases. dim connectionStrings(1) dim connections(1) dim curConn connectionStrings(0) = "Provider=sqloledb;Server=.\EXPRESS2012;Database=master;uid=youruser;pwd=yourpwd" connectionStrings(1) = "Provider=sqloledb;Server=.\EXPRESS2012;Database=model;uid=youruser;pwd=yourpwd" for curConn =...
When you set HDR=NO the column titles from the excel table will be ignored and it will be used internal names. See older answer: c#, oledb connection string issue
sql,sql-server,excel,vba,adodb
This one will fail because you are appending extra text after the "Trusted_connection" parameter. If you use a trusted connection, you don't need a username or password, and the syntax is different for SQLOLEDB than with {SQLServer} (it should be Integrated Security=SSPI;. SqlConn.Open "Provider=SQLOLEDB;Data Source=[SeverName];Initial Catalog=[DBName];Trusted_connection=yes;", "[User]", "[Pass]" You also...
It is recommended to run large queries via recordset and not like sheet query table. To understand the IN operator see this. This is example how you can build and run your query: Sub BuildAndRun_Sane_Query() ' Create IN_string: Dim SN, S_In As String, s_query As String For Each SN In...
Looks like you're not clearing the value of "value". Just before you close your recordsets, use: value = "" ... to make sure it's empty. Better still, use different variables between different code blocks....
"SELECT TOP 25 * FROM " & "data.CSV" & "ORDER BY " & "ELAPSED" & DESC " Is the one that looks closest to working except it's missing some spaces. What language is this in? I don't know what the ampersands and quotes are all meaning, but if you can...