Solved

strSQL insert

Posted on 2002-06-06
13
1,769 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
  • 3
  • 3
  • 3
  • +4
13 Comments
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
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
Comment Utility
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
Comment Utility
I don't understand what you want. WHat is it you want with the null value? In SQL or ASP?

CJ
0
 
LVL 7

Expert Comment

by:weesiong
Comment Utility
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
Comment Utility
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
Comment Utility
<%
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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 6

Expert Comment

by:Nitin Sontakke
Comment Utility
I am sorry, CJ_S said above.
0
 
LVL 2

Author Comment

by:Paullkha
Comment Utility
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
Comment Utility
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 6

Accepted Solution

by:
Nitin Sontakke earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now