|
Hi guys,
I have some place holder functionality in our Aata Access Layer that should return a properly sorted and paged subset of requested data. However, at the moment we do not have sufficient data to warrant writing the full blown stored procs to implement the most efficient sorting / paging.
However, i would still like to be able to return the correct subset of data in a data table. So how would i go about reducing the contents of the DataTable, whilst retaining the strongly typed data?
Cheers
Tris
Note: Using .NET 2.0
|
|
|
|
|
I would use a DataView and use The RowFilter property, as long as it is a small set. There is also sort functionality in the DataView as well.
Something like:
C#
DataView dv = yourDataTable.DefaultView;
dv.RowFilter = "column1 = '2'";
VB
dim dv as DataView = yourDataTable.DefaultView
dv.RowFilter = "column1 = "'2'";
Then you bind whatever control to the DataView
Hope that helps.
Ben
|
|
|
|
|
Hi Ben,
Thanks for the response.
I need to retain the strong type information of the origional table, basicaly as if the table had been populated origionaly from a pre-sorted and paged SQL query.
Cheers
Tris
|
|
|
|
|
Hello, ALL!
I'am a new user in MS SQL and I have a problem. I have a database backup file. My friend send it to me. I wanna load it into my SQL server (I haven't got that database on my PC). I do it so:
1> restore database new_kupimebel from disk='c:\share\newdb.bak'<br />
2> go
But i got an error
<br />
Msg 5133, Level 16, State 1, Server HOME\SQLEXPRESS, Line 1<br />
Directory lookup for the file "c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\newdb.mdf" failed with the operating system error 3(Системе не удается найти указанный путь.(rus) System can't find that path (en).).<br />
Msg 3156, Level 16, State 3, Server HOME\SQLEXPRESS, Line 1<br />
File 'newdb' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\newdb.mdf'. Use WITH MOVE to identify a valid location for the file.<br />
Msg 5133, Level 16, State 1, Server HOME\SQLEXPRESS, Line 1<br />
Directory lookup for the file "c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\newdb_log.ldf" failed with the operating system error 3(Системе не удается найти указанный путь.(rus) System can't find that path (en).).<br />
Msg 3156, Level 16, State 3, Server HOME\SQLEXPRESS, Line 1<br />
File 'newdb_log' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\newdb_log.ldf'. Use WITH MOVE to identify a valid location for the file.<br />
Msg 3119, Level 16, State 1, Server HOME\SQLEXPRESS, Line 1<br />
Problems were identified while planning for the RESTORE statement. Previous messages provide details.<br />
Msg 3013, Level 16, State 1, Server HOME\SQLEXPRESS, Line 1<br />
RESTORE DATABASE is terminating abnormally.<br />
1>
The directory MSSQL.2 isn't in my PC
C:\Program Files\Microsoft SQL Server>dir<br />
<br />
29.12.2006 20:29 DIR .<br />
29.12.2006 20:29 DIR ..<br />
29.12.2006 20:25 DIR 80<br />
29.12.2006 20:26 DIR 90<br />
29.12.2006 20:26 DIR MSSQL.1<br />
0 files 0 byte<br />
5 dirs 2 763 608 064 bytes free<br />
<br />
C:\Program Files\Microsoft SQL Server>
How can I load that database into my SQL server?
Sincerely,
tradakad
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
Hi
Just attach existing database instead of restoring database. This is simple as you are not going to restore it... right ??
Harini
|
|
|
|
|
But I haven't got that database on my PC yet. should I create it? i tryed so:
<br />
1> create database newdb<br />
2> go<br />
1> restore database newdb from disk='D:\urban\tmp\newdb.bak'<br />
2> go<br />
Msg 3154, Level 16, State 4, Server I_01\SQLEXPRESS, Line 1<br />
The backup set holds a backup of a database other than the existing 'newdb' database.<br />
Msg 3013, Level 16, State 1, Server I_01\SQLEXPRESS, Line 1<br />
RESTORE DATABASE is terminating abnormally.<br />
1><br />
may be I didn't understand you?
how can I attach that backup file into existing db? thanx!
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
Hi
Use SQL Enterprise Manager (SQL 2000) to attach database.
Just follow this steps (if you have installed in your machine).
If this is the first time you are opening it. Then you need to register SQL Server. Just right click on the group, Click New SQL Server Registration, do create it using the wizard window.
Once you create it.
Right Click the Database-> All tasks -> Attach Database. And select the backup file. By default 'Attach as' will be your database name, you can change it as your wish.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Thank you. Harini 
|
|
|
|
|
hm.. I have MS SQL 2005 Express Edition. I think there aren't any GUI tools there.. can I do it from sqlcmd?
and another question - I think SQL Enterprise Manager isn't a free tool? can I download it? should I registred it after that?
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
Use the Enterprise Manager and right click the database and select the restore the database then follow the wizard but dont forget to change the path where the logical database will be store
|
|
|
|
|
I havent got ANY GUI tool so I cant do it. now I download "SQL Server Management Studio Express" but I solved this problem with sqlcmd
1> restore database new_kupimebel<br />
2> from disk='D:\urban\tmp\newdb.bak'<br />
3> with move 'newdb' to "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\newdb.mdf",<br />
4> move 'newdb_log' to "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\newdb.ldf"<br />
5> go
now all is ok.
thanx 2 all
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
That is great. You did it.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Thank you. Harini
|
|
|
|
|
thank you to
i did it.
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
Hello there.I have a textbox control for 'username',a textbox control for 'password' and a 'submit' button.the user enters the username and password and clicks on the submit button.i need to write code for this button click event to check for the availability of the user in the database.i should also create a table in the database which has the fields username,password and usertype(administrator,dealer,client etc).so using this table i need to validate the user.can i get help in writing code.i will be greatful if u could help me to some extent atleast.
thanx in advance.
|
|
|
|
|
this is a unit with sql class
<br />
using System;<br />
using System.Data;<br />
using System.Configuration;<br />
using System.Web;<br />
using System.Web.Security;<br />
using System.Web.UI;<br />
using System.Web.UI.WebControls;<br />
using System.Web.UI.WebControls.WebParts;<br />
using System.Web.UI.HtmlControls;<br />
using System.Data.SqlClient;<br />
<br />
public class SqlClass<br />
{<br />
public SqlClass()<br />
{<br />
}<br />
<br />
<br />
static public SqlConnection myConnection = null;<br />
<br />
public string GetConnection()<br />
{<br />
string connection_string = System.Configuration.ConfigurationManager.ConnectionStrings[1].ToString();<br />
<br />
try<br />
{<br />
myConnection = new SqlConnection(connection_string);<br />
myConnection.Open();<br />
}<br />
catch (SqlException err)<br />
{<br />
return "Erron in connection :" + err.Message;<br />
}<br />
<br />
finally<br />
{<br />
if (myConnection != null)<br />
myConnection.Close();<br />
}<br />
<br />
return null;<br />
}<br />
<br />
public DataSet SqlOpen(string sql_cmd)<br />
{<br />
if (myConnection != null)<br />
myConnection.Close();<br />
<br />
myConnection.Open();<br />
<br />
SqlDataAdapter myDataAdapter = new SqlDataAdapter(sql_cmd, myConnection);<br />
DataSet myDataSet = new DataSet();<br />
<br />
myDataAdapter.Fill(myDataSet);<br />
<br />
myConnection.Close();<br />
<br />
return myDataSet;<br />
<br />
}<br />
<br />
public void SqlRun(string sql_cmd)<br />
{<br />
if (myConnection != null)<br />
myConnection.Close();<br />
<br />
myConnection.Open();<br />
SqlCommand cmd = new SqlCommand(sql_cmd, myConnection);<br />
cmd.ExecuteNonQuery();<br />
myConnection.Close();<br />
<br />
}<br />
<br />
}<br />
this is a part of unit with password check;
<br />
protected void DeleteCookies()<br />
{<br />
Response.Cookies["UType"].Expires = DateTime.Now.AddMinutes(-30);<br />
Response.Cookies["UName"].Expires = DateTime.Now.AddMinutes(-30);<br />
Response.Cookies["UId"].Expires = DateTime.Now.AddMinutes(-30);<br />
Response.Cookies["UPwd"].Expires = DateTime.Now.AddMinutes(-30);<br />
Response.Cookies["FPwd"].Expires = DateTime.Now.AddMinutes(-2);<br />
}<br />
<br />
<br />
public SqlClass s = new SqlClass();<br />
<br />
protected void PasswdBtn_Click(object sender, EventArgs e)<br />
{<br />
string sql_cmd = "select UTypeId,UId from tblUsers where UName='" + Request["LoginText"] + "'";<br />
DataSet myDataSet = new DataSet();<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
DataRow myRow = myDataSet.Tables[0].Rows[0];<br />
DataColumn myCol = myDataSet.Tables[0].Columns[0];<br />
string UTypeId = myRow[myCol].ToString();<br />
myCol = myDataSet.Tables[0].Columns[1];<br />
string UId = myRow[myCol].ToString();<br />
<br />
sql_cmd = "select Type from UType where UTypeId='" + UTypeId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
string UType = myRow[myCol].ToString();<br />
<br />
switch (UType)<br />
{<br />
case "admin":<br />
#region case admin type<br />
sql_cmd = "select OwnerSId from Owners where UId='" + UId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
string OwnerSId = myRow[myCol].ToString();<br />
sql_cmd = "select Status from OwnerStatus where OwnerSId='" + OwnerSId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
string OwnerStatus = myRow[myCol].ToString();<br />
if (OwnerStatus == "active")<br />
{<br />
sql_cmd = "select UPassword from tblUsers where UId='" + UId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
DeleteCookies();<br />
<br />
if (myRow[myCol].ToString() == Request["PasswordText"])<br />
{<br />
string OwnerPassword = myRow[myCol].ToString();<br />
Response.Cookies["UType"].Value = UType;<br />
Response.Cookies["UType"].Expires = DateTime.Now.AddMinutes(30);<br />
Response.Cookies["UName"].Value = Request["LoginText"];<br />
Response.Cookies["UName"].Expires = DateTime.Now.AddMinutes(30);<br />
Response.Cookies["UId"].Value = UId;<br />
Response.Cookies["UId"].Expires = DateTime.Now.AddMinutes(30);<br />
Response.Cookies["UPwd"].Value = Request["PasswordText"];<br />
Response.Cookies["UPwd"].Expires = DateTime.Now.AddMinutes(30);<br />
<br />
}<br />
else<br />
{<br />
Response.Cookies["FPwd"].Value = "1";<br />
Response.Cookies["FPwd"].Expires = DateTime.Now.AddMinutes(2);<br />
}<br />
}<br />
}<br />
}<br />
}<br />
<br />
<br />
#endregion<br />
break;<br />
case "store":<br />
#region case store type<br />
sql_cmd = "select StoreStatusId from Stores where UId='" + UId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
string StoreStatusId = myRow[myCol].ToString();<br />
sql_cmd = "select Status from StoreStatus where StoreStatusId='" + StoreStatusId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
string StoreStatus = myRow[myCol].ToString();<br />
if (StoreStatus == "active")<br />
{<br />
sql_cmd = "select UPassword from tblUsers where UId='" + UId + "'";<br />
<br />
try<br />
{<br />
myDataSet = s.SqlOpen(sql_cmd);<br />
}<br />
catch (SqlException err)<br />
{<br />
Response.Write(err.ToString());<br />
}<br />
<br />
<br />
if (myDataSet.Tables[0].Rows.Count == 1)<br />
{<br />
myRow = myDataSet.Tables[0].Rows[0];<br />
myCol = myDataSet.Tables[0].Columns[0];<br />
<br />
DeleteCookies();<br />
<br />
if (myRow[myCol].ToString() == Request["PasswordText"])<br />
{<br />
string OwnerPassword = myRow[myCol].ToString();<br />
Response.Cookies["UType"].Value = UType;<br />
Response.Cookies["UType"].Expires = DateTime.Now.AddMinutes(30);<br />
Response.Cookies["UName"].Value = Request["LoginText"];<br />
Response.Cookies["UName"].Expires = DateTime.Now.AddMinutes(30);<br />
Response.Cookies["UId"].Value = UId;<br />
Response.Cookies["UId"].Expires = DateTime.Now.AddMinutes(30);<br />
Response.Cookies["UPwd"].Value = Request["PasswordText"];<br />
Response.Cookies["UPwd"].Expires = DateTime.Now.AddMinutes(30);<br />
<br />
}<br />
else<br />
{<br />
Response.Cookies["FPwd"].Value = "1";<br />
Response.Cookies["FPwd"].Expires = DateTime.Now.AddMinutes(2);<br />
}<br />
}<br />
}<br />
}<br />
}<br />
<br />
#endregion<br />
break;<br />
}<br />
}<br />
<br />
}<br />
LoginText.Text = "";<br />
<br />
<br />
}<br />
<br />
Good luck!
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
thank u very much for ur response
|
|
|
|
|
I'm very glad I can help somebody because I'm a very new user in ASP.NET.
Godd luck!
--
"Success is simple. Do what's right, the right way, at the right time."
best regards,
tradakad
|
|
|
|
|
I have execute SQL select max(distinct column_name1) from Table_name where column_name2='XXX'
This query is executed well in Query analyzer.
I tried with different ways.
I used CReceordset class.
In this case i am getting Invalid descriptor index as database exception while using Open(AFX_DB_USE_DEFAULT_TYPE,csQuery)
What way i cab execute this query???
Vibha Bhandarkar
|
|
|
|
|
It's possibly not working because no column name is being returned. Try changing the query to:
SELECT MAX(DISTINCT column_name1) AS max_column_name1
FROM Table_name
WHERE column_name2 = 'XXX' Using AS here will ensure that the column is named in the result set.
|
|
|
|
|
I have to monitor the database if still processing or not, because I have a script that transfer data from other database to another database, I dont have any idea if the process is finished
|
|
|
|
|
Try to run SQL Profiler and find out the process over there
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
Score it if you like my post
|
|
|
|
|
Hi,
Im working on a simple message board, where the user has two options when he visit the page, either he post for comment (update) or post new thread (insert).
On my table I have the following fields
MessageID
MessageTitle
MessageText
below is the SP that I made but does'nt seem to do the work, can you advice me on other apporach.
Thanks
Dom
ALTER procedure [dbo].[sp_alterMessage]
(
@messageTitle varchar(50),
@messageText text,
@messageID int output
)
as
begin
if not exists(select messageTitle from Messages where messageTitle= @messageTitle)
begin
insert into
Messages(MessageTitle, MessageText)
values
(@messageTitle, @messageText)
set @messageID = scope_identity()
end
else
begin
update Messages set MessageText = @messageText where MessageID = ltrim(rtrim(@messageID))
end
end
Thanks
Dom;)
-- modified at 4:50 Wednesday 28th February, 2007
|
|
|
|
|
I have some comments on your post:
1-What is the question?
2-It's better to replace:
select messageTitle from Messages where messageTitle= @messageTitle
with
select messageTitle from Messages where MessageID= @MessageID
pass it = 0 for new posts
3-don't use sp_ as a prefix for SP names, this will make SQL Server look for them in master database first which is a performance loss.
|
|
|
|
|
Hi Hesham,
1. This is the error that im getting when I execute the SP which is wierd becuase when you check on the SP that I created, I don't use @message_id as parameter, any ideas?
Msg 201, Level 16, State 4, Procedure sp_altermessage, Line 0
Procedure or Function 'sp_altermessage' expects parameter '@message_id', which was not supplied.
2. if not exists(select messageTitle from Messages where messageTitle= @messageTitle)
I use this to check whether the messagetitle that the user created was already on the database, i cannot use @messageID because this will surely pass the condition, remember messageID is not yet created because im on the process of adding record.
3. noted, thanks for the info.
regards
Dom;)
-- modified at 20:58 Wednesday 28th February, 2007
|
|
|
|
|
Hi
I know that you don't pass @message_id, I suggest that you pass it instead of @messageTitle for 2 reasons:
1- performance.
2- titles can duplicate.
In case that you in insert mode , Pass @message_id=0.
Or you can make it an optional parameter like:
Cerate Proc sp_altermessage (@message_id int =0)
thus when you don't pass it, SQL Server will assume it is equal to zero.
I wish this makes it clear
good luck
|
|
|
|
|
Boombastic! :->
Thanks
Dom
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
