|
I am trying to write a query against a view to return a recordset to a webpage and can't get my where and and's to work right. Any suggestions would be greatly appreciated.
SELECT *
FROM sde.view_projects
WHERE Docu_Title AND Docu_Type AND Docu_Date AND Docu_Author LIKE '%VAR%'
What do you think? It does not want to take the AND, I am looking for contain's from any/all of these fields.
This is from and ASP Page talking to SQL 2000!
Thanks for your time!
David E.Wright - URS Corporation
|
|
|
|
|
As a general rule I like to stack my AND clauses so that they are additive: (Docu_Title LIKE someTitle) AND (Docu_Type LIKE someType) AND ... well, you get the idea. I'm no expert, but I really think you need to recast this query in a more explicit form. You're asking for an awful lot from a fairly simple inference engine. Each LIKE clause should stand alone, and be joined by AND clauses.
"Your village called -
They're missing their idiot."
|
|
|
|
|
|
Not sure how to word this, or maybe my brain just isn't working today:
I need to add records to a table. I know how to do this if I SELECT a set of data prior to adding a new record, but I would like to do it without having to do a SELECT first.
Or, how do I open an empty recordset for a table? I am trying to avoid the delay of SELECT searching a large table only to come up empty.
Apologies if this doesn't make sense or is ridiculously simple (see line 1).
>>>-----> MikeO
|
|
|
|
|
Ok, brain kicked into gear.
SELECT TOP 0 * FROM tablename
>>>-----> MikeO
|
|
|
|
|
select * from tablename where 1=2
|
|
|
|
|
Honestly Im not very sure of what language Im working with here. The file is called upload.asp and it is called upon by a javascript in an HTML file
pretty much what it does is recieve some variables to upload a file. But what I need is to delete the same file... I'll send the info of the folder, the file name and path so that I can delete it.
------------------------------------------
var doc = new ActiveXObject( 'Msxml2.DomDocument' );
doc.load( Request );
var elt = doc.selectSingleNode( 'root/file' );
var href = elt.getAttribute( 'href' );
href = href.replace( /.*[\\\/](.*)/, '$1' );
var folder = '/images/';
//folder += elt.getAttribute( 'folder' );
var name = elt.getAttribute( 'name' );
var path = elt.getAttribute( 'path' );
var all = path + folder + href
var str = new ActiveXObject( 'Adodb.Stream' );
str.type = 1;
str.open();
str.write( elt.nodeTypedValue );
str.saveToFile( all, 2 );
str.close();
Response.contentType = 'text/xml';
Response.write( '<root><file href="..' + folder + href + '">' );
str = null;
doc = null;
------------------------------------------
HELP!
Jon
X-ZD Designs
|
|
|
|
|
Not sure what your question is. If you are asking what language that is, then it is JScript on the server side.
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
|
|
|
|
|
Hello,
I have a problem with .NET. I have to load a xls-File in .NET. That’s no problem with the OleDbConnection, the DataAdapter and the DataSet. Now I would like to insert all this data in a Database.
I tried to do that with SqlConnection, the SqlCommand,
MyCommand.Connection.Open() and MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close().
Unfortunately that doesn't work.
Also I tried to put the SQLInsertCommand in a loop for each row in the DataSet, but then I got an error, that my valuevariables are already set.
Then I tried to do it with the upload statement. But that doesn't work as well. This is just for all these rows which are changed in some way.
May be you can help me and give me another way to insert a hole DataSet into a Database or how to insert the Excel-data into the database using ASP/VB .NET ?! 
Thanks a lot,
Doerte
ps: don't blame me for my English. Actually I'm from Germany and just for an programming internship in Australia 
|
|
|
|
|
dataadapter.update(dataset, "table name")
|
|
|
|
|
Hi everyone,
I am developing an application that needs to do multiple writes many tables in the database. I will explain the structure of the tables and what I am doing:
Right now, I make an entry for every new data. There could easily be more than 40,000 entries for each application run. I would have liked to store the entries in memory (in an array) and would like flush them out in one stored procedure. The only glitch is that I do not know if stored procedures can handle C++ arrays.
I saw many articles on the web about using comma seperated strings etc and passing it to the stored procedure. However, I am always wary of strings, especially hige concatenations. I was wondering if someone has any experience regarding this and what would be the best course of action. The performance of the application right now is almost unacceptable and I would like to optimize it before it grows out of bounds.
Thanks a lot.
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Do you have to the insert in a single batch?
How much data is in each insert? i.e. what data types are the fields?
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG blog:: brokenkeyboards
|
|
|
|
|
I have to get the user's acces permission for a specified database!
For that I use the command...PERMISSIONS()
I use ADO for establishing a connection to the database..my code is something like this now..
void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
BOOL m_bIsConnectionOpen;
// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
try
{
if (SUCCEEDED(hr))
{
//Open a connectionConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
m_pConnection->Open(ConStr, "", "", 0);
//If database opened successfully then set IsConnectionOpen to TRUE
if (SUCCEEDED(hr)) m_bIsConnectionOpen = TRUE;
}
/* sprintf(str1,"PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
}
..
and then the catch function !
Now..
How do i display the return value of the PERMISSIONS function??
kindly help me in this regard!!
ranjani
|
|
|
|
|
I was playing with this right now and in SQL Query Analyzer I had to use it like this (This is from Northwind):
USE Northwind<br />
GO<br />
SELECT PERMISSIONS(OBJECT_ID('Customers'))
This would return a value of 1881108543. This means that in your code you forgot to add SELECT before PERMISSIONS funtion in order to get the result back. Now you have to AND this value with certain other values in order to get the appropriate permission level. For this and the AND values, check the PERMISSIONS function in SQL Books On-Line.
Regards,
Husein
|
|
|
|
|
AND which value with what.
my question is...
how do i get the integer value from it...
when i run the folllowing in query analyzer i get the proper result..i have to do the same in..c++ ,,how do i do it?
USE pubs
IF PERMISSIONS()&2=0x2
PRINT 'The current user can create a table.'
ELSE
PRINT 'The current user cannot create a table.'
thanxxxx
ranjani
|
|
|
|
|
PERMISSIONS function takes two optional parameters.
PERMISSIONS ( [ objectid [ , 'column' ] ] )
If you specify PERMISSIONS, as in the sample you provided in the previous message, you want to know if the user has certain statement permission. These include CREATE DATABASE (master db only), CREATE TABLE, etc. Their permissions are like this:
Bit Statement
0x1 CREATE DATABASE<br />
0x2 CREATE TABLE etc etc
If you specify PERMISSIONS(objectid), then you want to know if the user can perform certain operation on the object, like SELECT, INSERT, DELETE, UPDATE, etc. Again the bits are the same as above 0x1 for SELECT ALL, 0x2 UPDTE ALL, etc.
If you also specify column name with objectid, you are checking if the user can SELECT, UPDATE or reference a certain column.
You should first figure out what is it that you want to check. That is, first determine what kind of permission you want to check for your user. After that build the query in the SQL Query Analyser, and if everything works fine, create a stored procedure that does the calculation and returns a scalar value.
I hope this solves your problem. For more information, check Books On-Line to see the list of the bits used with PERMISSIONS function.
Have fun,
Husein
|
|
|
|
|
how do i create a stored procedure in C++?
my program should for any of the sql servers..
so..manually creating a stored procedure won't work..
so..
i have to create it programatically...how do i go abt doing it?
ranjani
|
|
|
|
|
I have a PL/SQL procedure that returns a index-by table of records.
In the PL/SQL part everything is working fine, but I have no idea how to implement it in my C# code (using OleDB).
I'd like to put the data returning from the procedure into a DataSet.
Does anybody have any idea how to do this?
Many thanks for help in advance.
<br />
<br />
TYPE RECORD_ROUTE IS RECORD<br />
(<br />
ROUTE_VAR VARCHAR2(10),<br />
LP VARCHAR2(2),<br />
NUM NUMBER(3)<br />
);<br />
<br />
<br />
TYPE ROUTES_TYPE IS TABLE OF RECORD_ROUTE INDEX BY BINARY_INTEGER;<br />
<br />
<br />
PROCEDURE ROUTE_VISUAL(RECORDS OUT ROUTES_TYPE);<br />
misiek
|
|
|
|
|
In my c++ code ..i use ADO commands..and to get the permissions..for userss for each and every databse..
i use the following commands..
sprintf(str1,"SELECT PERMISSIONS(OBJECT_ID('TABLE1'))");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
the abopve function returns an integer value..
how do i read the value??
ranjani
|
|
|
|
|
Use Recordset's Field property and use integer based index (0 in this case) to retrieve the value.
|
|
|
|
|
do u eman to say..use
pRecordset->Fields()-> ..
and what is integer basd index as u have mentioned???
plz reply as soon as poss
thank u in advance
ranjani
|
|
|
|
|
as far as I remember with ADO you would say:
_variant_t vtMyVal;<br />
<br />
vtMyVal = pRecordset->Fields->Item[long(0)]->Value
and this would retrieve the value.
Item[] can take either integer based index (eg. Item[long(0)]->Value) or string based (eg. Item["SomeColumn"]->Value)
I hope this helps.
Best regards,
Husein
|
|
|
|
|
The full code is enclosed herewith!!
When I include the aboce said and run it..the control goes to the catch function..as there's some problem in the above mentioned stt!
void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
_bstr_t bstrProc =( L"sp_StartByteImport" );
BOOL m_bIsConnectionOpen;
// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
cout << "Connection object created." ;
_variant_t vRecsAffected(0L);
try
{
if (SUCCEEDED(hr))
{
//Open a connection where database is access database : "c:\\emailparsing\\email_details.mdb"
ConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
/* Provider=Microsoft.Jet.OLEDB.3.51; Data Source="**/
// strcat(ConStr, "C:\\MSSQL7\\Data\\Shree_Data" );
m_pConnection->Open(ConStr, "", "", 0);
//If database opened successfully then set IsConnectionOpen to TRUE
if (SUCCEEDED(hr))
{
m_bIsConnectionOpen = TRUE;
}
}
sprintf(str1,"USE Shree SELECT PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->Fields->Item[long(0)]->Value;
printf("\n%s\n\n",vtMyVal);
ConStr[0] = '\0';
strcat(ConStr, "SELECT * FROM ");
strcat(ConStr,"TABLE1");
pRecordset = m_pConnection->Execute(ConStr, vRecordsAffected, 1);
if (!pRecordset->GetEOFile())
{
int i = 0;
while (!pRecordset->GetEOFile())
{
printf("row %d is '%s %s'\n",i,
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("id")->GetValue()),
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("product_name")->GetValue()));
i++;
pRecordset->MoveNext();
}
}
pRecordset->Close();
}
catch (_com_error &e)
{
printf("Description = '%s'\n", (char*) e.Description());
printf("%s\n%s\n%s\n",(char *) e.ErrorMessage(), (char *)e.Source(), (char *)e.Description());
}
::CoUninitialize();
}
ranjani
|
|
|
|
|
The full code is enclosed herewith!!
When I include the aboce said and run it..the control goes to the catch function..as there's some problem in the above mentioned stt!
void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
_bstr_t bstrProc =( L"sp_StartByteImport" );
BOOL m_bIsConnectionOpen;
// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
cout << "Connection object created." ;
_variant_t vRecsAffected(0L);
try
{
if (SUCCEEDED(hr))
{
//Open a connection where database is access database : "c:\\emailparsing\\email_details.mdb"
ConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
/* Provider=Microsoft.Jet.OLEDB.3.51; Data Source="**/
// strcat(ConStr, "C:\\MSSQL7\\Data\\Shree_Data" );
m_pConnection->Open(ConStr, "", "", 0);
//If database opened successfully then set IsConnectionOpen to TRUE
if (SUCCEEDED(hr))
{
m_bIsConnectionOpen = TRUE;
}
}
sprintf(str1,"USE Shree SELECT PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->Fields->Item[long(0)]->Value;
printf("\n%s\n\n",vtMyVal);
ConStr[0] = '\0';
strcat(ConStr, "SELECT * FROM ");
strcat(ConStr,"TABLE1");
pRecordset = m_pConnection->Execute(ConStr, vRecordsAffected, 1);
if (!pRecordset->GetEOFile())
{
int i = 0;
while (!pRecordset->GetEOFile())
{
printf("row %d is '%s %s'\n",i,
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("id")->GetValue()),
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("product_name")->GetValue()));
i++;
pRecordset->MoveNext();
}
}
pRecordset->Close();
}
catch (_com_error &e)
{
printf("Description = '%s'\n", (char*) e.Description());
printf("%s\n%s\n%s\n",(char *) e.ErrorMessage(), (char *)e.Source(), (char *)e.Description());
}
::CoUninitialize();
}
ranjani
|
|
|
|
|
The first call in the main(void) should be ::CoInitialize(NULL)
This should do the trick.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
