• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Error inserting data into Access

Hello Experts,

    I have an ASP page that is being used to submit certain information into an Access table. However whenever I click submit nothing gets entered into Access. Only a blank row with none of the information written in the form.

Here is the code I am using:

<html>
<head>
<title>Your case has been submitted</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<%
Dim conn                  'Holds the Database Connection Object
Dim objRSAddCase      'RecordSet to add the Case
Dim strSQL                  ' SQL Query to Query the Database

'Create the ADO Connection Object
set conn=Server.CreateObject("ADODB.Connection")

'Set the active connection to the connection object using a DSN-less connection
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("IT.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
Set objRSAddCase = Server.CreateObject("ADODB.Recordset")
objRSAddCase.Open "SELECT * from tblRequests", conn,2,3

objRSAddCase.Addnew
objRSAddCase("EmployeeFirstName") = EmployeeID
objRSAddCase("DateEntered") = DateEntered
objRSAddCase("Problem") = Problem
objRSAddCase("SubCat") = SubCat
objRSAddCase("Priority") = cboPriority
objRSAddCase("Title") =Title
objRSAddCase("Description") = Description
objRSAddCase("Status") = Status

'Write the updated recordset to the database
objRSAddCase.Update
objRSAddCase.Requery
objRSAddCase.movelast
%>

<body bgcolor="#FFFFFF" text="#000000">

Your case has been submitted to tech support.
Return to the <a href="index.htm">main</a> screen.
</body>
</html>

Am I missing something?
0
sithman17
Asked:
sithman17
  • 29
  • 25
  • 3
  • +3
1 Solution
 
WMIFCommented:
you shouldnt need the requery or movelast on that page to do the update.  are you checking the database table to see if it got added or what?


often times when you are just doing an add, it is recomended that you dont pull the entire table initially like you have here:
objRSAddCase.Open "SELECT * from tblRequests", conn,2,3

instead you should put a where clause that will return 0 records.  you dont need records, just the table structure.  like this:
objRSAddCase.Open "SELECT * from tblRequests where 1= 2", conn,2,3


there is also another way to add records so you dont have to use a recordset.  create a sql statement using the values that you have.
query = "insert into tblRequests (col1,col2,etc) values (" & var1 & "," & var2 & ")"

then just execute the query:
connectionobject.execute query
0
 
sithman17Author Commented:
Yes I am checking the table and nothing is getting added to the rows. The rows are blank. There is data in there already that I have been working with directly until I get the ASP pages up and running.

I've tried it with values, however once I added more fields to the query and the form I started receiving all sorts of errors. The original query I was using was this:

strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Problem, SubCat, DateEntered, DateNeeded, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("title"),"'","''") &"', '"& Replace(Request.Form("description"),"'","''") &"', "& cint(Request.Form("ProblemID")) &","& cint(Request.Form("Priority")) &","& cint(Request.Form("SubCatID")) &")"

tblRequests uses three other tables as lookup tables; tblEmployees, tblProblem and tblSubCat. However It didnt like using the int values of the Access tables.
0
 
WMIFCommented:
what are the errors you are getting?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sithman17Author Commented:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver]
Number of query values and destination fields are not the same.
/it/CaseSubmitted.asp, line 26

Line 26 was objRSAddCase.Open strSql, conn and the strSQL statement was the one I gave you above.
0
 
John_LennonCommented:
before inserting the info in the table, try using response.write theValue to be sure that the variables has values

if you are using this
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Problem, SubCat, DateEntered, DateNeeded, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("title"),"'","''") &"', '"& Replace(Request.Form("description"),"'","''") &"', "& cint(Request.Form("ProblemID")) &","& cint(Request.Form("Priority")) &","& cint(Request.Form("SubCatID")) &")"

put this line after and see what it returns
response.write strSQL
response.end

and if you are using the first metod (addNew), then put something like this
objRSAddCase("EmployeeFirstName") = EmployeeID
response.write EmployeeID & "<br>
objRSAddCase("DateEntered") = DateEntered
response.write DateEntered & "<br>
objRSAddCase("Problem") = Problem
response.write Problem & "<br>
objRSAddCase("SubCat") = SubCat
response.write SubCat & "<br>
objRSAddCase("Priority") = cboPriority
response.write cboPriority & "<br>
objRSAddCase("Title") =Title
response.write Title & "<br>
objRSAddCase("Description") = Description
response.write Description & "<br>
objRSAddCase("Status") = Status
response.write Status & "<br>

