insert into syntax

[dbertanjoli]

I have problems writing data from my webform to two linked tables. I am quiet sure that my insert into syntax is not correct. Please take a look if you have a moment, if not I understand:

<%
   ' Declaring variables
   Dim name, age, dateDB, QuestionOne, data_source, con, conOne, sql_insert, sql_insertOne
 
   ' A Function to check if some field entered by user is empty
   Function ChkString(string)
   If string = "" Then string = " "
   ChkString = Replace(string, "'", "''")
   End Function
 
   ' Receiving values from Form
   name = ChkString(Request.Form("name"))
   age = ChkString(Request.Form("age"))
   dateDB = ChkString(Request.Form("dateDB"))
   QuestionOne = ChkString(Request.Form("QuestionOne"))
 
 
   data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ 
            Server.MapPath("form.mdb")
 
   sql_insert = "insert into users (name, age, dateDB) values ('" & _
            name & "', '" & age & "', '" & dateDB & "'); insert into Diagnosis (QuestionOne) values ('" & _ QuestionOne & "')"
 
 
   ' Creating Connection Object and opening the database
   Set con = Server.CreateObject("ADODB.Connection")
 
   con.Open data_source
   con.Execute sql_insert
   ' Done. Close the connection
   con.Close
   Set con = Nothing
 
 
   Response.Write "All records were successfully entered into the database."
 %>

[DrBunchman]

Hi there,

Are you recieving an error when you run your code?

Dr B

[CroCrew]

Hello dbertanjoli,

Give this a try. And like DrBunchman had asked please post any errors that you get when replying or posting new questions along with your code. It helps us in helping you.

Hope it helps~

<%
    ' Declaring variables
    Dim name, age, dateDB, QuestionOne, data_source, con, conOne, sql_insert, sql_insertOne
 
    ' A Function to check if some field entered by user is empty
    Function ChkString(string)
        If string = "" Then 
            string = " "
        End If
        ChkString = Replace(string, "'", "''")
    End Function
 
    ' Receiving values from Form
    name = ChkString(Request.Form("name"))
    age = ChkString(Request.Form("age"))
    dateDB = ChkString(Request.Form("dateDB"))
    QuestionOne = ChkString(Request.Form("QuestionOne"))
 
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("form.mdb") 
 
            SQL = "insert into users (name, age, dateDB) values ('" & name & "', '" & age & "', '" & dateDB & "')"
        Conn.Execute(SQL)
            SQL = "insert into Diagnosis (QuestionOne) values ('" & _ QuestionOne & "')"
        Conn.Execute(SQL)
 
    Conn.Close
    Set Conn = Nothing
 
    Response.Write "All records were successfully entered into the database."
%>
 

[dbertanjoli]

Hello,
Thank you very much for your reply. I pasted the code above and this is a message I am getting: (two tables are linked by idName)

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'users'.

/2000040101/form_ac.asp, line 34

I would greatly appreciate your reply.

Debbie

[CroCrew]

It sounds like your tables have a relationship. Can you find out what needs to be passed in to the “Diagnosis” table from the “Users” table. Then we can edit the code to insert that information.

[dbertanjoli]

Hi CroCrew,

table users:
idName
name
age
dateDB

table:
Diagnosis:
idName
QuestionOne

They are linke by idName.

Thank you very much for your help.

Debbie

[jhardman]

Hi CroCrew,

table users:
idName
name
age
dateDB

table:
Diagnosis:
idName
QuestionOne

They are linke by idName.

Thank you very much for your help.

Debbie

I noticed that the idName field is not added by your sql insert statement. Is this because it is an auto-generated field? If so, I have scratched my head over this problem a couple times already. The only solution I have found is to add the first record, then search for it to pull up the auto-generated field, then use it when inserting the second record. However, I was recently brushing up my SQL and I saw this possibility:

SQL = "insert into users (name, age, dateDB) values ('" & name & "', '"
sql = sql & age & "', '" & dateDB & "');"
sql = sql & "insert into Diagnosis (QuestionOne, idName) values ('" 
sql = sql & QuestionOne & "', (SELECT idName FROM users WHERE name = '"
sql = sql & name & "' AND age = '"& age & "' AND dateDB = '"& dateDB & "'))"

I haven't tried it, but it should work. Let me know. Anyone else see a solution?

Jared

[dbertanjoli]

Hello Jared,
This is updated code and I am still receiving same error message (idName is autonumber):

<%
    ' Declaring variables
    Dim name, age, dateDB, QuestionOne, data_source, con, conOne, sql_insert, sql_insertOne
 
    ' A Function to check if some field entered by user is empty
    Function ChkString(string)
        If string = "" Then 
            string = " "
        End If
        ChkString = Replace(string, "'", "''")
    End Function
 
    ' Receiving values from Form
    name = ChkString(Request.Form("name"))
    age = ChkString(Request.Form("age"))
    dateDB = ChkString(Request.Form("dateDB"))
    QuestionOne = ChkString(Request.Form("QuestionOne"))
 
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("form.mdb") 
 
 SQL = "insert into users (name, age, dateDB) values ('" & name & "', '" & age & "', '" & dateDB & "');"
