Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

ASP to SQL

Hi all ...

is there anything wrong with this code? I'm new to writing ASP with SQL here ... please help thanks!!!

<%

name = request.form(name)
dept = request.form(dept)

Set cnDB = Server.CreateObject("ADODB.Connection")
cnDB.Open "Provider=sqloledb;" & _
"Data Source=192.168.95.9;" & _
"Initial Catalog=Req_Form;" & _
Set rsQuery = Server.CreateObject("ADODB.RecordSet")
rsQuery.Open "Select * from itreqform",cnDB,1,3

rs.addnew

rs(name)=name
rs(dept)=dept
%>
0
airnike
Asked:
airnike
  • 8
  • 5
  • 3
  • +1
3 Solutions
 
aymansoftCommented:
the first you missing the ""

this code for sql server 2000
<%
name = request.form("name")
dept = request.form("dept")

set Con = server.createobject("ADODB.Connection")  ' //Create Connection to DataBase
set RS = server.createobject("ADODB.RecordSet")    ' //Create Connection to DataSource
myVar = "driver={SQL Server};server=nameserver;UID=sa;PWD=sa;DataBase=databasename"

rs.open "Select * from itreqform",myvar,1,3

rs.addnew

rs("name")=name
rs("dept")=dept

%>
you can use for query this code

sql="Select * from itreqform"
set rs=con.execute(sql)
0
 
houguanCommented:
aymansoft is right, and i believe need also the rs.update
<%
name = request.form(name)
dept = request.form(dept)
Set cnDB = Server.CreateObject("ADODB.Connection")
cnDB.Open "Provider=sqloledb;" & _
"Data Source=192.168.95.9;" & _
"Initial Catalog=Req_Form;" & _
Set rsQuery = Server.CreateObject("ADODB.RecordSet")
rsQuery.Open "Select * from itreqform",cnDB,1,3
rs.addnew
rs("name")=name
rs("dept")=dept
rs.update
%>
0
 
neeraj523Commented:
Hello

i would recomend to do in this way

<%
name = Request.Form("name")
dept = Request.Form("dept")

Set con = Server.CreateObject("ADODB.Connection")  
con.Open "driver={SQL Server};server=nameserver;UID=sa;PWD=sa;DataBase=databasename"

sql = "INSERT INTO itreqform(name,dept) VALUES('" & name & "','" & dept & "')"
con.Execute sql
con.Close
%>

No need to open a recordset if only need to add a record to the table.. it will reduce unnessecary load from the server

hope it will help u out..

neeraj523
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
airnikeAuthor Commented:
This is my actual code but still facing error ...

<%
    name = Request.Form("name")
    dept = Request.Form("dept")
    problem = Request.Form("problem")
    status = Request.Form("status")
    ip = Request.Form("ip")
    reqdate = Request.Form("date")
      
    Set cnDB = Server.CreateObject("ADODB.Connection")
    cnDB.Open "Provider=sqloledb;" & _
    "Data Source=192.168.0.234;" & _
    "Initial Catalog=Req_form;" & _
    Set rsQuery = Server.CreateObject("ADODB.RecordSet")
    rsQuery.Open "Select * from itreqform",cnDB,1,3

    rs.AddNew
    rs("name")=name
    rs("dept")=dept
    rs("problem")=problem
    rs("status"=status
    rs("ip")=ip
    rs("reqdate")=reqdate

    'Save record
    rs.Update

    set rs=nothing
    set conn=nothing

    Response.Redirect("cr_form.asp")

%>

i've tried the given solutions but still some problems .... I'm getting HTTP 500 like what i have with this code ...

please help
0
 
houguanCommented:
you left out one bracket
rs("status")=status

0
 
airnikeAuthor Commented:
yeah ... i added it back but its still the same ...

when i use the response.write to debug ... the reuest.form is ok ... until the connection creation part ... that is where the problem starts ...

please advice....
0
 
houguanCommented:
The last thing i can advice
include the response.buffer at the top

Response.Buffer=True
0
 
airnikeAuthor Commented:
at where?

the initial stage?

<%

Response.Buffer=true

name=Request.Form(name)
.
.
.
%>

is it this?
0
 
neeraj523Commented:
Hello

You have issue with ur connection string.. u havnt mentioned any UID or password in it..

your connection string should look like this

driver={SQL Server};server=nameserver;UID=sa;PWD=sa;DataBase=databasename


if still confused.. plz let me know ur sql server name, uid and password if any.. i will modify ur code ..

neeraj523


0
 
airnikeAuthor Commented:
hi...

i did not specify any password in the SQL table if self.
0
 
neeraj523Commented:

then keep password empty

like this

driver={SQL Server};server=SERVERNAME;UID=UID;PWD=;DataBase=DBNAME

neeraj523
0
 
airnikeAuthor Commented:
is it like this?

<%
..
..
..
    "Data Source=192.168.0.234;" & _
    "Initial Catalog=Req_form;" & _
    "User Id=User Id;" & _
     "Password=" &_
    "DataBase=itreqform"

    Set rsQuery = Server.CreateObject("ADODB.RecordSet")
    rsQuery.Open "Select * from itreqform",cnDB,1,3

..
..
..

%>
0
 
neeraj523Commented:

yes.. perfectly alright if you replace "User Id" value with the user id for your db

all the best

neeraj523
0
 
airnikeAuthor Commented:
hi,

i've tried and does not work ... i wonder what is wrong .....

"User ID=User Id;" &_

I've no "user id" set at the data base ...
0
 
neeraj523Commented:

Dear airnik

something is wrong with ur understanding with sql Server.. u can't set your sql server without a userid.. yes password can be blank but not userid..

better u check out your database in bit more depth or ask someone having some knowlade of databases..

else u can have a word with me on IM ..

Yahoo: neeraj523

all the best

neeraj523
0
 
airnikeAuthor Commented:
hi neeraj523,

i have set the username for it but unable to access ... furthermore i don't have Yahoo IM but instead i have MSN. if possible, my MSN is vvs98@hotmail.com.
0
 
airnikeAuthor Commented:
hi all ... thanks for the help ... although i've created the ODBC in the IIS server and to no avail ... i will still award the points to you guys who have assisted me...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now