are this showing you the values you need to insert?

this:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver]
Number of query values and destination fields are not the same.
/it/CaseSubmitted.asp, line 26

its because you have 7 fields and you are only passing 6 values to the query
1EmployeeFirstName
2Problem
3SubCat
4DateEntered
5DateNeeded
6Title
7Description

1cint(Request.Form("EmployeeID"))
2Replace(Request.Form("title"),"'","''")
3Replace(Request.Form("description"),"'","''")
4cint(Request.Form("ProblemID"))
5cint(Request.Form("Priority"))
6cint(Request.Form("SubCatID")) &")"
0
 
sithman17Author Commented:
I was passing DateNeeded as blank. So I removed that from the query.  Now my query looks like this:

strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Problem, SubCat, DateEntered, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("title"),"'","''") &"', '"& Replace(Request.Form("description"),"'","''") &"', "& cint(Request.Form("ProblemID")) &","& cint(Request.Form("Priority")) &","& cint(Request.Form("SubCatID")) &")"

And now I receive this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/it/CaseSubmitted.asp, line 25
0
 
sithman17Author Commented:
I've also removed DateEntered since the Access database handles that for me with a default value of NOW() in the appropriate field.  I also removed the cint from Priority because that is no longer looking at a lookup table in access. So the query actually looks like this:

strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("title"),"'","''") &"', '"& Replace(Request.Form("description"),"'","''") &"', "& cint(Request.Form("ProblemID")) &","& Replace(Request.Form("Priority"),"'","''") &","& cint(Request.Form("SubCatID")) &")"

And I am receiving a Syntax error message when I try and submit.
0
 
Carl TawnSystems and Integration DeveloperCommented:
It might be worth checking which columns you are writing to. It seems a little odd that you are inserting an int into a column named "EmployeeFirstName".
0
 
sithman17Author Commented:
EmployeeFirstName is a lookup field tied to the Primary Key of the employee table. thats why I am inserting an interger value into EmployeeFirstName
0
 
sithman17Author Commented:
Does it make a difference that I am writing the values in an ASP.NET page then trying to pass them to classic ASP?
0
 
WMIFCommented:
follow john's earlier suggestion and paste in the query that you have once its built.  there could be a problem with some of the data coming in.
0
 
sithman17Author Commented:
Here is what I get:

Error Type:
Response object, ASP 0185 (0x8002000E)
A default property was not found for the object.
/it/CaseSubmitted.asp

0
 
John_LennonCommented:
please post your code and tell us wich line is the error producing

you may want to double check your fields in the table and see if the value you are trying to insert are the same type
0
 
sithman17Author Commented:
Here is the ASP.NET page that populates certain values from the same database.

<%@ Page Language="VB" Debug="true"%>
<%@ Import Namespace="System.data" %>
<%@ Import Namespace="System.Data.oleDB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    Protected Sub cboEmployees_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
           
        'Set the text vlaues of the employee
        Dim con As OleDbConnection
        Dim Cmd As OleDbCommand
        Dim dr As OleDbDataReader

        con = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\it\it.mdb")
        con.Open()

        Cmd = New OleDbCommand("SELECT * from qryEmployees where EmployeeID = " & cboEmployees.SelectedValue & " ", con)
        dr = Cmd.ExecuteReader
        While dr.Read()
            txtEmployeeID.Text = dr("EmployeeID")
            txtEmpLastName.Text = dr("EmployeeLName")
            txtOffice.Text = dr("Office")
            txtExtension.Text = dr("Extension")

        End While

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
       
        Dim ThisMoment As Date
        ThisMoment = Now   ' Assign current system date and time.
        DateEntered.Text = ThisMoment
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>New Case Information</title>


