Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

strSQL insert

Posted on 2002-06-06
13
Medium Priority
?
1,818 Views
Last Modified: 2008-02-01
' 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
 
0
Comment
Question by:Paullkha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +4
13 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 7060277
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
 
LVL 2

Author Comment

by:Paullkha
ID: 7060339
I know how to build the different SQL stmts. I need the string built. for this:
    objDatabase.Execute strSql, , adCmdText + adExecuteNoRecords
0
 
LVL 22

Expert Comment

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

CJ
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:weesiong
ID: 7061006
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
 

Expert Comment

by:dwergje
ID: 7061295
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
 
LVL 1

Expert Comment

by:johan_brohn
ID: 7061438
<%
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
 
LVL 10

Expert Comment

by:Nitin Sontakke
ID: 7062004
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
 
LVL 10

Expert Comment

by:Nitin Sontakke
ID: 7062007
I am sorry, CJ_S said above.
0
 
LVL 2

Author Comment

by:Paullkha
ID: 7062059
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
 
LVL 2

Author Comment

by:Paullkha
ID: 7062071
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
 
LVL 10

Accepted Solution

by:
Nitin Sontakke earned 200 total points
ID: 7062118
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
 
LVL 2

Expert Comment

by:manihopever
ID: 7062303
dont try for much logic,, its simpler to use like below,

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

thats it
mani.v
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 7062387
>> 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question