|
Hello,
I am currently building a script to populate a database with an initial set of data. As the database will potentially be brand new (but may not be) when the scripts are run I want to find a way to set the value the next insert will put in an identity column after the initial set of records are in. If the database is brand new I don't want new records overwriting or causing problems with the initial set. If the database was previously populated the values are deleted and the initial set is re-inserted, but in this case I don't want the new records to be placed with IDs too far removed from the IDs used in the initial set.
I am fairly certain it can be done, but after trawling through the docs I can't seem to find what I am looking for - The initial value can be set when the table is created, but I'm doing that after the table is created.
Any ideas?
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Hi everyone,
I thought you might be interested in WiredNav, which has just been released by Mabry Software. WiredNav is an innovative self-wiring .NET control that I authored for filling, navigating, searching, updating, and reporting ADO.NET data tables. You can use the control in database applications without making any initial property settings or writing a single line of code.
There are no DataAdapter, DataSet, DataSource, or DataTable properties for the control. At runtime, the navigator automatically wires itself to every data object found on the Windows form to which it is added. With this technique, you only need one navigator per form instead of one per data table. WiredNav can be used with SQL, Oracle, OLEDB, and ODBC data adapters.
Along with the Windows Forms DataGrid control, WiredNav can connect to your favorite third-party grids:
- ComponentOne Flex Grid for .NET
- ComponentOne True DBGrid for .NET
- Infragistics UltraWinGrid
- Janus GridEX Control for .NET
- Xceed Grid for .NET
I designed WiredNav to address many of the data binding and other disconnected data problems that I’ve read about on this message board. In addition to self-wiring, WiredNav has the following features:
- Automatic data control switching for navigating and updating
- Data table filling based on selection criteria
- Regular, transaction, and automatic source table updating
- Update results lists with total rows modified, added, and deleted
- Update error lists with error resolution when updates fail
- Row search based on primary key values or other search criteria
- Report printing and viewing with table filtering
- Selection criteria saving when filling, searching, and reporting data tables
- Time-saving date codes for common date values used in selection criteria
- Text validation for TextBox and ComboBox input controls with error providers
- Customizable navigator bar with section hiding
- Auto-incrementing key value retrieval for SQL Server, Oracle, and Access 2000
- Globally unique identifier (GUID) value generation for UniqueIdentifier columns
- Navigator buttons context menu with customizable shortcut keys
If you build database applications using ADO.NET, you should check out WiredNav. I think this is how Microsoft envisioned disconnected data would be handled. A trial copy of WiredNav can be downloaded from Mabry’s web site at www.mabry.com.
LT
|
|
|
|
|
Iam totally new to SQL Data bases.
Is it that..SQL Databases have more than 1 login ID and Password..
I've been assigned an assigned in which I have to write aprogram which checks the access permission for a particular login id and password!
what all API's are used for this job??
kindly help me in this regard!
ranjani
|
|
|
|
|
I forgot to mention the language in which Iam coding!
I will have write a C++ code..
thanxxx
ranjani
|
|
|
|
|
Hi All,
I am storing details of many hotels in a database. Details are coming from different providers. And we store it into our database.
Now sometimes, details about the same hotel come from different providers. And each provider may use different ID or name to refer a hotel. So when we enter this details we end up entering dublicate rows.
For example. Provider A give the details of InterContinental hotel and they refer the hotel with the ID "Inter". Provider B also give the detail of the same hotel but they refer that hotel with the ID "InterCon".
So after saving the details of InterContinental provided by the provider A when I store the detail given by provider B I need to make out that details of InterContinental is already stored and so I should not store it second time.
But as the IDs are different how do I make it sure. More over I dont have any other ID etc.
Can any one give me answere quickly.
Regards,
Vaibhav
|
|
|
|
|
Hi Vaibhav,
Question 1: What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel?
Question 2: How can you be sure, that the hotel "Inter" sent by Provider A is the same as the hotel "InterCon" submitted by Provider B. If the address of "Intercon" is the same as that of "Inter", except for an extra comma, how can you determine that both the records are of the same hotel, programmatically?
Question 3: What is/are the field/s that is/are common and unchangeable for both the records?
As fas as your question goes.
If we assume that only the id changes, and the rest of the details remain the same, then you can design the database this way..
tblHotel<br />
----------<br />
HOTEL_ID (pk) (IDENTITY)<br />
HOTEL_NAME<br />
HOTEL_ADDRESS<br />
and so on for the unchanging fields.<br />
<br />
-----------
another table would be there,
tblProviderHotel<br />
-------------<br />
PROVIDER_HOTEL_ID (pk) identity (just a key field)<br />
HOTEL_ID (fk from tblHotel)<br />
PROVIDER_HOTEL_ID (this would be the id, given by the providers, "inter", "intercon" etc)<br />
PROVIDER_ID (fk from Provider table if you want)<br />
-------------
Now in this scenario (inter and intercon scenario), tblHotel would contain just one record for the hotel details, whereas the tblProviderHotel would contain two records with the same HOTEL_ID (from tblHotel) but different PROVIDER_HOTEL_IDs (inter and intercon)
Similarly, if a provider C sends you a record of the same hotel, but the id is differnt, say "Inter-Conti", then you can check if the rest of the fields such as HOTEL_ADDRESS, CITY, ZIP etc matches with any record in tblHotel and if it does as in thsi case, then take the HOTEL_ID for the matching record from the tblHotel and just make a new entry in the tblHotelProvider table, with the id that the provider gave you..
So now, you have 1 record in tblHotel, but 3 related records in tblHotelProvider all pointing to one hotel, but submitted by different providers..
Also this is implemented, assuming all the other details are matching for all the 3 hotel records provided by the providers..as I asked you earlier.
Please do let me know if this helps..
Cheers,
Rahul
I do not understand what I do. For what I want to do I do not do, but what I hate I do. - Romans 7:15
|
|
|
|
|
Hi Rahul,
First of all, thanx for giving it a try.
What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel?
Ans 1: There is nothing to be match by which we can be sure that the two hotels provided by different providers are same, otherewise problem should not arise 
Ans 2: This is again second way of asking first question. You cant be sure that the "Inter" by provider A is same as the hotel "InterCon" by provider B. If you can assure that then porblem should not arise
Ans 3: Address etc are given by both providers but there is no garuntee that from both the providers you will get the same address string for the same hotel. As one can write "Street" while other can use "St." instead of.
Vaibhav.
|
|
|
|
|
Hi,
I found a lot of this -- "System.Data.Sqlclient.TdsParser" running when I run my multi-thread program. When I cancelled the command and closed the connection in my cancel thread, even I aborted my threads. This one is still running. Because the command and connection are gone, it even gave me some exceptions.
What can I do? How can I control this? How can I quit my thread totally, clearly and safely?
I would greatly appreciate your help.
|
|
|
|
|
Hi,
I set the Timeout time in a ConnectionString, like this:
"Password=;Username=sa;......Connect Timeout=60;pooling='false';"
Then I connect to sqlserver over and over again using this. But at about No.250 thread, the conn.Open() keeps waiting even after 60 seconds, 60 minutes.
Why this happen? Could anyone please help me?
Thanks in advance.
|
|
|
|
|
I am looking for sample code of how to use ADO in Visual C++6.
I want to create a connection to a MS Access Database, open recordsets with SQL-Paramters and create/modify/delete data in different tables.....
thx, Daniel
|
|
|
|
|
I have 8 fields in my table:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 Null Null Null Null
19 98 01 01 Null Null Null Null
I need to set century2,century2,year2,month2,day2 to the next day
after century1,year1,month1,day1, so my date will look like this:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 20 01 12 02
19 98 12 31 19 99 01 01
How do I do this?
|
|
|
|
|
Assuming that you are using SQL-Server:
The following will turn your fields into a datetime value:
convert(datetime, day1 + (100 * month1) + (10000 * year1) + (1000000 * century1))
The dateadd functiion will allow you to add one day.
Use convert(char(8), mydate, 112) to convert the date to a character field with the format "YYYYMMDD" (I think 112 is correct - i don't have SQL-server on this machine!).
Use substring to extract each element of the date into string values.
You should be able to stick it all together to solve your problem.
Andy
|
|
|
|
|
Hi,
I want to sent the timeout = 0 to make it wait infinitely. Because I use an IDbCommand variable which actually point to a SqlCommand or OleDbCommand at the runtime, I set IDbCommand.CommandTimeout = 0, but I got the Timeout exception just after a very short time I run the query.
Can anyone tell me why and please help me out.
Thanks in advance.
|
|
|
|
|
I assume that it interprets 0 as zero seconds, i.e. time out immediately.
Try using Int32.MaxValue instead.
|
|
|
|
|
I think so too. But I don't understand how this happened. Because it actually points to the SqlCommand. In SqlCommand, 0 means infinite.
|
|
|
|
|
Hi,
I am using DCOM in my web page and back end is SQL server. My server's(2000)RPC port- 135 is blocked. I need to connect server.How can i establish database connection using ado? (by default port no:1433 for sql server is not sufficient.).
I am using connection string as
"PROVIDER=SQLOLEDB.1;Network Library = dbmssocn;Data Source =datasource,1433;persist Security Info = False;UID=uid;pwd=pwd; Initial Catalog=initial_catalog" .
In web page,i am getting error as "Distributed Transaction Error". But from a separate sample vc++ application, i can establish database connection using this connection string.
Please help me.?
Meenakshi
|
|
|
|
|
I have a DDQ where I am transfering the data from SQL to AS400.
After the DDQ is exists I am updating flag to as success.
If row fails I need to change flag to diference value then if row get transfer suceesufuly.
how do I do this?
|
|
|
|
|
For my test i have created the following stored procedure which is called through a (sqldataconnection, sqldataadater and dataset) created with tthe wizerd. I have also placed a datagrid on the form which thes properties point back to the relivent datasource, datamember and datakeyfield. I have utilized a label to display errors which is telling me that there are now rows to populate the datagrid with.
If I select the dataadapter and run the preview data option (fill in all of the stored procedure variables and click fill data set the table will display the Log_ID value of the record added to the db (value a uniqueid field created by the sql default setting command of (newid()) when a record is added.
***Question why cant i populate the datagrid with the value thacan see when previwing the data through the dataadapter?
--===================================
CREATE PROCEDURE dbo.tsi_spASPautolg
(
@log_date datetime,
@cl_temp nvarchar(255),
@user nvarchar(50),
@domain nvarchar(25),
@pc nvarchar(25),
@ip nvarchar(15),
@mac nvarchar(20)
)
AS
SET NOCOUNT ON;
Insert Into aspAutoLog (Login_datetime, cl_temp, cl_user, cl_domain, cl_pc, cl_ip, cl_mac)
Values (@log_date,@cl_temp,@user,@domain,@pc,@ip,@mac)
SELECT Log_ID From aspAutoLog WHERE login_datetime = @log_date
GO
--===========================================================
On my form I run the following code with a button click:
Try
SqlDataAdapter1.SelectCommand.Parameters("@log_date").Value = Now()
SqlDataAdapter1.SelectCommand.Parameters("@cl_temp").Value = "C:\DOCUME~1\DANJOR~1\LOCALS~1\Temp"
SqlDataAdapter1.SelectCommand.Parameters("@user").Value = "Dan Jordan"
SqlDataAdapter1.SelectCommand.Parameters("@domain").Value = "DEVZONE-DCJXP"
SqlDataAdapter1.SelectCommand.Parameters("@pc").Value = "DEVZONE-DCJXP"
SqlDataAdapter1.SelectCommand.Parameters("@ip").Value = "10.116.105.109"
SqlDataAdapter1.SelectCommand.Parameters("@mac").Value = "00-00-39-5F-7B-3D"
SqlDataAdapter1.Fill(DataSet11, "Log_id")
DataGrid1.DataBind()
DataGrid1.Visible = True
Catch eFillError As System.Exception
Label1.Text = (eFillError.ToString)
Finally
End Try
Thank you for any insite into my confused state...
|
|
|
|
|
I have a program to run a 6-minute query in multi-thread. In each thread it will be executed over and over again. At the beginning the sql server is very busy, but after about 10 minutes, it stops. The cpu becomes free. From the log of my program, I find that the new started thread also kind of stop, it's connecting to the sql server. So I try Query Analyzer and EM, both keep connecting to the sql server until now. It's almost half hour.
Anyone know what's happening here?
Thank you very much.
|
|
|
|
|
Is the machine paging? Pull up the Performance MMC and start looking at more than just processor: disk use, especially paging, is a good place to start. Then get back to me with the details.
Another thing is that depending on how you've got your Query Analyzer etc. set up, you can be inadvertently locking tables by using them through the GUI. I recommend exiting all your client stuff, then starting with just one thread running the query, then two, four, eight, etc. Throw on all the performance monitors you can stand. After a few hours of fiddling around, you will at least have more idea of what's going on on your machine performance-wise.
Also, you should pull up the query plan of your query and analyze it for possibility of deadlock. You can try running it simultaneously through several connections in QA to see if you can cause a deadlock, but I think it's usually better to just try to spot these things with the eye. Look for the possibility of putting two threads at cross purposes, where one thread has resource A and waits on B, while another thread is in the reverse situation. The easiest way to avoid this sort of thing is by making sure you always access things in the same order, and never backtrack if possible. (This is almost always possible to do.)
Sorry if some of this seems like newbie info-- I don't really know your full situation.
Thank you.
Jeff Varszegi
|
|
|
|
|
Below is quoted from a book about ADO.NET, does anyone understand that? Could you please explain a little bit to me or refer me to some link could explain it?
"The .Net framework provides client-side connection pooling for both data providers for connections in a single process, and it also contains provision for working with databases that use server-side connection pooling."
Thanks in advance.
|
|
|
|
|
The functions in ADO.NET are asynchronism or synchronism?
such as, myConnection.Open, myCommand.ExecuteReader.....
|
|
|
|
|
SQL 2000 has a method in the query analyzer to debug a stored procedure. (Use object browser, select the stored proc, then right click and select debug).
This is really nifty, with the exception of passing dates to a datetime parameter. I cannot figure out the format for the date. String mm/dd/yy doesn't work, either with or without single quote.
I am stuck, any help would be greatly appreciated.
"It takes a minimum of redesign to turn a crucifix into a pogo stick"
|
|
|
|
|
yyyy-mm-dd works, IIRC. If all else fails, try converting to a number:
SELECT CAST( CAST( '2003-01-01' AS datetime ) AS int )
|
|
|
|
|
Dave S wrote:
String mm/dd/yy doesn't work, either with or without single quote.
That depends totally on the configuration of the database. There's always the date functions and/or ISO format "yyyy-mm-dd"
--
I am of The Clan of Xymox. I wear a pink kilt!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
