Recording data into oracle  | Familiar Sight | | Join Date: Dec 2007 Location: Patna
Posts: 198
| |
I am using oracle 9i with ASP
I am using following way to store data into oracle. This is a part of code
I am always getting missing expression or invalid column name or any other. This looks like a forest. I can't able to rectify the any problem. Is there any other way to insert data into oracle table like writing each column with form variable and then update the table.
Can you give me any better way writing a saving program.
Regards, -
Set conn = Server.Createobject("ADODB.Connection")
-
conn.Open "DSN=ORA; User ID = scott; Password = tiger"
-
Set R = Server.CreateObject("ADODB.Recordset")
-
-
SQL = "INSERT INTO SABF (empno, empname, design, sex, grade, category, dob, doj, dos, ndos, doa, dojgr, reason,option1, commutation, rehabilitation, basic2003, stag2003, personalpay2003, splpay2003, adhoc2003, npa2003, da2003, protectpay2003, basic, stag, personalpay, splpay, adhoc, npa, da, protectpay, beneficiaryspouse, dobos, altnominee1, altnominee2, altnominee3, altnominee4, altnominee5, altnominee6, address, contact, assesseno, annuityno, annuityamt, onethird, refund, monthlypension, choiceopt) VALUES (" & vempno & ",'" & vempname & "', '" & vdesign & "', '" & vsex & "', '" & vgrade & "', '" & vcategory & "', to_date('" & vdobdd & "/" & vdobmm & "/" & vdobyyyy & "'), to_date('" & vdojdd & "/" & vdojmm & "/" & vdojyyyy & "'), to_date('" & vdosdd & "/" & vdosmm & "/" & vdosyyyy & "'), to_date('" & vndosdd & "/" & vndosmm & "/" & vndosyyyy & "'), to_date('" & vdoadd & "/" & vdoamm & "/" & vdoayyyy & "'), to_date('" & vdojgrdd & "/" & vdojgrmm & "/" & vdojgryyyy & "'), '" & vreason & "', '" & voption & "', '" & vcommutation & "', '" & vrehabilitation & "', " &vbasic2003 & ", " &vstag2003 & ", " & vpersonalpay2003 & ", " & vsplpay2003 & ", " & vadhoc2003 & ", " & vnpa2003 & ", " & vda2003 & ", " & vprotectpay2003 & ", " & vbasic & ", " & vstag & ", " & vpersonalpay & ", " & vsplpay & ", " & vadhoc & ", " & vnpa & ", " & vda & ", " & vprotectpay & ", '" & vbeneficiaryspouse & "', to_date('" & vdobosdd & "/" & vdobosmm & "/" & vdobosyyyy & "'), '" & valtnominee1 & "', '" & valtnominee2 & "', '" & valtnominee3 & "', '" & valtnominee4 & "', '" & valtnominee5 & "', '" & valtnominee6 & "', '" & vaddress & "', '" & vcontact & "', '" & vassesseno & "', '" & vannuityno & "', " & vannuityamt & ", " & v1third & ", " & vrefund & ", " & vmonthlypension & ", '" & vchoiceopt & "')"
-
Conn.Execute SQL
-
|  | Expert | | Join Date: Jan 2008 Location: Michigan
Posts: 338
| | | re: Recording data into oracle
Hello vikas251074,
Add this before your "Comm.Execute SQL" command to see what SQL get generated from your building of the query. Then you can test that in a SQL analyzer to see if your query is formatted correctly. -
Response.Write(SQL)
-
Response.End
-
Hope that help in finding the problem,
CroCrew~
|  | Moderator | | Join Date: Jan 2007 Location: logan, utah
Posts: 2,690
| | | re: Recording data into oracle
CroCrew's response isn't just advice this is what you need to do. You need to check your query in a query analyzer because the script is running fine, it is the query that isn't working. If the query returns an error (and this happens to all of us on occasion) we all look at the query and if we can't find the problem, we run it through a query analyzer. The more you work with dbs, the more you will do just that.
Jared
| | Familiar Sight | | Join Date: Dec 2007 Location: Patna
Posts: 198
| | | re: Recording data into oracle
Since it is long query, it very difficult to identify problem, whether the following way can be used for this substitute.
I have used the following way but getting error 'Rowset cannot be scrolled backward'. So I want to how can I use the following way because this is efficient way to control input data. -
Set conn = Server.Createobject("ADODB.Connection")
-
conn.Open "DSN=ORA; User ID = scott; Password = tiger"
-
Set R = Server.CreateObject("ADODB.Recordset")
-
R.Open "Select * from SABF"
-
R.Addnew
-
R("empno") = Request.Form("vempno")
-
--------------------------------------------------------
-
--------------------------------------------------------
-
R("dob") = CDate(Request.Form("vdobmm")&"/"&Request.Form("vdobdd")&/"&Request.Form("vdobyyyy"))
-
-------------------------------------------------------
-
-------------------------------------------------------
-
R.Update
-
R.close
-
| | Familiar Sight | | Join Date: Dec 2007 Location: Patna
Posts: 198
| | | re: Recording data into oracle
How can I save more than thirty column including date.
Please tell me as it is important. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!@@@@@@@@@@@@
| | Moderator | | Join Date: Jan 2007 Location: logan, utah
Posts: 2,690
| | | re: Recording data into oracle
Quote:
Originally Posted by vikas251074  Since it is long query, it very difficult to identify problem, whether the following way can be used for this substitute.
I have used the following way but getting error 'Rowset cannot be scrolled backward'. So I want to how can I use the following way because this is efficient way to control input data.
-
Set conn = Server.Createobject("ADODB.Connection")
-
conn.Open "DSN=ORA; User ID = scott; Password = tiger"
-
Set R = Server.CreateObject("ADODB.Recordset")
-
R.Open "Select * from SABF"
-
R.Addnew
-
R("empno") = Request.Form("vempno")
-
--------------------------------------------------------
-
--------------------------------------------------------
-
R("dob") = CDate(Request.Form("vdobmm")&"/"&Request.Form("vdobdd")&/"&Request.Form("vdobyyyy"))
-
-------------------------------------------------------
-
-------------------------------------------------------
-
R.Update
-
R.close
-
By default your recordset opens with limited abilities. In order to move the cursor better, I would do it like this: - Set conn = Server.Createobject("ADODB.Connection")
-
conn.Open "DSN=myDSN; User ID = myUID; Password = myPWD"
-
Set R = Server.CreateObject("ADODB.Recordset")
-
R.Open "Select * from SABF", conn, 2,3 'opens an editable recordset that you can move the cursor through forward and backward
-
-
for x = 0 to 10 'make whatever loop you need
-
R.addNew()
-
R("field1")=x
-
R("field2")=y
-
R.update
-
next
-
-
R.close()
For more information on the constants that I used to open the recordset, search for "AdOpenDynamic" and "AdLockOptimistic". Hope this helps.
Jared
| | Moderator | | Join Date: Jan 2007 Location: logan, utah
Posts: 2,690
| | | re: Recording data into oracle
and I forgot to mention, if you have each input named the same as the column names that they will fill, you can do this: -
for each x in R.fields
-
R(x) = request.form(x)
-
next
but BEWARE! This could leave you open to SQL injection attacks. You will definitely need to qualify each of the fields, especially make sure there are no semi-colons (I'm not sure you can do a SQL injection without semi-colons. Maybe with the 'GO' keyword, but that only works in MS SQL Server, I think).
Jared
|  |
Similar ASP / Active Server Pages bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,376 network members.
|
