|
Thanks ! I found another solution using sp_sqlexecute() but this one is more elligant.
|
|
|
|
|
Glad it helped. Here is some info on sp_sqlexec.
SQL Books Online
SQL Server 6.x
sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application.
SQL Server 2000
Removed; no longer available. Remove all references to sp_sqlexec.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi all,
In a test database I have two tables, which are joined on a primary key. In the first table, this key is an auto-incrementing field.
Can anyone tell me a SQL (ie generic) way of being able to insert records into the primary table, retrieve the Primary Key value and be able to use that in the secondary table to create a related record?
I have thought about using a second query to return the last record entered, hwoever this seems a bit ugly, and would be prone to error in a multi-user environment.
Thanks,
Martin.
|
|
|
|
|
Is the database SQL server 2000..?U can use the "@@identity" to retrieve the primary key of the last record entered..
|
|
|
|
|
At the moment it's Access - but it could be SQL Server, Oracle OR DB2 in the future
|
|
|
|
|
I don't think there is a way that is so generic that it could handle all that. Each database vendor uses a slightly different flavour of SQL. For example SQL Server 2000 is based roughly on ANSI SQL-92, whereas SQL Server 2005 is roughly based on ANSI SQL-99. Oracle will have its on proprietary features and ways of doing things, as will DB2 and so on.
You could try looking at an open source product called NHibernate[^] which has its own SQL varient which is then translated in to the specific flavour of SQL required by the back end database.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Frank Kerrigan wrote: MS Enterprise Libraries offer a Db access layer support across different databases manufactures;
True, but not to the same extent as Hibernate, unless I missed something in the Enterprise Library documentation. Hibernate (and NHibernate) offers the ability to translate SQL into the various flavours. For example. In SQL Server you might have
SELECT TOP 10 * FROM MyTable , while in Oracle the equivalent (if I remember) is something like
SELECT * FROM MyTable LIMIT 10,0 NHibernate permits the developer to develop in their varient which is then translated to any of the supported vendors SQL Language varient with not much more effort that a change in a config file.
As far as I can see the Enterprise Library still requires that the developer write SQL against the varient used by the back end database system - so there will still be a lot of work to do to get it to work nicely with various database systems.
Frank Kerrigan wrote: Look where you want to go not where you don't want to crash.
Excellent advice.  I remember from a skid driving course I did a few years ago something similar.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi, I want to list all tables names in combobox. Can somebody write a code example. Thank you
|
|
|
|
|
select * from information_schema.tables where table_type='Base table'
-
बुरा जो देखण मै चला, बुरा न िमलया कोय,
जो मन खोजा आपणा तो मुझसे बुरा न कोय।
Translation
|
|
|
|
|
thank You
|
|
|
|
|
I want to write genric select statement in which i want to get the top 10 people fom the tabel based on their total marks column.
I am getting the result when i give
"select top 10 percent fom studentmarks"
But it is not working when i making the statement generic.
"select top @p persent from stiudentmarks"
I am using SQL server 2000
I want to run it on the same (i have already tried it on server 2005 it is working fine)
Please any body help me how to make it work on SQlserver 2000
Varmag
|
|
|
|
|
varmag wrote: I am using SQL server 2000
This feature is not supported on SQL Server 2000. It is a new feature introduced in SQL Server 2005.
varmag wrote: Please any body help me how to make it work on SQlserver 2000
You could use dynamic SQL and construct a string like this:
DECLARE @sql VARCHAR(1000);
SET @sql = 'select top '+CAST(@p AS VARCHAR(10))+' percent fom studentmarks';
EXEC(@sql)
However, be careful as dynamic SQL can be a source of SQL Injection Attacks. If @p is already an integer type then the code should be fine.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 11:16 Thursday 13th April, 2006
|
|
|
|
|
Colin Angus Mackay wrote: However, be careful as dynamic SQL can be a source of SQL Injection Attacks
What is SqlInjection attack?
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
HimaBindu Vejella wrote: What is Sql Injection attack?
See here[^] or here[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi,
In a project I'm working on, we are using the DataAdapter.Fill(table) to get the data from the database. The table we are filling is created before we call the fill. When the DataTable contains a column with a System.Char type I get a FillError.
Here is some code to make it clear.
The field status is a CHAR in the database to.
<br />
' Create a table<br />
Dim table As New System.Data.DataTable("Customer")<br />
<br />
With table .Columns()<br />
.Add("id", GetType(System.Int32));<br />
.Add("name", GetType(System.String));<br />
.Add("status", GetType(System.Char));<br />
End With<br />
<br />
' Other code to create and open the connection / dataadapter<br />
...<br />
<br />
' Create connection / command / dataadapter<br />
Dim cn As New SqlConnection("connectionstring")<br />
cn.Open()<br />
<br />
Dim cm As New SqlCommand("SELECT id, name, status FROM Customer", cn)<br />
cm.CommandType = CommandType.Text<br />
<br />
Dim da As New SqlDataAdapter(cm)<br />
<br />
' I add a handler to catch my exception<br />
AddHandler da.FillError, New FillErrorEventHandler(AddressOf Helper.FillError)<br />
<br />
table.BeginLoadData()<br />
da.Fill(table)<br />
table.EndLoadData()<br />
Because the predefined DataTable contains a column with a Char type I always get the FillError. I catch the error and try to solve it, see the next code snippit.
<br />
Public Shared Sub FillError(ByVal sender As Object, ByVal args As FillErrorEventArgs)<br />
Dim table As System.Data.DataTable<br />
Dim row As System.Data.DataRow<br />
Dim values As Object()<br />
<br />
Dim valueType As System.Type<br />
Dim tableType As System.Type<br />
<br />
' Try to solve exception<br />
If args.Errors.GetType() Is Type.GetType("System.ArgumentException") Then<br />
' There was an error during the fill<br />
table = args.DataTable<br />
values = args.Values<br />
<br />
If args.Values.Length = table.Columns.Count Then<br />
row = table.NewRow<br />
<br />
' Try not to get here, not good for the performance<br />
For i As Integer = 0 To table.Columns.Count - 1<br />
valueType = values(i).GetType<br />
tableType = table.Columns(i).DataType<br />
<br />
If valueType.Equals(tableType) OrElse values(i) Is System.DBNull.Value Then<br />
row(i) = values(i)<br />
Else<br />
row(i) = Convert.ChangeType(values(i), tableType)<br />
End If<br />
Next<br />
<br />
table.Rows.Add(row)<br />
<br />
' Continue without error<br />
args.[Continue] = True<br />
Else<br />
' Throw error<br />
args.[Continue] = False<br />
End If<br />
End If<br />
End Sub<br />
Now you wonder why I post this code. Because it seems I have a solution. But the solution is slow and not correct all the time. That's why I have the following questions.
Is there a better solution? One without catching the FillError event.
Is there a reason why the Fill doesn't work with a Char?
I hope somebody can help or someone else is helped with the code I provided.
Jochen
|
|
|
|
|
I Have Windows XP 2003 on my System( Pentium4 Pc )!
and i give this Error when i want to install Ms Sql Server 2005
***************************************
- SQL Compatibility With Operating System (Error)
* SQL Compatibility With Operating System
Messages
* This SQL Server edition is not supported on the operating system currently running on this computer.
- Minimum Hardware Requirement (Warning)
* Minimum Hardware Requirement
* The current system does not meet the minimum hardware requirements for this SQL Server release.
****************************************
what O.S is require for SQL Server ?
and What HardWare Requirement is Need for SQL Server?
---------------------
Areff Bahrami(KAVEH)
Areff.HB@Gmail.com
---------------------
|
|
|
|
|
First, there's no such thing as Windows XP 2003. It's either Windows XP or Windows Server 2003. If your using XP, you have to have SP2 installed. Windows Server 2003 needs at least SP1.
Hardware requires at least 512MB of RAM, 1GB is recommended.
You can find the system requirements for each SQL Server edition here[^].
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
It probably depends on the edition of SQL Server you are trying to install. With SQL Server 2000, I remember the standard edition could not be installed on XP, where you needed to install the personal edition.
Windows XP is one thing (client operating system) and Windows Server 2003 is another (server operating system.) Maybe you need the server.
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
|
|
|
|
|
Hi,
I am trying to implement a data processing extension for reporting services 2000 because our company have many custmers with the same schema and we don't want to build the reports for everycompany this reports are accessible throug url as well as SOAP. I have read the msdn as well as many fourms but there doesn'seem to be enough for me to understan how I can go about it cause the samples that i have seen are either for file or something else. I am now very sure how i can make this work especially how to implement the IdbCommand and Idatareader interfaces. My reports are already built for one company and all the reports access stored procedures and some commands are in text to fill up parameters in a drop down list format.
any thoughts are welcome
Thank you so much  
jemi
|
|
|
|
|
I want to know whether i can use ado.net in vc++.net(MFC)!I have just used ado.net in vb.net.
thanks!
|
|
|
|
|
http://www.codeproject.com/managedcpp/DataGridVCNET.asp?df=100&forumid=135647&exp=0&select=1383413#xx1383413xx
|
|
|
|
|
Hi!
Can someone please assist me? I want to capture the "Created Date" and an "Update Date" on entries in my DB. I am running SQL 2000.
This would be greatly apriciated
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
SQL Server does not record the timestamps when a record is created or modified. If you want to do this yourself, you'll need to add columns for this, and ensure you update the columns appropriately when you make a modification.
You could create triggers to perform these operations automatically on an INSERT or UPDATE.
You should be aware that the GETDATE() feature is only as accurate as your computer's clock, which typically has a resolution of 15ms, although this can vary depending on the exact hardware. If you're trying to decide whether a given row has changed since you last read it, you may find that you miss updates if multiple updates occur within one clock period. If that's your goal, you might consider using the ROWVERSION column type, which is automatically updated by SQL Server whenever a modification is made to a row. Despite the name, the version is not a sequential number, it's a GUID, and therefore you can't rely on ordering between different version 'numbers'.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
Can anyone help me with a query i need to run. So far my SQL is as follows:
SELECT * FROM tbl_Orders WHERE orderPaid = 1 AND (DATE BETWEEN 'date1' AND 'date2')
However i need to group the records together by the person placing the order via the combined fields orderTableID and orderUserID, so that i can get a SUM total of the price of the cumulative order orderPrice.
Additional to this i need to be able to get the sub-totals of the different product types eg Drinks, Meal, Deserts. In total their are five different sub totals i need - Bebidas, Vinos, Paellas, Postres, Tapas, Deserts. These are identified by the column orderCategory.
I know this is a lot to ask, but thats why i need help, from someone who's a genius in SQL.
So THANKS in advance for any help!!!
munklefish
:-> <--hoping some charm might persuade!
 <--going for the sympathy vote
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