sql = sql & "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne & "', (SELECT idName FROM users WHERE name = '" & name & "' AND age = '"& age & "' AND dateDB = '"& dateDB & "'))"
 
 Conn.Execute(SQL)
 
 
 Conn.Close
 Set Conn = Nothing
 
    Response.Write "All records were successfully entered into the database."
%>

[jhardman]

I'm not ready to give up this approach yet! Lets split up the two commands as CroCrew suggested, then confirm that the error is coming from the SECOND execute line and not the first:

 SQL = "insert into users (name, age, dateDB) values ('" & name 
sql = sql & "', '" & age & "', '" & dateDB & "')"
 Conn.Execute(SQL)
 
sql = "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne
sql = sql & "', (SELECT idName FROM users WHERE name = '" & name 
sql = sql & "' AND age = '"& age & "' AND dateDB = '"& dateDB & "'))"
 Conn.Execute(SQL)

Is the error coming from the second execute line?

Jared

[dbertanjoli]

Hi Jared,
I already tried this... same message I received :-)

Error is coming from line 34:

SQL =  "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne & "', (SELECT idName FROM users WHERE name = '" & name & "' AND age = '"& age & "' AND dateDB = '"& dateDB & "'))"

Deb

[markrawlingson]

I'm not sure why you're recieving this error message, congratulations you've got me stumped. :P

But it's definitely because of a relationship between the two tables, so why don't you open a recordset to the two tables with your sql statement containing a select statement with an SQL join. Then update the information using rs("column_name") statements? There shouldn't be a relationship issue at that point if you use a proper join type.

If you still get the same issue - send me the database (it's ms access right???) and I'll run some SQL passed it until I find a solution for you :P

Sincerely,
Mark

Hi Jared,
I already tried this... same message I received :-)

Error is coming from line 34:
SQL = "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne & "', (SELECT idName FROM users WHERE name = '" & name & "' AND age = '"& age & "' AND dateDB = '"& dateDB & "'))"

Deb

[dbertanjoli]

Hello Experts
I followed Mark's advise and I got this message: I am attaching my access database (I stripped all the unnecessary parts from it).
__________________________________________________ _
Microsoft VBScript compilation error '800a03fd'

Expected 'Case'

/2000040101/form_ac.asp, line 33

SELECT users.form, Diagnosis.form, Type FROM users JOIN Diagnosis ON users.idName = Diagnosis.idName
-------^
__________________________________________________________________
<%
    ' Declaring variables
    Dim name, age, dateDB, QuestionOne, data_source, con, sql_insert, rs
 
    ' A Function to check if some field entered by user is empty
    Function ChkString(string)
        If string = "" Then 
            string = " "
        End If
        ChkString = Replace(string, "'", "''")
    End Function
 
    ' Receiving values from Form
    name = ChkString(Request.Form("name"))
    age = ChkString(Request.Form("age"))
    dateDB = ChkString(Request.Form("dateDB"))
    QuestionOne = ChkString(Request.Form("QuestionOne"))
 
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("form.mdb") 
 
 
SELECT users.form, Diagnosis.form, Type FROM users JOIN Diagnosis ON users.idName = Diagnosis.idName 
 
 
 
rs.Open SQL = "insert into users (name, age, dateDB) values ('" & name & "', '" & age & "', '" & dateDB & "')"  
 Conn.Execute(SQL)
 
rs.Open SQL =  "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne & "')"  
 
Conn.Execute(SQL)
 
 
 Conn.Close
 Set Conn = Nothing
 
    Response.Write "All records were successfully entered into the database."
%>

Many thanks for any help.
Debbie

[NeoPa]

Silly question, but would [Age] & [Date] both be string variables (as per your SQL)?

