Insert problem

CW · September 4th, 2003, 04:50 AM

I have tried to insert some records in to the database, but there is an error.
here is the code i am using.

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE>AddNew Student Form</TITLE>
</HEAD>
<BODY>
<h1>Chapter 9 Recordset Methods</h1>
<h2>AddNew Sailor Form</h2>
<p>Enter the name for the new student to add:</p>
<p>
<FORM ACTION="AddnewstudentwithSQL.asp" METHOD="post">
FirstName: <INPUT NAME="StudentNameFirst" size=30><br>
LastName: <INPUT NAME="studentNameLast" ><br>
<INPUT TYPE="submit" value="Submit"

</FORM>

</BODY>
</HTML>

This is the file that is being called : AddnewstudentwithSQL.asp

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE>AddNew Student Form</TITLE>
</HEAD>
<BODY>

<%
  Dim strInsert
  Dim strValues
  Dim adCmdText
  'Dim blnCriticalError

  'Set required variables
   adCmdText = 1

  If Request.Form("Action") = "Add" Then
    strInsert = "Insert into Student(StudentNameLast,StudentNameFsrt"
    srtValues = "Values ('" & CStr(Request.Form("txtLastName")) & _
    "','" & CStr(Request.Form("txtFisrtName")) & "'"

  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.Open "DNS=Students"

'Command object
  Set objCmd = Server.CreateObject("ADODB.Command")

'Object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert & ") " & strValues & ") "
objCmd.CommandType = adCmdText
  'Execute the command
objCmd.Execute
  'Display he insert string
'Response.write = "The following operations have been done" & _
' "The insrted values were .<p>"
'Response.Write strInsert & ") " & strValues & ") "

End If

'Close and dereference database objects

Set objCmd = Nothing
objConn.Close
Set objConn = Nothing

%>

</BODY>
</HTML>
This is the error that is coming :

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Click the Refresh button, or try again later.

Open the my home page, and then look for links to the information you want.
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'objConn'
/AddnewstudentwithSQL.asp, line 44

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)

Page:
POST 43 bytes to /AddnewstudentwithSQL.asp

POST Data:
StudentNameFirst=asd&studentNameLast=asdsad

Time:
Thursday, September 04, 2003, 9:26:12 AM

More information:
Microsoft Support

can any one solve this problem.

thanks in advance

jacob · September 4th, 2003, 05:14 AM

Hi...

I see several errors or mistypings. First of all when you do this...

Code:

If Request.Form("Action") = "Add" Then

...what do you get. I cannot see what you should get from this!? No input control is called 'Action' (Perhaps something I do not know).

Then in the form you say 'StudentNameFirst' and in the Request.Form method you are trying to fetch txtFirstName. The same for last name.

The identifier 'StudentNameFsrt' could contain a type error (not sure since this depends on the database).

In order to get a better overview you should structure you statement better when constructing it. I would do something like this...

Code:

sql = "'" & CStr(Request.Form("txtLastName"))  & "'," & _
      "'" & CStr(Request.Form("txtFisrtName")) & "'"
sql = "Insert into (StudentNameLast, StudentNameFsrt) Values(" & sql & ")

Finally you have 'studentNameLast' with a small 's'. This probably does not conflict, however one should try to get it right.

Hope this helps ;)

Jacob.

pgtips · September 4th, 2003, 05:23 AM

Also, on this line
objConn.Open "DNS=Students"
it should be DSN not DNS.

Imar · September 4th, 2003, 06:27 AM

I think this is also the cause of the error. You create and open the connection in the If block, but try to close it again after the End If.

If the code in the If block does not execute, there is no objConn object, so you'll get an error when you try to close it (objConn.Close).

Cheers,

Imar

Quote:

quote:

Code:

If Request.Form("Action") = "Add" Then

...what do you get. I cannot see what you should get from this!? No input control is called 'Action' (Perhaps something I do not know).

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

CW · September 4th, 2003, 06:31 AM

Thanks guys, i will try your suggestions , and i will let you know.

thanks again.
rgds

CW

cOnflictXX · September 4th, 2003, 09:52 AM

  If Request.Form("Action") = "Add" Then
    strInsert = "Insert into Student(StudentNameLast,StudentNameFsrt"
    srtValues = "Values ('" & CStr(Request.Form("txtLastName")) & _
    "','" & CStr(Request.Form("txtFisrtName")) & "'"

You for got 2 )'s. You need to close the ('s.
Should be:

  If Request.Form("Action") = "Add" Then
    strInsert = "Insert into Student(StudentNameLast,StudentNameFsrt"
    srtValues = ") Values ('" & CStr(Request.Form("txtLastName")) & _
    "','" & CStr(Request.Form("txtFisrtName")) & "')"

Good luck.

[=cOnflictXX=]

jacob · September 4th, 2003, 10:13 AM

He is doing that later on!

Jacob.

CW · September 5th, 2003, 03:06 AM

Hi , guys again.

I have tried your suggestions, and i don't get the same error.

Here is the modified file.

<%
  Dim strInsert
  Dim strValues
  Dim adCmdText

  'Set required variables

   adCmdText = 1

    strInsert = "Insert into Student(StudentNameFirst,StudentNameLast"
    strValues = "Values ('" & CStr(Request.Form("txtStudentFirstName")) & _
                "','" & CStr(Request.Form("txtStudentLastName")) & "'"

  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.Open "DSN=Students"

'Command object
  Set objCmd = Server.CreateObject("ADODB.Command")

'Object properties
Set objCmd.ActiveConnection = objConn

objCmd.CommandText = strInsert & ") " & strValues & ") "
objCmd.CommandType = adCmdText
  'Execute the command
objCmd.Execute

'Close and dereference database objects

Set objCmd = Nothing
objConn.Close
Set objConn = Nothing

%>

</BODY>
</HTML>

I am getting this error.

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Click the Refresh button, or try again later.

Open the asad home page, and then look for links to the information you want.
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)

Page:
POST 45 bytes to /AddnewstudentwithSQL.asp

POST Data:
StudentNameFirst=fyf&studentNameLast=lhlhlhlk

Time:
Friday, September 05, 2003, 6:21:45 AM

More information:
Microsoft Support

pgtips · September 5th, 2003, 04:31 AM

For this error "Operation must use an updateable query" take a look at http://www.aspfaq.com/show.asp?id=2062

jacob · September 5th, 2003, 12:52 PM

Well, as far as I can see you still got two input controls 'StudentNameFirst' and 'studentNameLast' (last name not capitalized), and you are requesting something like 'txtFirstName' and 'txtLastName', which does not correspond.

You can identify this problem by looking at the error message, namely...

Code:

POST Data:
StudentNameFirst=fyf&studentNameLast=lhlhlhlk

The data you are trying to retrieve is the data above!

Hope it can help you ;)

Jacob.