How to insert a string value in the table

Question

Using VB6 and SQL Server

Dim a as string
a = 0008", "0009", "1011", "1208

I want to insert this string values in the table

Expected Output

Table1

How to make a query for the above condition.

Need Query Help.

I didn't know you could omit leading and trailing `"` in VB6... — ta.speot.is, Dec 04 '11 at 05:57

Elias Hossain · Accepted Answer · 2011-12-10T08:44:41.670

1

Please try with the below way:

Dim a as string
a = "0008,0009,1011,1208"
dim strArray as string() = Split(a, ",")

b for each item as string in strArray

   query = "INSERT INTO  Table1(ID) values('"&item&"')"
   // Write Connection and Command code here
next

Update:

You can generate insert SQL statement for each item at a time and then write Connection and Command code, that would be more efficient.

dim query as string
for each item as string in strArray
   query = query + "INSERT INTO  Table1(ID) values('"&item&"'); "
next
// Write Connection and Command code here after generating insert statement 
// so that all data will save into database in one shot.

edited Dec 10 '11 at 08:44

answered Dec 04 '11 at 09:07

Elias Hossain

4,410
1
19
33

I am using VB6, You code is not working, showing error in "Split", Can you check plz.... – Gopal Dec 10 '11 at 06:18
I've changed my code block accordingly, would you please check on more time, thanks for your time. – Elias Hossain Dec 10 '11 at 08:34
Hello @Gopal, I've updated my answer so that it can do in more efficient way, thanks for your time. – Elias Hossain Dec 10 '11 at 08:47

S.K · Answer 2 · 2011-12-04T06:00:08.433

0

DECLARE @Str VARCHAR(100)
SET @Str = '0008", "0009", "1011", "1208'
SET @Str = REPLACE(@Str,'"','')
SELECT * FROM [dbo].Split(@Str)

You can use Split function from this link.
Check this Split function.
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/split-string-in-sql-server-2005-clr-vs-t

edited Dec 04 '11 at 06:00

answered Dec 04 '11 at 05:51

S.K

165
1
13

We can pass complete string as parameter to SP. – S.K Dec 04 '11 at 06:09

Esi · Answer 3 · 2011-12-04T12:42:15.897

0

at first u must split this string to a string[]

dim a as string 

a = 0008", "0009", "1011", "1208
dim str as string() = (a.Replace('"',' ')).Split(",")

and if

a = "0008,0009,1011,1208"
dim str as string() = a.Split(",")

now add values in the table with a query on the foreach loop

imaging your table name is testTable andyour column name is ID

 dim query as string
 for each item as string in str

            query = "INSERT INTO  testTable(ID) values('"&item&"')"
 next

edited Dec 04 '11 at 12:42

answered Dec 04 '11 at 06:13

Esi

389
5
14

`string[] str = a.Split(",");` is supposed to be `dim str as string() = a.Split(",");` – Seph Dec 04 '11 at 06:55
yap(;-) but without ';') at first i create sample in the c# and then i forget Correct this sample thanks – Esi Dec 04 '11 at 07:16
2

And when you're inserting, you should use **parametrized queries** instead of concatenating together your SQL commands! Doing so open the door to SQL injection attacks - not something you want to have to deal with...... – marc_s Dec 04 '11 at 11:21

How to insert a string value in the table

3 Answers3