|
Be careful of the differences between a "User Defined Function" (what you have here) and a "Stored Procedure". (There is no such thing as a "Stored Function")
User Defined Functions (UDF) are more restrictive and can only use deterministic statements and functions. They cannot change the state of the database either. However they can still be useful as they can be inserted into complex SELECT statements to reduce the complexity and make them easier to read. But there is a performance penalty for this as the query optimiser cannot optimise what is in the UDF with the rest of the SELECT statement.
If you want to use a UDF from .NET code you need to wrap it up in a SELECT like this:
SELECT * FROM dbo.Function1(@UserName)
As to your questions. The .NET code you've provided does not match with the SQL code above it, so I don't really know how to answer.
chodicimrkev wrote:
How can I get data from the database? (I want to save them to the structure.)
Also, I am not sure what you mean by "the structure". What structure? Do you want to read the data into a dataset, or your own objects or what?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Thank you for your answer, you saved me for the second time When you help me with stored procedure last time, I decided to make more user-friendly interface - I decided to add possibility of editing of existing records - My idea was to make a function ReadUserData, which will be called in two cases: 1)During the editing 2)After successful registration to show all data again. So I created data structure that will be used to save of the data of edited user.
Private Structure UserData<br />
Dim strRecordID As Integer<br />
Dim strAllowed As Integer<br />
Dim strName As String<br />
Dim strSurname As String<br />
Dim strClass As String<br />
Dim strEmail As String<br />
Dim strUsername As String<br />
Dim strPassword As String<br />
Dim strSkin As String<br />
Dim strImagePath As String<br />
End Structure
What I want is to get the data from db to this structure, then I need to update it. I haven't a clue how to do it so I decided to try to make a function:
ALTER FUNCTION dbo.Function1<br />
(<br />
@Username varchar(50)<br />
)<br />
RETURNS TABLE<br />
AS<br />
RETURN ( SELECT * FROM Users WHERE Username = @Username)
Although I don't know how to retrieve data from db, so I was thinking about something like:
Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Parameters.Add("@Username", txtUsername.Text)<br />
cmd.Parameters.Add("@Password", inputPassword1.Value)
but in the other way - to get the values of these parameters.
Thanks a lot
|
|
|
|
|
First, an optimisation tip. Although I'm thinking it may not be important in VB.NET as the ByVal and ByRef must be declared in method parameters - In C# classes are by reference by default and structures are by value by default. However, I still think the general rule is sound that a structure should always be small (no more than 16 bytes is one figure I've heard). So, for something like this a class would be better (in my opinion).
Looking at the structure you've given. I'm assuming that RecordId is the primary key of the table. If that is the case then UserName should have a unique constraint applied to it also to make sure that two users with the same UserName do not get inserted in the database.
Disclaimer: I've tried to write in VB.NET, which isn't my language - I've not done any VB since version 3, so please be aware that there may be errors in the syntax.
To get the data from the database create a stored procedure that is similar to this:
CREATE PROCEDURE GetUserDetails(@UserName varchar(64), @Password varchar(64))
AS
-- This should always return zero or one records only.
SELECT RecordId, Allowed, Name, Surname, Class, Email, UserName, Password, Skin, ImagePath
FROM Users
WHERE Username = @UserName
AND Password = @Password
GO Then in your .NET application some code like this:
Dim cmd As New SqlClient.SqlCommand("GetUserDetails", vilemConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", txtUsername.Text)
cmd.Parameters.Add("@Password", inputPassword1.Value)
' The data from the Select can be returned in to a DataSet or through a DataReader
' As you are populating your own structure it is probably better to use the DataReader
Dim dataReader As cmd.ExecuteDataReader()
Dim details As New UserData()
If dataReader.Read()
Then
' A record has returned from the Stored Procedure
' The numbers in the GetInt32 / GetString methods refer to
' the position of the field. So, care must be taken to SELECT
' the fields in the same order in the stored procedure.
details.strRecordId = dataReader.GetInt32(0)
details.strAllowed= dataReader.GetInt32(1)
details.strName = dataReader.GetString(2)
details.strSurname = dataReader.GetString(3)
details.strClass = dataReader.GetString(4)
details.strEmail = dataReader.GetString(5)
details.strUserName = dataReader.GetString(6)
details.strPassword = dataReader.GetString(7)
details.strSkin = dataReader.GetString(8)
details.strImagePath = dataReader.GetString(9)
Else
' There is no record, therefore the user name and password don't match
' TODO: Put code here that will handle the failed login details.
End If
As this is quite a long reply... I'll continue in part two (Inserting the data) in a little while...
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Okay - Now for part two: Inserting the data
I've abbreviated the code here, because it gets a little repetative, you should get the idea.
Create a stored procedure like this:
CREATE PROCEDURE UpdateUserDetails(@RecordID int, @Allowed int, @Name varchar(64), @Surname varchar(64),
@Class varchar(64), @Email varchar(64), @Username varchar(64), @Password varchar(64),
@Skin varchar(64), @ImagePath varchar(64))
AS
-- Check to make sure the data exists already to be updated.
IF EXISTS(SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password)
BEGIN
-- Perform the update, all fields, except UserName and Password are updated here
UPDATE Users
SET Allowed = @Allowed, Name = @Name, Surname = @Surname -- and so on....
WHERE UserName = @UserName AND Password = @Password
END
ELSE
BEGIN
-- THere is no existing data to be updated, Raise and error - This will cause
-- a SqlException to be thrown in the .NET Application.
RAISERROR('The User does not exist', 16, 1);
-- Alternatively, an INSERT could be performed here.
END
GO In the .NET application you would write some code similar to this:
Dim cmd As New SqlClient.SqlCommand("UpdateUserDetails", vilemConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", details.strUserName)
cmd.Parameters.Add("@Password", details.strPassword)
' Keep adding parameters until all the parameters that the stored procedure takes are in.
' For consistency the parameters should be in the same order they appear in the stored procedure
' definition.
A final note. RecordId appears to be the Primary Key for the Users table. It is not a good idea to update that. It can cause all sorts of problems with Foreign Key relationships and depending on how you have your SQL Server set up it would most likely cause an error.
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
You're a database genius, again thank you very much. I really regard all your help and the time you devoted to it. I wouldn't be able to continue in this project without you.
The first part has just started working, I am going to try the second part (tomorrow, now I go to bed ). At the end of the development I will publish the whole project in Code Project.
Bye.
|
|
|
|
|
chodicimrkev wrote:
You're a database genius
Thanks.
Although I would like to just say that I've worked for over 12 years with various database systems and I am a firm believer that pratice makes perfect.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Dim Rs as ADO.Recordset
Rs.CursorLocation = adUseServer
Rs.CursorType = adForwardOnly
Rs.LockType = adReadOnly
Rs.Cachesize = 100
Rs.Statement = "SELECT * FROM TABLE"
Rs.Open
Base on the given properties above, ADO will first fetch the
initial 100 records from the server. Using a forward only
cursor, once I reached the 100th record, it will fetch the
next 100 records. Does the previous 100 records retain
in the recordset?
|
|
|
|
|
I think because it is a forward only recordset, the previous 100 records will not be available in the recordset in this case.
store your internet favourites online - www.my-faves.co.uk
|
|
|
|
|
Thanks. So if there are 100,000 records in the table, there will be only 100 records always present in the recordset? Meaning that the memory allocated for the recordset will more or less be constant (will not grow)?
|
|
|
|
|
I'm using Carlos Antonilli's ado class in VC6, and when I try to compile the project, I get the following error:
<br />
ado2.cpp(241) : error C2065: 'adExecuteRecord' : undeclared identifier<br />
Anybody have any clues for me?
------- sig starts
"I've heard some drivers saying, 'We're going too fast here...'. If you're not here to race, go the hell home - don't come here and grumble about going too fast. Why don't you tie a kerosene rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
This is constant used by ADO. so, you need to include the adodata.h file for them or replave constants with values.
So,
Either include
#include "adodata.h"
in your project or
Replace
adExecuteRecord with 2048
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
I don't have a file called "adodata.h".
Should I?
------- sig starts
"I've heard some drivers saying, 'We're going too fast here...'. If you're not here to race, go the hell home - don't come here and grumble about going too fast. Why don't you tie a kerosene rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Can any one help with the following. I am using a datagrid to display data from a sql database. There are other processes that update the database in the background and I want to display the most current data to my user. So I periodically update the displayed data by re-filling my bound dataset from the database. If the user has scrolled the datagrid down from the top then when the refresh occurs the datagrid scrolls to the top and the user loses his view of the area that he is interested in. I want to be able to update the displayed data but not have the datagrid scroll or alternatively I want to be able to scroll the datagrid back to it's previous position under program control. I have tried to use the NavigateTo method but this causes an exception and does not do what I want. This must be a common situation and I'm sure that I'm missing something. Suggestions please.
Lloyd Kreuzer
|
|
|
|
|
Is it possible increment/decrement and such a DataColumn through the Expression Property?
|
|
|
|
|
(Using VC++ 6.0 - I will not use .NET for this project, so don't bother suggesting it)
I want to have as little impact on the user's installation requirements as possible. In other words, I only want them to have to install my app and maybe a newer version of MDAC, but nothing more.
I'm pretty new to this database stuff. (I think) I want to use an Access97 data source, and I need to be able to create the database programmatically (including keys, indexes, and relationships) because the file won't be there when the app is intitially executed.
It's going to be a fairly small database with 7-10 tables and the largest table will have under 2250 records (worst case).
Is ADO a good choice? I'm planning to make use of Carlos Antollini's ADO class article.
Can someone recommend a decent book that covers both ADO and SQL?
Any other suggestions (not including the use of .NET) are welcomed.
------- sig starts
"I've heard some drivers saying, 'We're going too fast here...'. If you're not here to race, go the hell home - don't come here and grumble about going too fast. Why don't you tie a kerosene rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
I'd recommend not using Access if you can help it. If you own SQL Server, you can distribute MSDE to your clients. Then you can use stored procedures, which IMO simplifies the data layer enormously. Also, you can install SQL Buddy or similar and be able to browse the data on a client machine if you ever need to.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Why not Access?
I don't own SQL server (that I know of).
------- sig starts
"I've heard some drivers saying, 'We're going too fast here...'. If you're not here to race, go the hell home - don't come here and grumble about going too fast. Why don't you tie a kerosene rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Because Access is crap. I just bought the developer edition of SQL Server for $89, so it's bound to cost around $40-$50 in the states. That gives me the right to distribute MSDE (which is SQL Server, but cut down so it doesn't like to have more than 20 ( from memory ) users at once - perfect for a desktop app ). Access does not support stored procedures AFAIK. That alone is the end, as far as I am concerned.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
The DTL is a pretty nice way to talk to databases from C++. Makes recordsets look like STL collections and does a pretty good job with disconnected data.
Much nicer than the whole Recordset.Next() thing.
Uses ODBC, so access should be fine.
Ryan.
It's yet another shock to our system to discover that not only are we not the centre of the Universe and we're not made of anything, but we started out as some kind of slime and got to where we are via being a monkey. It just doesn't read well.
Douglas Adams
|
|
|
|
|
From my experience I think ADO is good choice. I have been using Carlos Antollini's ADO classes for a few years now, in combination with Access2000 data sources, without any problems. All those apps had limited users (max 10) and relative small databases.
I have been using the classes in VC++ 6.0, VS.NET 2002 and VS.NET 2003.
|
|
|
|
|
Hi All!
I need to put a column in a table and want that column to store the date at which that particular row was updated or some new row is added into the table.
I am doing this to check when the table is updated.
Kindly guide me for creating such a column in SQL.
With best regards,
Zee_Zee
|
|
|
|
|
If you want to store the time a row is inserted, create a default on it with a value of getdate()
e.g.
create table foo<br />
(<br />
bar datetime default getdate()<br />
)<br />
If you want to know the time a row is updated, try using a trigger and setting the time (via getdate) in the trigger.
store your internet favourites online - www.my-faves.co.uk
|
|
|
|
|
Hi all,
I have a problem. I have a database "Users", there are usernames, passwords, and other information, and I'm doing on the registration page - I want to be sure there will be no duplicity of usernames - I searched the old comments but it didn't help me, I have written this code:
Dim vilemConn As New SqlClient.SqlConnection
vilemConn.ConnectionString = "...."
'Here I set the Select command
Dim selectCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT * FROM Users", vilemConn)
Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
vilemDA.SelectCommand = selectCMD
Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(vilemDA)
vilemConn.Open()
Dim vilemDS As DataSet = New DataSet
vilemDA.Fill(vilemDS)
Dim vilemDR As DataRow = vilemDS.Tables(0).NewRow()
vilemDR("jmeno") = Server.HtmlEncode(txtJmeno.Text)
vilemDR("prijmeni") = Server.HtmlEncode(txtPrijmeni.Text)
vilemDR("trida") = Server.HtmlEncode(txtTrida.Text)
vilemDR("email") = Server.HtmlEncode(txtEmail.Text)
vilemDR("login") = Server.HtmlEncode(txtLogin.Text)
vilemDR("heslo") = Server.HtmlEncode(inputHeslo1.Value)
vilemDR("skin") = Server.HtmlEncode(dropdownSkin.SelectedValue)
vilemDR("image") = Server.HtmlEncode(txtImage.Text)
vilemDS.Tables(0).Rows.Add(vilemDR)
vilemDA.Update(vilemDS)
vilemDS.Reset()
vilemDA.Fill(vilemDS)
vilemConn.Close()
Although I don't know what SQL command I should use (and please send me all the syntax of it, with txtLogin.Text added, because I have problems with this - I am absolute beginner in SQL)
So I don't know what SQL command I should use, where should I place it and how to execute it, because I don't want to rewrite the Select command for later adding of the new record.
This is my experiment completed with msdn and this forums, either correct it or don't mind it, it doesn't work
'Nastavení příkazu SQL pro vyloučení duplicity
Dim NoDuplicityCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT COUNT(*) FROM Users WHERE login= " & txtLogin.Text, vilemConn)
'NoDuplicityCMD.CommandText = "SELECT COUNT(*) FROM Users WHERE login=@txtLogin.Text"
Dim a As Integer
a = NoDuplicityCMD.ExecuteNonQuery() 'is it good place to execute the query and should I write a= noduplicity... or only noduplicitycmd.executenonquery...
Response.Write(a)
Thanks very very much for your answers.
|
|
|
|
|
First and foremost - For SECURITY. NEVER pass a value directly from a user control in to a SQL Command. This is suseptable to injection attack. What would happen if, in the txtLogin box, I had written
''; shutdown with nowait;
The answer is that your CommandText would fully read:
SELECT COUNT(*) FROM Users WHERE login=''; shutdown with nowait; This would run your SELECT and then immediately terminate your SQL Server Process - any other queries would fail and no new connections would be permitted. And you (or your DBA) would have to manually restart the SQL Server.
Okay - now that you know more about security I'll get on with your question...
I would always interact with the database using stored procedures. You add a layer of security there if you only permit access to the stored procedures, then all you can ever do is what the stored procudures can do.
CREATE PROCEDURE RegisterUser(
@UserName varchar(64),
@Password carchar(64)
)
AS
IF EXISTS(SELECT * FROM Users WHERE login=@UserName)
BEGIN
RAISERROR('The user '+@UserName+' already exists', 16, 1);
RETURN;
END
INSERT INTO Users (login, password)
VALUES(@UserName, @Password)
GO
Then from your .NET application you can do something like this
SqlCommand cmd = new SqlCommand("RegisterUser", vilemConn);
cmd.Parameters.Add(new SqlParameter("@UserName", txtLogin.Text);
cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text);
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
}
This SQL and .NET code will register a user if one does not already exist, if one does exist an error is raised which you can then handle as appropriate.
Sorry, but I can only read VB.NET code, I don't know enough VB.NET to create the VB code without referring to a lot of books. Hopefully you will see what I am trying to achieve with the C# code - remember it is the Framework and not the language that is important here.
You will also need to add as parameters and extend the stored procedure for the other items in your table.
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Hi,
thank you for your reply, it helped a lot. However, I wasn't able to run it. It says this error:
Line 1: Incorrect syntax near 'RegisterUser'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'RegisterUser'.
Source Error:
Line 82: cmd.Connection.Open()
Line 83: cmd.Parameters.Add("@Password", inputHeslo1.Value)
Line 84: cmd.ExecuteNonQuery()
Line 85: '
Line 86: 'Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
Source File: c:\inetpub\wwwroot\VilemWeb\Register.aspx.vb Line: 84
Here's a code:
Dim vilemConn As New SqlClient.SqlConnection<br />
vilemConn.ConnectionString = "...."<br />
<br />
Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
cmd.Connection.Open()<br />
cmd.Parameters.Add("@UserName", txtLogin.Text)<br />
cmd.Parameters.Add("@Password", inputHeslo1.Value)<br />
cmd.ExecuteNonQuery()<br />
cmd.Connection.Close
and the stored procedure is here:
ALTER PROCEDURE RegisterUser<br />
( <br />
@UserName varchar(64), <br />
@Password varchar(64)<br />
)<br />
AS<br />
IF EXISTS(SELECT * FROM Users WHERE login=@UserName)<br />
BEGIN<br />
RAISERROR('The user CurrentUser already exists', 16, 1); <br />
RETURN<br />
END<br />
INSERT INTO Users (login, heslo)<br />
VALUES(@UserName, @Password)<br />
RETURN
BTW: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why?
|
|
|
|
|