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:
[code=asp]
<%
' 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(strin g)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
name = ChkString(Reque st.Form("name") )
age = ChkString(Reque st.Form("age"))
dateDB = ChkString(Reque st.Form("dateDB "))
QuestionOne = ChkString(Reque st.Form("Questi onOne"))


data_source = "Provider=Micro soft.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.CreateOb ject("ADODB.Con nection")

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."
%>[/code]

[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~

[code=asp]
<%
' 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(strin g)
If string = "" Then
string = " "
End If
ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
name = ChkString(Reque st.Form("name") )
age = ChkString(Reque st.Form("age"))
dateDB = ChkString(Reque st.Form("dateDB "))
QuestionOne = ChkString(Reque st.Form("Questi onOne"))

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" & Server.MapPath( "form.mdb")

SQL = "insert into users (name, age, dateDB) values ('" & name & "', '" & age & "', '" & dateDB & "')"
Conn.Execute(SQ L)
SQL = "insert into Diagnosis (QuestionOne) values ('" & _ QuestionOne & "')"
Conn.Execute(SQ L)

Conn.Close
Set Conn = Nothing

Response.Write "All records were successfully entered into the database."
%>
[/code]

[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):

[code=asp]<%
' 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(strin g)
If string = "" Then
string = " "
End If
ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
name = ChkString(Reque st.Form("name") )
age = ChkString(Reque st.Form("age"))
dateDB = ChkString(Reque st.Form("dateDB "))
QuestionOne = ChkString(Reque st.Form("Questi onOne"))

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "DRIVER={Micros oft 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(SQ L)


Conn.Close
Set Conn = Nothing

Response.Write "All records were successfully entered into the database."
%>[/code]

[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:[code=asp] SQL = "insert into users (name, age, dateDB) values ('" & name
sql = sql & "', '" & age & "', '" & dateDB & "')"
Conn.Execute(SQ L)

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(SQ L)[/code]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.