|
One thing you could do is use the SQLDMO library to encapsulate the objects in the server as types in your application.
~javier lozano
(blog)
|
|
|
|
|
Last week I asked for some help here[^] and didn't get a reply. I was at a client site most of the week and today I got back to some more testing and now know more and hopefully am asking the right question. If the SQL query assigned to a recordset instance refers to a table, then everything works, but if the SQL query assigned to the recordset is a view, then it doesn't work. Most baffling and I can't seem to dig up an answer in the help on this.
This works fine
CString strSQL("SELECT LANG_CODE, LANG_NAME FROM LANGUAGES_TABLE");
CRecordset rs(&db);
if ( rs.Open(AFX_DB_USE_DEFAULT_TYPE, strSQL) != TRUE )
{
return false;
}
while ( !rs.EOF() )
{
CString strLangCode;
CString strLangName;
rs.GetFieldValue("LANG_CODE", strLangCode);
rs.GetFieldValue("LANG_NAME", strLangName);
rs.MoveNext();
}
But when I change the SQL string to what I posted in my original post where the FROM clause refers to a view, then I get an exception thrown in the GetFieldValue call. The error is something about the Cursor State being illegal for fetching data. If anyone can help, I'd really be grateful. 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]
|
|
|
|
|
rs.GetFieldValue turns (eventually) into a call to SQLGetData. The documentation for this function says that if you get state = 24000, one of the following is true (this is simply copied from the documentation):- (DM) The function was called without first calling SQLFetch or SQLFetchScroll to position the cursor on the row of data required.
- (DM) The StatementHandle was in an executed state, but no result set was associated with the StatementHandle.
- A cursor was open on the StatementHandle and SQLFetch or SQLFetchScroll had been called, but the cursor was positioned before the start of the result set or after the end of the result set.
It can't be the first, because CRecordset::Open calls MoveNext before returning. So either the database didn't return a resultset (which should have been caught by Open) or no rows were returned - but this should have led to IsEOF returning FALSE.
Why this should work for a table but not a view I don't know. What ODBC driver are you using? For which database software?
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for the comments, Mike. I'm back in my office again today and will be doing some more experimenting to see if I can figure it out. I think it will likely be more than just the fact that I'm querying a view. The particular view happens to contain about 4 function calls in it and that is likely playing a role in the failure. As far as the back end goes it's an Oracle 9i database and I've tried this with both the Oracle ODBC driver and the Microsoft ODBC driver with Oracle 9i client installed. I can run the SQL fine from the command line in SQL*Plus, but it is just from within a program that I've run into this stumbling block. Hopefully, I'll know more later. Thanks again.
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 hate it when things go this way, but this morning, I trashed my schema, re-built all the tables, views, functions and procedures. Then re-loaded data into the tables and my test and development programs are now working. So as odd as the problem was, it's disappeared after re-creating everything.
Thanks again for the comments, Mike.
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 was thinking the same thing. I will try it out the weekend and let you know. THanks for the help.
Brendan
|
|
|
|
|
Hi,
I have a category table in my SQL server database. I'm a bit confused and I'm hoping someone can help me. Each of my categories can have a sub category, and each sub category can have it's own sub category, and so on. I would like to know how to design my table(s) in such a way to link the various categories with its sub categories. I can create a subcategory table, but the only thing is I don't know how far the subcategories structure will go.
Can someone please advise me?
Brendan
|
|
|
|
|
You would use a self-referential join. In other words you join the table to itself.
Category
---------------------------------------
ID int not null
Name nvarchar(256) not null
ParentCategoryID int null
The ParentCategoryID is a foreignkey that refers to the ID column in Category. If it is null then it is a root category, if it has a parent then it is a sub-category. You can go as deep as you want (2billion+ subcategories deep if you need it)
This design also does away with the category and sub-category tables and replaces it with just one. Unless you need extra/different things in category and subcategory I'd stick with my design above, if category and sub-category require a different set of columns then the design will have to be with two tables.
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Have a problem with the Server.MapPath command.
This works on the local ServerA
Server.MapPath("\FSDIR\WORK\track.mdb");
If I want access it from ServerB, programm requires UNC path. This is UNC, isn't it ? He doesn't accept \\ServerB
Server.MapPath("\\ServerB\FSDIR\WORK\track.mdb");
Background :
I have a Connectionstring and need access to an external server. Is there another method to do this ? Has anybody an example for a connectionstring which handles external access ?
|
|
|
|
|
|
It's almost certainly a permissions problem.
IIS runs as an anonymous user, which probably won't have access to your share.
Try enabled NTLM (Integrated Windows Authentication) on your webserver, and then logging into your web application like that. IIS will then use your credentials, and it'll probably work.
using System.Beer;
|
|
|
|
|
Jon Hulatt wrote:
IIS runs as an anonymous user, which probably won't have access to your share.
IIS runs as a special user, not anonymous. The user name is different depending on the version of windows being used. In IIS you can set which user will be used when the end user is logged on as anonymous. This user can be configured as a domain user, by default it is a machine user.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Is Microsoft SQL Desktop Engine comes along with the distribution of VS.net 2002? I really do not know how to identify such. After installing VS.net 2002, I do not get any SQL shortcut or executable for me to access it. Or I have to download it from Microsoft's Site?
And another thing, in case I got MSDE or Microsoft SQL server express 2005, is it possible to package it with local based application using it as its database? Just MSAccess where you do not need to install access to a local computer for your program to run?
Thank you in advance for your helps.
I just have to know
|
|
|
|
|
aarontlu wrote:
Is Microsoft SQL Desktop Engine comes along with the distribution of VS.net 2002? I really do not know how to identify such. After installing VS.net 2002, I do not get any SQL shortcut or executable for me to access it. Or I have to download it from Microsoft's Site?
I found a message in my Visual Studio install that reads: Visual Studio .NET 2003 does not include Microsoft SQL Server Desktop Engine (MSDE). To download MSDE, go to http://go.microsoft.com/fwlink/?linkid=13962[^]. So, yes you have to download it.
aarontlu wrote:
is it possible to package it with local based application using it as its database?
Yes. There should be some instructions that come with it. If not, there there are a few articles about it[^].
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
aarontlu wrote:
is it possible to package it with local based application using it as its database?
collin wrote:
Yes. There should be some instructions that come with it. If not, there there are a few articles about it[^].
Thank you very much, these things save me a lot of time. And I'm planning ahead for future project. About local use of the SQL Desktop Engine, I have 2 questions..
1. Is it the same if I use Sql Server express 2005?
2. Will it act like access where I do not need to install the Sql Server Express 2005 on the computer that I'm installing my program on?
Your help are fully appreciated
I just have to know
|
|
|
|
|
|
1. No. SQL server exress 2005 is a newer version of SQL server (Yukon - not yet released) than the MSDE distributed with VS. Net 2003 (SQL 2000). Most SQL 2000 functions should work the same on Yukon (It is backwards compatible), but there is no gaurantee at this point since it is not released.
2. No it will not work like Access, you will need to install the server if it is not already present, and attach or create your database to the server.
Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright
|
|
|
|
|
I'm new to .NET and C# coming from VisualFox background.
I'm having a tough time with this control, namely determining which row has been selected.
I have four columns, three of which are visible, the fourth contains an id which becomes the parameter to a Stored Procedure which then populates a DataGrid.
e.g.
private void listView2_SelectedIndexChanged(object sender, System.EventArgs e)
{
cmdSelectZoneCosts.Parameters["@zoneid"].Value=this.listView2.SelectedItems[0].SubItems[3].Text;
dsZoneCosts.Clear();
daZoneCosts.Fill(dsZoneCosts, "ZoneCosts");
}
For some reason, the code is executed TWICE(?) the first time the .SelectItems.Count = 0 which crashes the line. If I "trap" for that i.e.
if (this.listView2.SelectedItems.Count !=0)
{
cmdSelectZoneCosts.Parameters["@zoneid"].Value=this.listView2.SelectedItems[0].SubItems[3].Text;
dsZoneCosts.Clear();
daZoneCosts.Fill(dsZoneCosts, "ZoneCosts");
}
It works this way, but why is it running the code twice ??
Can anyone point me in the right direction to a sample walkthrough of a simple form which will display records from a database, Add, Edit, Delete functions.
Roger
|
|
|
|
|
Can any one help me with the following SQL statement. I am getting a syntax error. I am trying to update fields in a access database. Thanks
strSQL = "UPDATE tblSetup SET"
strSQL = strSQL & "tblSetup.Company_Name = '" & txtName & "',"
strSQL = strSQL & "WHERE tblSetup.Company_ID = '" & txtID & "'"
|
|
|
|
|
|
Thanks. I will try it out.
|
|
|
|
|
I`m new to ADO.NET, can we update a table from the datagrid? Thanks
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
You can update the database from the datagrid it is possible in web.ui.datagrid.u must use the templates of datagrid properties 
e.veera raghavendra
|
|
|
|
|
How Can I Generating wrapper code for SQL Server Stored Procedure ??
If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server
http://www.codeproject.com/vb/net/OracleSPWrapper.asp
my question .. where is this tools ???
and thanks with my regarding
Fraas
|
|
|
|
|
I want to practice my SQL on Excel spreadsheets. When I have a project open in VS2003.NET and open the 'Server Explorer' window, it tells me (as it should) that there are no SQL Servers to connect to.
When I was learning SQL in school we would connect to the Oracle 9 Database on the Network. Now I want to continue practicing SQL/C#, but have no network/database or internet connectivity.
Is there a way I can add an add an "SQL Server" to this path when in fact the "Server" is located on the same laptop.
Please forgive my ignorance on the subject. I'm in no way pretending to know what I'm talking about but very much want to learn...
thanks for your time....
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
