|
How can I see messages sent from SQL using ADO? In any quire analyzer I can see messages sent back from the db ie:
“Select * from authors” returns the following message “(23 row(s) affected)”
I need a way to programmatically trap and display the messages to the user. I know that Print statements are stored as error 0 and I can get those.
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
ADO or ADO.Net? Either way, the message "(23 row(s) affected)" is not sent back to the client; query analyzer makes that message. If you are using a datareader, then you can get the number of rows affected through the RecordsAffected property; in this case it would return 23, and you could show that to the client any way you want. If you are calling a stored procedure containing the SET NOCOUNT ON option, it will not return the row affected (I think it will always report 0, but I'm not sure).
|
|
|
|
|
So does the analyzer that is running the query generate all messages. How can I as a programmer know if a query actually went through?
set @Execstr = '[PUBS].[dbo].['+@table_name+']'
dbcc dbreindex ( @Execstr )
This code returns in query analyzer:
“DBCC execution completed. If DBCC printed error messages, contact your system administrator.”
How can I catch that?
|
|
|
|
|
If you want to see messages generated by 'PRINT', you have to trap the InfoMessage event of the connection object.
If you want to see the rows affected by a query, and the query is run using ExecuteNonQuery, then the return value is the number of rows affected.
You can also return any info you want from your stored procedures as output parameters, which you can read after running the query.
When running a stored procedure from query analyzer, messages con come from several sources:
1. Query analyzer can generate a friendlier message from info it receives (such as the 17 rows affected messages). You catch this in code using the return value of 'ExecuteNonQuery'.
2. The stored procedure can use the 'PRINT' statement, which causes query analyzer to display some info. You catch this in code in the IfoMessage event of the connection.
3. The stored procedure uses RAISEERROR. In code, if the severity level in RAISEERROR is less than 11, the the message can be caught in the InfoMessage event of the connection. If the severity level is greater than 10, I think it shows up as an exception, which you can catch in a try block.
So, you can collect any messages from dbcc by attching code to the InfoMessage event of the connection, and looking at the messages you collected after running the query.
|
|
|
|
|
So is there a way to do this in ADO and VB (not net) ExecuteNonQuery is not part of ADO and Event handling is not supported in VBScript. someday my boss will realize that asp isn't worth supporting.
|
|
|
|
|
In old ADO, the Command.Execute method accepts a RecordsAffected parameter which you can use to get the value.
I think the only place you can get the informational messages is through the InfoMessage event (which was available in old ADO as well), so you may be out of luck.
|
|
|
|
|
Even just using Print statements I can only get them as long as there is nothing else happening ie:
Print ‘haH’
Print ‘hehe’
Select * from authors
Print ‘hoho’
All I can ever catch is haha and hehe
How can I get the selects from the following:
declare @counter int
declare @countvalue int
select @counter = 0
select @countvalue = 100
Counter:
-- Controlled Loop Statement
if @counter < @countvalue
-- WHILE START
BEGIN
declare @msg1 varchar (225)
select @msg1 = '---------- MSG1 Current Counter Value = [ '
+convert ( char(10), @counter )
+']'
print @msg1
select @counter = @counter + 1
goto Counter
END
-- WHILE END
else
BEGIN
goto Terminate
END
Terminate:
ADO doesn’t seem to Return anything
|
|
|
|
|
How can I stop a quire before it has completed? In any query analyzer if a given query is taking to long to complete I can stop it and get the records that have returned. I need to do this with the ADO. My software seems to “go away” sometimes when large numbers of records are returned and this causes some users to think the computer has halted. Obviously thins will not do. Any suggestions would be much appreciated.
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
|
That won't work because I need the ability to capture all the data as well as having the ability to stop when a user clicks stop. I need an inturupt
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
sp_who2
fine the spid of the query your running
kill spid if its doing a write/update it will need to rollback if incomplete
if you do kill spid again it will show you how long it has left to rollback
|
|
|
|
|
Hey! I am making a website in Dreamweaver and I want to make a database for user registration and login. I set up the mySQL server and I am pretty sure I made the database right, using the mySQL Administrator program, but I keep getting the "HTTP Error Code 405 Method Not Allowed" error when I try to set up a connection from dreamweaver. I am trying to run mySQL locally and develop a website on the same machine (this one). I dont know what to do next because all of the websites out there refer me to set up an FTP connection in Dreamweaver that leads off to a domain....
Please help!!!
Thanks!
Anthony
|
|
|
|
|
Hi, I'm not sure if I'm doing my UPDATE's and DELETE's the proper way:
Basically, I want to make sure what I updates, or deletes, in fact exists (ie not deleted by another user) before I actually perform the operation:
Code:
o_session = SetupBISConnection(...)<br />
trx = o_session.BeginTransaction()<br />
<br />
Try<br />
'IF the object with specified UIN does not exist (that it's deleted by another user), then NHibernate will throw ObjectNotFoundException.<br />
obj = o_session.Load(GetType(CObj), UIN)<br />
Catch ex As NHibernate.ObjectNotFoundException<br />
'object to be updated does not exist - abort delete<br />
Throw ex<br />
End Try<br />
<br />
'Okay, if no exception so far, it means the object is there...<br />
o_session.Delete(plan)<br />
o_session.Flush()<br />
<br />
trx.Commit()
Any pointer? Thanks in advance.
Norman Fung
|
|
|
|
|
Hey guys, just downloaded, "NHibernate Query Analyzer", I unpacked it, but now wondering what to do with it. Is there any tutorial for this tool? Or should I resort to SQL Server's SQL Profiler... Thanks.
Norman Fung
|
|
|
|
|
Hi all,
I have got a problem with retrieving data from the tables, because I found out that the keys to join these two tables are mismatching. The key in one table HAS SPACES in it, but the key in the other table does not. Even I have tried to use the REPLACE function to get rid of the spaces to make them matching, it still does not work. Please have a look at the code below, and give me some ideas how to solve it.
SELECT P.UniformItem, P.ProductCode, P.ProductSize, U.Description, U.Rank, U.RepPolicy
FROM tProductCode P INNER JOIN vUniformItemJoin U
ON Replace(P.UniformItem,' ','') = Replace(U.UniformItem,' ','')
Very much appreciate if you can help me out.
Cheers
|
|
|
|
|
GoldenStar wrote:
SELECT P.UniformItem, P.ProductCode, P.ProductSize, U.Description, U.Rank, U.RepPolicy
FROM tProductCode P INNER JOIN vUniformItemJoin U
ON Replace(P.UniformItem,' ','') = Replace(U.UniformItem,' ','')
Wow - you match using string values, and then call functions like this within your select statement ? You need to fix your database, I'd suggest that P and U should both have an index which represents a UniformItem, and then a table should match the index to a string representation. That's called database normalisation. If you're not going to do that ( and you should ), then you should at least massage data going in, so you don't need this sort of stuff making the most simple select statement messy.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ADO/ADOX Gurus out there:
I cannot seem to find any way to read the description of an existing field in an existing Access DB. The frustrating part is that I can certainly write one when creating a new field and appending it to a table, but I can never seem to read the description for an existing field.
The following code will work and display the description on the webpage, but only because it reads it from the object it just appended. If I were to close everything, set it to nothing, then open a new object, it cannot be read. One would think that if you can write it, you can read it...am I wrong here?!?
I have tried any number of combinations of objects, properties, and collections from ADO and ADOX. Has anyone actually been able to do this?
Your help is deeply appreciated,
Brian
biomedbz@hotmail.com
dim objConnDatabase,objCat,objTable
Set objConnDatabase = Server.CreateObject("ADODB.Connection")
objConnDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConnDatabase
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConnDatabase
Set objTable = CreateObject("ADOX.Table")
with objTable
.Name = "newtable"
.ParentCatalog = objCat
end with
with objTable
with .Columns
.Append "Field_A",adDouble
.Item("Field_A").Properties("Description") = "This is a field of numbers"
response.write "The ""Field_A"" field has been added!
"
end with
end with
response.write "description=" & .Item("Field_A").Properties("Description") & "
"
|
|
|
|
|
Hi, my friend have a database in SQL server 2000 which already apply service pack 4. Recently he try to export the data from his database to Microsoft Excel. During the middle of data transformation progress an error message box appear with the following information: "Incorrect syntax near the keyword 'column name'. Statement(s) could not be prepared". I have check the microsoft knowledge base for the solution and found the information from here[^] and here[^]. But I think that the information is not relate to what i need. Does anyone has any idea or solution? I need the comment to solve the problem.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Have seen this when colum names uses reserved SQL names like DATE, TYPE, CASE..ect
Perhaps this is part off the problem
Hmm...
|
|
|
|
|
Hi All
I am developing a system that will retrieve information from pastel account information, it will have to retrieve the invoice number and store it in the SQL server database so I need help on how to connect to pastel so that I can be able to get the information from pastel and from the database to pastel. The two applications have to share some sort of information. The help that is provided must be in visual basic 6.0 because that is the one I am using.
Hope you can help
thanks in advanced
|
|
|
|
|
|
string strSQL= " Select * From Category" ;
SqlConnection cnn = new SqlConnection(CNN_STRING);
SqlCommand cmd = new SqlCommand(strSQL,cnn);
DataSet ds = new DataSet("Products");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
ds.Tables[0].TableName = "Product";
TextBox1.Text = ds.GetXml() ;
Here GetXml() returns the out put as tag ways
< Products><br />
< Product><br />
<CategoryID>3</CategoryID><br />
<Name>A</Name><br />
<Description>ADescription</Description><br />
</Product><br />
< Product><br />
<CategoryID>4</CategoryID><br />
<Name>B</Name><br />
<Description>B Description</Description><br />
</Product> <br />
</Products>
I need to change it as Attributes in a simple way
< Products>
< Product CategoryID="3" Name="A" Description = "ADescription" />
< Product CategoryID="4" Name="B" Description = "B Description" />
</Products>
|
|
|
|
|
As far as I know there is no method to change the output of the GetXml function. So you will have to convert it afterwards. If you are willing to read some docs have a look at the XslTransformclass. I provides a .Net wrapper around XSLT which can be used to transform the structure of XML files.
|
|
|
|
|
I want to publish my site and want to register new server from the enteprize maager.i entered the name of the server and followed the wizard but the"sql server doenot exist or access is denied"has bee shown.(i used the windows autentication).also when i used server au.. and enter the login and the user name, the message has bee shown again.
what is the problem?
mabey it is from the way that i setup the sqlserver or the version of sqlserver,because the sqlserver work correectly when i use the local server.
Thank you!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
