Hey Everyone ...! I been trying to correct a SQL error that I received within Visual Basic.Net 3.5 to no avail. I've consistently seen this error: The Variable name '@Sum' has already been declared. Variable names must be unique within a query batch or stored procedure. Here's an example of the code. Thanks in advance. May each of you have a blessed day as the Lord wills. JEP_Dude
You declared your SqlCommand before for loop and you try to add @Sum and @Symnl parameters for L1 * L2 times to the SqlCommand. Getting that error is pretty normal. There are several ways to handle this error; 1) You also should declare the SqlCommand in the for loop. 2) Must clear the parameters before executing SqlCommand 1) Or 2) I didn't try if they work properly or not. If you get any errors, let me know. But the 1 st code should work without any errors anyway.
tnrsr ....! THANK YOU VERY MUCH! The first option proved to work wonders! I was wondering if I should use the second option as well? Yet, if its an extra step that is not justified since the main problem is finally fixed, then why add it? PS: Here's an simple question for you. Is the lower example code are the SQL commands in the correct order? ....and is it necessary if my aim is to minimize maintenance? Example: SQL_Connection.Close() SQL_Connection.Dispose() End Sub
2nd option is more suitable than the 1st one, if we talk about performance. 1st code writes and disposes the SQL_Command variable to the memory L1 * L2 times. That means more I/O traffic. However, with X GB of memory, you don't even notice anything. Firstly, this is not a good example. You shouldn't repeat yourself, shouldn't write any of your methods twice. This is the main aim of the object oriented programming. But, think about this; Your code has thousands of this kind of methods. If you don't use the "extra step", the number of the lines you wrote will be lower and the size of the compiled output of your code will be lower as well. But you'll access thousands of times more to the memory according to 1st option. That is your choice. Which one do you prefer? And for the last question; Yes, they are in the correct order and yes you should use them. Consider that the size of an SqlConnection variable is 64 bytes (don't know the size, just an assumption) and your computer has 512 MB of ram free. That means you can declare (512 * 1024 * 1024) / 64 SqlConnection variables "in theory". Or if you don't use the lines that you asked and do not dispose the SqlConnection variable that you declared before, you won't be able to call the method after (512 * 1024 * 1024) / 64 times, cause there will be (512 * 1024 * 1024) / 64 SqlConnection variables in the memory.
tnrsr ...! Thanks for your feedback! I've made the following changes.... 1) Made the adjustments as per the above described option #2. 2) Reinstated the ...Close and ...Dispose statement. <-------------- Why are these needed? 3) Cut back the output of "For L2 = 1 To MaxSampleSize" to just one element for test purposes. Yet plans are for the results of five samples. (Therefore the files did have over 3k rows. Now has up to 500.) This SQL Server is STILL new to me. ...However, I'm learning! It should have been covered in college. But that's just wishful thinking. As you can tell, I miss flat files. ;-P May you have a blessed day (or night) as the Lord wills. JEP_Dude
No prob JEP.. You need Close and Dispose statements to release memory which you used to declare the SqlConnection variable. Declare -> Write to memory Dispose -> Delete (Release) from memory
tnrsr ......... & others just now tuning in ..... Thanks for your reply. As you can tell, I'm new to SQL within VB.Net 3.5. I'm good with statistics, but I lack experience with SQL. 1 - Would you say the modifications like those stated far above option #2 are optimal? (It does appear to work VERY well.) If they are, and I hope that is true, then the method to store statistical information will soon be complete. 2 - The next question is what should be the optimal manner to retrieve the same data from the same SQL table (as per option #2)? How should such a routine operate? Would you prefer to see the code I plan to use? May everyone who takes the time to read any part of this have a blessed day as the Lord wills. JEP_Dude
"Optimal" depends on your needs. But, for optimal usage that is agreed with many people, you can search for patterns practises. I coded a reusable data access layer which is "best" for my needs and i used to use it for all my projects. Then i discovered Microsoft Enterprise Library - Data Access Application Block which is something similar to my data access layer and gets updated with new features. It's open source, I suggest you to use and understand how it works (to learn, this is important..). If i misunderstood your question, sorry for this. Im not too good at English. Have a nice day..
tnrsr ..... & others now tuning in ... I have to say that I agree with you as for your definition of optimal. Since, I've been working with a clearly defined goal, I foolishly assumed my intentions were known. Sorry for the misunderstanding. To be clear, ....... I need to use SQL so the typical website visitor can retrieve the results of an app' I'm designing. However, the downside is it needs to viewable by various users at the same time. Hence, SQL Server 2008 is a popular solution. I hope it will store the results and ASP.Net 3.5 should retrieve them for display in alphabetical order. I hope my limited intentions for SQL are optimal as my goal is only to store, sort, and retrieve the results of another app'. I guess that I'll need to use close to three commands: TRUNCATE TABLE, INSERT INTO, and SELECT. Can you foresee any barriers that I've overlooked? ... God bless JEP_Dude