</head>
<body>
    <form id="NewCase" runat="server">
    <div title="New Case Information">
        <br />
        <strong><span style="text-decoration: underline">Employee Information<br />
        </span></strong>&nbsp; &nbsp; &nbsp; &nbsp;
        <br />
        <table>
            <tr>
                <td style="width: 100px">
                    Employee</td>
                <td style="width: 100px">
                    <asp:DropDownList ID="cboEmployees" runat="server" DataSourceID="obdDS" DataTextField="EmployeeFName"
                        DataValueField="EmployeeID" OnSelectedIndexChanged="cboEmployees_SelectedIndexChanged" AutoPostBack="True" AppendDataBoundItems="True">
                        <asp:ListItem Selected="True">Select an Employee</asp:ListItem>
                    </asp:DropDownList><asp:AccessDataSource ID="obdDS" runat="server" DataFile="~/IT.mdb"
                        SelectCommand="SELECT [EmployeeID],[EmployeeFName] FROM [tblEmployees] ORDER BY [EmployeeFName]">
                    </asp:AccessDataSource>
                </td>
                <td style="width: 100px">
                    <asp:TextBox ID="txtEmpLastName" runat="server" ReadOnly="True" AutoPostBack="True"></asp:TextBox></td>
                <td style="width: 59px">
                    <asp:TextBox ID="txtEmployeeID" runat="server" Visible="False"></asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 100px">
                    Office</td>
                <td style="width: 100px">
                    <asp:TextBox ID="txtOffice" runat="server" ReadOnly="True"></asp:TextBox></td>
                <td style="width: 100px; text-align: right">
                    Ext</td>
                <td style="width: 59px">
                    <asp:TextBox ID="txtExtension" runat="server" Width="50px" ReadOnly="True"></asp:TextBox></td>
            </tr>
        </table>
   
    </div>
        <br />
        <strong><span style="text-decoration: underline">
            <br />
            Problem Section<br />
        </span></strong>
        <br />
        <table style="width: 679px; height: 76px">
            <tr>
                <td style="width: 375px">
                    Problem</td>
                <td style="width: 485px">
                    <asp:DropDownList ID="cboProblem" runat="server" DataSourceID="objDSProblem" DataTextField="Problem"
                        DataValueField="Problem" AutoPostBack="True">
                    </asp:DropDownList><asp:AccessDataSource ID="objDSProblem" runat="server" DataFile="~/IT.mdb"
                        SelectCommand="SELECT [Problem] FROM [tblProblem] ORDER BY [Problem]"></asp:AccessDataSource>
                </td>
                <td style="width: 449px">
                    Priority</td>
                <td style="width: 519px">
                    <asp:DropDownList ID="cboPriority" runat="server" DataSourceID="objDSPriority"
                        DataTextField="Priority" DataValueField="Priority">
                    </asp:DropDownList><asp:AccessDataSource ID="objDSPriority" runat="server" DataFile="~/IT.mdb"
                        SelectCommand="SELECT DISTINCT Priority FROM tblRequests ORDER BY Priority"></asp:AccessDataSource>
                </td>
            </tr>
            <tr>
                <td style="width: 375px">
                    Sub-Category</td>
                <td style="width: 485px">
                    <asp:DropDownList ID="cboSubCat" runat="server" DataSourceID="objSubCat" DataTextField="SubCat"
                        DataValueField="SubCat">
                    </asp:DropDownList><asp:AccessDataSource ID="objSubCat" runat="server" DataFile="~/IT.mdb"
                        SelectCommand="SELECT DISTINCT [SubCat] FROM [qryProblemTypes] WHERE ([Problem] = ?)">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="cboProblem" Name="Problem" PropertyName="SelectedValue"
                                Type="String" />
                        </SelectParameters>
                    </asp:AccessDataSource>
                    &nbsp;
                </td>
                <td style="width: 449px">
                    Date Entered</td>
                <td style="width: 519px">
                    <asp:TextBox ID="DateEntered" runat="server" ReadOnly="true"></asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 375px">
                    Date Encountered</td>
                <td style="width: 485px">
                    <asp:TextBox ID="DateEncountered" runat="server"></asp:TextBox></td>
                <td style="width: 449px">
                    Date Needed</td>
                <td style="width: 519px">
                    <asp:TextBox ID="DateNeeded" runat="server"></asp:TextBox></td>
            </tr>
        </table>
        <br />
        Title:
        <asp:TextBox ID="Title" runat="server" Width="641px"></asp:TextBox><br />
        <br />
        Description: (Please be as descriptive as possible)<br />
        <asp:TextBox ID="Description" runat="server" Height="109px" Width="673px"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" Text="Submit" PostBackUrl="~/CaseSubmitted.asp" />
        <input id="Reset1" type="reset" value="reset" />
    </form>
