|
I'm missing something and would appreciate a pointer to what might be wrong. The following code
CString strSQLSelectID = "SELECT PARAM_COUNT, SELECT_STRING FROM P2P.SELECT_SOURCE_COMPLETE";
CString strParamCount;
CString strSelectString;
CRecordset* pSet = new CRecordset(m_DB);
if ( pSet->Open(CRecordset::forwardOnly, strSQLSelectID, CRecordset::readOnly) )
{
pSet->GetFieldValue("PARAM_COUNT", strParamCount);
pSet->GetFieldValue("SELECT_STRING", strSelectString);
}
results in an exception being thrown in the first call to GetFieldValue(). The details of the error are
Error: GetFieldValue operation failed on field 0.
Invalid cursor state
State:24000,Native:0,Origin:[Microsoft][ODBC Driver Manager]
Thinking that the cursor is not being setup correctly I tried more 'ordinary' values on the open statement, like the following
if ( pSet->Open(AFX_DB_USE_DEFAULT_TYPE, strSQLSelectID) )
but this results in the Open call throwing an exception with it's details being
No columns were bound prior to calling SQLFetchScroll/SQLExtendedFetch
State:SL009,Native:0,Origin:[Microsoft][ODBC Cursor Library]
The SQL should return 1 row and when I step through the Open call, I can see that the instance of pSet does allocate memory for two field definitions that corresponds to the two columns being queried. I'm missing something basic, I hope, but not sure what. Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
I am new to programming so this is probably a newbie question. I am using visual studio 2002, and am writing in c#. I just want to connect to a jet 4.0 database. These are the steps that I followed.
1. Opened up Server Explorer.
2. Dragged and Dropped the Connection I wanted onto the Form.
3. Dragged and Dropped a DataAdapter from the toolbox onto the Form.
4. I configured the DataAdapter to query all the columns from the Data Table.
5. I right-clicked the DataAdapter and chose generate dataset.
At this point I closed visual studio. When I Opened it again an error came up which said "Specified cast is not valid". Can anyone tell me why I am getting this error?
Thanks.
|
|
|
|
|
Hmmm... it'd be easier if we could see the code from the InitializeComponent part of the form, but just a quick check, Are you using OleDbConnection and OleDbDataAdapter?
|
|
|
|
|
Good day. I was able to connect to a database server using SQL Server Enterprise Manager. The Server name specified on the tree is JOMARGON(Windows NT). But no server was detected using either Visual Studio .NET and SQL Server's query analyzer.
I highlighted one database (master) on the SQL Server Enterprise Manager and chose 'SQL Query Analyzer' under the 'Tools' menu. It worked. The Title of the Query Analyzer window is
SQL Server Analyzer - [Query- JOMARGON.master.JOMARGON\JM Gonzalez
and below on the status bar, I can see
JOMARGON(8.0) and JOMARGON\JM Gonzalez(52)
But again, I cannot connect manually using Query Analyzer as nothing is listed in the SQL Server drop-down list
Thanks.
|
|
|
|
|
|
Did that already and still cannot connect. By the way, I'm using Windows NT Integrated Security. Pleae help.
|
|
|
|
|
I am able to connect to the server using query analyzer. All I did was restart my PC. The server name was JOMARGON.
But the only problem is, I still cannot connect to the server using Visual Studio .NET's Server Explorer. Please help.
Thanks.
|
|
|
|
|
jomargon wrote:
I still cannot connect to the server using Visual Studio .NET's Server Explorer
Ah, your previous post appeared to indicate that it was Query Analyser you had problems with.
In Visual Studio, right click the "Data Connections" node and select "Add Connection..." If the server name does not appear in the drop down box you can type it in. Type the other details in and click "Test Connection". Hopefully everything should work out.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Did that too. And a server already appeared on the drop-down list. But after choosing the server and clicking on the Select database on the server, it says
--SQL Server does not exist or Access denied.--
But how come I can access the same server through Enterprise Manager and Query Analyzer? I also use Windows Integrated Security to access the server. By the way, thanks for your replies
|
|
|
|
|
Did that too. And a server already appeared on the drop-down list. But after choosing the server and clicking on the Select database on the server, it says
--SQL Server does not exist or Access denied.--
But how come I can access the same server through Enterprise Manager and Query Analyzer? I also use Windows Integrated Security to access the server. By the way, thanks for your replies. Please help.
|
|
|
|
|
I am executing a call to "sp_helptext" from a web service, in order to obtain the text of a stored procedure. The call itself seems to be ok but eventually it throws an exception saying that the specified object does not exist in the database - which is a false statement, i've checked carefully.
I probably do not have enough rights to access something from the ASPNET account. Do i have to login as "sa"? Is this the only solution available?
"though nothing
will keep us together
we can beat them
for ever and ever"
rechi
|
|
|
|
|
You don't give enough information. An exception gives information about what went wrong, What does it say?
Bogdan Rechi wrote:
Do i have to login as "sa"? Is this the only solution available?
Never ever ever under any circumstances run SQL Server through ASP.NET an application with the sa (or any user that has the sysadmin fixed server role). I cannot emphasise enough the large wide gaping security hole you will open in your application if you do.
Also, the SQL Server documentation have this to say about sp_helptext: "Execute permissions default to the public role." That means everyone who can log in to the SQL Server and has access to that database can call the stored procedure. So, you have absolutely no reason what-so-ever for using sa.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
An exception gives information about what went wrong, What does it say?
It's an SqlException saying that 'dbo.a_procedure' does not exist in database 'a_database', where a_procedure is a stored procedure and a_database is the database.
If i call sp_helptext 'a_procedure' from the Query Analizer then it works just fine and prints the text as expected. That's why i was so confused that i even thought about using 'sa'.
Thank you for the reply.
"though nothing
will keep us together
we can beat them
for ever and ever"
rechi
|
|
|
|
|
I believe sp_helptext is in the master database so it needs the full namespace to the stored procedure. Try executing it with the prefix "master.dbo.sp_helptext" like so.
EXEC master.dbo.sp_helptext yourstoredprocedure
If this doesn't work then step two is to type in the servername before "master".
If that doesn't work, then also try
EXEC master.dbo.sp_helptext yourdatabase.yourdatabaseowner.yourstoredprocedure
The first one should be all that's needed, but if it doesn't then try the other two.
|
|
|
|
|
I'm not exactly sure whether what tojamismis said is enough. sp_helptext reads syscomments table of the particular database.
AFAIK, first you need to give ASPNET permission to execute the stored procedure itself. If it's not enough, try giving it read access to the syscomments table of the database.
HTH!
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi,
In my c# application, I have two separate layers for db and ui i.e. two dlls. I want to make generalized insert, update, delete functions that support these operations for any table and any column types...
To accomplish this, first of all how can we generate an insert/update/delete sql statement if the table name is given ??
Cheers
CNU
|
|
|
|
|
NOTE: You don't specify what your backend database is, so I will assume SQL Server.
Cnu wrote:
To accomplish this, first of all how can we generate an insert/update/delete sql statement if the table name is given ??
The SqlDataAdapter can do this. By supplying a SELECT statement it can figure out the INSERT/DELETE/UPDATE for the table. However, it does not work with joins.
Anyway, I am not sure that this is the best way to proceed. A better way would be to create appropriate stored procedures and have your DAL (Data Access Layer) call the stored procedures. This adds a level of security and performance is improved over calling ad hoc SQL queries against a database.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
I have a server app that opens a connection to the database, does some SQL queries, then closes the connection.
I'm wondering if simply keeping the connection open for the duration of the server's lifetime is a better idea. Any opinions?
My thinking behind this: opening and closing the connection in a multithreaded server with an asynchronous MSDE connection is difficult to do; it'd be easier just to keep the connection open at all times. What do you guys think?
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Bad bad!
Always close the connection. There is no point in tying up server resources. Besides, framework does connection pooling for you. 
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
Thanks for the reply. As I mentioned in the first post, my current code opens and closes on demand, just as you suggest. However, once I've got simultaneous SQL commands being executed/queried, what then?
For example, consider the following:
void DoTwoThingsAtOnce()
{
Thread reader = ReadSomeStuff();
Thread doer = DoSomeStuff();
reader.Start();
doer.Start();
}
SqlConnection connection;
void ReadSomeStuff()
{
connection.Open();
...
connection.Close();
}
void DoSomeStuff()
{
connection.Open();
connection.Close();
}
The above code will begin executing ReadSomeStuff and DoSomeStuff both on different threads. When ReadSomeStuff finishes, it will close the connection -- but alas, DoSomeStuff is not finished with the connection, so closing it will cause DoSomeStuff to fail.
Should I instead be creating connections objects for each operation?
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Yes, you should have separate connection objects. A connection is normally associated with some resource - for example, in SQL Server a connection is associated with a 'server process', which is itself tied to a User Mode Scheduler thread, which can only run on a single CPU.
Your concurrent operation here is a really bad idea as SqlConnection objects are not thread-safe. You're likely to corrupt the state of the object entirely if you call Open simultaneously on both threads. Even if you passed an opened connection to both threads, they're likely to stomp all over each others' internal TDS buffers. The SQL Server Managed Provider does all the high-level command formatting in managed code, then uses the Net-Library stack to submit the command to SQL Server.
Use SqlConnection's connection pooling. The cost of retrieving an existing connection from the pool is minimal.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, that's exactly the information I needed. Yeah, as far as the concurrent operations go, I was simply locking the connection object, and doing synchronous reads and writes.
So to retrieve an existing connection from the connection pool, do I just call connection.Open again? Or is there some other specific way of doing this?
*edit* nevermind, MSDN says yes, connection.Open does in fact use pooling as long as the connection string is the same. Thanks for your help Mike.
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
That's the worst possible thing you could do... Open the connection only for as long as you need it, then close as soon as possible. There is no excuse or reason to ever hold a connection open if your not using it.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Right, and my previous code was in fact opening/closing as needed; I guess I was asking for options in the face of asynchronous operations more than anything. It looks like connection pooling gives me what I want, and that is what I'll be using. Thanks for your reply.
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Is it possible to use a Stored Procedure with a Disconnected Recordset? The code below gives me the error: <b>Operation is not allowed when the object is open.</b>
I have been trying to figure this out for a few hours now... and am hoping someone can give me some guidance.
<code><% @Language = "VBScript" %>
<%
Option Explicit
Response.Buffer = true
Response.Expires = 0
Dim objConn
Dim objCmd
Dim objParams
Dim objRs
Dim item
Dim rsFirstName
Dim rsLastName
%>
<!-- #include file="_inc/adovbs.inc" -->
<%
Set objConn = Server.CreateObject("ADODB.CONNECTION")
objConn.Open(Application("StoredProcedures_ConnectionString"))
Set objCmd = Server.CreateObject("ADODB.COMMAND")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "usp_authors_perm"
objCmd.CommandType = adCmdStoredProc
Set objParams = objCmd.CreateParameter "au_lname",advarChar,adParamInput,40)
objCmd.Parameters.Append objParams
objParams.value = "m"
Set objRs = Server.CreateObject("ADODB.RECORDSET")
objRs.CursorLocation = adUseClient
Set objRs = objCmd.Execute
Set objRs.ActiveConnection = Nothing 'Problem occurs here: Operation is not allowed when the object is open.
objRs.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set rsFirstName = objRs("au_fname")
Set rsLastName = objRs("au_lname")
objRs.CacheSize = 10
while NOT objRs.eof
Response.Write rsLastName & "<br/>"
objRs.MoveNext
wend
%></code>
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
