Solved

strSQL insert

Posted on 2002-06-06
13
1,809 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 9

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 9

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 9

Accepted Solution

by:
Nitin Sontakke earned 50 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

624 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