ASP to add to Database

gilgalbiblewheel · August 29th, 2004, 06:46 PM

Is this right?

Code:

<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<TITLE>amos12.asp</TITLE>
</head>
<%
Const DB_NAME = "add.mdb"

Function GetConnectionString()
    GetConnectionString =   "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                "UID=;PWD=;"
End Function

call Function AddInstructor (cnn, _
                                  TeacherID, _
                                  FirstName, _
                                  LastName)
'***********************************************************************
'Adds a record to the table, tblTeachers
'***********************************************************************
    Dim cmd    'A command object
    Dim SQL    'A variable for the SQL statement

    SQL = "INSERT INTO tblTeachers"

    SQL = SQL & "(TeacherID, FirstName, LastName, HireDate)"
    SQL = SQL & " VALUE ('"
    SQL = SQL & TeacherID & "', '"
    SQL = SQL & FirstName & "', '"
    SQL = SQL & LastName & "', #"
    SQL = SQL & Cstr (Date()& "#)" )

        'Create a recordset object
Set cmd = CreateObject ("ADODB.Command")

cmd.CommandText = SQL
cmd.ActiveConnecton = cnn
cmd.Execute

    'Report that the write was successful
    AddInstructor = True
    Exit Function
    On Error Resume Next
    AddInstructor = False
End Function

Function GetTeachers (cnn)
'***********************************************************************
'Returns an HTML string that shows all the teachers
'***********************************************************************

    Dim rs    'A variable for a Recordset object
    Dim SQL   'A variable for the SQL statement
    Dim str   'A variable for a string buffer

    SQL = "SELECT * FROM tblTeachers"

        'Create a recordset object
      Set rs = CreateObject ("ADODB.Recordset")

      'Set the active connection
      rs.ActiveConnection = cnn

      'Open the recordset using the SQL
      'statement
      rs.Open SQL
      str = "<TABLE Width=100% >"
      'Traverse the recordset
      While Not rs.EOF
            'Create a new <TABLE> row
            str = str & "<TR>"
            'Add the Course Name <TABLE> cell
            str = str & "<TD>"
            str = str & rs ("TeacherID") & "</TD>"
            'Add the Last First Name <TABLE> cell
            str = str & "<TD>"
            str = str & rs ("LastName") & "</TD>"
            'Add the Last Name <TABLE> cell
            str = str & "<TD>"
            str = str & rs ("FirstName") & "</TD>"
            'Close off the <TABLE> row
            str = str & "</TR>"
            'Move to the next racord
            rs.MoveNext
      Wend
      str = str & "</TABLE>"
      GetTeachers = str
      End Function

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'SCRIPT ENTRY POINT
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    'Page level variables
    Dim cnn     'A variables that references the connection object
    Dim str     'Avariable that references the connection string
    'Variables to store form data
    Dim TeacherID
    Dim FirstName
    Dim LastName

    'Get the data from the form an assign it to the page level variables
    TeacherID = Request.Form("txtTeacherID")
    FirstName = Request.Form("txtFirstName")
    LastName = Request.Form("txtLastName")

    'Create the Connection object
    Set cnn = CreateObject("ADODB.Connection")

    ' Abbreviate the connection string using the default OLE DB provider
    str = "Data Source=AriMiddle"

    'Open a connection using the connection string as a parameter
    'To the Open method.

cnn.Open str
    'Call the AddInstructor() funtion to add a
    'new teacher to the database
    If AddInstructor(cnn, TeacherID, FirstName, LastName) Then
    ' Return the new list, with the new teacher
    ' Display a list header
    Response.Write ""
    Response.Write "Addition successfull!!!" & "<P>"
    Response.Write ""
    Response.Write ""
    Response.Write "Aristotle Middel School<BR>Teacher Roster"
    Response.Write "<P>"
    Response.Write GetTeachers(cnn)

    else
    Response.Write ""
    Response.Write "Addition Failed!!!" & "<P>"
    Response.Write ""
    End if
    'Close the connection
    cnn.Close


%>
</html>

sinapra · August 30th, 2004, 04:37 AM

whats the error!!

sinapra

gilgalbiblewheel · August 30th, 2004, 12:27 PM

Well the original started this way:

Code:

<SCRIPT LANGUAGE=vbscript RUNAT=Server>

I added the constant DB_NAME, and

Code:

Function GetConnectionString()
    GetConnectionString =   "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                "UID=;PWD=;"
End Function

But the error pointed at

Code:

call Function AddInstructor (cnn, _
                                  TeacherID, _
                                  FirstName, _
                                  LastName)

The error was this

Quote:

quote:Microsoft VBScript compilation error '800a03ea'

Quote:

Syntax error

/amos12.asp, line 15

call Function AddInstructor (cnn, _
-----^

rajanikrishna · August 30th, 2004, 08:38 PM

Is it
call Function addInstructor(..)

or

function addInstructor(...)
????

gilgalbiblewheel · August 31st, 2004, 04:17 PM

I think I have start fresh. That part was an addition to a VBscript.

I don't know what fits to my interest. THis is what I'm looking for if you can direct me.

I have an asp response page of results of the query. I want to select the ones I like and save (or add) to another (new) database. I think I will need checkboxes next to each row (record), perhaps an array with the value being the ID field (right?). I will need a submit button on top of the column and a checkbox selecting all checkboxes.

But on the next page is more of a problem to me because I don't know what will be necessary. I'm still looking around to what's to my need, but maybe I'm not looking the right places, or don't know what keywords to search in Googles.

If I wasn't clear on the last response page, I'm looking for a page that displays what I've submitted in the new database.

happygv · September 3rd, 2004, 02:37 PM

Looks like it is where you define the function. So you got to remove the keyword CALL from there.

Code:

<s>call</s> Function AddInstructor (cnn, _
                                  TeacherID, _
                                  FirstName, _
                                  LastName)

Otherwise your code seems okay.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · September 3rd, 2004, 06:28 PM

Ok thanks.
I have two tables. I want to copy the searches of one table to another. Let's say I search a keyword I want the search to go through the first table and copy the records where the keyword is to be found to the second table. I find that would be easy to study. Whatever I don't want from that search I can easily delete it. And at the same time I have the original table not tampered.

Is there any tutorial on this on the web?

happygv · September 3rd, 2004, 06:43 PM

Like the INSERT INTO, I replied to your other post, you can search for the keyword and based on that criteria, run the INSERT INTO to populate the destination table and use that resulting table for lookup on your web page. I don't remember seeing any tutorial available on this.

_________________________
- Vijay G
Strive for Perfection