ASP.NET C# Code Behind SQL Update on a form
-
strSQL="UPDATE [q1], [q2], [q3], [q4], [q5], [q6], [q7], [q8],"+ " [q9], [q10], [q11], [q12], [q13], [q14], [q15],"+ " [q16], [q17], [q18], [q19], [q20], [q21], [q23],"+ " [q24], [q26], [q28], [q34], [q35], [q38], [q39],"+ " [q40], [q42], [q43], [q44], [q45], [q46], [q47],"+ " [q48], [q51], [q52], [q53], [q54], [q57], [q58],"+ " [q61], [q62], [q63], [q66], [q67], [q70], [q71],"+ " [q72], [q73], [q74], [q75], [q84], [q85], [q86],"+ " [q87], [q88], [q89], [q90], [q91], [q98], [q99],"+ " [q100], [q101],"+" [q102], [q103], [q104], [TCAIMSSite],"+ " [DTG_Submit], [LoginID], [TestPhase]"+ " FROM t_EOT_User WHERE RecNum="+RecNum.Text;error says SYNTAX errror on UPDATE Statement -
strSQL="UPDATE [q1], [q2], [q3], [q4], [q5], [q6], [q7], [q8],"+ " [q9], [q10], [q11], [q12], [q13], [q14], [q15],"+ " [q16], [q17], [q18], [q19], [q20], [q21], [q23],"+ " [q24], [q26], [q28], [q34], [q35], [q38], [q39],"+ " [q40], [q42], [q43], [q44], [q45], [q46], [q47],"+ " [q48], [q51], [q52], [q53], [q54], [q57], [q58],"+ " [q61], [q62], [q63], [q66], [q67], [q70], [q71],"+ " [q72], [q73], [q74], [q75], [q84], [q85], [q86],"+ " [q87], [q88], [q89], [q90], [q91], [q98], [q99],"+ " [q100], [q101],"+" [q102], [q103], [q104], [TCAIMSSite],"+ " [DTG_Submit], [LoginID], [TestPhase]"+ " FROM t_EOT_User WHERE RecNum="+RecNum.Text;error says SYNTAX errror on UPDATE StatementWhat happens if you type this text into SQL Server directly ? I trust this code is not in your .aspx.cs file ?
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
What happens if you type this text into SQL Server directly ? I trust this code is not in your .aspx.cs file ?
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
What happens if you type this text into SQL Server directly ? I trust this code is not in your .aspx.cs file ?
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
Great - so long as it's not in the actual code behind, that would be bad design, is all.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
OK, an MDB file ? What if you recreate your table structure in SQL Server and try this SQL ? I presume the SQL is wrong, it looks odd to me. Do the two tables you're inserting from and to have the same fields in them ? Have you tried rewriting it to work differently ?
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
Great - so long as it's not in the actual code behind, that would be bad design, is all.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
It is in in the actual code behind? How else could it be in the .CS file? I am confused... Been working way to long to answer questions LOL... Going on 100+ hours... I got it to work...
private void Button1_Click(object sender, System.EventArgs e) { bool bNoError=true; OleDbConnection con = new OleDbConnection(strConnection); string strSQL; DataSet ds = new DataSet(); strSQL="UPDATE t_EOT_User set [q1]='"+q1.SelectedValue+"', [q2]='"+q2.Text+"',"+ " [q3]='"+q3.SelectedValue+"', [q4]='"+q4.Text+"', [q5]='"+q5.SelectedValue+"',"+ " [q6]='"+q6.Text+"', [q7]='"+q7.SelectedValue+"', [q8]='"+q8.Text+"',"+ " [q9]='"+q9.SelectedValue+"', [q10]='"+q10.Text+"',"+ " [q11]='"+q11.SelectedValue+"', [q12]='"+q12.Text+"',"+ " [q13]='"+q13.SelectedValue+"', [q14]='"+q14.Text+"',"+ " [q15]='"+q15.Text+"', [q16]='"+q16.Text+"',"+ " [q17]='"+q17.Text+"', [q18]='"+q18.Text+"',"+ " [q19]='"+q19.SelectedValue+"', [q20]='"+q20.SelectedValue+"',"+ " [q21]='"+q21.SelectedValue+"', [q23]='"+q23.Text+"',"+ " [q24]='"+q24.SelectedValue+"', [q26]='"+q26.Text+"',"+ " [q28]='"+q28.Text+"', [q34]='"+q34.SelectedValue+"',"+ " [q35]='"+q35.Text+"', [q38]='"+q38.SelectedValue+"',"+ " [q39]='"+q39.Text+"', [q40]='"+q40.SelectedValue+"',"+ " [q42]='"+q42.Text+"', [q43]='"+q43.SelectedValue+"',"+ " [q44]='"+q44.Text+"', [q45]='"+q45.SelectedValue+"',"+ " [q46]='"+q46.Text+"', [q47]='"+q47.SelectedValue+"',"+ " [q48]='"+q48.Text+"', [q51]='"+q51.SelectedValue+"',"+ " [q52]='"+q52.Text+"', [q53]='"+q53.SelectedValue+"',"+ " [q54]='"+q54.Text+"', [q57]='"+q57.SelectedValue+"',"+ " [q58]='"+q58.Text+"', [q61]='"+q61.Text+"',"+ " [q62]='"+q62.Text+"', [q63]='"+q63.SelectedValue+"',"+ " [q66]='"+q66.SelectedValue+"', [q67]='"+q67.Text+"',"+ " [q70]='"+q70.SelectedValue+"', [q71]='"+q71.Text+"',"+ " [q72]='"+q72.SelectedValue+"', [q73]='"+q73.Text+"',"+ " [q74]='"+q74.SelectedValue+"', [q75]='"+q75.Text+"',"+ " [q84]='"+q84.SelectedValue+"', [q85]='"+q85.Text+"',"+ " [q86]='"+q86.SelectedValue+"', [q87]='"+q87.Text+"',"+ " [q88]='"+q88.SelectedValue+"', [q89]='"+q89.Text+"',"+ " [q90]='"+q90.Text+"', [q91]='"+q91.Text+"',"+ " [q98]='"+q98.SelectedValue+"', [q99]='"+q99.SelectedValue+"',"+ " [q100]='"+q100.Text+"', [q101]='"+q101.SelectedValue+"',"+ " [q102]='"+q102.Text+"', [q103]='"+q103.SelectedValue+"',"+ " [q104]='"+q104.Text+"' WHERE RecNum= -
It is in in the actual code behind? How else could it be in the .CS file? I am confused... Been working way to long to answer questions LOL... Going on 100+ hours... I got it to work...
private void Button1_Click(object sender, System.EventArgs e) { bool bNoError=true; OleDbConnection con = new OleDbConnection(strConnection); string strSQL; DataSet ds = new DataSet(); strSQL="UPDATE t_EOT_User set [q1]='"+q1.SelectedValue+"', [q2]='"+q2.Text+"',"+ " [q3]='"+q3.SelectedValue+"', [q4]='"+q4.Text+"', [q5]='"+q5.SelectedValue+"',"+ " [q6]='"+q6.Text+"', [q7]='"+q7.SelectedValue+"', [q8]='"+q8.Text+"',"+ " [q9]='"+q9.SelectedValue+"', [q10]='"+q10.Text+"',"+ " [q11]='"+q11.SelectedValue+"', [q12]='"+q12.Text+"',"+ " [q13]='"+q13.SelectedValue+"', [q14]='"+q14.Text+"',"+ " [q15]='"+q15.Text+"', [q16]='"+q16.Text+"',"+ " [q17]='"+q17.Text+"', [q18]='"+q18.Text+"',"+ " [q19]='"+q19.SelectedValue+"', [q20]='"+q20.SelectedValue+"',"+ " [q21]='"+q21.SelectedValue+"', [q23]='"+q23.Text+"',"+ " [q24]='"+q24.SelectedValue+"', [q26]='"+q26.Text+"',"+ " [q28]='"+q28.Text+"', [q34]='"+q34.SelectedValue+"',"+ " [q35]='"+q35.Text+"', [q38]='"+q38.SelectedValue+"',"+ " [q39]='"+q39.Text+"', [q40]='"+q40.SelectedValue+"',"+ " [q42]='"+q42.Text+"', [q43]='"+q43.SelectedValue+"',"+ " [q44]='"+q44.Text+"', [q45]='"+q45.SelectedValue+"',"+ " [q46]='"+q46.Text+"', [q47]='"+q47.SelectedValue+"',"+ " [q48]='"+q48.Text+"', [q51]='"+q51.SelectedValue+"',"+ " [q52]='"+q52.Text+"', [q53]='"+q53.SelectedValue+"',"+ " [q54]='"+q54.Text+"', [q57]='"+q57.SelectedValue+"',"+ " [q58]='"+q58.Text+"', [q61]='"+q61.Text+"',"+ " [q62]='"+q62.Text+"', [q63]='"+q63.SelectedValue+"',"+ " [q66]='"+q66.SelectedValue+"', [q67]='"+q67.Text+"',"+ " [q70]='"+q70.SelectedValue+"', [q71]='"+q71.Text+"',"+ " [q72]='"+q72.SelectedValue+"', [q73]='"+q73.Text+"',"+ " [q74]='"+q74.SelectedValue+"', [q75]='"+q75.Text+"',"+ " [q84]='"+q84.SelectedValue+"', [q85]='"+q85.Text+"',"+ " [q86]='"+q86.SelectedValue+"', [q87]='"+q87.Text+"',"+ " [q88]='"+q88.SelectedValue+"', [q89]='"+q89.Text+"',"+ " [q90]='"+q90.Text+"', [q91]='"+q91.Text+"',"+ " [q98]='"+q98.SelectedValue+"', [q99]='"+q99.SelectedValue+"',"+ " [q100]='"+q100.Text+"', [q101]='"+q101.SelectedValue+"',"+ " [q102]='"+q102.Text+"', [q103]='"+q103.SelectedValue+"',"+ " [q104]='"+q104.Text+"' WHERE RecNum=OK, your code is in fact in the code behind. Yes, this will work, but it's not how to produce well written software. Your presentation code should call methods on a dll which contain your buisiness logic and database functionality. This if fine if you're writing something for fun, but if someone is paying for it, or it's going to be put to serious use, this is not how to go about it.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
OK, your code is in fact in the code behind. Yes, this will work, but it's not how to produce well written software. Your presentation code should call methods on a dll which contain your buisiness logic and database functionality. This if fine if you're writing something for fun, but if someone is paying for it, or it's going to be put to serious use, this is not how to go about it.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
I am not a school'd programmer. Infact, this is the first Dot Net website, I have ever done. I am a VB programmer, my jon was eliminated and my company offered me this position. This is how all of the pages that were given to me function. Every piece of code we use in our code behind. I work for the GOVT, we arent allowed to USE SQL server yet!
-
I am not a school'd programmer. Infact, this is the first Dot Net website, I have ever done. I am a VB programmer, my jon was eliminated and my company offered me this position. This is how all of the pages that were given to me function. Every piece of code we use in our code behind. I work for the GOVT, we arent allowed to USE SQL server yet!
Ibuprofen wrote:
I am a VB programmer
OK, even in a client VB app, you should use an n-tiered approach.
Ibuprofen wrote:
Every piece of code we use in our code behind.
So I gathered. That really, really sucks.
Ibuprofen wrote:
we arent allowed to USE SQL server yet!
SQL Server has nothing to do with what I am saying
Ibuprofen wrote:
I work for the GOVT
Then your job is probably safe for now, but if you want to be employable in the 'real world', I recommend doing a google on n-tiered architecture and learning how to write this stuff. I am not schooled either, but if you're self taught, all the more reason to always be looking for things to learn.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
strSQL="UPDATE [q1], [q2], [q3], [q4], [q5], [q6], [q7], [q8],"+ " [q9], [q10], [q11], [q12], [q13], [q14], [q15],"+ " [q16], [q17], [q18], [q19], [q20], [q21], [q23],"+ " [q24], [q26], [q28], [q34], [q35], [q38], [q39],"+ " [q40], [q42], [q43], [q44], [q45], [q46], [q47],"+ " [q48], [q51], [q52], [q53], [q54], [q57], [q58],"+ " [q61], [q62], [q63], [q66], [q67], [q70], [q71],"+ " [q72], [q73], [q74], [q75], [q84], [q85], [q86],"+ " [q87], [q88], [q89], [q90], [q91], [q98], [q99],"+ " [q100], [q101],"+" [q102], [q103], [q104], [TCAIMSSite],"+ " [DTG_Submit], [LoginID], [TestPhase]"+ " FROM t_EOT_User WHERE RecNum="+RecNum.Text;error says SYNTAX errror on UPDATE StatementDid you look what actually ends up in strSQL? You are likely to spot problem in final query (with user input) then from this code alone. BTW for us here it's impossible to say if your code is ok since we don't know structure of your database. In addition to what CG said, there is another problem with your code.
Ibuprofen wrote:
" [DTG_Submit], [LoginID], [TestPhase]"+ " FROM t_EOT_User WHERE RecNum="+RecNum.Text;
I recon RecNum is Textbox or similar. Now, what happens when user write, for example "1 AND TRUE"? You will end up with all records updated instead of just one. It's serious security hole, known as SQL Injection.
"Throughout human history, we have been dependent on machines to survive. Fate, it seems, is not without a sense of irony. " - Morpheus "Real men use mspaint for writing code and notepad for designing graphics." - Anna-Jayne Metcalfe
-
Ibuprofen wrote:
I am a VB programmer
OK, even in a client VB app, you should use an n-tiered approach.
Ibuprofen wrote:
Every piece of code we use in our code behind.
So I gathered. That really, really sucks.
Ibuprofen wrote:
we arent allowed to USE SQL server yet!
SQL Server has nothing to do with what I am saying
Ibuprofen wrote:
I work for the GOVT
Then your job is probably safe for now, but if you want to be employable in the 'real world', I recommend doing a google on n-tiered architecture and learning how to write this stuff. I am not schooled either, but if you're self taught, all the more reason to always be looking for things to learn.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
I know, that SQL has nothing to do with it. :) Actually, I wasnt a VB.net programmer, just a VBScript programer did alot of excel / access programering, as well as I wrote alot of instrumentation programs the govt uses to analyze network packets that they collect with there High Speed Lan Taps. I know how to write true VB apps. Why would you want to compile a whole websites piece of code into a DLL file? I will google it like you said, I am always looking to learn, but this project wasnt mind from the begining, I got given it after someone was working on it for two years, there is lots of devlopment done to it, and there is no way I could go back and change it all is what I was getting at.
-
Did you look what actually ends up in strSQL? You are likely to spot problem in final query (with user input) then from this code alone. BTW for us here it's impossible to say if your code is ok since we don't know structure of your database. In addition to what CG said, there is another problem with your code.
Ibuprofen wrote:
" [DTG_Submit], [LoginID], [TestPhase]"+ " FROM t_EOT_User WHERE RecNum="+RecNum.Text;
I recon RecNum is Textbox or similar. Now, what happens when user write, for example "1 AND TRUE"? You will end up with all records updated instead of just one. It's serious security hole, known as SQL Injection.
"Throughout human history, we have been dependent on machines to survive. Fate, it seems, is not without a sense of irony. " - Morpheus "Real men use mspaint for writing code and notepad for designing graphics." - Anna-Jayne Metcalfe
-
Did you look what actually ends up in strSQL? You are likely to spot problem in final query (with user input) then from this code alone. BTW for us here it's impossible to say if your code is ok since we don't know structure of your database. In addition to what CG said, there is another problem with your code.
Ibuprofen wrote:
" [DTG_Submit], [LoginID], [TestPhase]"+ " FROM t_EOT_User WHERE RecNum="+RecNum.Text;
I recon RecNum is Textbox or similar. Now, what happens when user write, for example "1 AND TRUE"? You will end up with all records updated instead of just one. It's serious security hole, known as SQL Injection.
"Throughout human history, we have been dependent on machines to survive. Fate, it seems, is not without a sense of irony. " - Morpheus "Real men use mspaint for writing code and notepad for designing graphics." - Anna-Jayne Metcalfe
-
I know, that SQL has nothing to do with it. :) Actually, I wasnt a VB.net programmer, just a VBScript programer did alot of excel / access programering, as well as I wrote alot of instrumentation programs the govt uses to analyze network packets that they collect with there High Speed Lan Taps. I know how to write true VB apps. Why would you want to compile a whole websites piece of code into a DLL file? I will google it like you said, I am always looking to learn, but this project wasnt mind from the begining, I got given it after someone was working on it for two years, there is lots of devlopment done to it, and there is no way I could go back and change it all is what I was getting at.
Ibuprofen wrote:
Why would you want to compile a whole websites piece of code into a DLL file?
Because that's how it is done - the business logic and data access exist in different assemblies to the presentation layer. In theory, it means different parts can be replaced. This does not happen in the real world. However, it does help with organising the code, nonetheless. Certainly having all the DB code in one place is invaluable. And, using stored procs is even better.
Ibuprofen wrote:
there is lots of devlopment done to it, and there is no way I could go back and change it all is what I was getting at.
Well, you may be able to improve new parts at least. Or, at a minimum, learn how to do it, so you can tell your bosses that you're not happy having to work with a monolithic structure, that it will cause maintenance issues in the future, but you have no choice.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )