|
ASP & SQLserver2k & Windows2003
I use ASP to add data to 2 tables
I have 2 tables: Invoice and InvoiceDetail
Invoice contains: InvoiceID, ClientName, InvoiceDate........
InvoiveDetail contains: InvoiceID, Name,Price,Quantity....
I set default of InvoiceID(Invoice table)=newid() so I dont need to insert a new value when I add a new record but I can not get value of newid()
Example:
Invoice for Client "Tom"
I use "INSERT Invoice (ClientName, InvoiceDate) VALUES ('Tom','30/1/2004')"
and SQLserver2k auto create a value for InvoiceID such as {BB10687B-87F7-44E4-9397-8D510BBDFA9C}
and then ..... I WANT TO GET {BB10687B-87F7-44E4-9397-8D510BBDFA9C}
I NEED IT to add detail of this invoice for InvoiceDetail table like:
InvoiceID - Name - Price - Quantity
{BB10687B-87F7-44E4-9397-8D510BBDFA9C} - book - 26.5$ - 5
{BB10687B-87F7-44E4-9397-8D510BBDFA9C} - CD - 700.95$ - 1
I think
1) SELECT InvoiceID FROM Invoice WHERE ClientName='Tom' AND InvoiceDate='30/1/2004'" but this is not good, may be duplicate all value in all column
2) May I have to add a new column in table
3) I have to calculate my InvoiceID and not use newid() function of SQLsrv2k 
Please help me to get value of newid() function in SQLserver2k
thanks alot
|
|
|
|
|
From what little testing I have done, I don't thing you will be able to obtain the GUID from the table after an insert like with an auto incrementing or random integer column. With an integer you can use SELECT @ID = @@Idendity. But it doesn't appear to work with the GUID.
DROP TABLE tempdb..TestGUID
DROP TABLE tempdb..TestAutoID
GO
CREATE TABLE tempdb..TestGUID (
ID uniqueidentifier ROWGUIDCOL Not NULL DEFAULT (newid()),
TestValue varchar(50)
)
CREATE TABLE tempdb..TestAutoID (
ID int Identity (1, 1) Not Null,
TestValue varchar(50)
)
GO
INSERT INTO tempdb..TestGUID
( TestValue )
SELECT 'Test GUID'
SELECT @@Identity
INSERT INTO tempdb..TestAutoID
( TestValue )
SELECT 'Test Integer ID'
SELECT @@Identity
SELECT * FROM tempdb..TestGUID
SELECT * FROM tempdb..TestAutoID OutPut
(1 row(s) affected)
----------------------------------------
NULL
(1 row(s) affected)
(1 row(s) affected)
----------------------------------------
1
(1 row(s) affected)
ID TestValue
------------------------------------ --------------------------------------------------
E51F034D-BD69-4A34-9983-A2B1007FBA74 Test GUID
(1 row(s) affected)
ID TestValue
----------- --------------------------------------------------
1 Test Integer ID
(1 row(s) affected)As you can see the @@Identity returns a null for the GUID and the actual value for the integer ID. You may have to obtain the GUID value prior to the insert by placing it in a variable. Then you can use the variable in both the header and detail tables.
Sorry I couldn't be much more help.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I want to join 5 tables and in that way get returned a schema of 10 columns that I can fetch with a recordset that I have made in C++ for this reason, but if one or more of the tables are empty (with this ID) the returned schema doesn't get 10 columns (or doesn't come at all depending on how you do it) and so I cant fetch it with my recordset. I want those column empty if the row is not there. I know I can declare variables like: select chvName = '' from Person
but I dont know if it is empty when I do my sql-string without checking that first. Anybody that is good at Transact-SQL?
Robert
|
|
|
|
|
Select A.Column1, B.Column1
From tblA A, tblB B
WHERE A.Column1 *= B.Column1
If B.Column1 does not exist you will return A.Column1's Value and Null for B.Column1's Value.
The *= or =* is the same as an inner or outer join in T-SQL.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thank you very much, now it works!
|
|
|
|
|
*= and =* are the same as LEFT JOIN and RIGHT JOIN, no? And I don't think they're supported any more, Microsoft's reason being that they're not in the SQL-92 standard. I'd counsel a newbie to use left and right joins instead.
Regards,
Jeff Varszegi
|
|
|
|
|
I read in an internal document from our company DBAs saying the "SQL Server 2000 can cache execution plan for any SQL statement if the names of the objects in the statement is fully qualified" (for example, select * from [dbo].MyTable). The document did not say that this is for SQL statements in stored procedures only.
I am not very knowledgeable about SQL servers. My question is, does this mean that I can use dynamic SQL statements and get some of the same benefits as stored procedures?
Thanks.
My articles and software tools
|
|
|
|
|
Yes, SQL Server does cache execution plans for arbitrarily-supplied SQL statements. The reason for requiring full qualification is simply because different users can own objects with the same name.
SQL Server tries to automatically determine which parts of the query are parameters (called auto-parameterization). This allows it to match multiple queries to the same plan.
Using a stored procedure instead means that SQL Server will take fewer resources trying to determine whether a query is a match for a cached query plan, and fewer resources in auto-parameterization (since you've already told it what parameters you have).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
|
I have a Database project in which I put all my DDL code. I have a database connection for this project connecting to an Oracle database. Unlike when I am using SQLServer, my scripts do not run properly when I right click on a script file and say run.
If I have a simple select * from table then it runs. However the DDL such as:
CREATE TABLE "CMD"."TBL_BLAH" (
"BLAH_ID" VARCHAR2(38) NOT NULL,
"DESCRIPTION" VARCHAR2(50) NOT NULL,
PRIMARY KEY("BLAH_ID")
)
/
I get an error saying:
ORA-00922: missing or invalid option
The same runs on sqlplus.
Ok, so now I removed the / and it owrks. But I have a new problem. The DDL is actually:
CREATE TABLE "CMD"."TBL_BLAH" (
"BLAH_ID" VARCHAR2(38) NOT NULL,
"DESCRIPTION" VARCHAR2(50) NOT NULL,
PRIMARY KEY("BLAH_ID")
)
GRANT SELECT ON "Q"."TBL_BLAH" TO "BLAH_BLAH";
And now I get the same error again. Anyone know how to set these scripts up so that they can be run from the IDE?
Thanks.
|
|
|
|
|
Which IDE do you mean? Assume that you mean Enterprise manager?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Ah, I should have made myself clear. I meant the VS .Net IDE. The IDE lets you create Database projects and you can execute DDL from the VS .Net IDE itself. Works really well with SQLServer. With Oracle I am having problems as mentioned in my post.
|
|
|
|
|
Ahhhhh.....don't have an ORA installation here to test, but if manage it, will let you know....
Sorry about that!
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I have a web-server that has an MSDE database. I'm finding it a bit of a pain to administer the database just using the command line tools.
Can anybody recommend a light-weight visual tool that has the main functionality of SQL Enterprise Manager. (In particular, Users and Roles configuration).
I'd prefer a desktop app rather than a web-hosted app.
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
If you have Visual Studio 6, then you get the MSDE for Visual Studio - which includes EM, and QA. If VS.NET, then can do alot of jobs through Server Explorer.
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
RichardGrimmer wrote:
If you have Visual Studio 6, then you get the MSDE for Visual Studio - which includes EM, and QA. If VS.NET, then can do alot of jobs through Server Explorer.
Thanks, but I need something to install on the remote web-server. The machine is locked down and so I can't access it remotely apart from via terminal services.
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
Here is a web app
http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
|
|
|
|
|
Does anyone know how to use Named Commands in ADO from C++? The MSDN shows how to use Named Commands from VB, but there is no mention of C++.
Thanks.
|
|
|
|
|
It probably requires some smart tricks with IDispatch. VB does that kind of thing behind the scenes, but you'll probably find it easier simply to keep a pointer to the command object around.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
select * from mytable where datecreated > (CONVERT(varchar(30),DATEPART(year, GETDATE())) + '-' + CONVERT(varchar(30),DATEPART(month, GETDATE())) + '-' + CONVERT(varchar(30),DATEPART(day, GETDATE())-1)) + ' 12:07:00'
|
|
|
|
|
Change the type of the column, datecreated, to be of date type instead of string or varchar type. The query would then be
select *
from mytable
where datecreated > to_date(to_char(trunc(sysdate-1),'YYYY-MM-DD')|' 12:07:00'),'YYYY-MM-DD HH:MI:SS');
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
I would agree Chris. Make sure the DateCreated column is a dateTime datatype and the parameter being passed is the same DateTime datatype. Also, you can put an index on the DateCreated column.
Create Index idx1 on myTable (DateCreated)
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Chris Meech wrote:
select * from mytable where datecreated > to_date(to_char(trunc(sysdate-1),'YYYY-MM-DD')|' 12:07:00'),'YYYY-MM-DD HH:MI:SS');
following not recognised by query analyzer/ T-Sql
trunc
to_date
to_char
|
|
|
|
|
Anonymous wrote:
query analyzer/ T-Sql
Sorry. That suggests you are running this on SQL Server. My example was from an Oracle database. TRUNC is just a truncation routine. In this case, the sysdate call returns a current datetime value from which the time value is truncated. TO_DATE, and TO_CHAR are just conversions routines to convert strings to dates and vice versa. There should be similar functions available on SQL Server, I would assume.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
How to get 'IRowsetFind' interface in oledb sdk !
dear all:
i will use following code in my program(C++) :
discover 'pICommandProperties->SetProperties(1,rgInitPropSet);' return failed code 'DB_S_ERRORSOCCURRED'
so 'pIRowset->QueryInterface(IID_IRowsetFind,(void **)&pIRowsetFind);' return failed!
dear all, please help me! how to get 'IRowsetFind' interface in oledb sdk , and how to use 'IRowsetFind->FindNextRow()'
InitProperties[0].dwPropertyID=DBPROP_IRowsetScroll;
InitProperties[0].dwOptions=DBPROPOPTIONS_REQUIRED;
InitProperties[0].dwStatus=DBPROPSTATUS_OK;
InitProperties[0].colid=DB_NULLID;
InitProperties[0].vValue.vt=VT_BOOL;
InitProperties[0].vValue.lVal=VARIANT_TRUE;
InitProperties[1].dwPropertyID=DBPROP_IRowsetFind;
InitProperties[1].dwOptions=DBPROPOPTIONS_REQUIRED;
InitProperties[1].dwStatus=DBPROPSTATUS_OK;
InitProperties[1].colid=DB_NULLID;
InitProperties[1].vValue.vt=VT_BOOL;
InitProperties[1].vValue.lVal=VARIANT_TRUE;
rgInitPropSet[0].cProperties=2;
rgInitPropSet[0].guidPropertySet=DBPROPSET_ROWSET;
rgInitPropSet[0].rgProperties=InitProperties;
hr=pICommandText->QueryInterface(IID_ICommandProperties,(void **)&pICommandProperties);
hr=pICommandProperties->SetProperties(1,rgInitPropSet);
if (DB_S_ERRORSOCCURRED==pICommandProperties->SetProperties(1,rgInitPropSet))
cout<< "test";
if(FAILED(hr))
{
cout<< "OLEDB Error !\r\nExecute Failed To Set Rowset Properties";
}
hr = pIRowset->QueryInterface(IID_IRowsetFind,(void **)&pIRowsetFind);
if(FAILED(hr))
{
cout << "Failed to get IID_IRowsetFind interface.\n";
cout << endl;
} //end if
laker
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
