Database

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Feb-05 22:53

A Stored procedure can return more than one record set, e.g.

CREATE PROCEDURE MyProc
AS
    SELECT * FROM Products
    SELECT * FROM Orders
GO

You don't specify which version of VB so I'll assume you mean VB.NET. You can use the SqlDataAdapter object to fill a DataSet with the results of the stored procedure.

Re: Sql Stored procedure

Anonymous13-Feb-05 23:01

Anonymous

13-Feb-05 23:01

Thanks for the solution

one more thing is that i want to get those recordsets through VB 6.0

Please help me

Re: Sql Stored procedure

Anonymous14-Feb-05 6:44

Anonymous

14-Feb-05 6:44

Use the RecordSet's NextRecordset() method

VB codes - Triggering Event

FuzMic13-Feb-05 20:39

FuzMic

13-Feb-05 20:39

Dear Visual Basic & Access Experts
In my application program, I have a Access (version xp) form that calls a update event handler. In this handler I need to know if the user has clicked on the "Close" button of the form. What VBasic codes do we use to check what action triggers off the the chain of events that lead up to the update event & onwards? In particular I am interested to to check if the triggering event is the clicking of the "Close" button of the form; if so I will then write some code to handle this particular case. I tried to handle this via Close event handler but since it comes after the "Update" event, it does not work.

Please help...Cheers! Michael

detect error while running script using "osql " utility

devvvy13-Feb-05 16:14

devvvy

13-Feb-05 16:14

How do I detect error while running script with "osql" utility?

Dim cmd As String = "osql.exe"<br />
Dim args As String ="-S localhost -U joe -P xxx -d master -i"<br />
Dim fullCmd As String = args + " '" + scriptfile + "'"<br />
<br />
info = New ProcessStartInfo(cmd, fullCmd)<br />
info.UseShellExecute = False<br />
<br />
ps = Process.Start(info) 'Is there any way to monitor if any error encountered during script execution?<br />
ps.WaitForExit()<br />

Thanks in advance!

Norman Fung

Re: detect error while running script using "osql " utility

Colin Angus Mackay13-Feb-05 22:45

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Feb-05 22:45

Why not just dump the contents of the script file into a SqlCommand object and Execute the command? That way you'd get a SqlException if something goes wrong and you can use the properties on the SQL Exception object to determine what the error is and where it occurred.

Extended stored proc

SnuhEyeless13-Feb-05 14:43

SnuhEyeless

13-Feb-05 14:43

Hi,
(not sure if this is the right forum to post into)
I have an extended stored proc (C++ dll) that calls a managed DLL that calls a web service, and all is going well until the point in the managed DLL that invokes the actual webservice. At that point SQL server complains of a connection lost.

This is probably got to do with the fact that uses SQL, however the webservice is on an entirely different box, with it's own SQL server, so I'm not sure exactly what to do about it.

Has anyone come across this before or has anyone got any suggestions on how to fix it?

thanks in advance

how to speed up sqlconnection

sianatia13-Feb-05 8:04

13-Feb-05 8:04

hi,

im using databases in MsSqlservers but i have a major problem that when i connect to my database by SqlConnection the connection is too slow but when i use query analyzer to get the same date its very fast(about 20 times faster)

i wnat to know if there is a way to speed up the sqlconnection..

Colin Angus Mackay13-Feb-05 22:57

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Feb-05 22:57

UAU wrote:
when i connect to my database by SqlConnection the connection is too slow

UAU wrote:
i use query analyzer to get the same date its very fast

I'm confused. Do you mean that the act of initially connecting is slow? Or do you mean the act of transfering data to the application is slow?

Also, you don't supply the code you are using so it is difficult to tell if there are any bottlenecks that are causing the problem because I've always had consitent results between the speed of the Query Analyser and my .NET applications.

sianatia14-Feb-05 17:20

14-Feb-05 17:20

hi,
i mean the act of transfering data to my application is slow.

i use sqldatareader in my application and my qurey is just a simple select query,telling that when i tested the connection by SqlDataAdapter the speed was as fast as query analyzer.

is it possible that using Sqldatareader makes the data transfer too slow?

thanks

Colin Angus Mackay14-Feb-05 20:33

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

14-Feb-05 20:33

Since an SqlDataAdapter uses an SqlDataReader internally to perform the transfer it sounds like the code you have that takes the data from the SqlDataReader and does what ever your application needs to do with the data is causing the slowness, however I don't know what that code is or what it does so I cannot help further.

sianatia15-Feb-05 17:14

15-Feb-05 17:14

hi,
here is my code(it runs on a different thread from the main thread of application)

