Capture only text boxes with data to be inserted into database 1

[Beng79]

Hello All,

I am inserting data into database from user inputs in the textboxes.

Right now, my code below is only able to work if all the textboxes are being filled up with data. How can I alos insert the data if only some of the textboxes are being filled up?

Any help is appreciated.
Thank you

<%
Dim connectAdd
Dim recstAdd
Dim SQL
Dim strProjID1, strProjID2, strProjID3, strProjID4, strProjID5
Dim strFeature1, strFeature2, strFeature3, strFeature4, strFeature5
%>

<%
Set connectAdd = Server.CreateObject("ADODB.Connection")
connectAdd.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

strProjID1 = request.form("ProjID1")
strProjID2 = request.form("ProjID2")
strProjID3 = request.form("ProjID3")
strProjID4 = request.form("ProjID4")
strProjID5 = request.form("ProjID5")

strFeature1 = request.form("Feature1")
strFeature2 = request.form("Feature2")
strFeature3 = request.form("Feature3")
strFeature4 = request.form("Feature4")
strFeature5 = request.form("Feature5")


SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature1 & "','" & strProjID1 & "')"
Set recstAdd = connectAdd.Execute(SQL)

SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature2 & "','" & strProjID2 & "')"
Set recstAdd = connectAdd.Execute(SQL)

SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature3 & "','" & strProjID3 & "')"
Set recstAdd = connectAdd.Execute(SQL)

SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature4 & "','" & strProjID4 & "')"
Set recstAdd = connectAdd.Execute(SQL)

SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature5 & "','" & strProjID5 & "')"
Set recstAdd = connectAdd.Execute(SQL)

Response.Redirect("New Features Added.htm")

recstAdd.Close
Set recstAdd = Nothing
connectAdd.Close
Set connectAdd = Nothing
%>

 

[johnwm]

The problem is usually caused by trying to insert a NULL into a field set to NOT NULL. Just add an empty string to the incoming strings, example

strProjID1 = "" & request.form("ProjID1")

Also as I mentioned in the other thread, you don't need a recordset object with an Execute statement, so

 Set recstAdd = connectAdd.Execute(SQL)

should be

 Set connectAdd.Execute(SQL)

Then of course you won't have a recordset object to close and set to Nothing at the end

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Tarwn]

Minor correction to John's last post, I believe he meant the last code section to be:

connectAdd.Execute SQL

One other option would be to use if statements to determine whether you have data to insert, for instance:

If strFeature1 <> "" And strProjID <> "" Then
   SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature1 & "','" & strProjID1 & "')"
   connectAdd.Execute SQL
End If

That would prevent an insert from firing if both text boxes hadn't been filled for that entry. If you wanted to fire the SQL statement when either or both boxes were filled then you could use an Or instead.

-T

 

[johnwm]

Thanks for the correction Tarwn - my bad! That's what comes from a quick copy&paste without checking!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Tarwn]

Been there