Apologies in advance for my ignorance on the subject of coding ASP.NET applications. This is my first real application development attempt in this environment and it's pretty obvious that I've still got a whole lot to learn.
I am currently finishing up some validation within a web survey form that I have developed as a VB ASP application. I am, at the moment, engaged in trying to make sure that users aren't (accidentally or purposely) adding duplicate records to the database
in which I am storing survey results. My method for doing such is to attempt an SQL query utilizing the ADODB.Recordset object, which is a method that is working just fine for me in the stand-alone Visual Basic application that processes and generates reports
based upon the survey results. I know that when I've mentioned that I'm stuck using ADODB before, people have tried to get me to switch to some other method for accessing the database; please refrain from this, I have to have this project completed in the
next 2 weeks and I simply do not have time to learn a new method and reimplement this for all of the working code that I already have.
Anyway, here is the code that I am utilizing to attempt to open the database and read a record:
Dim strConn, sqlTemp As String
Dim objConn, dbRec
objConn = Server.CreateObject("ADODB.Connection")
dbRec = Server.CreateObject("ADODB.Recordset")
Try
strConn = "Trust me, this part is set up right :)"
objConn.Open(strConn)
Catch
errBlurb.Text &= "Error accessing the database, please try again later!" & vbCrLf
success = False
End Try
sqlTemp = "SELECT (Fname, Lname) FROM [Student_Employment] WHERE (Fname = '" & firstName.Text & _
"') AND (Lname = '" & lastName.Text & "') AND (ProgramMajor = '" & progMajor.SelectedValue & "')"
Try
dbRec.Open(sqlTemp, objConn)
errBlurb.Text &= "No error executing " & sqlTemp & vbCrLf
Catch ex As Exception
success = False
errBlurb.Text &= "Error executing " & sqlTemp & vbCrLf & "Exception: " & ex.ToString & vbCrLf
End Try
The error that this is dying with is as follows:
Error executing SELECT (Fname, Lname) FROM [Student_Employment] WHERE (Fname = 'Domingo') AND (Lname = 'Martes') AND (ProgramMajor = 'Carpentry (Residential)')
Exception: System.Runtime.InteropServices.COMException (0x80040E14): Incorrect syntax near ','.
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at StudentEmploymentSurvey._Default.validateInfoz(Boolean success, Double& wage) in C:\Users\Khelair\Desktop\src\StudentEmploymentSurvey\StudentEmploymentSurvey\Survey.aspx.vb:line 148
Error accessing dbRec; Exception: System.Runtime.InteropServices.COMException (0x800A0E78): Operation is not allowed when the object is closed.
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at StudentEmploymentSurvey._Default.validateInfoz(Boolean success, Double& wage) in C:\Users\Khelair\Desktop\src\StudentEmploymentSurvey\StudentEmploymentSurvey\Survey.aspx.vb:line 156
All of my database insertion code that I've utilized in this web application has worked so far, and the methods that I'm utilizing to read this query in order to determine whether or not a record is duplicate or not seem to be similar, to me.. Also as I
mentioned they are working just fine in the stand-alone visual basic application, although I know that there are differences that I'm not currently aware of. I would be extremely grateful for any assistance that anyone could offer in this matter, many thanks
in advance!
Of course I am also able to provide any other information that may be necessary in order to assist in this matter. Oh, for what it's worth I also tried adding references to the COM Active Data Objects v.6.0 (I believe there were 2 different references that
I highlighted and added that were both v.6.0).
First，sorry to tell you that this forum only supports .NET（ASP.NET）product，so you cannot use ASP controls here。I strongly suggest you using SqlConnection+
SqlCommand for MSSQL（or OleDbConnection+OleDbCommand for Access）。Especially use DbParameter to avoid SQL injection。
As for your problem，I suggest you try to use Response.Write to output your SQL select statement and paste it into SQL Management Studio and have a try with that to check whether there's something wrong。
Now，I think it's the problem of ()——Try to remove this and change to：
sqlTemp = "SELECT Fname, Lname FROM [Student_Employment] WHERE (Fname = '" & firstName.Text & _
"') AND (Lname = '" & lastName.Text & "') AND (ProgramMajor = '" & progMajor.SelectedValue & "')"
Marked as answer by dgets on Feb 18, 2012 11:01 PM
I know that when I've mentioned that I'm stuck using ADODB before, people have tried to get me to switch to some other method for accessing the database; please refrain from this
But that is the only good advice! You're using old ADO code, where you really should be using ADO.NET
dgets
I have to have this project completed in the next 2 weeks and I simply do not have time to learn a new method and reimplement this for all of the working code that I already have.
If the rest of your code is simllar like this (and most likely it is!), then I think you will have a lot of work to do. Because although you say you have working code, your code is vulnarable to SQL injections, so it really needs to be changed. Because from
your code I cannot tell if you use Access or SQL server as the backend, read this 2 articles about the risks you face and how to solve it:
I did manage to track that parenthetical bug down and that has fixed it. Now, of course, another one has crept up and my program is telling me that the connection object is closed. :|
Thank you for your input on that! I'll start looking into SqlCommand.
I'll be keeping this in mind for the next project that I undertake. I'm also starting to look into SqlCommand, as the above user has mentioned.
You're absolutely right that the rest of my code looks like this; and I do appreciate your explanation for why not to use ADO; I'll make sure that I'm familiarizing myself with something better for the future. Thank you for your references, as well as confirming
my suspicion about SQL injection attacks. I'll have to fix something up for that without a doubt.
dgets
Member
11 Points
23 Posts
Unable to utilize ADODB.Recordset object in ASP.NET application
Feb 15, 2012 06:11 PM|LINK
Apologies in advance for my ignorance on the subject of coding ASP.NET applications. This is my first real application development attempt in this environment and it's pretty obvious that I've still got a whole lot to learn.
I am currently finishing up some validation within a web survey form that I have developed as a VB ASP application. I am, at the moment, engaged in trying to make sure that users aren't (accidentally or purposely) adding duplicate records to the database in which I am storing survey results. My method for doing such is to attempt an SQL query utilizing the ADODB.Recordset object, which is a method that is working just fine for me in the stand-alone Visual Basic application that processes and generates reports based upon the survey results. I know that when I've mentioned that I'm stuck using ADODB before, people have tried to get me to switch to some other method for accessing the database; please refrain from this, I have to have this project completed in the next 2 weeks and I simply do not have time to learn a new method and reimplement this for all of the working code that I already have.
Anyway, here is the code that I am utilizing to attempt to open the database and read a record:
Dim strConn, sqlTemp As String Dim objConn, dbRec objConn = Server.CreateObject("ADODB.Connection") dbRec = Server.CreateObject("ADODB.Recordset") Try strConn = "Trust me, this part is set up right :)" objConn.Open(strConn) Catch errBlurb.Text &= "Error accessing the database, please try again later!" & vbCrLf success = False End Try sqlTemp = "SELECT (Fname, Lname) FROM [Student_Employment] WHERE (Fname = '" & firstName.Text & _ "') AND (Lname = '" & lastName.Text & "') AND (ProgramMajor = '" & progMajor.SelectedValue & "')" Try dbRec.Open(sqlTemp, objConn) errBlurb.Text &= "No error executing " & sqlTemp & vbCrLf Catch ex As Exception success = False errBlurb.Text &= "Error executing " & sqlTemp & vbCrLf & "Exception: " & ex.ToString & vbCrLf End TryThe error that this is dying with is as follows:
All of my database insertion code that I've utilized in this web application has worked so far, and the methods that I'm utilizing to read this query in order to determine whether or not a record is duplicate or not seem to be similar, to me.. Also as I mentioned they are working just fine in the stand-alone visual basic application, although I know that there are differences that I'm not currently aware of. I would be extremely grateful for any assistance that anyone could offer in this matter, many thanks in advance!
Of course I am also able to provide any other information that may be necessary in order to assist in this matter. Oh, for what it's worth I also tried adding references to the COM Active Data Objects v.6.0 (I believe there were 2 different references that I highlighted and added that were both v.6.0).
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Unable to utilize ADODB.Recordset object in ASP.NET application
Feb 17, 2012 12:51 AM|LINK
First，sorry to tell you that this forum only supports .NET（ASP.NET）product，so you cannot use ASP controls here。I strongly suggest you using SqlConnection+
SqlCommand for MSSQL（or OleDbConnection+OleDbCommand for Access）。Especially use DbParameter to avoid SQL injection。
As for your problem，I suggest you try to use Response.Write to output your SQL select statement and paste it into SQL Management Studio and have a try with that to check whether there's something wrong。
Now，I think it's the problem of ()——Try to remove this and change to：
sqlTemp = "SELECT Fname, Lname FROM [Student_Employment] WHERE (Fname = '" & firstName.Text & _ "') AND (Lname = '" & lastName.Text & "') AND (ProgramMajor = '" & progMajor.SelectedValue & "')"hans_v
All-Star
35986 Points
6550 Posts
Re: Unable to utilize ADODB.Recordset object in ASP.NET application
Feb 17, 2012 08:37 AM|LINK
But that is the only good advice! You're using old ADO code, where you really should be using ADO.NET
If the rest of your code is simllar like this (and most likely it is!), then I think you will have a lot of work to do. Because although you say you have working code, your code is vulnarable to SQL injections, so it really needs to be changed. Because from your code I cannot tell if you use Access or SQL server as the backend, read this 2 articles about the risks you face and how to solve it:
http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
dgets
Member
11 Points
23 Posts
Re: Unable to utilize ADODB.Recordset object in ASP.NET application
Feb 18, 2012 10:55 PM|LINK
I did manage to track that parenthetical bug down and that has fixed it. Now, of course, another one has crept up and my program is telling me that the connection object is closed. :|
Thank you for your input on that! I'll start looking into SqlCommand.
dgets
Member
11 Points
23 Posts
Re: Unable to utilize ADODB.Recordset object in ASP.NET application
Feb 18, 2012 10:57 PM|LINK
I'll be keeping this in mind for the next project that I undertake. I'm also starting to look into SqlCommand, as the above user has mentioned.
You're absolutely right that the rest of my code looks like this; and I do appreciate your explanation for why not to use ADO; I'll make sure that I'm familiarizing myself with something better for the future. Thank you for your references, as well as confirming my suspicion about SQL injection attacks. I'll have to fix something up for that without a doubt.
Much appreciated.