468,514 Members | 1,697 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

Recording data into oracle

vikas251074
198 100+
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,


Expand|Select|Wrap|Line Numbers
  1. Set conn = Server.Createobject("ADODB.Connection")
  2. conn.Open "DSN=ORA; User ID = scott; Password = tiger"
  3. Set R = Server.CreateObject("ADODB.Recordset")
  4.  
  5. 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 & "')"
  6.      Conn.Execute SQL 
  7.  
Oct 16 '09 #1
7 1864
CroCrew
564 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. Response.Write(SQL)
  2. Response.End
  3.  
Hope that help in finding the problem,
CroCrew~
Oct 16 '09 #2
jhardman
3,405 Expert 2GB
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
Oct 16 '09 #3
vikas251074
198 100+
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.



Expand|Select|Wrap|Line Numbers
  1. Set conn = Server.Createobject("ADODB.Connection") 
  2. conn.Open "DSN=ORA; User ID = scott; Password = tiger" 
  3. Set R = Server.CreateObject("ADODB.Recordset") 
  4. R.Open "Select * from SABF"
  5. R.Addnew
  6. R("empno") = Request.Form("vempno")
  7. --------------------------------------------------------
  8. --------------------------------------------------------
  9. R("dob") = CDate(Request.Form("vdobmm")&"/"&Request.Form("vdobdd")&/"&Request.Form("vdobyyyy"))
  10. -------------------------------------------------------
  11. -------------------------------------------------------
  12. R.Update
  13. R.close
  14.  
Oct 18 '09 #4
vikas251074
198 100+
How can I save more than thirty column including date.

Please tell me as it is important. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!@@@@@@@@@@@@
Oct 19 '09 #5
jhardman
3,405 Expert 2GB
@vikas251074
By default your recordset opens with limited abilities. In order to move the cursor better, I would do it like this:
Expand|Select|Wrap|Line Numbers
  1. Set conn = Server.Createobject("ADODB.Connection") 
  2. conn.Open "DSN=myDSN; User ID = myUID; Password = myPWD" 
  3. Set R = Server.CreateObject("ADODB.Recordset") 
  4. R.Open "Select * from SABF", conn, 2,3 'opens an editable recordset that you can move the cursor through forward and backward
  5.  
  6. for x = 0 to 10 'make whatever loop you need
  7.    R.addNew()
  8.    R("field1")=x
  9.    R("field2")=y
  10.    R.update
  11. next
  12.  
  13. R.close()
For more information on the constants that I used to open the recordset, search for "AdOpenDynamic" and "AdLockOptimistic". Hope this helps.

Jared
Oct 19 '09 #6
jhardman
3,405 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. for each x in R.fields
  2.    R(x) = request.form(x)
  3. 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
Oct 19 '09 #7
CroCrew
564 Expert 512MB
Hello vikas251074,

Some advice: You might want to take some time and read up on “Database Normalization”.

Google: http://www.google.com/search?hl=en&q...&aq=f&oq=&aqi=

CroCrew~
Oct 19 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by francis70 | last post: by
3 posts views Thread by Jussi Rasku | last post: by
6 posts views Thread by Dovelet | last post: by
1 post views Thread by Andrew Arace | last post: by
8 posts views Thread by jer | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.