</body>
</html>


On click it is suppose to redirect to CaseSubmitted.asp which is this page

<html>
<head>
<title>Your case has been submitted</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<%
Dim conn                  'Holds the Database Connection Object
Dim objRSAddCase      'RecordSet to add the Case
Dim strSQL                  ' SQL Query to Query the Database

'Create the ADO Connection Object
set conn=Server.CreateObject("ADODB.Connection")

'Set the active connection to the connection object using a DSN-less connection
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("IT.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("title"),"'","''") &"', '"& Replace(Request.Form("description"),"'","''") &"', "& cint(Request.Form("Problem")) &","& Replace(Request.Form("cboPriority"),"'","''") &","& cint(Request.Form("SubCat")) &")"

Set objRSAddCase = Server.CreateObject("ADODB.Recordset")
'objRSAddCase.Open "SELECT * from tblRequests", conn,2,3
objRSAddCase.CursorType = 2
objRSAddCase.LockType = 3
objRSAddCase.Open strSql, conn

Response.Write strSQL
Response.end

objRSAddCase("EmployeeFirstName") = EmployeeID
response.write EmployeeID
objRSAddCase("DateEntered") = DateEntered
response.write DateEntered
objRSAddCase("Problem") = Problem
response.write Problem
objRSAddCase("SubCat") = SubCat
response.write SubCat
objRSAddCase("Priority") = cboPriority
response.write cboPriority
objRSAddCase("Title") =Title
response.write Title
objRSAddCase("Description") = Description
response.write Description
objRSAddCase("Status") = Status
response.write Status

'Write the updated recordset to the database
objRSAddCase.Update
conn.close

%>

<body bgcolor="#FFFFFF" text="#000000">

Your case has been submitted to tech support.
Return to the <a href="index.htm">main</a> screen.
</body>
</html>
0
 
John_LennonCommented:
in CaseSubmitted.asp i see some weird things, like:

this instruction
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("title"),"'","''") &"', '"& Replace(Request.Form("description"),"'","''") &"', "& cint(Request.Form("Problem")) &","& Replace(Request.Form("cboPriority"),"'","''") &","& cint(Request.Form("SubCat")) &")"

means that
EmployeeFirstName -> cint(Request.Form("EmployeeID"))
Priority -> Replace(Request.Form("title"),"'","''")
Problem -> Replace(Request.Form("description"),"'","''")
SubCat -> cint(Request.Form("Problem"))
Title -> Replace(Request.Form("cboPriority"),"'","''")
Description -> cint(Request.Form("SubCat"))

and i guess this is wrong
also, if you are going to use an INSERT STATEMENT, you don't need to do this:
objRSAddCase("EmployeeFirstName") = EmployeeID
response.write EmployeeID
objRSAddCase("DateEntered") = DateEntered
response.write DateEntered
objRSAddCase("Problem") = Problem
response.write Problem
objRSAddCase("SubCat") = SubCat
response.write SubCat
objRSAddCase("Priority") = cboPriority
response.write cboPriority
objRSAddCase("Title") =Title
response.write Title
objRSAddCase("Description") = Description
response.write Description
objRSAddCase("Status") = Status
response.write Status

'Write the updated recordset to the database
objRSAddCase.Update
0
 
sithman17Author Commented:
So it's an out of insert order process in the statement?
0
 
John_LennonCommented:
in the statement, the order you list the fields must be the same of the values you want to insert, so the insert statement must be like this:

INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("cboPriority"),"'","''") &"', '"& cint(Request.Form("Problem")) &"', "& cint(Request.Form("SubCat")) &","& Replace(Request.Form("title"),"'","''") &","& Replace(Request.Form("description"),"'","''") &")"
0
 
sithman17Author Commented:
Here is what its returning:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/it/CaseSubmitted.asp, line 27
0
 
John_LennonCommented:
wich line is 27?
because in the code you post line 27 is Response.end and i don't think there's a problem in there

