|
Hi Friends,
My problem is that , my application is using common transaction object ,
when multiple users trying to access the application resources simultaneously
it will show me the error datareader is already opened . Can anybody suggest me , how to maintain these problem? My application is using ASP.NET as front-end. Could it be maintained by maintaining isolation/concurrency in database?
Please , reply your suggestions.
Thanking you in advance,
Param
param
|
|
|
|
|
Best practice is to use a separate connection for every logical database query (if you have a lot of queries in the same function, it's fine to use the same connection for all of them, but you shouldn't use the same connection object in different functions). Ensure that you Dispose the connections when you've finished with them.
ADO.NET has connection pooling support which is enabled by default. This will avoid the overhead of connecting unless there are no free connections in the pool.
Your error is occurring because another thread, processing another request, already has a DataReader open on the connection, and that's not allowed.
|
|
|
|
|
Hi Thanks for your reply.
But,
I have closed the connection n used seperate connection foe every logical database query..but in my code i have used common transaction object for every query..will it create any problem?
becoz every time wen multi user are accessing the system random error are comings.sometime it gives internal fatal error.some time it gives ur reader is opened with this connection.I am very sure tht i have close the connection n the data reader both.
I wud be very pleased if u can give some solution to this problem
Thanking you in advance
Param
param
|
|
|
|
|
While i am use bulk insert to import the csv file into my SQL SERVER through my system develop using ASP.NET by VB script, it able to import all csv data into my database.
Unfortunately, some data such as those decimal or money data type already change to vchar for all the data type after import in my database and cause them to be close by double quote ("1,111.00"). This unable let me to total up those data while i do it with my system.
As i research, there have CONVERT and CAST function to convert the data type, can i use them while i import the csv file into my database through the BULK INSERT? Thanks.
|
|
|
|
|
Bulk load to a staging table, then clean the data when transferring to your production tables. This will allow you to leave the BulkInsert task alone, and expand the tranfer task as changes to the underlying data or destination tables changes. This is why we call it ETL. Extract the data from the source, transform it match your systems and the Load it into your systems.
- Sage
|
|
|
|
|
Excuse me, can you mention more detail and how it to be done such as give some simple example?
Thank you for you response.
|
|
|
|
|
This was thrown together, but should work if you replace the tablenames, columnnames and filenames with values that apply to your application
BULK INSERT DBName.dbo.StagingTable -- StagingTable can be a copy of your destination table
From 'C:\YourFile.txt' -- actual path to your file
With (
FieldTerminator = ',',
RowTerminator = '\n' -- for new line
)
Alter Table StagingTable
ADD SafeToTransfer BIT NOT NULL Default(1)
-- Run some integrity checks prior to attempting to tranfer
if ( select Count(*) From DBName.dbo.StagingTable Where IsNumeric( ColumnA )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where IsNumeric( ColumnA )=0
END
if (select Count(*) From DBName.dbo.StagingTable Where isDate( ColumnB )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where isDate( ColumnB )=0
END
if ( select Count(*) From DBName.dbo.StagingTable Where isNumeric( ColumnD )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where isNumeric( ColumnD )=0
END
Insert Into DBName.dbo.DestinationTable
(
ColumnA, -- an INT Column
ColumnB, -- a datetime column
ColumnC, -- a varchar(100) column
ColumnD, -- a numeric(9,5) column
ColumnE -- another varchar(100) column
)
SELECT
CAST( ColumnA as int), -- an INT Column
CAST( ColumnB as datetime), -- a datetime column
LEFT( ColumnC, 100), -- a varchar(100) column
CONVERT( numeric(9,5), ColumnD), -- a numeric(9,5) column
LEFT( ColumnE, 100) -- another varchar(100) column
From DBName.dbo.StagingTable
Where SafeToTransfer = 1
Alter Table StagingTable
DROP COLUMN SafeToTransfer
|
|
|
|
|
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.
What would be the more efficienct method to use?
'Dim myItemData As New DataReader
Dim myItemReader As SqlClient.SqlDataReader
Dim myItemCommand As New SqlClient.SqlCommand
Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx")
myItemCommand.Connection = myConnection
myItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"
myItemCommand.Connection.Open()
myItemReader = myItemCommand.ExecuteReader()
Dim myfileout As StreamWriter
myfileout = File.CreateText(Server.MapPath("out.txt"))
myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")
While myItemReader.Read
If myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then
'what method to to look up for example the category name using the category_quid referenced in the item master
'Write out record for parts file
Ronhawker
|
|
|
|
|
The simplest way is to use join in sql statement
for i.e.
select i.itemname, i.categorytype from item i
inner join categorytype c on c.categorytype=i.categorytype
pass above statement is sqlcommand object.
Thanks & Regards
Kumar Prabhakar
abc
|
|
|
|
|
I have Error In Visul C++ When Conncet To table
"
OleDbConnection *con;
con=new OleDbConnection();
con->ConnectionString=S"Provider=VFPOLEDB.1;Data Source=c:\1;";
con->Open();
"
Error :
An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll
Additional information: Format of the initialization string does not conform to specification starting at index 34.
but I Connect Complete With Visul Basic.net But I Dont Connect To Visul c++.net
Please Help Me
|
|
|
|
|
C, C++, C#, and all other members of the C family use the \ character as a string escape character. The combination \1 becomes Unicode character U+0001, a control character normally used as Start Of Heading, once the compiler has translated the code.
To prevent this happening, escape the backslash with another backslash:
con->ConnectionString=S"Provider=VFPOLEDB.1;Data Source=c:\\1;";
|
|
|
|
|
Hi,
I have a column in my database that holds a description. I want to be able to search for one word in the description and get a match if the word exists.
I'm using VB.Net and ASP.Net(2005).
Thanks
|
|
|
|
|
If you mean that you want to retrieve records that have a particular word in the description:
SELECT * FROM myTable WHERE myDescriptionField LIKE '%myWord%'
Steve
|
|
|
|
|
Thanks that is what I wanted to know
|
|
|
|
|
I have a datagrid that is populated in two columns with a smalldatetime value. The column is only reflecting the date portion in the datagrid, but the full datetime in the query. How can I format this comlum to reflect just the time portion of the value?
Thanx in advance
Jude
|
|
|
|
|
Create a DataGridTableStyle. Then Create DataGridColumnStyle for each column. In the format property for the column you mention, enter:
"t" - for ShortDateTime
"T" - for LongDateTime
Steve
|
|
|
|
|
Thanx for the reply. Could you give an example of this or a link explaining this?
Thank you
Jude
|
|
|
|
|
Are you using Visual Studio?
If so, you can do this using the properties window in the designer.
Or do you need code?
Steve
|
|
|
|
|
I found it..I'll work on it a little and figure it out.
I have another question regarding datasets and datarows/columns.
I populated a combobox with this:
SqlCommand cmdSelect = sqlConnection1.CreateCommand();
cmdSelect.CommandText = "SELECT EmpID,EmpName FROM Employees";
SqlDataAdapter sdaEmp = new SqlDataAdapter();
sdaEmp.SelectCommand = cmdSelect;
sdaEmp.Fill(dstEmp,"Employees");
cboEmployee.DataSource = dstEmp.Tables["Employees"];
cboEmployee.DisplayMember = "EmpName";
cboEmployee.SelectedIndex = 0;
Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
What would be the correct porocedure to accomplish this?
Thanx in advance!
Jude
|
|
|
|
|
Greetings:
I am creating an SQL table programmatically using C#. I have set up my SqlCommand and I am using ExecuteNonQuery to execute the following SQL statement:
<br />
CREATE TABLE StationData (StnID INT NOT NULL AUTO_INCREMENT, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);<br />
This statement is throwing an exception and the exception message says: "Incorrect syntax near "AUTO_INCREMENT"".
If I remove the AUTO_INCREMENT specifier from the above statement, then the operation completes successfully and the table and columns are created as expected. It just seems to be that AUTO_INCREMENT specifier.
I have checked many similar example statements from various sources and I don't see anything wrong with what I have done here.
Any suggestions?
Thanks,
Mark
|
|
|
|
|
Try this:
CREATE TABLE StationData (StnID INT NOT NULL <code>IDENTITY </code>, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);
--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
|
|
|
|
|
OK, thanks. It worked.
Just wondering, however, IDENTITY seems to have the same effect as AUTO_INCREMENT. What then, is the status of the AUTO_INCREMENT specifier? Obsolete? Why was SQL rejecting it?
Thanks,
Mark
|
|
|
|
|
MySQL syntax is AUTO_INCREMENT[^]
SQL Server is IDENTITY [^]
--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
|
|
|
|
|
I'm going to have to watch out for that. I've seen a lot of web tutorials and references that talk about AUTO_INCREMENT but it turns out that they were all talking about MySQL, not SQL. I had assumed that they would be similar in syntax but this is turning out to be not the case.
Thanks again,
Mark
|
|
|
|
|
I'm working with an existing database so splitting date values into seperate DD, MM & YYYY columns is not ideal. Is there a way to cast smalldatetime to British date format: DD/MM/YYYY ?
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
