Insert Into SQL

[svsuthar1]

How can I write a Insert into SQL statment that will accept null values and only will insert statement that is not null.

Here is my SQL for that statment... I am using ASP. I hope I am clear.. the SQL works but bombs out when it has null value.. I tried Where clause but could not get it to work because of syntax... you could help on that if possible.

Samir

Dim iLoop
For iLoop = 0 To iCount
strProductID = Request(iLoop & ".ID")
strOnHand = Request(iLoop & ".OnHand")
strOnOrder = Request(iLoop & ".OnOrder")
strDate = Request(iLoop & ".Date")

SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _
& "'" & strProductID & "', " _
& "'" & strOnHand & "', " _
& "'" & strOnOrder & "', " _
& "'" & strDate & "' )"
'& " WHERE (((strOnOrder)>0) AND ((strOnHand)>0))"

objConn.Execute(SQL)
Response.Write(SQL)
Next

 

[ChrisHirst]

There can't be a WHERE clause in an INSERT statement you will need to check the variables for null before building the SQL string.



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[svsuthar1]

Then how would I do it.. I have tried this, but it gives me.

Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: ""]'
/saveSupplyOrder1.asp, line 26

which is the bold line.

Dim iLoop
For iLoop = 0 To iCount
strProductID = Request(iLoop & ".ID")
strOnHand = Request(iLoop & ".OnHand")
strOnOrder = Request(iLoop & ".OnOrder")
strDate = Request(iLoop & ".Date")

If (CStr(strOnHand > 0) AND CStr(strOnOrder > 0 )) Then
SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _
& "'" & strProductID & "', " _
& "'" & strOnHand & "', " _
& "'" & strOnOrder & "', " _
& "'" & strDate & "' )"
objConn.Execute(SQL)
Response.Write(SQL)
End If
Next

 

[Kendel]

Check variables for null like this:

strProductID = Request(iLoop & ".ID")
If IsNull(Request(iLoop & ".ID")) Then 
   strProductID = NULL 
End If

If this won't work, you might need to play around with the quotes " around NULL or variable strProductID in SQL statement.

Print out the SQL statement to see what you got.

 

[svsuthar1]

Yes, but that is what I want to avoid... I don't want any null fields... and the SQL is not leting any NULL fields to be inserted. OK If I pre fill the field with ZERO then How can write my SQL where it does not insert any ZERO value into my table.

Samir

 

[amorous]

Try This:

Dim iLoop
For iLoop = 0 To iCount
    strProductID = Request(iLoop & ".ID")
    strOnHand = Request(iLoop & ".OnHand")
    strOnOrder = Request(iLoop & ".OnOrder")
    strDate = Request(iLoop & ".Date")
    
    If strOnHand<>"" Then 
    If strOnOrder<>"" Then

        SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _            
                & "'" & strProductID & "', " _
                & "'" & strOnHand & "', " _
                & "'" & strOnOrder & "', " _
                & "'" & strDate & "' )"        
Else

        SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _            
                & "'" & strProductID & "', " _
                & "'" & strOnHand & "', " _
                & "'" & strDate & "' )"        

End If

Else

If strOnOrder<>"" Then

SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _            
                & "'" & strProductID & "', " _
                & "'" & strOnOrder & "', " _
                & "'" & strDate & "' )"        

End If

End If
        

objConn.Execute(SQL)
        Response.Write(SQL)
    
Next

-VJ

 

[svsuthar1]

Well I got it to work now... here is the final code.

Dim iLoop
For iLoop = 0 To iCount
strProductID = Request(iLoop & ".ID")
strOnHand = Request(iLoop & ".OnHand")
strOnOrder = Request(iLoop & ".OnOrder")
strDate = Request(iLoop & ".Date")

If (strOnHand <> 0 OR strOnOrder <> 0) Then
SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _
& "'" & strProductID & "', " _
& "'" & strOnHand & "', " _
& "'" & strOnOrder & "', " _
& "'" & strDate & "' )"
objConn.Execute(SQL)
Response.Write(SQL)
End If
Next

 

[svsuthar1]

Above code is work around that I could deal with...but I would really want not substatute "0" I would rather have my online form to be Null from beginning. Is it any thing else I could do to do that. Please advise.

Samir

 

[ChrisHirst]

use the IsNull() function.




Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[svsuthar1]

I have tried it, but it still does not work... Here is both pages maybe that might help.

This is the Form page where the data is entered.

<HTML><HEAD><BODY>
<!--#include file="includes/commonheader.asp"-->
<SCRIPT LANGUAGE="JavaScript">
<!--
function SaveOrder()
{
document.forms[0].action = "saveSupplyOrder.asp";
document.forms[0].submit();
}
// -->
</SCRIPT>
<LINK href="..\CSS\main.css" type=text/css rel=stylesheet>
<FORM NAME=getSuppliesOrder METHOD=post ACTION="saveSupplyOrder.asp">
<TABLE cellSpacing=0 cellPadding=1 border=1 align=center bordercolor="#CCCCCC" style='border-collapse:collapse;'>
<TR><TD align=center><B>Order#</B></TD>
<TD align=center><B>Product Name</B></TD>
<TD align=center><B>Price</B></TD>
<TD align=center><B>On Hand</B></TD>
<TD align=center><B>Ordered</B></TD>
</TR><BR>
<%
'Open up a connection our access database
'we will use a DSN-less connection