also, in the insert statement i post, there are errors,

'"& cint(Request.Form("Problem")) &"', 'Is this field a number? if it is, then take out the quotes
"& cint(Request.Form("Problem")) &",
^----------------------------------------^ 'No quotes

"& Replace(Request.Form("title"),"'","''") &"
"& Replace(Request.Form("description"),"'","''") &")"
if this 2 fields are string, then you have to put quotes, like this

'"& Replace(Request.Form("title"),"'","''") &"'
^------------------------------------------------^ 'put quote '
'"& Replace(Request.Form("description"),"'","''") &"')"
^---------------------------------------------------------^ 'put quote '
0
 
GawaiCommented:
did u solve this issue or not ?
0
 
beenish_anwarCommented:
what i m understand that,

before writing the query first check that is which field have which datatype

means i m getting confuse that you are trying to

objRSAddCase("EmployeeFirstName") = EmployeeID

mean logically, EmployeeId contain the number or string which have no name where as the employeeFirstName would contain the string.

may you got the point
0
 
sithman17Author Commented:
Sorry I didnt reply sooner. No it didnt fix the problem. I am now receiving a

Error Type:
Request object, ASP 0102 (0x80004005)
The function expects a string as input.
/it/CaseSubmitted.asp, line 20

0
 
John_LennonCommented:
can you write here line 20?
0
 
sithman17Author Commented:
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID"))  &", '"& Replace(Request.Form("cboPriority"),"'","''") &"', '"& cint(Request.Form(Problem)) &"', "& cint(Request.Form(SubCat)) &",'"& Replace(Request.Form("title"),"'","''") &"','"& Replace(Request.Form("description"),"'","''") &"')"

I edited the INSERT as you suggested. So line 20 looks like this.
0
 
John_LennonCommented:
use this one

strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & cint(Request.Form("Problem")) & "', " & cint(Request.Form("SubCat")) & ",'" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"

you miss the quotes for Problem and SubCat
0
 
sithman17Author Commented:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'tblEmployees'.
/it/CaseSubmitted.asp, line 24

I dont understand this because in tblRequests the value for EmployeeFirstName is being inserted with EmployeeID which is the primary key to tblEmployees. They are linked properly in Access.
0
 
John_LennonCommented:
can you post the structure of your tables? at least tblRequests and tblEmployee?
0
 
sithman17Author Commented:
***********             ************
*tblEmployees              * tblRequests
***********             ************
EmployeeID - PK  <-       CaseID - PK
EmployeeFName    |--> EmployeeFirstName (in Access this is a lookup Field to tblEmployees)
EmployeeLName           Problem
Office                           SubCat
Extension                      Description
Email                           DateEntered
                                  Completed
                                  WorkPerformed
                                  AssignedTo
                                  Status
                                  Title
                                   DateCompleted
                                  DueDate
                                  Solution
                                 Priority

In Access if I look at the table directly it looks like this
*****************************************************************************************************************
* CaseID * EmployeeFName * Problem  * SubCat  * Description   *  DateEntered    * WorkPerformed * AssignedTo * Status * Title * DateCompleted * Due*
******************************************************************************************************************
   Auto#    *            23        * FTPSite    * Can't ac* Cleint is ...  *   4/20/06           *blah blah            *DefaultValue*Default* Can't *
******************************************************************************************************************

Problem and SubCat are also look up fields in tblRequests that have thier own seperate fields. Those appear to work as planned.
0
 
John_LennonCommented:
questions:
SubCat is integer or string?
cint(Request.Form("SubCat"))  <-- Integer
or
* SubCat  *
**********  
* Can't ac*
string?

if its string, you have to change the insert statement, putting
'" & replace(Request.Form("SubCat"), "'", "''") & "',
instead of this:
" & cint(Request.Form("SubCat")) & "

also, if there are a relation betwen tblEmployees and tblRequests maybe you have to have the value of tblRequest.EmployeeFirstName enter first in tblEmployee.EmployeeID
be sure that tha value of EmployeeFirstName you want to insert in tblRequest exist in tblEmployee
0
 
sithman17Author Commented:
I changee the SQL statement and I get this again:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/it/CaseSubmitted.asp, line 24

