sql server programming: Dear All,

I got a SQL statement like that


Insert into TableA (columnA,columnB,ColumnC) select
VariableA, VariableB,VariableC

Where columnA,columnB,ColumnC are Varchar. VariableA,
VariableB,VariableC are variables return by VB Function. If the
VariableA = A, VariableC = C, VariableB contains no chacracter then
the statemnet would become as follows:

Insert into TableA (columnA,columnB,ColumnC) select A,,C

The above Insert statement is not valid because two comma exist after
select A (,,). Therefore I need to make VariableB contains an empty
space, i.e VariableB = " " . But I do not want VariableB contains a
space. My question is how to make "Insert into TableA
(columnA,columnB,ColumnC) select A,,C"
valid without adding a space character to VariableB. That means how I
can insert VariableB without adding a space character to VariableB?

Thanks

What about using single quotation --> '' ?

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

You can't get SQL server to accept a syntactically incorrect statement.

What you should be doing is a little interpretation on the VB side
before you fire off the T-SQL statement to the SQL server. My VB is a
little rusty but it would be something like:

dim a,b,c
dim SQL

...

SQL = "insert into tableA (colA, colB, colC) values ("

if (a = "") then SQL = SQL & "null, "
else SQL = SQL & a & ", "

if (b = "") then SQL = SQL & "null, "
else SQL = SQL & b & ", "

if (c = "") then SQL = SQL & "null)"
else SQL = SQL & c & ")"

...

send your T-SQL string off to the server now

...


It should be dead simple to write around particular cases when you're
preparing the SQL statement on the VB side. Alternately you could
specify "''" instead of "null" if you wanted an empty string in that
column rather than a null. There are a number of different ways you can
do it but they're all fiddling with the SQL string using VB before you
send it to the server.

--
*mike hodgson*
http://sqlnerd.blogspot.com



[quoted text, click to view]