Nothing wrong with the procedure itself. My SQL syntax for running the procedure was wrong.
The error actually telling you what is wrong? Imagine that!
For future reference:
exec Procedure Name @IntParameter=1, @StringParameter='Value'
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Monday, June 21, 2010
Strange Error in SQL Stored Procedure
I'm getting a Strange Error in my new SQL Stored Procedure for the Membership Application.
'1' is the first value in the procedure. The Insert Statement works with the same values when I run it as a query. It inserts everything in the correct fields. Yet when I run the procedure, I get that error.
I am going to walk away from it for a bit and come back later. A fresh perspective usually helps when you get stuck with something. And it saves me banging my head against the wall repeatedly.
So, I am going to work in RedDot for a bit. There are some weird things going on in on the home page. My Navigation Menu unordered list is showing but bullets on the home page, but not any other page. Also, there is something wrong with the publication work flow. The Building Announcements aren't publishing automatically when you change the visible page schedule.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '1'.
'1' is the first value in the procedure. The Insert Statement works with the same values when I run it as a query. It inserts everything in the correct fields. Yet when I run the procedure, I get that error.
I am going to walk away from it for a bit and come back later. A fresh perspective usually helps when you get stuck with something. And it saves me banging my head against the wall repeatedly.
So, I am going to work in RedDot for a bit. There are some weird things going on in on the home page. My Navigation Menu unordered list is showing but bullets on the home page, but not any other page. Also, there is something wrong with the publication work flow. The Building Announcements aren't publishing automatically when you change the visible page schedule.
Wednesday, June 9, 2010
SQL Stored Procedures and ASP.net
This was suppose to be published last night but for some reason it wasn't...
Looking back, I think it is sort of silly how I put off learning how to use SQL parameters in my ASP.net Projects. It is so much easier than creating dynamic SQL statements in the vb.net code!
To create a dynamic SQL statement, you have to write the SQL statement and store it in a String variable. Then you have to replace the values with other variables in your vb.net and remember to include all the commas, quotations marks, etc that both vb.net and SQL needs to work. Some of my SQL statements can be quite complex and I usually have to spend some time figuring out where I'm missing the comma. Very frustrating.
However, with Stored Procedures and SQL Parameters, it splits up your long dynamic SQL statement into parts. This is so much easier to debug!
SQL Server Management Studio has a pretty good template for creating simple Stored Procedures. I managed to piece together the vb.net code from a few different sites I found via Google that I neglected to bookmark. I have my code to look back on, so I am not too worried.
My favorite page on the topic I found has to be one on the Microsoft site. It's a bit older but has some good tips on how to protect your database from SQL injection attacks.
I have implemented SQL Parameters in part of the Online Memberships Project and will fully test it out tomorrow morning. Now that I have gotten back on track, the project is going well. Still late, but it will be done!
Looking back, I think it is sort of silly how I put off learning how to use SQL parameters in my ASP.net Projects. It is so much easier than creating dynamic SQL statements in the vb.net code!
To create a dynamic SQL statement, you have to write the SQL statement and store it in a String variable. Then you have to replace the values with other variables in your vb.net and remember to include all the commas, quotations marks, etc that both vb.net and SQL needs to work. Some of my SQL statements can be quite complex and I usually have to spend some time figuring out where I'm missing the comma. Very frustrating.
However, with Stored Procedures and SQL Parameters, it splits up your long dynamic SQL statement into parts. This is so much easier to debug!
SQL Server Management Studio has a pretty good template for creating simple Stored Procedures. I managed to piece together the vb.net code from a few different sites I found via Google that I neglected to bookmark. I have my code to look back on, so I am not too worried.
My favorite page on the topic I found has to be one on the Microsoft site. It's a bit older but has some good tips on how to protect your database from SQL injection attacks.
I have implemented SQL Parameters in part of the Online Memberships Project and will fully test it out tomorrow morning. Now that I have gotten back on track, the project is going well. Still late, but it will be done!
Tuesday, June 8, 2010
Progress on the Membership Application
I made progress on the Membership Application this morning!
I managed to reclaim my source code by using a free program called Reflector by RedGate. This program de-compiles your .net classes.
So I made a copy of my old project and copied the updated code into it. I also copied the aspx page source from the published project and changed the page tag to include the code behind file and removed the compiled file from the inherits property.
Unfortunately, Reflector does not retrieve the cleanest code. Though it keeps the names of the methods and form control names, most of the variables are now "Item" or "Num". You also need to either import the VisualBasic Compiler Services or change the data type conversions in the class you copied over.
Since I only copied over the methods for my Form page, my project was now missing a lot of the back end code needed for form control events. I had to re-associate the form controls the event methods again. I was lazy and just double clicked on the form control and copied the code into the new method.
Now that my project is back to where it was at the end of May, I can move on. I am working on creating Stored Procedures in my test database to access as SQL parameters in my asp.net project.
I haven't used this technique before; my past projects have just used SQL, which leaves myself open to SQL injection attacks. None of those projects are live anymore, thankfully. But I am looking forward to learning something new!
I managed to reclaim my source code by using a free program called Reflector by RedGate. This program de-compiles your .net classes.
So I made a copy of my old project and copied the updated code into it. I also copied the aspx page source from the published project and changed the page tag to include the code behind file and removed the compiled file from the inherits property.
Unfortunately, Reflector does not retrieve the cleanest code. Though it keeps the names of the methods and form control names, most of the variables are now "Item" or "Num". You also need to either import the VisualBasic Compiler Services or change the data type conversions in the class you copied over.
Since I only copied over the methods for my Form page, my project was now missing a lot of the back end code needed for form control events. I had to re-associate the form controls the event methods again. I was lazy and just double clicked on the form control and copied the code into the new method.
Now that my project is back to where it was at the end of May, I can move on. I am working on creating Stored Procedures in my test database to access as SQL parameters in my asp.net project.
I haven't used this technique before; my past projects have just used SQL, which leaves myself open to SQL injection attacks. None of those projects are live anymore, thankfully. But I am looking forward to learning something new!
Subscribe to:
Posts (Atom)