Adodb Recordset Problem

Mar 31, 2006

I am trying to access a table that I know exists and has data. But, when I create a recordset and check for RecordCount, I get a result -1 (no records). When I access the same table (using the same program), it reports (and I can view in a dbgrid) 752580 records exist.

Here's some of the code:

The table is originally copied from another database; I use the following code to be sure the previous connection is closed before proceeding.

  If Not adoRS Is Nothing Then
    If adoRS.State = adStateOpen Then adoRS.Close
    Set adoRS = Nothing
  End If
  If Not DbConn Is Nothing Then
    If DbConn.State = adStateOpen Then DbConn.Close
    Set DbConn = Nothing
  End If

Then a new connection (it works) is opened to access the database with the  copied table:

   strDbConn = "Provider=SQLNCLI;Integrated Security=SSPI;" & _
  "Persist Security Info=False;Database=" & strDbName & ";" & _
  "AttachDBFileName=" & DbPath & ";Data Source=.sqlexpress;" & _
  "User Instance=True"

Next I tried to create the recordset:

  Set adoNewRS = New ADODB.Recordset 'Set OHLC recordset
  Set adoNewRS.ActiveConnection = DestDbConn
  adoNewRS.Open TableName, DestDbConn, adOpenDynamic, adLockOptimistic

Next I try to get the RecordCount:

  NumRecords = adoNewRS.RecordCount

At this point, NumRecords (and adoNewRS.RecordCount) = -1 (even tho I know there are 752580 records in the table).

In the adoNewRS.Open statement, I also tried using the following sql statement:

sSQL = "SELECT * FROM TableName ORDER BY [DateTime];"

It also returns a recordcount = -1.

Anybody have clue?

View 1 Replies


ADVERTISEMENT

Script Component Has Encountered An Exception In User Code - Object Is Not An ADODB.RecordSet Or An ADODB.Record

Nov 26, 2007

hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error
using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Data.SqlClient;

using System.Data.OleDb;



[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

IDTSConnectionManager100 connMgr;

OleDbConnection sqlConn = null;

OleDbDataReader sqlReader;



public override void AcquireConnections(object Transaction)

{

connMgr = this.Connections.MyConnection;

sqlConn = (OleDbConnection )connMgr.AcquireConnection(null);

//sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

}

public override void PreExecute()

{

base.PreExecute();

/*

Add your code here for preprocessing or remove if not needed

*/

OleDbCommand cmd = new OleDbCommand("SELECT CustomerID,TerritoryID,AccountNumber,CustomerType FROM Sales.Customer", sqlConn);



sqlReader = cmd.ExecuteReader();

}

public override void PostExecute()

{

base.PostExecute();

/*

Add your code here for postprocessing or remove if not needed

You can set read/write variables here, for example:

Variables.MyIntVar = 100

*/

}

public override void CreateNewOutputRows()

{

/*

Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

*/

System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();

//SqlDataAdapter oLead = new SqlDataAdapter();

DataSet ds = new DataSet();



System.Data.DataTable dt = new System.Data.DataTable();

//DataRow row = new DataRow();

oLead.Fill(dt,this.Variables.ObjVariable);





foreach (DataRow row in dt.Rows)

{

{

Output0Buffer.AddRow();

Output0Buffer.CustomerID = (int)row["CustomerID"];

Output0Buffer.TerritoryID =(int)row["TerritoryID"];

Output0Buffer.AccountNumber = row["AccountNumber"].ToString();

Output0Buffer.CustomerType = row["CustomerType"].ToString();

}

}



}

}
the error
Script component has encountered an exception in user code
Object is not an ADODB.RecordSet or an ADODB.Record.
Parameter name: adodb
at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String
srcTable, Boolean multipleResults)
at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs,
Int32[] outputIDs, PipelineBuffer[] buffers)

thanks
kedarnath

View 4 Replies View Related

