|
If you use SqlCommand with parameters..This an solve alot of problems like formats and sql injection.
|
|
|
|
|
Assume that I have two tables in my database,one is Catgeory and the other is Item.These tables are related; Items can belong to categories. I want to have a business class for categories that represent a record in the category table. I also want this class to contain all information about items belonging to the category. My question is which class is suitable for storing the list for items information.(e.g. Collection,ArrayList,...).I fetch item records in my Data layer as a DataSet. Please show me how I can place data contained in DataSet to the class you think is suitable(e.g. Collection).I need to use this help me please
Hope you undrestand.
|
|
|
|
|
With .net 2.0 and generics..I would use List<Item>
In .net 1.1 I make a class that inherits CollectionBase as a specialized collection for Items.
Also I fill the collection using a datareader not a dataset..This should be faster.
|
|
|
|
|
Have you managed with this??
I also had a similar problem an no-one helped on any where so I tried fixing this myself, and it is working so far.
It's a very confusing subject, and you have to "study" to understand, I don't fully understand the concept, but what I am doing works so far.
I can send you screen shots of my code if you like.
Regards
Brendan
brcvogt@yahoo.com
|
|
|
|
|
Hey. I want to develop a little archive application (Windows Forms 2.0, and C#). I want to be able to install the application on one or more client machines, and have them access a database located on a server in a LAN.
I will be using either MS Access or SQL server 2005 Express Edition. My question is, where and how do I place the database so that it can be used by the clients? What is the smartest approach? Stored procedures? Web services?
I'm having a hard time finding an answer to this. I just need a few pointers to get started 
Maybe someone has some experience in writing client/server apps, and could tell me how they usually establish the connection between the client apps, and the database.
If, for example, I use MS Access, would I place the .MDB file in a shared folder on the server, and write the path in the connection string in the client app? Could I do this in any way with SQL server express?
What would be the "correct" way to go about this?
Thank you
-- modified at 3:51 Saturday 2nd September, 2006
|
|
|
|
|
mrgruby wrote: I will be using either MS Access or SQL server 2005 Express Edition. My question is, where and how do I place the database so that it can be used by the clients? What is the smartest approach? Stored procedures? Web services?
I would say SQL Server 2005 on the LAN with Stored Procedures. If you want to ever access it outwith the LAN then Web Services would be a better bet (but the web services would still use stored procedures - one is not a replacement for the other)
mrgruby wrote: how they usually establish the connection between the client apps, and the database.
With an appropriate connection string (like any other way to connect to the database). I'm really not sure what you expected of this answer. Perhaps you need to rephrase your question.
mrgruby wrote: Could I do this in any way with SQL server express?
SQL Server 2005 is a proper database server product. It is not like Access. SQL Server is designed to be accessed by potentially thousands of processes, unlike Access. Therefore, there is no concept of putting SQL Server files in a shared folder as external applications cannot and do not need access to them. They access the SQL Server via various communication mechanisms and the SQL Server process will manage all requests made to ensure efficient access to the database(s)
|
|
|
|
|
Thanks for answering
Colin Angus Mackay wrote: With an appropriate connection string (like any other way to connect to the database). I'm really not sure what you expected of this answer. Perhaps you need to rephrase your question.
When I look around, I find that there are alot of ways to accomplish the same thing. Some argue that one way is better than the other, and vise versa. But I feel that there must be a way that is appropriate and sufficient, without any fancy hacks 
All I need is to be able to access a data store, located on a server in a LAN, from any number of client machines. Just like I would access a database on a webserver from an asp.net app. I just don't know how to do it in a windows forms app, where the database is located on a LAN server. Maybe it's a simple answer, that makes me want to hide in shame. I just haven't been able to find it, and I have been looking around alot. Maybe my search criteria is bad, I don't know...
And don't worry, I'm not looking for a finished solution here, where is the fun in that? I just need to know a good way to establish a connection between a windows forms app and a database, in an environment such as the above mentioned.
No reason to walk down a dead end, if I can get the right directions from someone who's been there... 
|
|
|
|
|
mrgruby wrote: ust like I would access a database on a webserver from an asp.net app. I just don't know how to do it in a windows forms app, where the database is located on a LAN server.
You just specify the name of the machine on the LAN. www.connectionstrings.com[^] may be of help for you.
Paul
That's no moon, it's a space station. - Obi-wan Kenobi
|
|
|
|
|
mrgruby wrote: But I feel that there must be a way that is appropriate and sufficient, without any fancy hacks
I've never come across any "hacks" for getting database connections to work.
mrgruby wrote: Just like I would access a database on a webserver from an asp.net app. I just don't know how to do it in a windows forms app, where the database is located on a LAN server
Since connecting to the database is the responsiblity of ADO.NET (the part of the .NET Framework that deals with databases) it is the same in a windows application as for an ASP.NET application. Remeber the .NET Framework is divided neatly into areas that can be used on their own to build a whole application. Unfortunately many samples and wizard generated code likes to mash all the different components together so that it is difficult for a person new to .NET to see where one bit ends and another bit starts.
|
|
|
|
|
hi
Can anyone resolve my problem> I create a temp table and inserts data into it through Xml String
, spliting it by the character '|' . The data is inserted into the table fine but for last row it inserts only a part of it and does not inserts (" Remarks=""></item>|) of the xml string.
Can anyo please help or modify...the input xml string will be as given below.
{TO KNOW THE PROBLEM PRACTICALLY COPY THIS CODE IN NEW QUERY EDITOR AND EXECUTE}
--Declaring a temporary @TempTable
declare @TempTable table
(
AutoId int identity,
items varchar(max)
)
--Decaring a Variable
Declare @ExcelDataInXml varchar(max)
Set @ExcelDataInXml='<item TimeStamp="7/22/2006 12:00:00 AM" BlockName="s1" JobcodeId="80" UserInitial="amar" Error1="24" Error2="12" Error3="1" Error4="1" Error5="1" Error6="1" Error7="" Error8="1" Error9="" Error10="1" Remarks=" This is Test Data"></item>|<item TimeStamp="7/23/2006 12:00:00 AM" BlockName="s2" JobcodeId="80" UserInitial="amar" Error1="23" Error2="6" Error3="7" Error4="8" Error5="" Error6="7" Error7="" Error8="" Error9="" Error10="" Remarks="Second remark"></item>|<item TimeStamp="7/23/2006 12:00:00 AM" BlockName="s3" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="8" Error4="8" Error5="1" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s4" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="9" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s5" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="10" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s6" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="11" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s7" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="12" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s8" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="13" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s9" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="14" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="7/22/2006 12:00:00 AM" BlockName="s10" JobcodeId="80" UserInitial="amar" Error1="24" Error2="12" Error3="1" Error4="1" Error5="1" Error6="1" Error7="" Error8="1" Error9="" Error10="1" Remarks=" This is Test Data"></item>|<item TimeStamp="7/23/2006 12:00:00 AM" BlockName="s11" JobcodeId="80" UserInitial="amar" Error1="23" Error2="6" Error3="7" Error4="8" Error5="" Error6="7" Error7="" Error8="" Error9="" Error10="" Remarks="Second remark"></item>|<item TimeStamp="7/23/2006 12:00:00 AM" BlockName="s12" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="8" Error4="8" Error5="1" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s13" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="9" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s14" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="10" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s15" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="11" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s16" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="12" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2006 12:00:00 AM" BlockName="s17" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="13" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|<item TimeStamp="9/1/2007 12:00:00 AM" BlockName="sandeep" JobcodeId="80" UserInitial="amar" Error1="25" Error2="12" Error3="13" Error4="8" Error5="8" Error6="9" Error7="" Error8="1" Error9="1" Error10="1" Remarks=""></item>|'
--insert into TempTable
insert into @TempTable select * from SPLIT(@ExcelDataInXml,'|')
select * from @TempTable
thanx
sandeep
|
|
|
|
|
sandeep kumar pundhir wrote: insert into @TempTable select * from SPLIT(@ExcelDataInXml,'|')
I assume that SPLIT() is a UDF?
What does that function look like?
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Yes SPLIT is a User Defined Function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
-- following line added 10/06/04 as null
-- values cause issues
IF @String IS NULL RETURN
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
|
|
|
|
|
Your @ExcelDataInXml string is longer than 4000 characters, and your Split function only accepts 4000 characters.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
I have an ID field and it has been set to increment by 1 every time a record is added.
When I exported this table to another database server, it "lost" this identity. Now when I tried to select Modify from the menu, I keep on getting a SQL problem and then management studio closes. So my only other solution is to use a sql script to change this identity to IDENTITY (1, 1).
How do I do this??
The table name is Orders, and the ID field is OrderID_PK int, this is the primary key.
How do I keep the identity as it is when I import a table from one database to another??
I am using SQL Server 2005.
Regards,
ma se
|
|
|
|
|
When you import the table, try Copy object and data between SQL Server databases rather than just Copy table(s) and view(s) from the source database.
Check this out too: DBCC CHECKIDENT (Transact-SQL) [^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi
I get this exception .....sametime error: DeleteCommand affected 0 of the expected 1 posts.
Think i need commands for a commandbuilder object that doesn't include optimistic concurrency
i have primarykey in the table and so on !
sSql=?????
SelectCommand = New OleDb.OleDbCommand(sSql, con)
sSql=?????
DeleteCommand = New OleDb.OleDbCommand(sSql, con)
sSql = "Update (Postaddresser) SET Priority = @Priority, Id = @Id,Firmname = @Firmname,Mailaddress = @Mailaddress, " & _
"WHERE (Id = @Original_Id);" & _
"SELECT Priority,Id,Firmname,Mailaddress" & _
"FROM Postaddresser WHERE (Id = @Id)"
UpdateCommand = New OleDb.OleDbCommand(sSql, con)
rgds
|
|
|
|
|
Hi,
Iam using PUSH model to create my crystal report document.
The stored procedure which iam using returns a refcursor.
The first 3 columns of the table(FirstName,MiddleName and LastName) are combined as returned as single column,similarly columns address1,address2,city,state and zipcode are returned as single column.
Here is the Code:
OracleConnection cnOracle=new OracleConnection(connectionString);
cnOracle.Open();
OracleCommand cmdRegistrant = new OracleCommand("Pkg_WRTS_Retrieve.prc_getregdata", cnOracle);
cmdRegistrant.CommandType = CommandType.StoredProcedure;
try
{
OracleParameter paramFirstName=new OracleParameter("i_first_name",OracleDbType.Varchar2,50,ParameterDirection.Input);
if(txtFirstname.Text.Equals(string.Empty))
paramFirstName.Value=string.Empty;
else
paramFirstName.Value=txtFirstname.Text;
OracleParameter paramLastName=new OracleParameter("i_last_name",OracleDbType.Varchar2,50,ParameterDirection.Input);
if(txtLastname.Text.Equals(string.Empty))
paramLastName.Value=string.Empty;
else
paramLastName.Value=txtLastname.Text;
OracleParameter paramCountyName=new OracleParameter("i_county_name",OracleDbType.Varchar2,20,ParameterDirection.Input);
if(txtCounty.Text.Equals(string.Empty))
paramCountyName.Value=string.Empty;
else
paramCountyName.Value=txtCounty.Text;
OracleParameter paramCityName=new OracleParameter("i_city",OracleDbType.Varchar2,50,ParameterDirection.Input);
if(txtCity.Text.Equals(string.Empty))
paramCityName.Value=string.Empty;
else
paramCityName.Value=txtCity.Text;
OracleParameter paramCompany=new OracleParameter("i_compent_name",OracleDbType.Varchar2,200,ParameterDirection.Input);
if(txtCompany.Text.Equals(string.Empty))
paramCompany.Value=string.Empty;
else
paramCompany.Value=txtCompany.Text;
// create parameter object for the output
OracleParameter paramResultSet = new OracleParameter();
paramResultSet.OracleDbType=OracleDbType.RefCursor;
// this the output parameter which is returned from the stored procedure
paramResultSet.Direction = ParameterDirection.Output;
// add the parameter to the collection
cmdRegistrant.Parameters.Add(paramFirstName);
cmdRegistrant.Parameters.Add(paramLastName);
cmdRegistrant.Parameters.Add(paramCountyName);
cmdRegistrant.Parameters.Add(paramCityName);
cmdRegistrant.Parameters.Add(paramCompany);
cmdRegistrant.Parameters.Add(paramResultSet);
//Execute the CommandWrapper
OracleDataAdapter daUserDetails=new OracleDataAdapter(cmdRegistrant);
dataSet = new DataSet();
daUserDetails.Fill(dataSet,"WRTS_REGISTRANT");
RegReport = new RegistrantReportViewer();
RegReport.SetDataSource(dataSet.Tables[0]);
//RegReport.SetDataSource(dsReport);
RegistrantViewer.Visible=true;
RegistrantViewer.ReportSource = RegReport;
When i run the application the field's name and address are not displayed in the report,but all the other columns are displayed.
I think its because the datatabe has a combined value of 3 columns but im not able to display it in report properly.Similar for address column also.
Can anyone tell me how to map the datatable column to crystal report column so that the data will be displayed?????
Any reply will be appreciated!!!
Thanks,
Ratish.
Ratish
|
|
|
|
|
hi,
How to overcome this error
"TRANSACTION PROCESS ID 63 WAS DEADLOCKD ON LOCK RESOURCES WITH ANOTHER
PROCESS AND HAS BEEN CHOSEN AS THE DEADLOCK VICTIM.RETURN THE TRANSACTION"
Pls reply
Kavitha
|
|
|
|
|
Do you understand how a deadlock occurs?
Steve
|
|
|
|
|
A deadlock happens because two processes are locking ojects that the other needs. Essentially neither can continue until the other has completed. Since neither process can continue, then also neither process can complete. SQL Server will detect this and choose one process as the victim and cancel its transaction.
To prevent this happening in the first place you should try and ensure that all access to objects (e.g. tables) happen in the same sequence in all transactions. That is, if you have a transaction that updates table A then table B it will potentially deadlock with another transaction that updates table B then table A.
|
|
|
|
|
Hi,
i am geting this warning when i am sorting table through storedprocedure.
Warning: The table '#MM' has been created but its maximum row size (8186) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Plz help me out it's urrgent
|
|
|
|
|
SQL Server does not allow a row to span multiple physical file pages, which are 8KB in size. Therefore the row must be smaller than 8KB, minus a certain amount of per-page overhead, to fit on a single page. Temporary tables can potentially still be written out to disk, they just end up in tempdb's files.
The total size of all fixed-size fields, plus the maximum size specified for nvarchar, varchar and varbinary fields (plus a little overhead taken by nullable columns and the lengths and offsets of variable-length fields), exceeds the maximum allowed size. However, this is not in itself an error, because it's rare to use the maximum size of all variable-length fields in a single record. If you do, however, any INSERT or UPDATE statement where the lengths of the actual fields exceeds the limit will fail.
Solutions: reduce the number of fields in the table, reduce the maximum lengths of the fields, or change some fields to text, ntext or image types (which are allowed to be stored outside the page containing the rest of the row data).
|
|
|
|
|
I have SerialNumber(SN) ,city ,Country,version ,Region,,,etc in
parent table
For each SN in connectrix table there are multiple switch in
switch_header table. the details of switches are stored in
switch_details table.
For each switch we have different Frus that is stored in Fru table
that has information about fru details
I need to display Connectrix information along with their switches
and fru information as per search criteria.
When I Joined above 4 tables I am getting all the parent column as
repetation which I don't want to happen.
But some how user should understand the situation such that records
should return single SNO, and multiple switchsns ,multiple frus
I tried to populate data in heiarchial HTML table mannar using DataSet
But Gave up.
Is there any methodology from back end?
Any ideas on this?
Thanks in advance.
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
This sounds to be more of a question for .NET group or something.
One option in SQL Server would be to output more than one recordset from a procudure or something, but you are still dealing with a display issue which has everything to do with the application and nothing to do with SQL Server.
Since it is in the ADO group. Look into Nested Repeaters and you should find your answer there.
|
|
|
|
|
i need to wirte these stored procedures
1-
Add recored to the table
2-
use the above sp's to add the full data for the item in the tables(ks_media_item ,ks_media_item_content)
3-
update the data in the table ks_media_item
-if the value of item_no not found print msg "record not found "
-the columns (update_By , Udated_Date)willfill
4-
Rturn the all data Based on the Item_No from the tow tables
the tables are :
Ks_Media_Item(Item_no,Description,Added_Date,
Added_By,Update_By,Updated_Date)
2-
Ks_Media_Item_Content(Item_no,content,Text_Length)
i hope to answer me
thank you all
yazan_shalabi
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
