|
The following page has a some sample TSQL that does this.
Basically, it creates a new column of the same type as the identity, copies the data into the new column, drops the identity column and renames the new column to the name of the old identity column.
Affordable and reliable hosting? Click here!
|
|
|
|
|
Hi,
Thanks for the reply, however i think you may have forgotten to include the link.
Would be greatly appreciated if you could post it as i have found nothing in the way of doing this so far.
Thanks again,
|
|
|
|
|
|
Hello,
I have a problem with this code here.
cmd.CommandText = "UPDATE DBPROF SET (ID,NAME,PROF) = " +<br />
"(?,?,?) WHERE (rowguid = '"+strGUID+"')";<br />
<br />
cmd.Parameters.Add(@"ID",dID);<br />
cmd.Parameters.Add(@"NAME",strName);<br />
cmd.Parameters.Add(@"PROF",strPROF);<br />
Why this code isn't working for updating db ?
also values instead "?" don't work:
(@ID,@NAME,@PROF)
|
|
|
|
|
You don't say what database you are using - This is important information as there are subtle differences between each type of database (even from the same Vendor - MS Access has some differences with SQL Server)
Also, you have ommitted to describe what the cmd object is. Is it an OleDbCommand, a SqlCommand, an OracleCommand or what? There may be some slight differences in the way each of these work as a result of interacting with different databases. This information is also good to know as it helps diagnose the problem.
Why are you using parameters for the update values, but you are injecting the GUID into the string (this leaves you open to a SQL Injection attack)
Finally, a stab at the answer:
The syntax I would use is:
UPDATE DBPROF SET ID=@id, NAME=@name, PROF=@prof WHERE rowguid='GuidValueHere' This is valid for SQL Server.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
cmd.CommandText =
"UPDATE DBPROF SET ID = ?, " +
"NAME = ?, PROF = ? " +
"WHERE rowguid = ?";
cmd.Parameters.Add( "ID", dID );
cmd.Parameters.Add( "NAME", strName );
cmd.Parameters.Add( "PROF", strProf );
cmd.Parameters.Add( "rowguid", strGUID ); If you're using the SQL Server Managed provider, change the ? in CommandText for @param names, then supply the same @param names for the corresponding parameters:
cmd.CommandText =
"UPDATE DBPROF SET ID = @id, " +
"NAME = @name, PROF = @name " +
"WHERE rowguid = @rowguid";
cmd.Parameters.Add( "@id", dID );
cmd.Parameters.Add( "@name", strName );
cmd.Parameters.Add( "@prof", strProf );
cmd.Parameters.Add( "@rowguid", strGUID );
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thx, works
|
|
|
|
|
hi,
I have installed MSDE (sql2kdesksp3.exe) in my PC by following the guidelines given in Lars article
http://www.codeproject.com/database/ConfigureMSDE.asp[^]
but when i tried to connect using osql (C:\> osql –E –S localhost\myinstance),
it is giving the following error: "Server does not exist or access denied".
i dont have any idea what's wrong? i could see that the service is running.
i am running on a dual boot with Linux and Windows 2000 professional.
I installed it in Administrator login only without any password.
Can you please help?
|
|
|
|
|
Hello,
What is the best place to keep my SqlConnection object? Should I have only one application-wide? or one new every time I need it?
Where should I open it? every time I'm going to use it? or at the beginning of my application?
The application is what I consider more or less database intensive. It uses a lot of the database for some time, and then stands idle for some other time.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
What is the best place to keep my SqlConnection object?
I usually create some sort of ApplicationConfiguration class which pulls out the information from the app.config or web.config file and creates a SqlConnection for me.
Luis Alonso Ramos wrote:
Should I have only one application-wide? or one new every time I need it?
I'd say a new one each time you need it. .NET has connection pooling so you don't need to worry about storing one somewhere for when you need it. Also, if you create a new connection each time you need it you can potentially have two connections open at the same time.
Luis Alonso Ramos wrote:
Where should I open it? every time I'm going to use it? or at the beginning of my application?
I'd say open it, use it, close it. Like this:
SqlConnection conn = MyAppConfig.GetDatabaseConnection();
try
{
conn.Open();
}
catch(SqlException ex)
{
}
finally
{
conn.Close()
}
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
Colin's advice is good (as always). Just remember that for connection pooling to work, your connection string must be identical for all the connections (otherwise a new pool is created, which has significant overhead). Use a single string reference for the connection string,and preferably use integrated security so you don't have to worry about exposing usernames or passwords.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Hello,
I'm designing a new database, and I was wondering what is better: to use default values for some fields, or to allow NULL values?
I know NULLs are more of a hassle because you have to explicitly check for NULL everytime, but they allow the user to indicate if the value was provided or not. With default values you don't have to check every time for NULL, but you don't know if the user entered the default value or didn't enter anything.
I was thinking about using default values for most fields, and only using NULL values where I really need them (eg. date when an employee was fired).
Any thoughts on this?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
what is better: to use default values for some fields, or to allow NULL values?
It depends on your situation. If you need to beable to store a value that is essentially unknown, or doesn't exist then you need a null. If your column is mandatory but there is a frequent value being repeated you might consider making a default value.
I'd say the easiest rule to use is: If I need to know that the user didn't enter anything I use a null, otherwise I use a default (or force the user to input something)
So things like AddressLine2 might default to an empty string as it doesn't matter that the user didn't enter anything. But something like a TaskCompletionDate may be null because the task may be in progress and you don't know when it will complete.
By the looks of it, you have pretty much come to this conclusion anyway.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
Colin Angus Mackay wrote:
Does this help?
Thanks. Yes, it was what I was thinking. But somewhere I read that NULL values shouldn't exist, and they shouldn't be used. But I think they're like gotos, sometimes, in specific conditions, they might be the best solution.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
create trigger trgInsertDeposit
on Deposit --Deposit is table
for insert
as
declare @cAccountNumber char(15)
declre @mAmount money
declare @dDateDeposited datetime
if insert into Deposit values(@cAccountNumber ,@mAmount,@dDateDeposited )
begin
update Customer --customer is a table with column as mbalance
set mbalance = mbalance + insert.@mAmount where
cAccountNumber = @cAccountNumber
Print 'CUSTOMER BALANCE TABLE UPDATED'
Commit Tran
end
|
|
|
|
|
Rather than just say "What is wrong with this SQL code" and then paste the code, at least tell us the error message(s). I cannot tell from your code what it is supposed to do, so that also makes it difficult. Also, you don't mention the database product (there are subtle differences between each database product that may be important in solving problems)
From what I can see this is not even valid SQL code.
if insert into Deposit values(@cAccountNumber ,@mAmount,@dDateDeposited ) This is not a condition. You need a condition with an IF statement. In this case, a condition is something that will evaluate to true or false. An INSERT statement does not do this.
mbalance + insert.@mAmount That makes no sense what-so-ever. What is the insert doing there?
You do a COMMIT TRAN but is this really a wise thing to do inside a trigger?
Please don't take the following the wrong way; I am trying to help. There is so much that is wrong or not-understandable about this code that I cannot help you further. I would suggest that you find a good book on SQL and read it through (SQL Server Books On Line is a good reference, but you need a tutorial).
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
Hi,
I'm new to VB.Net so I think I'm doing something totally wrong.
I have a form with several textboxes. They are bound to a dataset at designtime by setting the properties in de propertypanel.
txtVoornaam.Text=dsAdressen.Personen.Voornaam
When I search for a specific record in a table of the dataset using its PK, I find the record, but my textbox doesn't show the found value. I use this code
Dim dr as DataRow
dr = dsAdressen.Personen.Rows.Find(PK)
or
dsAdressen.Personen.FindByPersoonsID(PK)
I know the record is found, because with this code my textbox shows the right information
txtVoornaam.Text = dr("Voornaam")
Why do I have to use code like -- txtVoornaam.Text = dr("Voornaam") -- to show the found value in the textbox. The textbox databinding properties are set, so I expected that the right value should appear automatically.
Where am I going wrong??
Thanks,
 Hendrik Jan