Receive Adodb.Recordset From MSMQ?

Feb 6, 2008

I've been trying different things to "READ" the recordset from the "Message Queue". I can read it but with some weird characters. I've tried

ActiveXMessageFormatter

BinaryMessageFormatter

XMLMessageFormatter


So, far I have no luck.

MessageQueue msgQ3 = new MessageQueue("SERVERNM\" + msg.Label, false);

Message msg3 = new Message();

msg3.Formatter = new ActiveXMessageFormatter();
msg3 = msgQ3.Receive(new TimeSpan(0, 0, 30));

byte[] b = new byte[msg3.BodyStream.Length];
msg3.BodyStream.Read(b, 0, (int)msg3.BodyStream.Length); System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();

returnVal = enc.GetString(b);



RESULTS:
I try to convert it to String and then deserialize later but I'm getting some junk.
<?xml version="1.0" encoding="utf-8" ?>

<string xmlns="http://tempuri.org/">5??m.????????#?_?XTG!???????#?_?Xg??c??????? ?<???m????_?X  |?"???????Dw=?????"I?<???m????_?X21? Reply_Code?$??5? MultiEntries?$??7?dName?.$??3? Page_Number?$??1? Number?$??3? Status_Code?$??/?_Name?.$??5? _Address?+$??/? B_City?$$??1? _State?$??-? _Zip?$??A? Bank_Telephone_Num?"$?? ??11  033000333YHONDA2nd street xavier VA 326200000(555) 555-5500</string>

View 1 Replies View Related

Read Table Description Into ADODB.Recordset.

Aug 20, 2003

How do I read the Tables description into ADODB.Recordset and then recreate the Tables+description into a new database from the ADODB.Recordset.

View 1 Replies View Related

Could I Use SQL Select Case .. When... In ADODB.Recordset().open

Jul 20, 2005

Is there any SQL Error?Or I have to use Select case in VB code to control SQL instead.Thank you for any ans.Nuno

View 4 Replies View Related

Script Task: ADODB Connection And Recordset

Jul 19, 2006

Hi,

I used adodb connection and recordset in script task. but i have an error saying adodb is not defined. how do i add it to reference? or, is adodb can run in script task or only ado.net?

cherrie

View 2 Replies View Related

Temporary Tables Adodb Recordset Error

Feb 23, 2008

Hi,

I am using the following code


I use SQLOLEDB Provider
It is not stored porcedure but program code

create new global temporary table with
CREATE TABLE ##tmp123 (...
create and open a recorsset to populate it as direct table
at the open stage I get the following error: Invalid object name '##tmp123'

How can I get it working?

View 2 Replies View Related

Vista ADODB Recordset Can't Create ActiveX Object

Jul 19, 2007

Somehow, (I think the user ran a registry cleaner) on Vista, I can no longer create an adodb recordset object.



The app is a VB6 app that works fine on my own Vista Ultimate, my XP boxes and about everthing else prior, but not on the one Vista Home Premium.



I get Error 429 ... Cannot Create ActiveX Object when creating a new adodb.recordset object.



I guess what I need is a way to repair the Vista Home-P machine without having to wipe it.



I see that MDAC_Typ is not recommended as a fix.



I'm at a loss on this one.



View 3 Replies View Related

Unable To Cast COM Object Of Type 'ADODB.CommandClass' To Interface Type 'ADODB._Command'

Dec 20, 2006

I have an application which runs successfully on a couple of my customer's machines but fails on a third. It seems to fail when opening the database:

Unable to cast COM object of type 'ADODB.CommandClass' to interface type 'ADODB._Command'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{B08400BD-F9D1-4D02-B856-71D5DBA123E9}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false; Initial Catalog=lensdb;Data Source = SQL

Before I got this error I was getting another problem (sorry didn't make a copy of that error's text) that made me think that adodb.dll simply wasn't loaded/registered. I got rid of that error by copying my adodb.dll onto the third machine and running gacutil /i. There is now an entry in winntassemblies for adodb.

Just in case you think it could be an obvious registry problem: when I started getting the current error I thought that maybe the registry needed updating and I merged the following lines into onto the target machine (from my dev machine):

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}]
@="_Command"

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid32]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}TypeLib]
@="{EF53050B-882E-4776-B643-EDA472E8E3F2}"
"Version"="2.7"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}]
@="_Command"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid32]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}TypeLib]
@="{EF53050B-882E-4776-B643-EDA472E8E3F2}"
"Version"="2.7"


