Database table audit in SQL 2000 - Database Discussion Boards

R. A. Abbasi12-Dec-05 6:24

12-Dec-05 6:24

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.

Re: StoredProcedure error

Al Ortega12-Dec-05 6:59

Al Ortega

12-Dec-05 6:59

R. A. Abbasi wrote:
SUM([Order details].quantity AS UnitsSold)

try

SUM([Order Details].quantity) as UnitsSold

Re: StoredProcedure error

R. A. Abbasi12-Dec-05 7:13

R. A. Abbasi

12-Dec-05 7:13

Thanks Al Ortega - you got an eagle's eye.
Regards.

Problem with Insert Command

fitz23456711-Dec-05 22:56

fitz234567

11-Dec-05 22:56

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. D'Oh! | :doh:

Generate “Dataset” of Enterprise .Net Windows Application Problem

Fariborz Golara11-Dec-05 22:25

Fariborz Golara

11-Dec-05 22:25

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

Re: Generate "Dataset" of Enterprise .Net Windows Application Problem

Colin Angus Mackay12-Dec-05 4:53

Colin Angus Mackay

12-Dec-05 4:53

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

Fariborz Golara12-Dec-05 8:08

Fariborz Golara

12-Dec-05 8:08

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

Database table audit in SQL 2000

sumitjh11-Dec-05 22:01

sumitjh

11-Dec-05 22:01

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

Ado.net sqldatareader not working

kal13na1311-Dec-05 21:59

kal13na13

11-Dec-05 21:59

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

Re: Ado.net sqldatareader not working

miah alom12-Dec-05 4:16

miah alom

12-Dec-05 4:16

You cannot bind DataReader to the Winform DataGrid. This is possible in ASP.NET though.
So please bing the DataGrid to a DataSet instead.

Q313482 INFO: Roadmap for Windows Forms Data Binding
http://support.microsoft.com/support/kb/articles/q313/4/82.asp

Re: Ado.net sqldatareader not working

kal13na1312-Dec-05 18:49

kal13na13

12-Dec-05 18:49

hi is this possible in asp.net i am using asp.net with C# .Could you tell how it can be done

thanks

kal13na13

Re: Ado.net sqldatareader not working

miah alom13-Dec-05 3:49

miah alom

13-Dec-05 3:49

Check this out.

http://www.sitepoint.com/article/asp-net-repeater-control

stored procedure problem

kal13na1311-Dec-05 21:39

kal13na13

11-Dec-05 21:39

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

Re: stored procedure problem

Fariborz Golara11-Dec-05 22:33

Fariborz Golara

11-Dec-05 22:33

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

Password

kenexcelon11-Dec-05 6:53

kenexcelon

11-Dec-05 6:53

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

Re: Password

Colin Angus Mackay11-Dec-05 21:03

Colin Angus Mackay

11-Dec-05 21:03

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

Re: Password

Al Ortega12-Dec-05 13:04

Al Ortega

12-Dec-05 13:04

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;"

Architecture of .Net windows applications problem

Fariborz Golara11-Dec-05 4:47

Fariborz Golara

11-Dec-05 4:47

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

Re: Architecture of .Net windows applications problem

Colin Angus Mackay11-Dec-05 4:50

Colin Angus Mackay

11-Dec-05 4:50

Regardless of what you do in your application I would set the referential integrity in the database. That is one of its key design features and one of the things it is best at. DataSets are bloated objects that, in my opinion, should generally be avoided if possible.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: Architecture of .Net windows applications problem

Michael P Butler11-Dec-05 5:03

Michael P Butler

11-Dec-05 5:03

Colin Angus Mackay wrote:
DataSets are bloated objects that, in my opinion, should generally be avoided if possible.

Interesting. As you are of the guys whose SQL and database knowledge I have a great deal of respect for, care to elaborate on what you see as the cons of DataSets so that I can see if I'm making any poor design decisions. (I use DataSets a lot for reading data into my C# apps, don't use them much for writing the data backout though)

Maybe an article or blog entry would be cool.

Michael

CP Blog [^] Development Blog [^]

Re: Architecture of .Net windows applications problem

Colin Angus Mackay11-Dec-05 6:05

Colin Angus Mackay

11-Dec-05 6:05

Here is an existing blog entry on the subject: http://weblogs.asp.net/fbouma/archive/2003/05/13/6966.aspx[^]

The main reason for me is that DataSets contain way to much functionality for most of what I ever want to do. Generally I only ever want to have one copy of data in memory at once. If I were to use datasets the DataAdapter would use the DataReader to get the information from the database anyway, then it would store it in the DataSet (copy one), which I would most likey transfer to my business objects (copy two) and then propogate to the user interface (copy three). I much prefer getting the information from a DataReader and controlling exacly what I do with it. (Maybe I'm just a control freak).

Generally I don't need to have referential integrity in a DataSet, or know a great detail about the table structure. Often, I will just create the business objects - one per row from the data reader. In fact, most of my SQL queries don't represent actual tables, but some sort of flattened view or the result of some aggregation or calculation. Certainly, there is absolutely no point in using a dataset if the results are going to be immediately streamed out to a file or the like because all you do is get the data, waiting for the dataset to be built, then stream it out.

I would say that if you are using databinding alot they are useful.

A quick way, I think, of determining if you can throw away the dataset is this: Are you creating the dataset, pulling the data into some other structure (your business objects, for example) then throwing away the dataset? If the answer is "yes", then you can get rid of the dataset.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

"show create table" equivalent in MSSQL

karanba11-Dec-05 1:58

karanba

11-Dec-05 1:58

I need a command in MSSQL that will give crete table sql script like in mysql "show create table..." command.

karanba

Re: "show create table" equivalent in MSSQL

Colin Angus Mackay11-Dec-05 4:48

Colin Angus Mackay

11-Dec-05 4:48

In the Enterprise Manager locate the table that you want the create script for and right click on it. Select All Tasks-->Generate SQL Script.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: &quot;show create table&quot; equivalent in MSSQL

karanba11-Dec-05 5:01

karanba

11-Dec-05 5:01

I know that but I need to do that with an sql command...

karanba

Re: "show create table" equivalent in MSSQL

Colin Angus Mackay11-Dec-05 5:15

Colin Angus Mackay

11-Dec-05 5:15

karanba wrote:
I know that but I need to do that with an sql command

To my knowledge you cannot.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Last Visit: 31-Dec-99 18:00 Last Update: 25-Apr-24 7:56

Refresh

ᐊ Prev 1...1995 1996 1997 199819992000 2001 2002 2003 2004 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.