strSQL insert

' Build our SQL String
strSQL = strSQL & "INSERT INTO scratch "
strSQL = strSQL & "(text_field, integer_field) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & strTextField & "'"
strSQL = strSQL & ", "
strSQL = strSQL & intIntegerField
strSQL = strSQL & ")"

or
strSql = "SELECT * FROM scratch WHERE id=0"
Set objRS = New ADODB.Recordset
objRS.Open strSql, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
objRS.AddNew
objRS.Fields("text_field").Value = CSTR(strTextField)
objRS.Fields("integer_field").Value = CINT(intIntegerField)
 
What are procedures to insert say a value into a field that accepts a null value?
I know a whole bunch of if stmts will work, but pretty ugly.
CSTR, CINT wont work with null values
 
LVL 2
PaullkhaAsked:
Who is Participating?
 
Nitin SontakkeDeveloperCommented:
I had rather painstakingly implement the if then else end if, as compare to .AddNew.

The point-blank answer to your question is NO. You cannot.

However, there is a good reason to it. Every language enviornment has its own implementation of initialising variables as they are declared. If you check for value Request.Form("UnknownVar") it will be "" but not NULL. Now, here we have to introduce the custome code.

Unfortunately, ASP doesn't support IIF(expression, true, false) statements. It could look much neater.

0
 
CJ_SCommented:
If you do an insert you can just ommit it in the sql statement

INSERT INTO scratch (textfield) VALUES ('TEXT')
Assuming that integer-field is the one that can receive a NULL value.

To explicitly set it to NULL you can use the following in Access:
INSERT INTO scratch(textfield) VALUES(null)

If you are refering to the VB method you would use the function isnull()
If(IsNull(rs("thefield"))) then response.write("is null")

CJ
0
 
PaullkhaAuthor Commented:
I know how to build the different SQL stmts. I need the string built. for this:
    objDatabase.Execute strSql, , adCmdText + adExecuteNoRecords
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
CJ_SCommented:
I don't understand what you want. WHat is it you want with the null value? In SQL or ASP?

CJ
0
 
weesiongCommented:
Paullkha,

If Not IsNumeric(intIntegerField) Then intIntegerField = 0


strSQL = strSQL & "INSERT INTO scratch "
strSQL = strSQL & "(text_field, integer_field) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & strTextField & "'"
strSQL = strSQL & ", "
strSQL = strSQL & intIntegerField
strSQL = strSQL & ")"


This is faster way.
0
 
dwergjeCommented:
They way your build your sql string can mutch simpleyer

like this:

strSql = " INSERT INTO scratch " & _
         " (text_field, integer_field) " & _
         " VALUES " & _
         " ('text',1) "

(i now this is not the answer for your question..
0
 
johan_brohnCommented:
<%
function strNVL(strVal)
  if strVal = "" then
    strNVL = null
  else
    strNVL = strVal
  end if
end function

function intNVL(strVal)
  if isNumeric(strVal) then
    intNVL = CLng(strVal)
  else
    intNVL = Null
  End if
end function

strSql = "SELECT * FROM scratch WHERE id=0"
Set objRS = New ADODB.Recordset
objRS.Open strSql, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
objRS.AddNew
objRS.Fields("text_field").Value = strNVL(strTextField)
objRS.Fields("integer_field").Value = intNVL(intIntegerField)

%>
0
 
Nitin SontakkeDeveloperCommented:
If you are building a query, after checking value of a variable, if you it to be null in db, just don't include it your query string. As simple as that.

For more flexibility, construct two strings, strColumns and strValues. Then create query string as

strQuery = "Insert Into tableName (" & strColumn & ") Values (" strValues & ")

I am just reinstating what CS_J said above.

0
 
Nitin SontakkeDeveloperCommented:
I am sorry, CJ_S said above.
0
 
PaullkhaAuthor Commented:
NitinSontakke - good you get it
sorry if I was not clear, I try

So If I have several columns that could be null, several checks, if col1 null then, if col2 null then, if col3 null then...on and on.

There is absolutely no string representation of null,
like #NULL# or some other token.

So the clear question, there is no solution that would allow me to not check each and every possible null column and create those two strings?

CJ_s? Nitinsontakke?
0
 
PaullkhaAuthor Commented:
Except for this:

    strSql = "SELECT * FROM scratch WHERE 1=0"
    Set objRS = New ADODB.Recordset
    objRS.Open strSql, strHelplineConn, adOpenKeyset, adLockOptimistic, adCmdText

    ' Add our record and set it's values.  You could bounce
    ' into an edit mode here to let people enter the initial
    ' values, but for simplicity I just add the record with
    ' some default values.
    objRS.AddNew
    objRS.Fields("textfield").Value = atextfield
    objRS.Fields("intintegerfield").Value = aintIntegerfield
 
Now I don't need to check for null, but not very 'clean'.
0
 
manihopeverCommented:
dont try for much logic,, its simpler to use like below,

if variable<>"" then variable = <assign value>

thats it
mani.v
0
 
CJ_SCommented:
>> There is absolutely no string representation of null,
>> like #NULL# or some other token.

x is null

select * from table where not x is null


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.