com.CommandText = "select something";<br />
		rd=com.ExecuteReader();<br />
		if(rd.HasRows)<br />
		{<br />
		 rd.Read();<br />
		 do<br />
		 {<br />
		  //some code goes here<br />
		 }while(rd.Read());<br />
		}<br />
		rd.Close();<br />
		if(cur.Nodes.Count > 0)<br />
		{<br />
		  com.CommandText="select somthing";<br />
		  rd=com.ExecuteReader();<br />
		  rd.Read();<br />
		 //some code goes here<br />
		}<br />
		rd.Close();

i have to run these codes for several times in my application..
thanks

Colin Angus Mackay15-Feb-05 19:50

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

15-Feb-05 19:50

Personally, I would have written this as

rd=com.ExecuteReader();
while(rd.Read())
{
    // some code goes here
}

Rather than do all that rd.HasRows() followed by a do{}while() but I don't see how this would make it faster unless HasRows is particularly slow.

When you timed the data transfer using a SqlAdapter did you include the time to perform all the other actions where you have indicated // some code goes here?

sianatia18-Feb-05 4:34

18-Feb-05 4:34

hi,

the action on the datareader is just pick up the datas and save them but on my machine with about 2.6 GHZ i dony think the code makes it sliw,is it possible that my sql query wich has where clause makes the transactions slow?

Colin Angus Mackay18-Feb-05 4:44

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

18-Feb-05 4:44

UAU wrote:
is it possible that my sql query wich has where clause makes the transactions slow?

Possibly, but it does not explain that your test using a SqlDataAdapter was faster (I am assuming you performed exactly the same query). It should have been slower because using the SqlDataAdapter makes a copy of the data in memory (in the DataSet object) before you can pick up the data and save them.

What does your SQL Query do?

sianatia18-Feb-05 18:19

18-Feb-05 18:19

hi,
this is the complete source code,but to tel you the truth i didnt use the same query with my SqlDataAdapter i made a simple select query with no where clause.

com.CommandText = "select * from general where parent = @id order by menuindex desc ";<br />
				rd=com.ExecuteReader();<br />
					if(rd.HasRows)<br />
					{<br />
						rd.Read();<br />
						do<br />
						{<br />
							if(rd.GetBoolean(2))<br />
								AddSeperatorDownLoad(cur,0);<br />
							else<br />
								AddNodetoNodeDownLoad(cur,0);<br />
							SetChild(cur.Nodes[0],rd);<br />
							list.Push(cur.Nodes[0]);<br />
						}while(rd.Read());<br />
					}<br />
					rd.Close();<br />
					if(cur.Nodes.Count > 0)<br />
					{<br />
						com.CommandText="select * from popup where menuid = @id ";<br />
						if(con.State == ConnectionState.Closed)<br />
							con.Open();<br />
						rd=com.ExecuteReader();<br />
						rd.Read();<br />
						SetPopup(cur,rd);<br />
					}<br />
					rd.Close();<br />
					if(curtag.menuItem != null && curtag.menuItem.GeneralSetting.IsPage)<br />
					{<br />
						curtag.menuItem.GeneralSetting.Page=new PageSetting();<br />
						com.CommandText="SELECT     Title, KeyWord, IsGuideLine, Description "<br />
										+" FROM         Page where menuid=@id";<br />
						rd=com.ExecuteReader();<br />
						if(rd.HasRows)<br />
						{<br />
							rd.Read();<br />
							curtag.menuItem.GeneralSetting.Page.Description=(string)rd["Description"];<br />
							curtag.menuItem.GeneralSetting.Page.IsGuideLine=(bool)rd["IsGuideLine"];<br />
							curtag.menuItem.GeneralSetting.Page.KeyWords=(string)rd["KeyWord"];<br />
							curtag.menuItem.GeneralSetting.Page.Title=((string)rd["Title"]).Replace("Karmania Media ::","");<br />
							curtag.menuItem.GeneralSetting.Page.PageContent"";<br />
						}<br />
						rd.Close();<br />
					}<br />

Colin Angus Mackay19-Feb-05 0:55

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

19-Feb-05 0:55

Okay, here are some general tips - They may or may not improve the speed

1. Don't use SELECT * because if your data model changes on the database then your code could break if the position of columns changes. You should always send a list of the columns you need that way if there are any additional columns in the table that you don't need to don't waste time retrieving them and you always know the order the columns will arrive in.

2. If the table is large you may wish to consider indexing the columns that are referenced in the WHERE and ORDER BY clauses. However, if you do a lot of INSERTs on the table you may have to balance the needs of inserting quickly over retrieving quickly. Adding indexes slows down INSERT, UPDATE and DELETE operations because it has to modify each affected index as well as the table.

3. You can replace

if(rd.HasRows)
{
rd.Read();
do
{
    // Do stuff here
}while(rd.Read());

with

while(rd.Read())
{
    // do stuff here
}

which removes the redundant rd.HasRows call. Because the first call to rd.Read() will return false anyway if there are no rows in the result set the rd.HasRows can be removed if the check on the while is moved to the top of the loop rather than at the bottom of the loop

I hope this helps.

sianatia23-Feb-05 17:52