I know there is a relative value in tblEmployees because if I make the EmployeeID field visible on the ASP.NET page then it populates it with the right employeeID from tblEmployees once a name is selected from the dropdown.
Maybe there is an easier way of doing this in .NET. I am thinking the two flavors of ASP dont mix well with what I am trying to do.
0
 
sithman17Author Commented:
Would it be easier if I zipped all this up and sent it to you in some public FTP?
0
 
John_LennonCommented:
i guess that will be easy, do you have a FTP to put the files?

rigth after the insert statement, put this lines
response.write "Value: " & strSQL
response.end

copy what it display and paste it here
0
 
sithman17Author Commented:
Value: INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (0, ' 4 - Normal', '', ,'test', 'test')

It doesnt look like its pickin up the EmployeeID field.

I can put them on an FTP site. hang on.
0
 
sithman17Author Commented:
ftp://63.205.221.110

User ID is Experts
PW is Exp3rts!
0
 
John_LennonCommented:
there are a problem with Request.Form("SubCat"), because in the output you post there are not value for subCat

(0, ' 4 - Normal', '', ,'test', 'test')
---------------------^ Empty

make sure you are trying to get the right value for this part, also check where are you getting this value, maybe you named diferent

i can't connect to the FTP
0
 
John_LennonCommented:
sorry, now i can connect to FTP, can you zipped instead of rar?
0
 
John_LennonCommented:
another problem
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (0, ' 4 - Normal', '', ,'test', 'test')
this means you want to insert the value 0 to the EmployeeFirstName, but there are no Employee with ID 0, so you CAN NOT insert that, you only can insert values to EmployeeFirstName only if they are first store in tblEmployee

i know get the file, you can erase it from the FTP
0
 
John_LennonCommented:
more details,
NewCase.asp and newCase2.asp both call CaseSubmitted.asp?
other files redirect to caseSubmitted.asp?

in CaseSubmitted.asp
'#############
------> line 18
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("Problem"),"'","''") & "', " & Replace(Request.Form("SubCat"),"'","''") & ",'" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"

-------> " & Replace(Request.Form("SubCat"),"'","''") & " must be in quotes, like this
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("EmployeeID")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("Problem"),"'","''") & "', '" & Replace(Request.Form("SubCat"),"'","''") & "','" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"
'#############
also, you ask for
Request.Form("EmployeeID")
but in newCase.asp and newCase2.asp, the name you are using for the comboBox is
cboEmpFirstName
so i guess you should ask for
Request.Form("cboEmpFirstName")

this same error can be in another fields, like 'Priority'
in newCase.asp you call it Priority, but in CaseSubmitted.asp you ask for Request.Form("cboPriority")

'###############
you are getting the error when trying to insert values to acces, because you are inserting wrong data (like EmployeeFirstName = 0, but there are no Employee with ID = 0)
and you are trying to insert wrong data because you have one name in NewCase.asp and newCase2.asp and then in CaseSubmitted.asp you ask for diferent values, check that and your problem will be gone
0
 
sithman17Author Commented:
NewCase.asp and NewCase2.asp are both classic ASP files I had started with. Since then I switched to the.net form of the page and I am actually working with NewCase.aspx. Sorry, I should have cleaned up the project folder a little better before I sent it.
0
 
John_LennonCommented:
still the same, in NewCase.aspx you have
in line 56
<asp:DropDownList ID="cboEmployees" runat="server" DataSourceID="obdDS" DataTextField="EmployeeFName"

line 110
<asp:DropDownList ID="cboSubCat" runat="server" DataSourceID="objSubCat"

i guess those are the name of the DropDowns (cboEmployees and cboSubCat), and in CaseSubmitted.asp you ask for:
Request.Form("EmployeeID")
and Request.Form("SubCat")

shouldn't those be
Request.Form("cboEmployees")
and Request.Form("cboSubCat")
?
0
 
sithman17Author Commented:
Your right. I was associating it with the names of the values I wanted it inserted. Not the names of the fields they belong to. Let me test it real quick.
0
 
sithman17Author Commented:
Ok. I get the right values with the response write, but nothing is written into the table.

Value: INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 'Network', Connection Issues,'this is a test', 'This is a test from the ASP page')

