[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1840
  • Last Modified:

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
 
0
Paullkha
Asked:
Paullkha
  • 3
  • 3
  • 3
  • +4
1 Solution
 
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
 
CJ_SCommented:
I don't understand what you want. WHat is it you want with the null value? In SQL or ASP?

CJ
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now