|
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
|
|
|
|
|
Hello:
Could someone point me to the right track..I have creat ASP.NET in VS2005 web site that ppl will log in and If the user update the Database I would like it to display that user name on the page.
Thanks,
Eyungwah.
|
|
|
|
|
The most common way of doing this is to use triggers on the table you want to check for changes in, and then save date and username in an other table.
You can also add two fields, last modifiedBy and lastModifiedTime to each table you want to check for changes in, in addition you add a trigger and copies these values to an other table eg: TableNameLog if the original table was named TableName.
If you do not wan to use triggers you have to save the information in two places when a save occurs. You can use client-code or storede procedrues in the database.
/M
|
|
|
|
|
Thank you Mattias for your replied.
I have a Login.aspx page. On my default.aspx page which is contained Database Table. I was wondering if you could please give me some sample code.
I wanted to add "Last Modified By" field table and if the Logged in user update the table then have his/her name display under that "Last Modified By".
Thanks again for your help.
Eyungwah
|
|
|
|
|
Hi Guys!
I'm working with SQL Server 2000 and I have only started working with it for the last 3 weeks now. I'm basically teaching myself how to use it. Therefore, lots of trial and error Here are the porblems:
When I create a report and try to run it by pressing the play button, I get a Cannot find TargetServer URL error message. What am I supposed to enter for TargetServer URL, please explain with as little jargon as possible.
Second problem: After I allow the report to continue its execution, the report will show the desired output, but, if I copy the rdl file to the server location that I want the report to be stored in, the report will not run. It gives me the following error message:-
"An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source 'XXXXX'. (rsErrorOpeningConnection) Get Online Help
SQL Server does not exist or access denied."
Whats going on? Please explain with as little computer jargon as possible.
Please note that I am working for a company that requires me to work with this software and there is no time to go for a course to learn the software, I need to urgently solve this problem asap. Please help me somebody, or else I'm fired!!! please help, somebody
|
|
|
|
|
I'm using SQL Server 2005.
Is there any function for converting a string to lower case except for the first one that should be upper case?
_____________________________
...and justice for all
APe
|
|
|
|
|
select upper(left(MyColumn,1)) + lower(substring(MyColumn, 2, len(MyColumn)))
from MyTable
|
|
|
|
|
Just be aware that there are a few oddball words like O'Daniel or McDonald etc that will need to have special consideration when proper casing text.
CleAkO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Hi,
i use a Firebird Embedded DB for my App and would like create all the tables etc. without typing 100 SQL Statements.
Does anybody of you know any tool which can do that? I just found some for the firebird SERVER not for the embedded one.
Thank you for helping.
|
|
|
|
|
I use IBExpert[^]
If you scroll to the bottom of the page, you'll find a link to the free Personal Edition.
|
|
|
|
|
Thank you, it works for me 
For everyone else, if you use this tool on FB Embedded and you are promted for username and password but you never used it because you don't had to, use sysadmin / masterkey. That's the standard.
|
|
|
|
|
i want to execute the following string
insert into test values 'h"ll'
but it gives some error. which escape i should use,,,
please help me. any advise will be appreciated.
thanks in advance
sebastian
|
|
|
|
|
Sebastian T Xavier wrote: it gives some error
Be specific! What is the error message?
|
|
|
|
|
Incorrect syntax near 'h"ll'.
this is the error message, this is because of the " inside the string. how will we insert such strings into database.
thanks
sebastian
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