I made sure I removed the ' from objRSAddCase.Open strSQL, conn
0
 
sithman17Author Commented:
I celened up the page and removed any unneccesary code so it looks like this:

<html>
<head>
<title>Your case has been submitted</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<%
Dim conn                  'Holds the Database Connection Object
Dim objRSAddCase      'RecordSet to add the Case
Dim strSQL                  ' SQL Query to Query the Database

'Create the ADO Connection Object
set conn=Server.CreateObject("ADODB.Connection")

'Set the active connection to the connection object using a DSN-less connection
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("IT.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("cboEmployees")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("cboProblem"),"'","''") & "', " & Replace(Request.Form("cboSubCat"), "'", "''") & ",'" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"
'response.write "Value: " & strSQL
'response.end

Set objRSAddCase = Server.CreateObject("ADODB.Recordset")
'objRSAddCase.Open "SELECT * from tblRequests", conn,2,3
objRSAddCase.CursorType = 2
objRSAddCase.LockType = 3
objRSAddCase.Open strSql, conn



'Write the updated recordset to the database
objRSAddCase.Update
objRSAddCase.Requery
objRSAddCase.movelast
%>

<body bgcolor="#FFFFFF" text="#000000">

Your case has been submitted to tech support.
Return to the <a href="index.htm">main</a> screen.
</body>
</html>

Now when I try to submit a record I receive this:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Connection Issues'.
/it/CaseSubmitted.asp, line 26

If I do response.write "value" & strSQL I receive the correct value that is suppose to be inserted into the database. So I dont know what the missing operator is?
0
 
John_LennonCommented:
try this code, i copy from your last post and adjust it
'####################

<html>
<head>
<title>Your case has been submitted</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<%
Dim conn               'Holds the Database Connection Object
Dim strSQL               ' SQL Query to Query the Database

'Create the ADO Connection Object
set conn=Server.CreateObject("ADODB.Connection")

'Set the active connection to the connection object using a DSN-less connection
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("IT.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("cboEmployees")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("cboProblem"),"'","''") & "', " & Replace(Request.Form("cboSubCat"), "'", "''") & ",'" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"
'response.write "Value: " & strSQL
'response.end

conn.execute(strSQL)
conn.close
set conn = nothing
%>

<body bgcolor="#FFFFFF" text="#000000">

Your case has been submitted to tech support.
Return to the <a href="index.htm">main</a> screen.
</body>
</html>
0
 
sithman17Author Commented:
Same thing:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Connection Issues'.
/it/CaseSubmitted.asp, line 21

0
 
John_LennonCommented:
put this line before conn.Open
conn.mode = 3

also, put this and see that the path of the DB is correct
response.write Server.MapPath("IT.mdb")
response.end
0
 
sithman17Author Commented:
Response Write returns:

c:\inetpub\wwwroot\it\IT.mdb

So that is the path.

When I try and post the record with conn.mode=3 before conn.open I receive the same error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Connection Issues'.
/it/CaseSubmitted.asp, line 25
0
 
John_LennonCommented:
i see your problem

"INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("cboEmployees")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("cboProblem"),"'","''") & "', " & Replace(Request.Form("cboSubCat"), "'", "''") & ",'" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"

this means
field                                  value                                                          type of Field on DB             type of Value
EmployeeFirstName           cint(Request.Form("cboEmployees"))               number                             number
Priority                             Replace(Request.Form("cboPriority"),"'","''")     text                                   text
Problem                           Replace(Request.Form("cboProblem"),"'","''")    number                             text
SubCat                             Replace(Request.Form("cboSubCat")               text                                   text
Title                                 Replace(Request.Form("title"), "'", "''")            text                                    text
Description                        Replace(Request.Form("description")              memo                                text

check that in 'problem', the table say its a numeric field and you are trying to insert a string value, thats why its showing you an error
0
 
sithman17Author Commented:
In the table SubCat is a Text Data tyoe with a row source in the lookup that reads:
SELECT tblSubCat.SubCatID, tblSubCat.SubCat FROM tblSubCat;
0
 
John_LennonCommented:
yes, but im talking about 'problem field'
field                                  value                                                          type of Field on DB             type of Value
Problem                           Replace(Request.Form("cboProblem"),"'","''")    number                             text

try putting this line before conn.execute(strSQL)
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, 4, 'Network', Connection Issues,'this is a test', 'This is a test from the ASP page')"

if work and this record is inserted into the table, that means you have to change Request.Form("cboProblem") so it contain a number instead of a string
0
 
John_LennonCommented:
you also have a wrong insert statement,
this wrong
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, 4, 'Network', Connection Issues,'this is a test', 'This is a test from the ASP page')

this good
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, 4, 'Network', 'Connection Issues','this is a test', 'This is a test from the ASP page')"
(quotes around Connection Issues)

change your code like this
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("cboEmployees")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("cboProblem"),"'","''") & "', '" & Replace(Request.Form("cboSubCat"), "'", "''") & "','" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"
0
 