but, no change alas.

All three machines are running Windows 2000.

Any advice would be appreciated.

Thanks in advance,

Ross

View 1 Replies View Related

ADODB And ActiveX

Jan 19, 2000

Hi,

Am having trouble writing to a table on the SQL 7 Server database, using a DTS ActiveX script.

When I try a .ADDNEW function, the following error comes up.
"The opperation required by the application is not supported by the provider."

The line preceding the .ADDNEW are as follows.
-----
SET Conn=CreateObject("ADODB.Connection")
SET RS = CreateObject("ADODB.RecordSet")
Conn.ConnectionString = "PROVIDER=SQLOLEDB;DATABASE=DataIn;User ID=sa;Password="
Conn.Open
RS.Open sqlSites, Conn
----

Q - Whats wrong ?

View 1 Replies View Related

DAO To ADODB Conversion?

Oct 25, 2013

I currently have an access database that is being converted to strictly a Front-End and SQL as the back-end. simple code conversion from DAO to ADODB? I would be very grateful for the infinite wisdom that resides within these boards.

Option Compare Database
Public LngLoginId As Long
Function LogMeIn(sUser As Long)
'/Go to the users table and record that the user has logged in
'/and which computer they have logged in from
Dim Rs As DAO.Recordset

[Code] ....

View 9 Replies View Related

Errors (ADODB)

Sep 3, 2007



Hi ! I'm using adodb in my program (Visual Basic)

I'm controlling the records with sql in a timer..Timer Interval = 7000

But i am getting some errors sometimes


Connectionwrite(send())

connectionwrite(recv())

It's often working non-problem but sometimes i am getting above errors

What are theese? and how can i solve this problem?

Thanks.

View 1 Replies View Related

ADODB To TextBox

Apr 5, 2007

First time poster, I am using MS Access and I have used the following code to get some data. It is as follows:






Code Snippet

Private Sub FillGUI()
On Error Resume Next



Dim myRS2 As New ADODB.Recordset



myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

MsgBox (myRS2.GetString)
myRS2.MoveFirst

End Sub





and I have the following output:



http://www.angelfire.com/oh5/ohiostate120/untitled1.JPG



This is what I want. However I need this to be in a text box so I have the following code:






Code Snippet

Private Sub FillGUI()
On Error Resume Next



Dim myRS2 As New ADODB.Recordset



myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

Me.txtEmployee.SetFocus
Me.txtEmployee.Text = myRS2.GetString
myRS2.MoveFirst

End Sub






And i get this:



http://www.angelfire.com/oh5/ohiostate120/untitled2.JPG




How can I get the text box format to look like the msgbox format? Thanks.........

View 2 Replies View Related

Textbox With ADODB

Apr 5, 2007

First time poster, I am using MS Access and I have used the following code to get some data. It is as follows:






Code Snippet

Private Sub FillGUI()
On Error Resume Next



Dim myRS2 As New ADODB.Recordset



myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

MsgBox (myRS2.GetString)
myRS2.MoveFirst

End Sub





and I have the following output:



http://www.angelfire.com/oh5/ohiostate120/untitled1.JPG



This is what I want. However I need this to be in a text box so I have the following code:






Code Snippet

Private Sub FillGUI()
On Error Resume Next



Dim myRS2 As New ADODB.Recordset



myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

