|
Ya I know how to use it and I have used it
But I want only the tablenames in an excel format not with data
Like tablename in one column and its description in other
Is there any SP for that
"I find that the harder I work, the more luck I seem to have."
|
|
|
|
|
you can modify the sp in this link
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1057989,00.html?bucket=ETA
|
|
|
|
|
I recently started a C# prototype using the OLE DB access method to read from a hefty Excel spreadsheet that we use at the office. The nice thing about OLE DB is you can read Excel worksheets pretty easily, and in block read style.
H O W E V E R, when was the last time you saw a clean error free, perfectly formatted spreadsheet?
That's where I am stuck. It seems the OLE DB driver/JET engine will happily retrieve the specified query range but it also supplies a null for any cell it doesn't like and considers to have an error. Is there a way to override this behavior and tell OLEDB through the DataSet or ExtendedProperties to retrive all data "as is" thus ignoring any cell errors?
FYI, here is how I am reading a worksheet in one read. In my case I know there are roughly 330-370 rows per worksheet, so I specify a range of 400 rows:
<br />
OleDbConnection con = new OleDbConnection( connectionOpts );<br />
con.Open();<br />
<br />
string query = string.Format( "SELECT * FROM [{0}$[1]]", tableName, queryRange );<br />
<br />
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();<br />
oleAdapter.SelectCommand = new OleDbCommand( query, con );<br />
DataSet ds = new DataSet();<br />
int rowsProcessed = oleAdapter.Fill( ds, "data" );<br />
<br />
oleAdapter.Dispose();<br />
oleAdapter.Close();<br />
etc.<br />
Mike Luster
CTI/IVR/Telephony SME
|
|
|
|
|
How can I check if SQL Server is running through C# code without causing an exception?
|
|
|
|
|
|
I will like to store my .swf file format in my database. I heard that it will be easier if we store only the URL in database and for my file i can keep it in web.
Can anyone guide the best way to upload .swf file in database and retrieve it. I am using sql 2000 and asp.net
|
|
|
|
|
|
Now i have 3 steps in doing it which is
1. browse my file from harddisk
2. giving my file a name
3. upload my file to web server
So my question over here is, how to upload my url for the specific path to database?
my code are something like this for the 3 step
------------------------------------------------
Sub DoUpload(ByVal Sender As Object, ByVal e As System.EventArgs)
Dim sPath As String
Dim sFile As String
Dim sFullPath As String
Dim sSplit() As String
Dim sPathFriendly As String
'Upload to same path as script
'Internet Anonymous User must have write permissions
sPath = Server.MapPath("../Tutorial")
'sPath = Server.MapPath(".")
If Right(sPath, 1) <> "\" Then
sPathFriendly = sPath 'Friendly path name for display
sPath = sPath & "\"
Else
sPathFriendly = Left(sPath, Len(sPath) - 1)
End If
'Save as same file name being posted
'The code below resolves the file name
'(removes path info)
sFile = txtFileName.Value
'sFile = txtUpload.PostedFile.FileName
sSplit = Split(sFile, "\")
sFile = sSplit(UBound(sSplit))
sFullPath = sPath & sFile
Try
txtUpload.PostedFile.SaveAs(sFullPath)
lblResults.Text = "Upload of File " & sFile & " to " & sPathFriendly & " succeeded"
Catch Ex As Exception
lblResults.Text = "Upload of File " & sFile & " to " & sPathFriendly & " failed for the following reason: " & Ex.Message
Finally
lblResults.Font.Bold = True
lblResults.Visible = True
End Try
|
|
|
|
|
Can someone please tell me how I can impliment the try...catch statement in a stored procedure?
Thanks!!
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
|
BEGIN TRY
SELECT * FROM Tablename;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
"I find that the harder I work, the more luck I seem to have."
|
|
|
|
|
I have a function that uses the SqlDataReader to get data from a SQL Server 2000 database. The function works pretty fine until the point where the SqlDataReader will be closed. This can take a minute or more (see a piece of the log file).
2006-03-13 08:43:06 ; GetJobByID: Beginning of function
2006-03-13 08:43:06 ; GetJobByID: Start opening DB connection
2006-03-13 08:43:06 ; GetJobByID: Finished opening DB connection
2006-03-13 08:43:06 ; GetJobByID: Start SQL execution
2006-03-13 08:43:06 ; GetJobByID: Finished SQL execution
2006-03-13 08:43:06 ; GetJobByID: Finished creation of job object
2006-03-13 08:43:06 ; GetJobByID: Close reader
2006-03-13 08:44:16 ; GetJobByID: Close connection
2006-03-13 08:44:16 ; GetJobByID: End of function
As you can see the "Close Reader" command takes 1 minute and 10 seconds and I have no clue why. Additional info: During the function the process "System" runs with about 70 percent.
Thanks in advance,
CADMIN
|
|
|
|
|
HI CADMIN,
Well i am not that of an expert but why dont you close the SqlConnection with out closing the SqlDatareader and the re-open the SqlConnection when ever needed.
i hope you can try this and see if its working, just a test.
Thanks
|
|
|
|
|
Thanks, but this does not speed it up. Now it takes a lot of time to close the database connection.
|
|
|
|
|
I suspect that the SqlDataReader has to read all the rows that you've selected before it can be closed - SQL Server is in what's termed 'firehose mode' when using a SqlDataReader, where it simply sends all the data selected to the socket and relies on the connection's flow control to get data. TCP 'graceful disconnect' requires that you read all the data buffered at the other end.
The solution is to only SELECT the rows you actually need, rather than aborting a loop early. I've never observed this problem.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you, Mike! Finally I found the problem in the SELECT statement. I had a huge table in the FROM statement which I did not really need for my query. After I removed it the query was as fast as usual.
Once again the user was the problem 
|
|
|
|
|
Hi There!
I have a FoxPro database that has a memo field which is filled with binary data and normal text. How can a person read this binary data?
|
|
|
|
|
How to execute a complex query in ado?when a finish a query words like "Select * From X.DB where conditon and condition..",how we explict the records?also adapt "m_pRecordset->putcollection("ID");
m_pRecordset->movenext();
?
I do not need all records but matching the conditon.
thanks.
Lee
|
|
|
|
|
Hi All!
My Dilema is as follows : I'm developing a WinForms application as front end (GUI) and an MS Access database as back end. In the middle there are a bounch of calsses (Middle Tier) that do data accessing (through the use of ADO.NET), input validations, Calculations (It's a tax calculating application) and so forth.
These classes expose data fields as properties (for instance, the "Firm" class exposes a "AnnualIncome" property which simply returns the "AnnualIncome" field of the current DataRow of the "tblFirms" Datatable).
What I want to do is bind this property to the "txtAnnualIncome" TextBox on the "frmFirm" form.
Suppose the property is declared as follows :
Public int Property AnnualIncome ()
get
{
return tblFirms.AnnualIncome;
}
The problem is that if the content of tblFirms.AnnualIncome is null an exception is raised. I can avoid the exception by using a try-catch block but then I get a 0 (zero) in the TextBox. I would prefer a blank TextBox for a null valued field.
I can also use an un-typed property that returns an object reference but I prefer not to (if there's an alternative).
Any sugestions anyone?
Thanks very much.
Walk the way. Top down.
nata2005.
|
|
|
|
|
nata2006 wrote: I would prefer a blank TextBox for a null valued field.
Hi nata will why dont you use the try-cach block an let the catch put set the textbox to blank
//sample
try
{
//...your code here
}
catch
{
//your code..plus
txtAnnualIncome.Text = "";
}
I hope this can solve it.
|
|
|
|
|
Thank you cbhkenshin for replying but it doesn't entirely solve my problem.
The thing is I want to create binding between the TextBox's txtAnnualIncome.Text property to the objFirm.AnnualIncome Property on the frmFirm_Load event. In this way, I can avoid initializing the TextBox to the saved value on the Form's startup and setting the value whenever it changes using txtAnnualIncome_Changed() event procedure.
The updating of the objFirm.AnnualIncome is done automaticly whenever the txtAnnualIncome.Text changes without a txtAnnualIncome_Changed() event procedure.
But then, of course, I can't use a try-catch block. I simply don't have where to put it.
I can give up binding all together but it's a very easy and powerfull mechanism and I'd like to use it if there's a work-around to solve the problem of the null valued data fields.
Hope I didn't mix things up too much.
nata2006.
Walk the way. Top down.
nata2005.
|
|
|
|
|
I want to add the names of the staffs, in the dropdownlist, who's login is not yet created.
Eg. consider 2 tables staff, and login
staffs: A,B,C,D,E
login: B,E
the logins are created for B and E so in the dropdownlist it must display A,C,D
i tried the following query but it is not working.
select sname from staff,login where staff.sname!=login.[name]
It leads to duplicated records.....
Please help!!
|
|
|
|
|
You're using some very old join syntax there. The following should work for you:
SELECT sname
FROM staff
LEFT JOIN login
ON staff.sname = login.[name]
WHERE [name] IS NULL How it works: the LEFT JOIN outputs all rows from the left-hand side joined to the right-hand side where the ON condition evaluates to true. If there is no match, it outputs the left-hand side row with NULLs in all the fields from the right-hand side. Because we want the rows which don't have a match, we select only the rows which have NULLs in the right-hand side.
If we wanted only the rows that did match, I'd use an INNER JOIN. That gives only rows where the ON condition evaluates to true.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
select staff.* , login.* from staff,login where staff.sname <> login.name
Proud to be Albanian
|
|
|
|
|
This will also work, if I understand your question correctly (i.e. you want to know what staff members do not have a corresponding record in the login table):
SELECT
s.sname
FROM
staff s
WHERE
s.sname NOT IN (SELECT name FROM login)
or this...
SELECT
s.sname
FROM
staff s
WHERE
NOT EXISTS (SELECT 1 FROM login l WHERE l.name = s.sname)
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