John_LennonCommented:
sorry, i made a mistake, there is nothing wrong with 'Problem field', you can keep it like number and still insert string values, the problem are the quotes around Request.Form("cboSubCat"), so the last code i post must work
0
 
sithman17Author Commented:
It doesnt make sense. I am still getting Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/it/CaseSubmitted.asp, line 25

I used your last INSERT statement. Its still wont take it.
0
 
John_LennonCommented:
put this before conn.execute

strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES ("& cint(Request.Form("cboEmployees")) & ", '"& Replace(Request.Form("cboPriority"),"'","''") & "', '" & Replace(Request.Form("cboProblem"),"'","''") & "', '" & Replace(Request.Form("cboSubCat"), "'", "''") & "','" & Replace(Request.Form("title"), "'", "''") & "', '" & Replace(Request.Form("description"), "'", "''") & "')"
response.write "<br>1: " & strSQL
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 'Network', 'Connection Issues','this is a test', 'This is a test from the ASP page')"
response.write "<br>2: " & strSQL

then post result
0
 
sithman17Author Commented:
I have to comment out  conn.execute to get the value to display, but here it is:

1: INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 'Adobe', 'PDF''s are incomplete','test', 'Test from an asp page')
2: INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 'Network', 'Connection Issues','this is a test', 'This is a test from the ASP page') Your case has been submitted to tech support. Return to the main screen.
0
 
John_LennonCommented:
i do a test with the files you give me, it's seems there are some troubles inserting data in tblRequest, specialy when you try to insert string data (problem and subCat) in the table

i try inserting this
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 1, 9,'this is a test', 'This is a test from the ASP page')
(note that i use number instead of text in problem and subcat)

and it worked fine, try it on your page, put this before conn.execute
strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 1, 9,'this is a test', 'This is a test from the ASP page')"
and uncomment conn.execute(strSQL)
i belive this time it will work

if it work, you will have to change
Request.Form("cboProblem")  and  Request.Form("cboSubCat")
so it contains number values instead of text
0
 
sithman17Author Commented:
I changed them in the database prior since I was thinking along the same lines as you were prior to your post. I can get the Problem field to populate, but the sub-cat field doesnt populate at all. did it populate with the files I gave you?
0
 
John_LennonCommented:
in access, with the files you gave me, i try inserting this
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 1, 9,'this is a test', 'This is a test from the ASP page')

and this
INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 'Network', 'Connection Issues','this is a test', 'This is a test from the ASP page')

with the first one, it worked fine, the values get inserted
with the second one, there is an error, it say's that 1 field will be null because of a type conversion failure, and when i open the table, the field problem dosn't get a value and the field subcat say it has a invalid type of value,

so, with this, i say you can only enter NUMBERS in the fields problem and subcat, thats why i believe this code should work in ASP

strSQL = "INSERT INTO tblRequests (EmployeeFirstName, Priority, Problem, SubCat, Title, Description) VALUES (25, ' 4 - Normal', 1, 9,'this is a test', 'This is a test from the ASP page')"
conn.execute(strSQL)
0
 
sithman17Author Commented:
Thanks. I actually changed the values in tblRequests from Lookups to text and the values get inserted just fine. I kept the relationships only between tblProblem and tblSubCat so my dependant dropdowns can populate.

Thanks for your help and extreme patience.
0
 
John_LennonCommented:
no problem, glad to help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 29
  • 25
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now