Me.txtEmployee.SetFocus
Me.txtEmployee.Text = myRS2.GetString
myRS2.MoveFirst

End Sub






And i get this:



http://www.angelfire.com/oh5/ohiostate120/untitled2.JPG




How can I get the text box format to look like the msgbox format? Thanks

View 1 Replies View Related

ADODB Connection With SQL2000SP3

Jul 23, 2005

my code is using ADODB connection to connect to SQL (Virtual) Server.the way it being done is (c++):ADODB::_ConnectionPtr m_dbConn;ADODB::_RecordsetPtr m_dbRst;m_dbConn.CreateInstance(__uuidof(ADODB::Connection ));m_dbRst.CreateInstance( __uuidof( ADODB::Recordset ));m_dbConn->ConnectionString=( L"DSN=mydsn" );m_dbConn->Open("","sa","",-1);lately after installing SP3 over SQL2000, it was impossible to connect- the error message showed: login failed for user 'null)'. reason: notassociated with a trusted sql server connection.so i changed the connection string to:m_dbConn-> ConnectionString =(L"DSN=mydsn; UID=sa; PWD=;");m_dbConn->Open("","","",-1);and now it works !!what is the reason for that ?what in sql SP3 interrupt for this kind of connection ?what is the difference ?

View 1 Replies View Related

ADODB.Command Question

Sep 2, 2006

How to using Adodb.command to get Access file data??
i using this script:

dim connection as new adodb.connection
dim command as new adodb.command
dim recordset as new adodb.recordset

connection.open("connectionstring")
command.activeconnection=connection
command.commandtext="querystring"
recordset=command.execute

but,the Recordset is empty.how to using to get data on Microsoft Access database??



thank!!!!

View 1 Replies View Related

Recover Adodb::_ConnectionPtr

Aug 22, 2007

Most of the the time my connection is created and closed fine.

However, sometimes I get Connection failures (these happen often because I am connecting to the server using a VPN connection). Once I get the connection failure, sometimes I cannot successfully recreate the connection until I kill the application and restart it. Below is my open and release code. I call release anytime the connection fails or a stored procedure fails. If you see anything wrong with it the code below that would prevent the connection from recovering please let me know.

Thanks in advance.




Code Snippet
adodb::_ConnectionPtr m_spConnection;

HRESULT InitializeConnections()
{

HRESULT hr = E_FAIL;
if (m_spConnection && m_spConnection->GetState() == adodb::adStateOpen)
{

hr = S_OK;
}
else
{

if (m_spOperationEvents)
{

const _bstr_t c_bstrEmpty(_T(""));
try
{

CComBSTR bstrInit;
hr = get_DbInitializationString(&bstrInit);
if (!m_spConnection)
{

hr = m_spConnection.CreateInstance(adodb::CLSID_Connection);
}
if(SUCCEEDED(hr))
{


m_spConnection->ConnectionTimeout=30;

hr = m_spConnection->Open((LPCWSTR)bstrInit, c_bstrEmpty, c_bstrEmpty, -1);
}
}
catch (_com_error ce)
{

hr = E_FAIL;
CString strArgs;
strArgs.Format(_T("x%x %s "), ce.Error(), (LPCWSTR)ce.Description());
m_strLastError = GetTranslatedString(eMsgConnectionError, strArgs);
}
catch(HRESULT hrException)
{

hr = hrException;
}
catch(...)
{

hr = E_FAIL;
m_strLastError = GetTranslatedString(eMsgUnknownConnectionError, NULL);
}



}

}
if (!SUCCEEDED(hr))
{

ReleaseConnections();
}
return hr;
}
// Queued thread job to release thread resources
HRESULT CSptAggregation::ReleaseConnections()
{

HRESULT hr = E_FAIL;
try
{

if (m_spConnection)
{

m_spConnection->Cancel();
if (m_spConnection->GetState()== adodb::adStateOpen)

m_spConnection->Close();
}
m_spConnection = NULL;
}
catch (_com_error ce) { hr = E_FAIL; }
catch(...) { hr = E_FAIL; }
return S_OK;
}

