|
I found a way to do it by inserting the string as "2004-01-30 10:15:32" it seems to work just as well as if it was unseparated.
- Aaron
|
|
|
|
|
Hi
I am trying to get some data from SQP procedure using XML Explicit as an output parameter. I am able to form XML using following statements:
---------********************
declare @CustomerID nchar(5)
select @CustomerID = 'ALFKI'
SELECT 1 as Tag,
NULL as Parent,
CustomerID as [C!1!customerid],
NULL as [O!2!orderid],
NULL as [O!2]
FROM Customers as C
where C.CustomerID = @CustomerID
UNION
SELECT
2 as tag,
1 as parent,
C.CustomerID,
O.OrderID,
O.ShipAddress
FROM Customers C, Orders O
WHERE (C.CustomerID = @CustomerID)
AND (C.CustomerID = O.CustomerID)
FOR XML EXPLICIT
---------********************
But, how do I save the ouput in an OUTPUT variable? Please advice.
Basically, the idea is to receive the xml in ASP.NET function and handle the xml there.
Thanks
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
AFAIK, you can't store it in an OUTPUT variable.
If you simply have the SELECT, you'll get a result set with a number of rows, each having one string field. You have to concatentate all the rows to get the full result.
You can simply use a SqlDataReader to retrieve the result.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thanks for the pointer. I applied and it works great 
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I want to make a copy of my sql server 2000 database. I want all the tables, relationships, stored procedures, diagrams etc but none of the data .
How can this be accomplished ?
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
Script the database....
Open Enterprise manager, and navigate to the database in question. Right-click, all tasks, Generate SQL Script. In the new dlg, click Show All, and select0 Script All Objects. Click OK and tell it where to save it.
This will generate a .sql file that can be run through Query Analyser / OSQL etc. that will create all entities in your database..
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Cool trick, tried on Northwind and the 11,000 lines plus of sql generated a slew of 'Invalid object name' errors. Probably just needs more investigation on my part though 
Thank you.
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
May possibly be because you (I assume) tried to restore it to the existing Northwind DB - the invalid object name ocurrs since you're trying to create duplicate objects IN_THE_SAME_DATABASE....
Again, from EM, navigate to the Databases node, right click, New Database. Configure the new DB, and in QA, select the name of the new DB in the drop down, or issue a "Use <dbname>" as the first cmd. Then run the script, and should be OK...
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
You could possibly be right, I did create a new database and ran the script on it but I may have run the script twice.
In any case it did work and all the tables are present and correct.
Thanks for your help 
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
|
Hi everyone,
I would like some help on the following problem..
I m doing a course in database management using Oracle and my lecturer just hint that if we include some web development stuff (ASP or ASP.NET page)in our final assignment he might give us some extra marks.
I've already done all my SQL coding that create my database for the assignment and now i only need to do an ASP/ASP.NET page.
But the problem is that i havent had any knowledge before in ASP/ASP.NET. I went and bought myself a book (SAMS teach Yourself ASP.NET in 24 hours) but i dont really have the time to go through the book in detail, so i cant produce what the lecturer require us to do.
So i was wondering whether anyone here can give me some head start on wat i m require to do...
I need a ASP/ASP.NET page that ask the user for Employee ID, Start Date, and End Date..then it will match the ID and time frame of the 2 dates entered with the database in Oracle to procude a record of information on the employee.
From the book, i've learn how to display all the records from the employee table in the database but i still havent figure out how to display records on a particular employee which ID and dates was entered by the user. I have also read something on it that said something on ADO.NET that maybe can help to do this.
I can do all this in Oracle using SQL queries but i desperately need those extra few marks, so i m still trying to code the ASP/ASP.NET page to do this.
Any help would be very appreciated. Thank you.![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
------------------
b.o.toezz
|
|
|
|
|
Could you not add a constraint to your query like "WHERE EmployeeID = XXX" etc...
You could dynamically form the SQL in code, and then use ADO to run that query, or alternatively write a PL/SQL package with parameters....
Simplest method would be something of the form:
<br />
Dim strSQL as string = "SELECT * FROM EMPLOYEE"<br />
<br />
strSQL = strSQL & "WHERE EmployeeID=" & txtEmployee.Text<br />
strSQL = strSQL & " AND StartDate > " txtStart.Text<br />
strSQL = strSQL & " AND EndDate > " txtEnd.Text<br />
<br />
Debug.Writeline (strSQL)<br />
<br />
'Execute the SQL<br />
Dim oConn as new System.Data.Oledb.OledbConnection (strConnectionString)<br />
Dim oDA as new System.Data.Oledb.OledbDataAdapter(oConn, strSQL)<br />
Dim oDS as Dataset<br />
<br />
oDA.Fill (oDS)<br />
<br />
'Output no. rows<br />
Debubg.Writeline (oDS.Tables(0).Rows.Count)<br />
Is this the sort of thing you need? Can supply an ADO rather than ADO.net version, and can't guarantee that the syntax of the above is correct - did it outside of the IDE!!)
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I've just spent time documenting a new database model in Visio 2003 professional.
I'm now at a stage that I need to build the DDL.
I've just discovered that my version of Visio 2003 does not build DDLs !
Is there any kind soul out there who would be prepared to take my visio diagram and return to me the SQL Server DDL ?
my email address is g a r y . r a n s o n @ c l a r a . n e t
|
|
|
|
|
I writing an N-tier application, where I will used strongly typed DataSets as data transport objects between some tiers.
The application consumes a web service. By default VS has created a proxy and a number of classes that are used to hold the data the web service returns, ie classes that mirrors the result xml structure. I want to move to DataSets instead.
Is there a clever way to make a web service proxy automatically fill a typed DataSet instead of the auto-created classes when I call the web service? Observe that this web service does not return a DataSet per se (this would be simple then), but a structure that would easily be held within a DataSet.
Manually I can look inside the wsdl, extract the schemas, let VS create typed DataSets from those schemas, write code that consumes the web service through the proxy, move over all retrieved data to my DataSets, done. Since this seems like a common scenario, I'm just hoping that VS has an automated way of doing this. What would be the easiest way?
Kind regards,
/Björn Morén
Sweden
|
|
|
|
|
Here's what I want to do: I have an application that delivers web based security forms for individuals to fill out. I am trying to write a GUI that will allow someone with little coding experience create a form.
I want to offer the option of copying an existing form (including the actual ASP page and SQL table). I can handle duplicating and renaming the actual ASP form, but I can find no information on how to copy a tables columns, data types, constraints and triggers from an existing table to a new one, renaming the new table to correlate to the new form. I hope someone can help me. Thanks!
|
|
|
|
|
Probaly the most straight forward method would be to create a DTS package, then call that from code - can supply an example if necessary, but not to bad to just "have a go at"...
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I would agree with Richard in using the DTS package if the requirement are to copy the triggers, indices & constraints as well.
For a just a table the easiest way would be to
Select *
Into [tblNewTable]
From [tblExistingTable]
Where 0=1
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)
|
|
|
|
|
Dear Friends,
I got two tables: order and orderDetails
Order table is:
OrderId CustomerCode OrderDate
1 55 2/12/2004
2 55 3/12/2004
3 56 4/15/2004
4 57 4/16/2004
OrderDetail table is:
OrderId ProductId Quantity
1 131 12 Litres
1 157 13 KGs
1 111 12 MT
2 131 19 litres
3 155 12 dozens
I want SQL query such that:
I want to print all the productId placed by customerCode 55 and orderdate
i.e result contains following columns:
CustomerCode, OrderDate, ProductId, Quantity
For customer code 55. What can be the query ??
|
|
|
|
|
does this sql statement help u in any way :
Select CustomerCode, OrderDate, ProductId, Quantity From [Order] a Inner Join OrderDetails b On a.OrderID = b.OrderID
Where a.CustomerCode = 55
Try not using Keywords for Table/Column names (like Order).
Parshu
|
|
|
|
|
parshu21 wrote:
Try not using Keywords for Table/Column names (like Order).
That's good advice - It can get confusing.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
Coming soon: The Second EuroCPian Event[^].
|
|
|
|
|
Hi,
I am having trouble adding new rows to an empty MS Access table.
I manually created the table in MS access named server and created two
columns Date & Time.
When I run the following code I get: "Syntax error in INSERT INTO
statement".
Any help would be great.
Thank you.
OleDbCommand fgSelectCommand = conn.CreateCommand();
fgSelectCommand.CommandText = "SELECT TOP 1 * FROM Server";
OleDbDataAdapter fgADOAdapter = new OleDbDataAdapter();
OleDbCommandBuilder fgCommandBuilder = new
OleDbCommandBuilder(fgADOAdapter);
fgADOAdapter.SelectCommand = fgSelectCommand;
DataTable fgDataTable = new DataTable("Server");
fgADOAdapter.FillSchema(fgDataTable, SchemaType.Source);
DataRow fgDataRow = fgDataTable.NewRow();
fgDataRow["Date"] = logWeb.LogDate;
fgDataRow["Time"] = logWeb.LogTime;
fgDataTable.Rows.Add(fgDataRow);
fgADOAdapter.Update(fgDataTable);
|
|
|
|
|
Try ExecuteScaler with a stored procedure and return a Success or Failure value.
Using a stored procedure would be better but here is some SQL to get you started.
This is obvious with VB.Net.
Dim cmd as New SqlCommand
cmd.Connection = cn 'Connection
cmd.CommandText = "Insert into [Server] ([Date], [Time]) Values (yourDate, yourTime)"
cmd.commandType = CommandText
return cmd.ExecuteNonQuery
Hope this helps,
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)
|
|
|
|
|
Michael,
Thank you for the advice.
Cheers,
Craig
|
|
|
|
|
hi,
the message in debug show "Run-Time Check Failure #2 - Stack around the variable 'ls_BureauID' was corrupted!"
what's the meaning?
thanks!
|
|
|
|
|
Is this field ref'd by a constraint ?
This could be part of the issue.
Tony
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
