|
Hi,
We are creating an ASP Database Application that connects to Sql Server 2000.
The application is for associating a zip code with a warehouse. For instance a zip code for the los angeles area would go to the los angeles warehouse and a zip code for the new york area would go to the new
york warehouse. We would like to enter zip code ranges into the following Wzip_Log table. Then we would like to programmatically populate the Wzip_Detail table and the Wzip table.
The Wzip_Log table holds zip code ranges, where as the Wzip_Detail and the Wzip table hold individual zip codes.
For instance, the Wzip_Log table might have the following record (completely fictitious)
Wzip_Log_ID: 1
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 1
Operator: jdoe
Time_Stamp: 00:00:00 6/1/2006
Then we would want the Wzip_Detail table to be populated as follows:
Wzip_Detail_ID: 1
Wzip_Log_ID: 1
PostalCode: 11111
CountryCode: USA
WarehouseNum: 1
Wzip_Detail_ID: 2
Wzip_Log_ID: 1
PostalCode: 11112
CountryCode: USA
WarehouseNum: 1
Wzip_Detail_ID: 3
Wzip_Log_ID: 1
PostalCode: 11113
CountryCode: USA
WarehouseNum: 1
The Wzip table would be populated exactly the same as the Wzip_Detail table at this point.
However, say a user later updated the Wzip_Log table as follows:
Wzip_Log_ID: 2
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 2
Operator: jdoe
Time_Stamp: 01:10:00 6/4/2006
The Wzip_Detail table would contain an INSERT of this data (giving it a total of 6 records)
whereas the Wzip table would contain an UPDATE of this data (giving it a total of 3 records)
For your reference, here are the table layouts:
Table Name: Wzip_Log
Wzip_Log_ID int 4 (primary key, Identity)
PostalCode_Start varchar 10
PostalCard_End varchar 10
CountryCode varchar 3
WarehouseNum int 4
Operator varchar 50
Time_Stamp datetime 8
Table Name: Wzip_Detail
Wzip_Detail_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4
Table Name: Wzip
Wzip_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
Wzip_Detail_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4
I think there is probably some way to do this programmatically in Sql Query Analyzer, but are not sure how, so I thought I would ask you here who have helped me tremendously in the past.
I am eager to learn more about writing such functions and am enjoying this project.
Thanks in advance for any help you can give,
Anne
|
|
|
|
|
Problem!!! i am creating database connection using VC++ and SQL server with authentication 'sa' and password "***". I am using ADO connetion (with DSN).
I have two accounts on my computer system Administrator and Operator.
1- Administrator have full access on computer system.
2- Operator have limited access in computer system (can't perform installation)
Well I can successfully connect with database from administrator account.
Buttt when i try to run my application from operator account then then database connection failed.
Would some body help me... Its urgent.
|
|
|
|
|
My colleague is not able to access the sql server that resides in my local system from the asp.net application in her pc even though my sql server is running.
She is able to access my sql server using her query analyser.
The code in web.config
add key="ConnectionString" value="Data Source=Myservername;User Id=mysqlservername;
Password=mysqlserverpassword;Initial Catalog=mydatabasename;Connection Lifetime=30"
I have used the same code in my web.config and i am able to access my sql server.
Do tell if i have to give some rights for her to access my sql server through asp.net application
-- modified at 6:59 Tuesday 23rd May, 2006
|
|
|
|
|
Check sql server authentication mode in your Enterprise manager. Try changing it to work in both windows and sql server mode.
|
|
|
|
|
Thanks for the reply.
The authentication mode is SQL Server and Windows.
|
|
|
|
|
Hi,
I need to do update operation for a set of ids, for this i need to use comma separator how can do it
I am giving like this
update Emp_Tbl set empstatus=1 where empid in (@empids)
is it enough or any other methods
|
|
|
|
|
This will work 
================
DECLARE @empids varchar(100)
SET @empids= '66696, 66695, 66694, 66693, 66692'
exec('update Emp_Tbl set empstatus=1 where empid in (' + @empid + ')')
Look where you want to go not where you don't want to crash.
Bikers Bible
-- modified at 6:46 Tuesday 23rd May, 2006
|
|
|
|
|
1)OLEDB Connection Pooling is handled by OLEDB.Net Provider and SQLClient Connection pooling is handled by____
2)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
3)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
4)Which object in ADO.Net is similar to the Recordset object of ADO?
plz let me konw the ans
witn regards
d.ravi kiran reddy
|
|
|
|
|
hi,
This is Dayakar
You need to add following namespaces for the specified data providers:
Data Provider Namespace
MS SQL Server System.Data.SqlClient
Oracle Database System.Data.OracleClient
OLE DB Databases System.Data.OleDBClient
ODBC Data Sources System.Data.ODBCClient
|
|
|
|
|
Hi,
I want to make one column as a index in one table. i.e how to make one column as a primary key at run time
|
|
|
|
|
ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_KeyName PRIMARY KEY CLUSTERED
(
FieldName
) ON [PRIMARY]
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Is there a way to view the results of an action query without committing the changes to the database in SQL Server 2000? Any suggestions or tips will be greatly appreciated.
-Garrett
|
|
|
|
|
I'm not really sure how wise this is but this:
SELECT * FROM [Order Details]
BEGIN TRANSACTION
DELETE FROM [Order Details]
SELECT * FROM [Order Details]
ROLLBACK TRANSACTION
SELECT * FROM [Order Details]
This shows the state of the Order Details table, then it shows the result of the delete operation, but after the transaction is rolled back the state of the database reverts.
Is this the kind of thing you are looking for?
[ADDITION]
I should point out that this does actually make changes to the database and it could impact performance of other things running in the database.
[/ADDITION]
"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
-- modified at 12:51 Monday 22nd May, 2006
|
|
|
|
|
Pretty much. Thanks for the help.
|
|
|
|
|
I have a sql problem.
suppose we have two tables ex. A and B which have A1,A2,A3 column and B has B1,B2,B3 Column A1 and B1 are primary key and B2 is foreign key of A(A1) Now B3 has some values in it which i wanted to map with column A3.
means i wanted to update column A3 as same as B3 where we have A1=B2 relationship. can we write this in a single sql statement. what i am doing is writing a cursor on table A which one by one retrieve value from table B(B3) and update table A(A3). But problem is that this process is very slow and lock the table at the time it perform operation so all other request will have to wait until this process terminate. otherwise some times sqlserver detects it as a deadlock condition and unconditionally terminate process. So my aim is improve performace of all such processes.
Error :
Transaction (Process ID 773) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I wanted some thing like as easy as
update A set A.a3= B.b3 where A.a1 =B.b2
which does not work in this case.
Table has bulk amount of data i. e. around 10-15 lacks records. I need some alternative solution for many of such process in which i have used same logic am working on. also the database is of prouction server so I cannot kept it busy for long time.
Pallav Deshmukh
|
|
|
|
|
something like this ???
update a set a.a3 = (SELECT top 1 b.b3 FROM b INNER JOIN a ON a.a1 = b.b1)
My second computer is your linux box.
|
|
|
|
|
Thanks Colin Angus Mackay & woudwijk
I am not having good experience of database thanks for help it works.
Pallav Deshmukh
|
|
|
|
|
Something like this:
UPDATE A
SET A3 = B.B3
FROM A
INNER JOIN B ON A.A1 = B.B2
"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
|
|
|
|
|
Thanks Colin Angus Mackay & woudwijk
I am not having good experience of SQL. thanks for help!! it works.
Pallav Deshmukh
|
|
|
|
|
I'm trying to make a simple database manager program that allows a user to select a database file, see a list of the tables and each column in the table, and to write and execute SQL to alter the database. I am able to find a list of tables in a .mdf file by using this sql statement:
"SELECT TABLE_NAME FROM information_schema.tables ORDER BY TABLE_NAME"
But now I want to get my program to work for an access database file (.mdb) and this does not work. Does anyone know the name of and how to access a schema or metadata table in an access database?
Any help would be appreciated.
|
|
|
|
|
SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=1
ORDER BY MSysObjects.Name
I think adding "AND MSysObjects.Flags=0" will exclude system tables, but I'm not 100% on whether or not that is all that will be excluded.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
EricDV wrote:
I think adding "AND MSysObjects.Flags=0" will exclude system tables, but I'm not 100% on whether or not that is all that will be excluded.
Yes, it does
Paul
|
|
|
|
|
I decided to go another way and use the oledb connection object's GetSchema function. So, for example this bit of code will return a datatable with all of my tables in it:
Dim strRestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}<br />
dtTables = myConnection.GetSchema("tables", strRestrictions)
and this bit of code returns the columns within a table:
Dim strRestrictions = New String() {Nothing, Nothing, "myTableName", Nothing}<br />
dtColumns = myConnection.GetSchema("Columns", strRestrictions)
my new problem is that I can't find out which columns are primary keys. Can anyone point me in the right direction?
|
|
|
|
|
I've a table where ia save the date when user have subscreibed.
How can i query the number of subscriber signed in the 20 April 2006 or all the users signed since to 12 may 2006?
Or between 14 april and 21 may?
|
|
|
|
|
if you save dates in the integer format like this: YYYYMMDD then you should be able to do something like this to get a count of subscribers on April 20, 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column = 20060420
To get a count of subscribers since May 12, 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060512
To get a count of subscribers between April 14 and May 21 of 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060414 AND Date_Column < 20060521
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
