storing multiple values within one field

[awasson]

How do I split this values 1, 3, 4, 5 to be inserted like INSERT INTO tbltest (pid) VALUES ('1),('3'),('4'),('5');

If the data is string data then the VALUES should have single quotes (') around them (the first one is missing its end quote).

If the data is numeric it should not have quotes around it: INSERT INTO tbltest (pid) VALUES (1),(3),(4),(5);

[joeyramirez]

this is the actual statement

   
<%
Dim sql,exec
sql="INSERT INTO tbltest (pid) VALUES ("&Request.QueryString("chkChosenField")&")"
Set exec=Conn.Execute(sql)
If err.number=0 then
Response.Write "Save Completed"

End If
Conn.Close
Set exec=nothing
%>

[joeyramirez]

But how can i pass those values (1,3,4,5) to the INSERT STATEMENT INSERT INTO tbltest (pid) VALUES (1),(3),(4),(5); I always get 'Column count doesn't match value count at row 1' when submitting multiple check box values.

[awasson]

It would be cool if you could use replace() to do this but I think you're going to need to do some string manipulation to make this work and it gets a bit complicated.

This isn't very imaginative but try this.... Split Request.QueryString("chkChosenField") on the comma into an array and then loop the array into a string formatted for your SQL query.

Something like this:

<%

' DECLARE YOUR VARIABLES
ChosenFieldString = Request.QueryString("chkChosenField") 
ChosenFieldArray = Split(ChosenFieldString, ",") 
ChosenFieldValues = ""

' LOOP THROUGH THE ARRAY AND CREATE A STRING
for each value in ChosenFieldArray
    ChosenFieldValues = ChosenFieldValues & "(" & value & "), "
next

' THERE IS ONE EXTRA COMMA IN THE ChosenFieldValues STRING SO YOU NEED TO TRIM THAT
ChosenFieldValues = left(ChosenFieldValues, len(ChosenFieldValues)-1)

' FINALLY CREATE YOUR SQL STRING
sql = "INSERT INTO tbltest (pid) VALUES " &  ChosenFieldValues

%>

It's been a while since I've done ASP coding so hopefully I haven't mangled the syntax but that should do the job... Just in case I would remark out Set exec=Conn.Execute(sql) and put a Response.Write sql statement to print the Query to the screen so you can see if it looks right before trying it.

[joeyramirez]

Thanks sir. I appreciate the help. I'll try your code and see if it works.

[joeyramirez]

Can't make it work. Should I display it like this ? Response.Write(Request("ChosenFieldValues")) to see the values?

[joeyramirez]

ok.got it now and it displayed INSERT INTO tbltest (pid) VALUES (1), ( 5), ( 6),

how do i remove the last comma?

[awasson]

Yes, and I see why.

I only removed one character from the end of the string, thinking it would be the comma but I forgot I also added a space after each comma which isn't necessary. Try this (I just removed the space in the loop ChosenFieldValues = ChosenFieldValues & "(" & value & "), "):

<%

' DECLARE YOUR VARIABLES
ChosenFieldString = Request.QueryString("chkChosenField") 
ChosenFieldArray = Split(ChosenFieldString, ",") 
ChosenFieldValues = ""

' LOOP THROUGH THE ARRAY AND CREATE A STRING
for each value in ChosenFieldArray
    ChosenFieldValues = ChosenFieldValues & "(" & value & "),"
next

' THERE IS ONE EXTRA COMMA IN THE ChosenFieldValues STRING SO YOU NEED TO TRIM THAT
ChosenFieldValues = left(ChosenFieldValues, len(ChosenFieldValues)-1)

' FINALLY CREATE YOUR SQL STRING
sql = "INSERT INTO tbltest (pid) VALUES " &  ChosenFieldValues

%>

[joeyramirez]

Thanks sir.I got it now. I really appreciate the help.

[awasson]

Thanks sir.I got it now. I really appreciate the help.

Great! Glad I could help out.

Cheers,
Andrew

[joeyramirez]

How can I display

INSERT INTO tbltest (pid,ticketnumber) VALUES (1,1000),(2,1000),(3,1000)

where 1000 is the ticketnumber and 1,2 and 3 are the PIDs.

my query looks like this :

sql = "INSERT INTO tbltest (pid,ticketnumber) VALUES " & ChosenFieldValues & , Request("tnum")

Request("tnum") is ticket number

How can I put the ticketnumber in the array?

Dim ChosenFieldString,ChosenFieldArray,ChosenFieldValues,sql,exec,value
ChosenFieldString = Request.QueryString("chkChosenField")
ChosenFieldArray = Split(ChosenFieldString, ",") 



for each value in ChosenFieldArray  
    ChosenFieldValues = ChosenFieldValues & "(" & value & ")," 
next

ChosenFieldValues = left(ChosenFieldValues, len(ChosenFieldValues)-1)


sql = "INSERT INTO tbltest (pid,ticketnumber) VALUES " & ChosenFieldValues & , Request("tnum")

[joeyramirez]

Got it now.

for each value  in ChosenFieldArray  
    ChosenFieldValues = ChosenFieldValues & "(" & value & ","& Request("tnum")& ")," 
next

[verschha]

you will find that searching for a specific value inside the column is very, very painful

Storing (and searching) multiple Bit values in a single column is in fact very simple

CREATE TABLE [#checkbox](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[checkbox1] [bit] NOT NULL,
	[checkbox2] [bit] NOT NULL,
	[checkbox3] [bit] NOT NULL,
	[checkbox4] [bit] NOT NULL)
 
CREATE TABLE [#bitwise](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[bitwise] [INT] NOT NULL)
 
DECLARE @checkbox1 bit
DECLARE @checkbox2 bit
DECLARE @checkbox3 bit
DECLARE @checkbox4 bit
 
SET @checkbox1 = 0
SET @checkbox2 = 0
SET @checkbox3 = 0
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 0
SET @checkbox3 = 0
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 0
SET @checkbox3 = 1
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 0
SET @checkbox3 = 1
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 1
SET @checkbox3 = 0
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 1
SET @checkbox3 = 0
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 1
SET @checkbox3 = 1
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 0
SET @checkbox2 = 1
SET @checkbox3 = 1
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 0
SET @checkbox3 = 0
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 0
SET @checkbox3 = 0
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 0
SET @checkbox3 = 1
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 0
SET @checkbox3 = 1
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 1
SET @checkbox3 = 0
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 1
SET @checkbox3 = 0
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 1
SET @checkbox3 = 1
SET @checkbox4 = 0
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SET @checkbox1 = 1
SET @checkbox2 = 1
SET @checkbox3 = 1
SET @checkbox4 = 1
INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
 
SELECT checkbox1, checkbox2, checkbox3, checkbox4 FROM #checkbox
SELECT CASE WHEN bitwise & 1 = 1 THEN 1 ELSE 0 END AS checkbox1
      ,CASE WHEN bitwise & 2 = 2 THEN 1 ELSE 0 END AS checkbox2
      ,CASE WHEN bitwise & 4 = 4 THEN 1 ELSE 0 END AS checkbox3
      ,CASE WHEN bitwise & 8 = 8 THEN 1 ELSE 0 END AS checkbox4
FROM   #bitwise

[r937]

dear verschha, at the time i made that post, we did not know what sort of multiple values were being discussed in this thread, did we

perhaps i should have said comma-delimited values in a single varchar column are extremely painful to search

your demo with bit strings is very interesting, but not all that useful, as disk space nowadays is pretty cheap and the need for bit strings is fairly rare

[verschha]

dear verschha, at the time i made that post, we did not know what sort of multiple values were being discussed in this thread, did we

Yes we did...

How to insert multiple checkbox values

[r937]

no we didn't

<form ...>
<input type="checkbox" name="stooge1" value="curly" checked="checked">Curly<br>
<input type="checkbox" name="stooge2" value="larry">Larry<br>
<input type="checkbox" name="stooge3" value="moe" checked="checked">Moe<br>
</form>

please, where are the bits?

[verschha]

Do you really mean that you don't understand that a checkbox (ussualy) represents a bit field in a database?
I see 3 checkboxes, which will ussauly be stored in the database as 3 bit values. However, I showed you that you can easily store multiple checkbox values in 1 integer field as well.

[r937]

Do you really mean that you don't understand that a checkbox (ussualy) represents a bit field in a database?

i understand it, but i don't believe it

please cite some statistics for your claim

in my experience, checkboxes are ~not~ usually stored in a database as bits

[joeyramirez]

What is the best way to insert many checkbox inputs into database(mysql)?

[r937]

What is the best way to insert many checkbox inputs into database(mysql)?

one value per row

[webber123456]

google "asp -.net insert checkbox values into database"

you will find many examples and discussion of this topic