|
why you dont declare the column in the design of table auto increamnet ?
|
|
|
|
|
Hi all, i am building a category tree like structure for my FAQ app. i need to show all active categories. The hitch is that
if a parent category is checked as inactive, then the sub or child categories under it should not be displayed even if they
are marked as active. I need a query which will return me such a resultset which i can display in a table. the columns in my
table are categoryid, category, ParentcategoryID,companyID and active. The ParentcategoryID contains id of another category
or 'No parentcategory' which denotes its the main category. It is easy to get the immediate parentcategory and find out if
its active or not. But to find out if that parent category's parent and so on is active or not is tough. please help.
|
|
|
|
|
|
|
hi,
1.first replace ? with @ symbol for all the variables declared with?
2.give ur query like this
select ExtractDigit ('<stringname>')
stringname--> your string name
T.Balaji
|
|
|
|
|
Dear All
in a multi threaded system, I want to access the sql server 2000 data base in 15 separate threads.
But now, when the connection of data base is lost , threads find it separately and they can not make other threads aware of this situation, also it is not possible to organize and manage the data base systematically.
now I want to manage my data base with better mechanisems.
I'd like to use design patterns for this purpose.
But I didn't find any useful information in this regard yet.
May you please let me know any information in this regard.
Thanks in advance
Mahdi
|
|
|
|
|
I'm no stranger to programming, but I am brand new to programming with databases. I use MS Visual C# 2005. I have tried making forms with datasets and table adapters and it looks just fine... that is, until I try to make a change.
I have a simple form with a single datagridview. I edit the records, click the Save button on the binding navigator, the tableadapter.Update() method returns the correct number of updated rows, but when I close the program and re-open it, the changes aren't there.
Am I leaving out something? Is there extra code needed? etc.?
Thanks.
|
|
|
|
|
I am able to pull the correct data in SQLDS1 for the dropdownlist. And, it seems as though SQLDS2 is pulling the correct data in the gridview. However, when selecting from the gridview to populate the detailsview, it only uses the rdb1aa field and i need it to be able to match the selected row data from rdb1aa and rdb2aa with two fields I am selecting from the JOIN in SQLDS3.
If anyone can help, I sure would appreciate it. My code is as follows:
Choose an Area of Study:
<asp:dropdownlist id="ddlPrefix" runat="server" datasourceid="SQLDS1" autopostback="true" datatextfield="rdb1aa" xmlns:asp="#unknown">
<asp:gridview runat="server" id="gvCourse" cellpadding="0" datasourceid="SQLDS2" datakeynames="rdb1aa" autogeneratecolumns="False" font-names="Arial" width="371px" allowpaging="True" pagesize="16" xmlns:asp="#unknown">
<footerstyle backcolor="#D59F0F" font-bold="True" forecolor="White">
<rowstyle backcolor="#FFFBD6" forecolor="#333333">
<pagerstyle backcolor="#D59F0F" forecolor="#333333" horizontalalign="Center">
<selectedrowstyle backcolor="#D59F0F" font-bold="True" forecolor="Navy">
<headerstyle backcolor="#820024" font-bold="True" forecolor="White">
<alternatingrowstyle backcolor="White">
<headerstyle backcolor="#820024" font-bold="True" forecolor="White">
<columns> <asp:commandfield showselectbutton="true">
<asp:boundfield datafield="rdb1aa" headertext="Prefix" readonly="true">
<asp:boundfield datafield="rdb2aa" headertext="Number" readonly="true">
<asp:boundfield datafield="rdi8aa" headertext="Title" readonly="true">
|
<asp:detailsview datasourceid="SQLDS3" cellpadding="2" headerstyle-backcolor="#BDCFE7" headertext="Course Description" autogeneraterows="False" id="dvDesc" runat="server" height="186px" width="164px" forecolor="#333333" gridlines="None" horizontalalign="Center" xmlns:asp="#unknown">
<footerstyle backcolor="#5D7B9D" font-bold="True" forecolor="White">
<commandrowstyle backcolor="#E2DED6" font-bold="True">
<rowstyle backcolor="#F7F6F3" forecolor="#333333">
<fieldheaderstyle backcolor="#820024" font-bold="True">
<pagerstyle backcolor="#284775" forecolor="White" horizontalalign="Center">
<fields>
<asp:boundfield datafield="r3raak" nulldisplaytext="No Description Found">
<headerstyle height="1px" horizontalalign="Left" verticalalign="Top">
<itemstyle font-bold="True" font-size="XX-Small" height="15px" horizontalalign="Left" verticalalign="Top">
<headerstyle backcolor="#D59F0F" font-bold="True" forecolor="White">
Font-Size="Small" Height="1px" HorizontalAlign="Center">
<editrowstyle backcolor="#999999">
<alternatingrowstyle backcolor="White" forecolor="#284775">
|
<asp:sqldatasource id="SQLDS1" connectionstring="<%$ ConnectionStrings:ConnectionString %>" xmlns:asp="#unknown">
SelectCommand="SELECT distinct RDB1AA from (select rdb1aa,rdb2aa from OPENQUERY (as400 , 'select rdb1aa, rdb2aa from shwcmfil.catmsp WHERE RDJHAA = ''A'' AND RDB8AA = ''AC''')) AS derivedtbl_1 ORDER BY RDB1AA" Runat="Server">
<asp:sqldatasource id="SQLDS2" runat="server" connectionstring="<%$ ConnectionStrings:ConnectionString %>" xmlns:asp="#unknown">
SelectCommand="SELECT RDB1AA, RDB2AA, RDI8AA FROM OPENQUERY (as400 , 'select * from shwcmfil.catmsp WHERE RDJHAA = ''A''') AS derivedtbl_1 WHERE (RDB1AA = @rdb1aa) ORDER BY RDB2AA">
<selectparameters>
<asp:controlparameter controlid="ddlPrefix" name="rdb1aa" propertyname="SelectedValue">
<asp:sqldatasource id="SQLDS3" connectionstring="<%$ ConnectionStrings:ConnectionString %>" runat="server" xmlns:asp="#unknown">
SelectCommand="SELECT RDB1AA, RDB2AA, RDI8AA, R3RAAK, R3IXAA FROM OPENQUERY (as400 , 'select * from shwcmfil.catmsp
JOIN shwcmfil.catxtp on (shwcmfil.catmsp.rdb1aa = shwcmfil.catxtp.r3b1aa and shwcmfil.catxtp.r3b2aa = shwcmfil.catmsp.rdb2aa)WHERE RDJHAA = ''A''') AS derivedtbl_1 WHERE (RDB1AA = @rdb1aa)">
<selectparameters>
<asp:controlparameter controlid="gvCourse" name="rdb1aa" propertyname="SelectedValue" type="String">
<asp:controlparameter controlid="gvCourse" name="rdb2aa" propertyname="SelectedValue" type="String">
|
|
|
|
|
hello, I WANT DESIGN ERD STORINGDUEF if must MY ENTITY(customer,yoodf,store,storekeeper)is it store receipt and store order entity or relation,please explain
|
|
|
|
|
How is database embedding Done?
|
|
|
|
|
Hi all,
I have to use MYSQL Database server as beckend for asp.net 2.0 web application. i have downlaoded and installed the MYSQL on my development machine(windows XP) and also downlaoded MYSQL Administrator for ease.
what are the remaining things which are essentail for connection asp.net 2.0 with MYSQL Database .
Best Regards
Rameez
|
|
|
|
|
http://connectionstrings.com/[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I have a Table.It is containing 4 column RegionID, MapFillColor, MapLineColor, MapCoordinates.
I want to run insert statement if the record is new when button press. But if the record already exist it should update the record REGIONID is primary Key in my Table. I donot know where i should put my function which check first the record exist if so then run update else insert statement.
Here is my Code
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=ZAFAR-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("Insert into Region(RegionID,MapFillColor, MapLineColor, MapCoordinates) values( '" + RegionID_txt + "','" + MapFillColor_txt + "','" + MapLineColor_txt + "','" + MapCoordinates_txt + "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
|
|
|
|
|
The T-SQL statement looks like this:
if(select count(*) from tablename where columnname='value') = 0<br />
begin<br />
insert into tablename values('value1','value2','value3','value4'.....)<br />
else<br />
Update tablename set columnvalue='newvalue' where regionID='value'<br />
end
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Since the T-SQL has already been given, I'll only add this:
don't put the SQL statement directly in the code; use a stored procedure instead. If the need arises to alter the SQL statement, it can be done at the stored procedure level without altering the code itself.
Tim
|
|
|
|
|
Typically,am new in stored procedures and have been trying to do the following.
To copy/move data in Payments Table to CustomerAccounts.The id in payment is CashId and want to Copy just the data when i pass a specific CashID.Here are my sample stored Procedures.
the Source of Data is "PaYments"
Destinatin is "CustomerAccounts".Any assistance is greatly appreciated.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 1
CREATE procedure Payments_CustomerAccounts
DECLARE @CatInsert varchar(2000),@CashID int,@Pay_Date date,@Amount money,@DrCr varchar(20), @PaymentModeId int,@BranchID int,@DeptID int
SET @CatInsert = 'INSERT INTO CUSTOMERACCOUNTS (ClientID,[Date],AmountPaid,[Cr/Dr],PaymentModeID,BranchID,DeptID) SELECT ' + CONVERT(varchar,@CashID) + ', Pay_Date,AmountPaid,PaymentModeID,BranchID,DeptID From PAYMENTS Where ClientID IN(' + @CashID + ')'
insert Into CUSTOMERACCOUNTS
([ClientID],[Date],[AmountPaid],[Cr/Dr],[PaymentModeID],[BarnchID],[DeptId]) VALUES(@CashId,@Pay_Date,@Amount,@[Cr/Dr],[@PaymentModeID],[BranchID],[DeptID])
-----------------2
Create Procedure INSERT_INTO_CUSTOMERACCOUNTS
Declare @CashID int
SELECT (cashID,Pay_Date,Amount,PaymentModeID,BranchID,DeptID)
From ACCOUNTS.PAYMENTS
Where CAshID=@CashID
INSERT INTO CUSTOMERACCOUNTS([ClientID],[Date],[AmountPaid],[PaymentModeID],[BranchID],[DeptId])VALUES(@CashID,@Pay_Date,Amount,PaymentModeId,BranchID,DeptID)
|
|
|
|
|
If I understood the problem correctly, in the first proc, instead of declaring @CashID as a variable, pass it as a parameter to procedure. After that a single INSERT INTO ... SELECT ... FROM ... WHERE ... will be sufficient.
Refer to INSERT (Transact-SQL)[^] and CREATE PROCEDURE (Transact-SQL)[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
CRecordSet::Update( ) is throwing the following exception.
Error:The cursor does not include the table being modified or the table is not updatable through the cursor
I donot know what could be the solution.
I am currently using SQL-Server 2005 ,VC++6.0 . Any help would be appreciated.
Thanks
|
|
|
|
|
|
This user is a habitual cross-poster. This message is in THREE forums.
Blog link to be reinstated at a later date.
|
|
|
|
|
hi ,
i make simple method that retrieve data from tables this is easy but my problem happen when i retrieve data from table that have relationship with other table, i make class to set value into properties when i read and bind it in Gridview.
i make for each table class that contains to properties and if i make all table in one class it work so i want to find solution to it with do this.
Tables
Create Table Catgerories (
CatgId int PRIMARY KEY,
Catgname nvarchar(50)
)
Create Table StudentInfo(
stId int PRIMARY KEY,
Stname nvarchar(50),
CatgId int REFERENCES Catgerories (CatgId)
)
create PROCEDURE dbo.StoredProcedure2
AS
SELECT StudentInfo.*, Catgerories.Catgname
FROM Catgerories INNER JOIN
StudentInfo ON Catgerories.CatgId = StudentInfo.CatgId
RETURN
Code:
public class CatgBL
{
int cid = 0;
public int Cid
{
get { return cid; }
set { cid = value; }
}
string catgName;
public string CatgName
{
get { return catgName; }
set { catgName = value; }
}
}
public class MyStudent
{
int stid;
public int Stid
{
get { return stid; }
set { stid = value; }
}
string sname;
public string Sname
{
get { return sname; }
set { sname = value; }
}
int catgid;
public int Catgid
{
get { return catgid; }
set { catgid = value; }
}
}
SqlConnection myCon = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\TestDb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
public ArrayList RetriveStdentInfo()
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = myCon;
cmd.CommandText = "dbo.StoredProcedure2";
cmd.CommandType = CommandType.StoredProcedure;
myCon.Open();
IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList sequence = new ArrayList();
ArrayList seqCat = new ArrayList();
ArrayList seqStudent = new ArrayList();
while (reader.Read())
{
MyStudent std = new MyStudent();
CatgBL cat = new CatgBL();
std.Stid = reader.GetInt32(0);
std.Sname = reader.GetString(reader.GetOrdinal("stname"));
std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
seqCat.Add(cat);
seqStudent.Add(std);
sequence.AddRange(seqStudent);
sequence.AddRange(seqCat);
}
return sequence;
}
|
|
|
|
|
I'm not sure if I understood you correctly, but I think you have two choices.
1. when looping through rows, create a new object for category only when the primary key changes. Because you have joined the data when fetching, you will have the same category multiple times.
2. fetch categories and students separately.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for you,may be i understand some things but i have some lack in ur soltion , i do this changes in code but the problem is still
public ArrayList RetriveStdentInfo()
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = myCon;
cmd.CommandText = "dbo.StoredProcedure2";
cmd.CommandType = CommandType.StoredProcedure;
myCon.Open();
IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList sequence = new ArrayList();
ArrayList seqCat = new ArrayList();
ArrayList seqStudent = new ArrayList();
while (reader.Read())
{
CatgBL cat = new CatgBL();
cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
seqCat.Add(cat);
seqCat.Add(cat);
}
while (reader.Read())
{
MyStudent std = new MyStudent();
std.Stid = reader.GetInt32(0);
std.Sname = reader.GetString(reader.GetOrdinal("stname"));
std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
seqStudent.Add(std);
}
sequence.AddRange(seqCat);
sequence.AddRange(seqStudent);
return sequence;
}
|
|
|
|
|
That's not quite what I meant. Something like this:
int prevCatId = 0;
while (reader.Read())
{
MyStudent std = new MyStudent();
std.Stid = reader.GetInt32(0);
std.Sname = reader.GetString(reader.GetOrdinal("stname"));
std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
if (prevCatID != reader.GetInt32(reader.GetOrdinal("Catgid")))
{
CatgBL cat = new CatgBL();
cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
seqCat.Add(cat);
prevCatID = reader.GetInt32(reader.GetOrdinal("Catgid"));
}
seqStudent.Add(std);
sequence.AddRange(seqStudent);
sequence.AddRange(seqCat);
}
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks again but the problem is still running
Object does not match target type
when i make debugging code the result is right but the problem appear in binding
StudentDAL std = new StudentDAL();
DataGrid1.DataSource = std.RetriveStdentInfo();
DataGrid1.DataBind();
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