PS. Debbie (and any others it pertains to), please remember to use the [ CODE ] tags (# button) when including code (As per comment on original post).

Good luck with your problem and Welcome to TheScripts.

[dbertanjoli]

Hello,

Age is a string, dateDB is Date/Time in my database.

I just changed my script, AGAIN, (changed field names) to avoid this message I am gettting (but didn't help):
------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error.

/2000040101/form_ac.asp, line 37
------------------------------------------------------------------------------

<%
    ' Declaring variables
    Dim FullName, age, dateDB, QuestionOne, data_source, con, conOne, sql_insert
 
    ' A Function to check if some field entered by user is empty
    Function ChkString(string)
        If string = "" Then 
            string = " "
        End If
        ChkString = Replace(string, "'", "''")
    End Function
 
    ' Receiving values from Form
    name = ChkString(Request.Form("FullName"))
    age = ChkString(Request.Form("age"))
    dateDB = ChkString(Request.Form("dateDB"))
    QuestionOne = ChkString(Request.Form("QuestionOne"))
 
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("form.mdb") 
 
sql_insert = "insert into users (FullName, age, dateDB) values ('" & FullName & "', '" & age & "', '" & dateDB & "')"
 Conn.Execute(sql_insert)
 
 
sql_insert = "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne & "', (SELECT idName FROM users WHERE FullName = '" & FullName & "' AND age = '"& age & "' AND dateDB = '"& dateDB & "'))"
 
 Conn.Execute(sql_insert)
 
 
 Conn.Close
 Set Conn = Nothing
 
    Response.Write "All records were successfully entered into the database."
%>

Please help,
Debbie

[NeoPa]

I swear you must have edited your post after I looked at it last time :S
If [dateDB] is a Date/Time field then your line 26 is wrong. The value needs to be formatted as a date and surrounded by "#" separators. Look in Literal DateTimes and Their Delimiters (#) for a full rundown on this point.
Good luck.

[dbertanjoli]

Hello,
Just for the sake of testing I changed it to string and still the same error message. I don'w know what else to try.
deb

[NeoPa]

Do you mean you redesigned the table so that the field dateDB was stored as a string?
That wasn't my suggestion, but as long as you don't expect the query to work, it should test the compilation at least.

[CroCrew]

Hello dbertanjoli,


Sorry, back now. I look at your database and everything looks good. You do have a relationship between the two tables. Try this working example. It should work for you with the database that you linked above for us to look at. The example is named Example.asp

Hope this helps~

Example.asp

<%
    Function ChkString(string)
        If string = "" Then 
            string = " "
        End If
        ChkString = Replace(string, "'", "''")
    End Function
 
    If (Request.Form("xSubmit")) Then
        LocalVarName = ChkString(Request.Form("xname"))
        LocalVarAge = ChkString(Request.Form("xage"))
        LocalVarDateDB = ChkString(Request.Form("xdateDB"))
        LocalVarQuestionOne = ChkString(Request.Form("xQuestionOne"))
 
        Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("form.mdb") 
 
        ' Add record into Users table
            Set rsUsers = CreateObject("ADODB.Recordset")
            rsUsers.CursorLocation = 3
            rsUsers.Open "SELECT TOP 1 * FROM users", Conn, 3, 3
            rsUsers.AddNew
                rsUsers("name") = LocalVarName 
                rsUsers("age") = LocalVarAge
                rsUsers("dateDB") = LocalVarDateDB
            rsUsers.Update
            NewID = rsUsers("idName")
            rsUsers.close
            set rsUsers = nothing
 
        ' Add record into Diagnosis table
            SQL = "insert into Diagnosis (idName, QuestionOne) values (" & NewID & ", '" & LocalVarQuestionOne & "')"
            Conn.Execute(SQL)
 
        Conn.Close
        Set Conn = Nothing
 
        Response.Write "All records were successfully entered into the database."
    End If
%>
 
<html>
    <head>
        <title>Example</title>
    </head>
    <body>
        <form method="post" action="Example.asp" name="xform" id="xform">
            <input type="hidden" name="xSubmit" value="true">
            Name: <input type="text" name="xname"><br>
            Age: <input type="text" name="xage"><br>
            DB Date: <input type="text" name="xdateDB" value="<%=Now()%>"><br>
            Question One: <input type="text" name="xQuestionOne"><br>
            <input type="submit" value="Submit">
        </form>
    </body>
</html>
 

[markrawlingson]

Sorry, been really busy!! Haven't been able to get around to this yet.

In your code, as quoted below, you haven't passed your SQL anywhere. In fact, it's not even contained within a variable or in quotes! It will most definitely throw an error.

The code provided by CroCrew should do the trick - this is what I meant in my post - sorry guess I should have been more specific :P

Sincerely,
Mark

Hello Experts
I followed Mark's advise and I got this message: I am attaching my access database (I stripped all the unnecessary parts from it).
__________________________________________________ _
Microsoft VBScript compilation error '800a03fd'

Expected 'Case'

/2000040101/form_ac.asp, line 33

SELECT users.form, Diagnosis.form, Type FROM users JOIN Diagnosis ON users.idName = Diagnosis.idName
-------^
__________________________________________________________________
<%
' Declaring variables
Dim name, age, dateDB, QuestionOne, data_source, con, sql_insert, rs
 
' A Function to check if some field entered by user is empty
Function ChkString(string)
If string = "" Then 
string = " "
End If
ChkString = Replace(string, "'", "''")
End Function
 
' Receiving values from Form
name = ChkString(Request.Form("name"))
age = ChkString(Request.Form("age"))
dateDB = ChkString(Request.Form("dateDB"))
QuestionOne = ChkString(Request.Form("QuestionOne"))
 
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("form.mdb") 
 
 
SELECT users.form, Diagnosis.form, Type FROM users JOIN Diagnosis ON users.idName = Diagnosis.idName 
 
 
 
rs.Open SQL = "insert into users (name, age, dateDB) values ('" & name & "', '" & age & "', '" & dateDB & "')" 
Conn.Execute(SQL)
 
rs.Open SQL = "insert into Diagnosis (QuestionOne, idName) values ('" & QuestionOne & "')" 
 
Conn.Execute(SQL)
 
 
Conn.Close
Set Conn = Nothing
 
Response.Write "All records were successfully entered into the database."
%>

Many thanks for any help.
Debbie