|
|
|
|
|
Can anyone suggest me on what best possible option it could be to port data from MS-SQL to Oracle? Espacially for large databases and when I need to do this several times a day.
I am asking about best possible option as I alreay have few options and could not decide which one to adopt.
|
|
|
|
|
Hi Sharma,
Could you tell me what are the options, you have and how big is your database? Are you using any tools to migrate the data? Which version of SQL and which version of Oracle? I may help you in this..
Thanks
Srinivas
|
|
|
|
|
Oracle has a SQL migration tool to port over from MSSQL. See the Oracle TechNet site.
onwards and upwards...
|
|
|
|
|
dear all:
I'm trying to use OLE DB to access the SQL Server instead of ODBC(MFC) which I was quite familar. Here is some simple codes I used to open data source:
CString m_username,m_password;
...
...
CDBProSet m_dbproset;
m_dbproset.AddProperty(DBPROP_INIT_DATASOURCE,L"SQL_SERVER_HOSTNAME");
m_dbproset.AddProperty(DBPROP_AUTH_USERID,m_username);
m_dbproset.AddProperty(DBPROP_AUTH_PASSWORD,m_password);
m_dbproset.AddProperty(DBPROP_INIT_CATALOG,L"DEFAULT_DATABASE");
CDataSource m_datasource;
m_datasource.Open(L"SQLOLEDB",&m_dbproset,1);
When I call CDataSource::Open menthod I was informed that some OLE DB process make mistake.
Can anyone tell me is there anything wrong with my codes or I missed sth?
Thank you very much.
Lisoft
|
|
|
|
|
Maybe if you posted the actual error message someone could help...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
As we known,we can get record set like the following:
_RecordsetPtr pRst = pCmd.Execute(0,0,"ss");//pCmd is a ADO Command object,"ss" is the name of stored procedure to be executed.
and get return value or output parameter after call pRst->Close();
but when the stored procedure has no recordset,pRst will be a closed Recordset,it can't be close again.This time I can't get the return value or output value,Why?
You can send mail to me discussing this problem:
xiaolinzi0941@hotmail.com,thanks!
|
|
|
|
|
Hi there,
I'm trying to use ADO in my app. Here is an example query:
SELECT [Translation] FROM TRANSLATED_OTHER WHERE [Language] = 'Chinese' AND Text = 'Length';
If I call Fields::GetItem("Translation") on my RecordSet I get back the correct value. However if I use Fields::GetItem(0) a _com_error is thrown and I get the following error:
Item cannot be found in the collection corresponding to the requested name or ordinal.
What am I missing?
Thank you.
|
|
|
|
|
I don't quite remember but it may be that Item index in Recordset starts from 1 instead of 0.
Try using Fields::GetItem(1) instead.
Edbert
Edbert P.
Sydney, Australia.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
