|
Simply run the update command. Below is a sample.
CREATE TABLE dbo.ctm
(
id numeric(18,0) IDENTITY,
col1 varchar(20) NULL,
col2 varchar(20) NULL
)
go
INSERT INTO dbo.ctm ( col1, col2 ) VALUES ( 'aa', 'bb' )
INSERT INTO dbo.ctm ( col1, col2 ) VALUES ( 'cc', 'dd' )
SELECT T.id, T.col1, T.col2 FROM dbo.ctm T
update ctm
set col1 = col2, col2 = col1
SELECT T.id, T.col1, T.col2 FROM dbo.ctm T
|
|
|
|
|
Hi experts,
I have the following problem when saving data from an XML-file to a database using the OleDbDataAdapter:
I'm using a typed dataset, derived from the xml-schema.
I load the xml-file into the dataset and try to save the data to the database. (the dataAdapter has all the commands (select, insert, update, delete) properly configured.
When it's all new data, there's no problem, all data is saved correctly.
But it's also possible that the xml-file contains data that is already in the database (primary key already exists), so this data needs to be updated (instead of re-inserted)(this is what a dataAdapter takes care of, right ?)
I tried 2 approaches:
1) Only loading the xml-data into the dataset:
When i use the update-method of my dataAdapter and there is data that is already in the database (primary key already exists) i get an oleDbException.
2) First load the database-data in the dataset, and then load the xml-file in the dataset:
I get a ConstraintException while loading the xml-data because primary key already exists in the datatable of the dataset.
How can I fix this problem so that I get 1 table with updated old data and inserted new data ?
Any help is much appreciated... Thanks in advance...
John
|
|
|
|
|
I need to Get data from my Sql Server 2000 database from my web site host to my local dataserver. I need to do this in a way that the data is secure. I can use Enterprise manager to import data but this is not secure or is it?
Thanks
|
|
|
|
|
You could use ASP.NET and encryption. See this.[^]
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Here is the situation:
The user logs onto the database and calls a sql server extended stored procedure. I wrote the extended stored procedure in C/C++. How do I call another stored procedure from the C/C++ code? I want to do this without connecting to the database again (the extended stored procedure is already running within the sql server process). What is the API I should use (if there is any)?
My articles and software tools
|
|
|
|
|
|
|
When creating a StoredProcedure (listed below) in Microsft SQL Server Express, I receive the following un-expected error:
Error 195: 'SUM' is not a recognized function name.
The listing of the StoredProcedure is as below:
CREATE PROCEDURE GetProductUnits
@ProductID AS int
AS
BEGIN
SELECT Products.ProductID, Products.ProductName,
Products.UnitsInStock, UnitsOnOrder,
SUM([Order details].quantity AS UnitsSold)
FROM
Products INNER JOIN [Order details]
ON Products.ProductID=[Order details].ProductID
WHERE
Products.ProductID=@ProductID
GROUP BY
Products.ProductID, Products.ProductName,
Products.UnitsInStock, UnitsOnOrder
END
Any suggestion is highly appreciated.
|
|
|
|
|
R. A. Abbasi wrote: SUM([Order details].quantity AS UnitsSold)
try
SUM([Order Details].quantity) as UnitsSold
|
|
|
|
|
Thanks Al Ortega - you got an eagle's eye.
Regards.
|
|
|
|
|
Hi All,
Hope someone can help me on this I have written an insert command in VB for a connection to a MySQL database here is the code -
cmdSelect = cnn.CreateCommand
cmdSelect.CommandText = "SELECT Transaction_No, User_ID, Expiry_Date, " & _
"Expiry_Date_Hex, Product_ID, Update_Date, Key_Serial_No, Developer_ID, " & _
"Locking_Code, Unlock_Code FROM KeyIssueDetails"
mda.SelectCommand = cmdSelect
cmdInsert = cnn.CreateCommand
cmdInsert.CommandText = "INSERT INTO KeyIssueDetails(Transaction_No, User_ID, Expiry_Date, Expiry_Date_Hex, Product_ID, Update_Date, Key_Serial_No, Developer_ID, Locking_Code, Unlock_Code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Transaction_No", Microsoft.Data.Odbc.OdbcType.Int, 0, "Transaction_No"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("User_ID", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "User_ID"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Expiry_Date", Microsoft.Data.Odbc.OdbcType.DateTime, 0, "Expiry_Date"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Expiry_Date_Hex", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "Expiry_Date_Hex"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Product_ID", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "Product_ID"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Update_Date", Microsoft.Data.Odbc.OdbcType.DateTime, 0, "Update_Date"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Key_Serial_No", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "Key_Serial_No"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Developer_ID", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "Developer_ID"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Locking_Code", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "Locking_Code"))
prm = cmdInsert.Parameters.Add(New Microsoft.Data.Odbc.OdbcParameter("Unlock_Code", Microsoft.Data.Odbc.OdbcType.VarChar, 50, "Unlock_Code"))
'
mda.InsertCommand = cmdInsert
prm.SourceVersion = DataRowVersion.Original
prm.Direction = ParameterDirection.Output
mda.Fill(mds, "KeyIssueDetails")
DGMain.DataSource = mds
DGMain.DataMember = "KeyIssueDetails"
My Problem is that the command works fine bar the last parameter. "Unlock_Code" comes back as NULL even though the dataset accepts the data I enter. I am fairly new to this so I may just be missing something obvious but any help would be great I can't figure out where I went wrong.
|
|
|
|
|
I’m trying to tell my purpose by an example.
E.g. suppose you want to build an inventory system which has got,
• The following forms:
1- Item definition
1- Warehouse receipt
2- Warehouse dispatch
• Each form has got one “DataAdaptor” for retrieve and modify data:
Please explain which of the following solutions are better?
1- Generate “Dataset” of each form’s “DataAdaptor” separately.
2- Generate only one “Dataset” for all of the form’s “DataAdaptor”.
Thanks.
Fariborz Golara
|
|
|
|
|
If this is an enterprise application then you need to layer your system better. Your forms should not have any data adapters whatsoever. You should be applying a layering pattern so that all data access happens away from your presentation layer.
So, neither are acceptable solutions for an enterprise application.
You need to create a DAL/DAO (Data Access Layer/Data Access Object) layer that contains classes that do nothing but retrieve and modify the database. You then have a layer that represents your business logic, this translates the stuff from the database into something that your application finds useful. The business logic layer manipulates the data (thus sending commands back to the DAL/DAO layer) or the presentation layer accesses the business layer to retrieve the information (this may, again, send commands to the DAL/DAO layer but to retrieve information). Finally, that information can be passed upto the presentation layer where it is presented to the user. The presentation layer should know absolutely zero about your database or any other source of information. It should just know that it calls some method in the business layer and it gets an answer back.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thank you Colin for your answer, but please tell me about real part of my question
Which of the following solutions are better?
1- Generate one “Dataset”
2- Generate several “Dataset”
Thanks
Fariborz Golara
|
|
|
|
|
Dear Master
-i have created one data base named "Test"
-i have 2 table in this (i may add more table) called "A" and "B".
-I have 3 filed in "A" and "B" table
-I want to auidt in separtae table "Log_Audit" that if any changes in table "A" or "B" it should be logeed in "Log_Audit" table.
-It must log table name,filed name,old data,new data and date,time
-pls help me in doing this and if possible give me examlpe and code
Regards
Sumit
|
|
|
|
|
Hi
I am using asp.net in C# I am using sqldatareader where I am trying to select some rows from the (radiobuttonlist) from database and display in the datagrid which is not happening it gets stuck in the datareader.The action is taking place in the click button event the details to be displayed in the same and next page . here is the code
SqlConnection conn=new SqlConnection("server=KHWA73R;database=NorthWind;user id=sa;password=sa");
string strSelect=("SELECT CustomerID,OrderID FROM Orders" + " WHERE CustomerID = '' " + Radiobuttonlist1.SelectedItem.Value + "ORDER BY OrderID");
SqlDataReader reader;
SqlCommand mycmd=new SqlCommand(strSelect,conn);
try
{
conn.Open();
reader=mycmd.ExecuteReader();
DataGrid2.DataSource=mycmd.ExecuteReader();
DataGrid2.DataSource=reader;
DataGrid2.DataBind();
}
catch(Exception se)
{
Response.Write("Exception.."+ se.StackTrace);
}
finally
{
conn.Close();
}
if (Radiobuttonlist1.SelectedIndex > -1)
{
Label1.Text = "You selected: " +
Radiobuttonlist1.SelectedItem.Text;
}
Label1.Text ="Customers ID " + Radiobuttonlist1.SelectedItem.Text;
Server.Transfer("Print.aspx");
thanks
kal13na13
|
|
|
|
|
|
hi
is this possible in asp.net i am using asp.net with C# .Could you tell how it can be done
thanks
kal13na13
|
|
|
|
|
|
hi
I have created stored procedure for the selection,insertion and updation for updations while excuting the stored procedure it asks for declaring the variables I dont know how to declare variables in the stored procedure .Can anyone help me.
thanks of help in advance
kal2na2
|
|
|
|
|
you can declare in SQL Server procedure like this
DECLARE @local_variable data_type
E.g.
DECLARE
@I INT,
@S VARCHAR(10),
@M MONEY
For more information you could search "DECLARE" in "SQL Server Book Online"
Fariborz Golara
-- modified at 4:33 Monday 12th December, 2005
|
|
|
|
|
How do you set a password on an Access database. Once you do that, how would you access the password using vb .net. Will it be encrypted, will I need to decrypt the password? If so, how would I go about that?
Thanks
|
|
|
|
|
I'm assuming you mean that you want to create some sort of user/password table because if Access was able to give out the password protecting the database then it would, obviously, be a major security hole in Access; There would be many displeased people out there.
Anyway - see the response[^] in my article on SQL Injection Attacks and tips on how to prevent them[^] as it shows how to implement a user table with a salted hash (a type of one-way encryption.) password.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
You set the password in Access under the Tools\Security menu
Once it is set you change your connection string to include the password
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=YourPasswordHere;"
|
|
|
|
|
Hi
Please tell me in .Net Windows application which of the following is better?
1- using only one "DataSet" because we can
set referential between "DataTable" vs. SQL Server referential
2- using several "Dataset" but set referential in SQL Server.
Thanks.
Fariborz Golara
|
|
|
|
|