View 1 Replies View Related

ADODB Vesion 7.0.3300.0 In Vb.net

Jun 23, 2006

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John



View 3 Replies View Related

ADODB And Mirrored SQL-Server

Aug 24, 2006


I did setup a Mirrored Database. Connecting from it using ADO.NET works well. It goes to the Mirror if the Principal fails.

But ADODB does not work. I get the error following error:
80004005 Invalid connection string attribute

When trying to connect to the DB in case the principal failed and the mirror is active. (MyProductiveDB is in failover state)

What do I do wrong?


Here is the code:

ADOConn = New ADODB.Connection
ADOConn.Open(CS)

CS is my Connections-String:
"Provider=SQLNCLI.1;Data Source=MyProductiveDB;Failover Partner=MyMirror;Initial Catalog=MyCat;Persist Security Info=True;User ID=MyUser;Password=xxxxxx;Pooling=True;Connect Timeout=5;Application Name=MyApplic"

Remark: When I try to add "Network Library=dbmssocn" to the connection String, I get the same error, even if the Principal is active.

Your help is very much appreciated.
Beat

View 4 Replies View Related

ADODB CommandText Length

Sep 27, 2007

Hi,

I am trying to write a Macro in Excel which would connect to the database and fetch the data for me.

I am using a SQL Query and pass it to a ADODB Command object as adCmdText. The SQL Query is very big, length could be 2500 characters.

I just have read access to the database and do not have a choice to create a Stored Procedure to return a resultset.


When i try to open a recordset with the query, i get a Automation Error.

Is there a Limit on the length of the string i can pass as CommandText?

Regards,
Vikram

View 4 Replies View Related

Timeout Expired With ADODB

Apr 29, 2008

Hi All,

I am using SQL 2005 DB and i connect it using PHP. I have one Store procedure in SQL which requires 1.5 minutes to get result.

I'm always getting error message in web after 30 seconds that Microsoft OLEDB provider for ODBC drivers, timeout expired.

I increased time in php.ini but the error comes from SQL Server. Can you please guide me to increase the time in SQL. so that timeout doesn't expired.
This will help me a lot.

THanks in advance.
GB.

View 3 Replies View Related

Connecting To SQL Data Base With ADODB

Aug 20, 2007

Hi I need help regarding ADODB Connection that i have used in connecting database in my web application, do tell me is this connection type is ok? or I need to switch to ADO.NET Connection.

View 1 Replies View Related

ADODB.Command Error '800a0cb3'

Feb 19, 2004

I have posted various questions on the microsoft ng trying to identify the cause of this error.

ADODB.Command error '800a0cb3'
Object or provider is not capable of performing requested operation.

I have MDAC 2.8 installed locally on my machine.

Via IIS I created a virtual directory via IIS that points to my ASP files on c:

Via the SQL Server IIS for XML configuration utility I created a virtual directory with a different names that points to the same directory as that created via IIS.

The SQL database is on a different machine and I connect via the OLEDB DSNless connection string.

I used a ADODB.Stream to transform the XML against the XSL but I couldnt get it to work. To simplify things and work towards a solution I inserted the code into my ASP from the MS KB article Q272266 (see below). I amended the ms code to change the connection code and call a stored procedure that exists on the database. The ms code gives the same error as my original code.

I tried changing the CursorLocation to server and client but the results were the same.

I put a SQL trace on the DB to determine if the stored procedure gets ran, it does not.

If I run the following in the URL it works:

If I run http://localhost/p2/?sql=SELECT+*+FROM+tblStatus+FOR+XML+AUTO&root=root&xsl=tblStatusDesc.xsl it works.
If I run the xml template it works: http://localhost/p2/xml/test.xml