'Create a path for the database
Dim objConn, path
path="C:\Inetpub\

http://wwwroot\Supply_DB_Back.mdb"

'Create a Connection using the path given above
Set objconn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString= "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
"DATA SOURCE=" & path
objConn.Open

'Create the SQL to be used for it to display
Dim strSQL, objRS, iCount
iCount = 0
strSQL = "SELECT fldProductID, [fldOrder#], fldProductName,fldProductPrice From tblProduct"

'Create a recordset of the SQL that is created above
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn,3,3

'loop through all the records outputting a row for each one
Do Until objRS.EOF
ProductID = objRS("fldProductID")

Response.Write("<TR>")
Response.Write("<Input Type=Hidden Name=""" & iCount & ".ID"" " & _
"Value=""" & objRS("fldProductID") & """>")
Response.Write("<TD align=Center>" & objRS("fldOrder#") & "</TD>")
Response.Write("<TD>" & objRS("fldProductName") & "</TD>")
Response.Write("<TD>" & FormatCurrency(objRS("fldProductPrice"),2) & "</TD>")
Response.Write("<TD><input type=text name=""" & _
iCount & ".OnHand"" Value=0 STYLE=text-align:center></TD>")
Response.Write("<TD><input type=text name=""" & _
iCount & ".OnOrder"" Value=0 STYLE=text-align:center></TD>")
Response.Write("<Input Type=Hidden name=""" & _
iCount & ".Date"" Value=217>")
Response.Write("</TR>")
objRS.MoveNext
iCount = iCount + 1
Loop
'Clean up our ADO objects
objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing
Response.Write "<TR><TD Align=Center colspan=5><INPUT TYPE=BUTTON VALUE=""Submit"" ONCLICK=""SaveOrder();"" id=BUTTON1 name=BUTTON1></TD></TR>"
Response.Write "<INPUT TYPE=HIDDEN NAME=Count VALUE=" & iCount - 1 & ">"
%>
</TABLE><BODY></HTML>


Now this page saves it...

<!--#include file="includes/commonheader.asp"-->
<LINK href="..\CSS\main.css" type=text/css rel=stylesheet><BR>
<%
Response.Buffer = True
Dim iCount
iCount =Request("Count")

Dim strProductID, strOnHand, strOnOrder, strDate
'---------------------------Make a connection to the db--------------------------------------------------------
Dim objConn, path, SQL
path="C:\Inetpub\

http://wwwroot\Supply_DB_Back.mdb"

'Create a Connection using the path given above
Set objconn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString= "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
"DATA SOURCE=" & path
objConn.Open

' Now, we want to loop through each form element
Dim iLoop
For iLoop = 0 To iCount
strProductID = Request(iLoop & ".ID")
strOnHand = Request(iLoop & ".OnHand")
strOnOrder = Request(iLoop & ".OnOrder")
strDate = Request(iLoop & ".Date")

If (strOnHand <> 0) OR (strOnOrder <> 0) Then
SQL = "Insert into tblProductOrder (fldProductID, [fld#Ordered], [fld#OnHand], fldDateID) values (" _
& "'" & strProductID & "', " _
& "'" & strOnHand & "', " _
& "'" & strOnOrder & "', " _
& "'" & strDate & "' )"
objConn.Execute(SQL)
End If
Next
Response.Write("<TABLE Align=center border=0>")
Response.Write("<TR><TD><P>")
Response.Write("<CENTER><B>Your Infomration has been saved Successfully</B></CENTER>")
Response.Write("</P></TD></TR>")

'Clean up our ADO objects
objConn.Close
Set objConn = Nothing
%>
<!--#include file= "includes/commonheader2.asp"-->

 

[ChrisHirst]

assuming I've worked what you actually want

this
If (strOnHand <> 0) OR (strOnOrder <> 0) Then

should be

if (strOnHand > 0 AND strOnOrder > 0) then

then it will only post the record if both these are greater than 0

but this may not work because at no point are you casting the request variables to integers. also your onhand & onorder fields are transposed with the values in the SQL string.



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[svsuthar1]

Chris,

No I do want the OR statement since I want either or to be > 0 to post. My main question is that the value for ORDER and ON Hand is 0 from Value=0 that I have put in... I don't want to pre-assign value as zero but keep it blank and whatever is keyed to be saved into the database. But when ever I tried that it gave expression something at my SQL...

I want to know how can I leave VALUE= "" or VALUE= and still able to save data.

Thanks alot for your response.

Samir

 

[ChrisHirst]

will the table column allow null values ?



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[svsuthar1]

I am not sure. I have 0 as my default value, now I am trying to take it off and try it.

Samir