Recording data into oracle

[vikas251074]

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

[CroCrew]

Hello vikas251074,

Add this before your "Comm.Execu te 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~

[jhardman]

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

[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

[vikas251074]

How can I save more than thirty column including date.

Please tell me as it is important. !!!!!!!!!!!!!!! !!!!!!!!!!!!!!! !!!!@@@@@@@@@@@ @

[jhardman]

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 "AdOpenDyna mic" and "AdLockOptimist ic". Hope this helps.

Jared

[jhardman]

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

[CroCrew]

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~