The two lines above run. My IIS server uses a virtual directory called dev, so when I run the ASP I type http://localhost/DEV/secure/aframes.asp the IIS virtual directory creted by sql server is called p2 but has the same source code directory.

Here is the MS code amended as described above that does not work.

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblStatus for XML Auto</sql:query></ROOT>"
'*************************************************

Dim txtResults ' String for results
dim CmdStream ' as ADODB.Stream


sConn = "Provider=SQLOLEDB;Data Source=[name of sql server];UId=sa; Pwd=xxxxx; Initial Catalog=[DB Name]"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn

adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

Response.End

View 8 Replies View Related

ADODB.Command Error '800a0d5d'

Jun 20, 2008

Hello all,
Need help with this error message:

ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/forum/pmsend.asp, line 146


Any input appreciated
Thanks

View 2 Replies View Related

ADODB Command (Stored Procedure)

Jun 4, 2007

Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.

View 8 Replies View Related

ADODB.Connection Error '800a0e7a'

Dec 27, 2007

Hi guys,

I am encountered with this error and can't seem to overcome it. Please help


ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

/Mod.asp, line 148

It is connecting to a server.

Thanks Guys!!!

View 1 Replies View Related

Access Is Denied: 'Interop.ADODB'.

Mar 28, 2006

I am using a com component in my asp.net programme and it was working fine for many days . now I am getting following error .



Source Error:



Line 196: <add assembly="System.EnterpriseServices, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 197: <add assembly="System.Web.Mobile, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 198: <add assembly="*"/>Line 199: </assemblies>Line 200: </compilation>

Source File: c:windowsmicrosoft.netframeworkv1.1.4322Configmachine.config Line: 198

Assembly Load Trace: The following information can be helpful to determine why the assembly 'Interop.ADODB' could not be loaded.





=== Pre-bind state information ===LOG: DisplayName = Interop.ADODB (Partial)LOG: Appbase = file:///e:/inetpub/wwwroot/SAPTRainingLOG: Initial PrivatePath = binCalling assembly : (Unknown).=== LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODBLOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///e:/inetpub/wwwroot/SAPTRaining/bin/Interop.ADODB.DLL.LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODB, Version=2.6.0.0, Culture=neutral, PublicKeyToken=null





Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300


How this can be solved ? Please help

View 1 Replies View Related

Access SQLServer Express Using ADODB

Mar 31, 2007

Hi,

How should I do to access a table in SQLServer Express database using VB.NET and ADODB?

Thanks!

View 6 Replies View Related

Apostrophe In Comment Confuses ADODB

Jun 5, 2007

We just encountered an odd failure in ADODB. If it gets an SQL query with a comment embedded in it, and the comment has an apostrophe as one of the characters, ADODB gets confused as it tries to plug in parameters for placeholders later in the query. For example:





SELECT id, name

FROM doc_type /* this won't work! */

WHERE name LIKE ?

If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works.

Is this a known bug (couldn't find it in the KB)?

Thanks,
Bob Kline

View 28 Replies View Related

Sometime ADODB Return No Result To Excel

Aug 3, 2007

Hi all, I have a strange situation.

I use VBA to call a MSSQL server 2000 to get data from one of the DB inside, using ADODB.

My problem is, the DB is around 20GB, and if we get data from it for few days, say 7 days, is fine. When I come to trying get more data like 1 month, the excel returns no data after waiting for about 30 sec.

The query is actually calling a stored procedure on the server side to return data.

I have add connectiontimeout=10000 to my connection string but still not working...

I run the same execute statement in query analyzer on the same machine using the Excel and it is fine. Around 2 minute waiting I can see my data. But in Excel, never.

Anyone can offer some helps to me? Thanks alot.

View 4 Replies View Related

ADODB 2.8 - SQL Insert Statement - Using Parameters

Nov 20, 2007

Hi there,
I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID.
But instead of it it returns error:

Run-time error '-2147217900 (80040e14)
Must declare the scalar variable "@ii_file"

Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
"?
I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side.
Thanks!

Darek


Public Sub save_import()
Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = ado_conn 'an existing connection
.CommandType = adCmdText
.CommandText = "insert into import_main (ii_file, id_file, oi_file, od_file, folder, import_desc, id_client,basis_of_study, id_is, project_leader, urisk_model_basis, as_at_date, extent_benchamark) " & _
"values (@ii_file, @id_file, @od_file, @od_file, @folder, @import_desc, @id_client, @basis_of_study, @id_is, @project_leader, @urisk_model_basis, @as_at_date, @extent_benchmark) " & _
"select @id_im = @@identity"
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)

.Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
.Parameters.Append .CreateParameter("@id_file", adVarChar, adParamInput, 255, Me.cbo_id)
.Parameters.Append .CreateParameter("@oi_file", adVarChar, adParamInput, 255, Me.cbo_oi)
.Parameters.Append .CreateParameter("@od_file", adVarChar, adParamInput, 255, Me.cbo_od)
.Parameters.Append .CreateParameter("@folder", adVarChar, adParamInput, 1000, Me.txt_folder)
.Parameters.Append .CreateParameter("@import_desc", adVarChar, adParamInput, 255, Me.txt_import_desc)
.Parameters.Append .CreateParameter("@id_client", adBigInt, adParamInput, Me.cbo_client)
.Parameters.Append .CreateParameter("@basis_of_study", adVarChar, adParamInput, 255, Me.txt_basis_of_study)
.Parameters.Append .CreateParameter("@id_is", adSmallInt, adParamInput, Me.cbo_status)
.Parameters.Append .CreateParameter("@project_leader", adVarChar, adParamInput, 255, Me.txt_project_leader)
.Parameters.Append .CreateParameter("@urisk_model_basis", adVarChar, adParamInput, 1000, Me.txt_urisk_model)
.Parameters.Append .CreateParameter("@as_at_date", adDate, adParamInput, CDate(Me.txt_as_at_date))
.Parameters.Append .CreateParameter("@extent_benchmark", adVarChar, adParamInput, 1000, Me.txt_extent_benchmark)
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)

.Execute rec_affected, , adExecuteNoRecords
If rec_affected > 0 Then
Me.cbo_import = .Parameters.Item("@id_im")
End If

End With


End Sub

View 3 Replies View Related

Problem Inserting Data To A Database Using ADODB

Nov 19, 2004

Hi! I have a vb.net program that writes data to an SQL Server database using ADODB. The problem is if I put the code that writes data to the database in between BeginTrans() and CommitTrans(), the database is not updated. Here is the flow of my program:

Dim connection as ADODB.Connection
'setup the connection to the SQL Server database here

connection.BeginTrans()

InsertData() ' --> data is written to the database
' this function works properly

connection.CommitTrans()



If I comment out the BeginTrans() and CommitTrans() functions, the data is properly inserted into the database.

Does an SQL Server database requires special settings to support transactions?

Please help.
And thank you in advance.

View 3 Replies View Related

ADODB.Connection Insert Into Sql Server Table

Jun 16, 2006

Hi Experts,


I’m trying to insert a record from Excel [Sheet2$] from Range (“A2”) to Range (“E2”) into a table on MS SQL server:
But I get the following error:

Error-2147217900(The INSERT INTO statement contains the following unknown filed name:’F1’).


Here is the ADODB.Connection:
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’’’’’’’’’’’’’’
Sub DB_con1()


Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
On Error GoTo test_Error

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:Book1.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=titan;Database=dev;" & _
"UID=sa;PWD=welcome1@].abk_import " & _
"Select * FROM [Sheet2$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

On Error GoTo 0
Exit Sub

test_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"

End Sub


‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’


Thanks in advance for any help.

Regards,

Abraham

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved