|
Hi Friends
I want a query which perform the following for me
i have one table with three fields (code,field1, field2)
i want to update field2 with the values of field one for 200 records based on teh criteira that
code =1 or code=2 or code = 3 ....code=200;
How can i do that please help me
Naveed Kamboh
|
|
|
|
|
I'm not sure I've understood the question correctly but...
update tablename
set field2 = code
where code between 1 and 200
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
hi,
is there any limit of columns in sql table, if in a table there r almost 800 columns then can it work properly ??? what is the capacity of an sql database??? i think it is some giga bytes but wats the exact value ???
|
|
|
|
|
|
:->hello friends!!
i am creating a database , a table into that database and appending a coumns to table.
code-->
Dim tbl As New Table, cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\TagInfo.mdb" & ";"
tbl.Name = "tblTagInfo"
For ii = 0 To TotalTags - 1
tbl.Columns.Append tagName(0), adVarWChar, 50
Next
cat.Tables.Append tbl
<--code
now i want to insert data to column
but i face the problem
how to insert a data of only first(or any but only one) column ?
The problem is----
The 'Required property' of a all columns are by defaul True
hence we can not insert only one record , we have to specify all column and respective value.
My problem is how to set 'Required Property' of column to False/No
Thanks in advace
Rahul here
|
|
|
|
|
Dear all,
how i can make backup for database in sql server 2000 using C#
and also how i can make restore for it after that
thanks
plz help me
A.shawky
|
|
|
|
|
ahmed shawky wrote: how i can make backup for database in sql server 2000 using C#
and also how i can make restore for it after that
Strictly speaking C# cannot do this. It passes some SQL along to the database (using SqlCommand) in order to get the database to do it.
You create a SqlCommand object as you would normally with the CommandText set with the BACKUP[^] or RESTORE[^] command and then call ExecuteNonQuery()
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (inventoryIds)
this scenario the inventoryids is a collection of ids like below
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (1,2,3,4,5)
the above query want to take 5 rows from DB
this scenario i want to pass the ids at run time as a string in C#
Also how will i take the string in the DA class(C#) to execute it
guide me please
This method is in DA class
public DataSet SelectAssetStatusValueAdjustmentList(string lessorName,string inventoryIds)
{
string SqlCommand = "SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID from Inventory_Profile where InventoryID in (@InventoryIDs)";
Database DB = DatabaseFactory.CreateDatabase(lessorName);
DbCommand DbCmd = DB.GetSqlStringCommand(SqlCommand);
DB.AddInParameter(DbCmd, "InventoryIDs", DbType.String, inventoryIds);
DB.LoadDataSet(DbCmd, InventoryIdList,"TABLE");
return InventoryIdList;
}
While i pass the inventoryIds as a string of
collection 1,2,3,4,5...
This time the DA Class addinparameter is defined as string DbType.String
how will the query will take as collection of numbers or as a string while execution
-
|
|
|
|
|
Take a look at this page[^]. I've used the iterative method for SQL Server 2000 and it works really well!!
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
|
|
|
|
|
 hello friends,
i am creating a table using this
Dim tbl As New Table, cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\TagInfo.mdb" & ";"
tbl.Name = "TagInfo"
For ii = 0 To TotalTags - 1 '(any number)
tbl.Columns.Append tagName(ii), adInteger '(array of string type )
Next
cat.Tables.Append tbl
Dim CN As ADODB.Connection
Dim RS1 As ADODB.Recordset
Set CN = New ADODB.Connection
Set RS1 = New ADODB.Recordset
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\TagInfo.mdb;persist security info=false"
CN.Open
Now suppose there may be 7 columns created !
my problem is that how to insert a data into that column
That data may be in some array
plz specify VB command
Thanks in advance
Rahul here
|
|
|
|
|
hey..
Does anyone know how to store a (hierarchical-acyclic) tree relationship within a relational database?!
Related To:
{Relational structure}
{Set Theory}
{Graph Theory}
{Optimization}
{Computing}
Thanx alot in advance..
Elie
|
|
|
|
|
Does this helps...
----------------------------------
NodeID | Node | Parent
----------------------------------
0 | Parent |
1 | Programming | 0
2 |C |1
3 |C++ |1
4 |Java |1
5 |J2ee |4
6 | Win32 |2
7 | Win32 |1
----------------------------------
+Parent
|-+ Programming
| |+ C
| |- Win32
| |-C++
| |-C#
| |+ Java
| |-J2EE
-
बुरा जो देखण मै चला, बुरा न िमलया कोय,
जो मन खोजा आपणा तो मुझसे बुरा न कोय।
Translation
|
|
|
|
|
How to pull this information in a sql statement w/ good performance?
Mike Lasseter
|
|
|
|
|
I think the only way to pull out information will be through SPs. Can't say about performance though.
|
|
|
|
|
I'm glad you tried to answer.. what i meant was writing the code in sql or c# with accordance to maintaining the hierarchical structure i mentioned earlier..
==> How do u insert a hierarchical relationship in a database?!
Thanx 4 ur help, and i hope ulll be able to answer me back 
Elie
|
|
|
|
|
|
I am trying to restore 2 bak files which was created using SQL Server 2000. However, I am now working with SQL Server 2005 and it wouldn't let me do so. Instead, I these 2 error messages for each file.
Restore failed for Server 'C0K69319'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\Snapshot.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
----
error for the second backup file:
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
Since I am new to SQL Server 2005, any help would be greatly appreciated.
John
"Some of the world's most greatest feats were made by people not smart enough to know that they were impossible"
|
|
|
|
|
Ok, I have a connection string from ADO.Net 2.0 that is named this, name="PDS.LSS.Properties.Settings.StateConnectionString" from my app.config file. This is a dummy database that is used just as a framework for the app.
When the user starts the app they do a File-Open and select the database that they are making changes to, this is the same structure as the dummy.
I have been looking at the ConnectionStringBuilder but can't seem to make sense of how do I replace the DataSource= section of this string, since all of the bound datasets seem to pull the connection string from here!
I am setting a text string to the path of the new datasource, I just can't find the right syntax structure to say dbpath = XXXXX???
Help!!!! Thanks everyone!
David
You are only as good as your last project.
|
|
|
|
|
hello,
I am wondering if it is possible to get a specific user group from my Ms Access database.In my application the user menu depends on the user group.So I need to assign a specific menu for each group of users. My connection string contains a UserId and Password attribute. What I'm left with is how to get to know the user group once the connection is established. I know of a method "Is_Member()" in sql Server but unfortunatelly it is not recognized by Access db.I need to implement a method for doing that and I couldn't get access to sysusers or sysfilegroups tables like in Sql. I'm new to MS Access so if any body can give me a hint.By the way I'm using C# ,MS Access, OleDb.
Thank you
|
|
|
|
|
Hi,
I know this is very easy... I just don't know how to do it:
We have the following table named Wzip_Detail
[Wzip_Detail_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarehouseNum] [int] NULL
Wzip_Detail_ID will be automatically populated.
CountryCode will always be 1
WarehouseNum will always be 1
HOWEVER:
PostalCode for row 1 will be:
00000
PostalCode for row 2 will be:
00001
PostalCode for row 3 will be:
00002
PostalCode for the last row will be:
99999
So there will be rows 00000 to 99999 incrementally a total of 100,000 rows (I think)
How do I do this programatically in SQL Query Analyzer.
Again, I'm sure it is very easy... I just don't know how to do it yet....
Thank you so much in advance for any help you can give,
Anne
|
|
|
|
|
Here's a way to populate the PostalCode column. I would set the default value of the columns CountryCode and WarehouseNum to 1 instead of explictly inserting the value 1 each time.
<br />
DECLARE @PostalCode varchar(10)<br />
DECLARE @Number int<br />
SET @Number = 0<br />
<br />
WHILE @Number < 100000<br />
BEGIN<br />
<br />
IF LEN(@Number) = 1<br />
BEGIN<br />
SET @PostalCode = '0000' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 2<br />
BEGIN<br />
SET @PostalCode = '000' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 3<br />
BEGIN<br />
SET @PostalCode = '00' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 4<br />
BEGIN<br />
SET @PostalCode = '0' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 5<br />
BEGIN<br />
SET @PostalCode = CAST(@Number AS varchar)<br />
END<br />
<br />
<br />
INSERT INTO Wzip_Detail(PostalCode)<br />
VALUES(@PostalCode)<br />
<br />
<br />
SET @Number = @Number + 1<br />
END<br />
|
|
|
|
|
Thank you so much John for your response and your very clean programming style!!!
 Anne
|
|
|
|
|
Using REPLICATE() is a little shorter:
DECLARE @PostalCode varchar(10)
DECLARE @Number int
DECLARE @cTemp varchar(10)
SET @Number = 0
WHILE @Number < 100000
BEGIN
SET @cTemp = CAST(@Number as VARCHAR(10))
SET @PostalCode = REPLICATE('0',5-LEN(@cTemp)) + @cTemp
INSERT INTO Wzip_Detail(PostalCode)
VALUES(@PostalCode)
SET @Number = @Number + 1
END
----------
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 all,
which one faster? ODP.NET or System.Data.OracleClient.
I do a test on my computer. the result following:
--
System.Data.OracleClient
100
00:00:03.0156250
10'000
00:00:03.0156250
100'000
00:00:04.2187500
1'000'000
00:00:13.5000000
Oracle.DataAccess.Client
100
00:00:03.1875000
10'000
00:00:13.3750000
100'000
00:01:53.0000000
1'000'000
(I don't test this item  I think it will be take a long time, so I don't test it.)
--
my computer environment
os: windows 2003 server enterprise edition - sp1
computer:
Intel(R) Celeron(R) CPU
2.53 GHz
2.55 GHz, 736MB of RAM.
--
How do you think about this? I thought the ODP.NET would be faster that System.Data.OracleClient before test.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
