[justicet]

//connection here and declaration of variable

Dim i As Integer
Dim chk As CheckBoxList
chk = CType(Me.FindControl("chkBx1"), CheckBoxList)
For i = 0 To chk.Items.Count - 1
  If chkbx.Items(i).Selected Then
    cmd.Parameters.AddWithValue("@OptionID", chk.Items(i).Value)
    cmd.ExecuteNonQuery()
    cmd.Parameters.Clear()
  End If
Next

I get the error message: Object reference not set to an instance of an object. Please assist.I need to insert each selected value to its column in the db.

Edited by Dogstopper:

[CharlieMay]

If I'm understanding what you want to do, this should be a lot simpler. It iterates through the checkeditems and passes the string to the parameter.

Otherwise, could you elaborate a little more on what you're attempting to do with this code as there are numerous inconsistencies that can't be determined with this patch of code.

    For Each itm As String In CheckedListBox1.CheckedItems
            cmd.Parameters.AddWithValue("@OptionID", itm.ToString)
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Next

[noorahmad]

- Try to renew cmd in each loop
- use Transaction when you are dealing with 2 linked tables

[justicet]

Thanx a lot for your quick respond.

I need to send data from my webform, however I need to send selected items from the CheckboxList into two column in a table and not send it seperated by comma.

When I use the code I sent it works fine only if I am sending one record.

I have a form with many controls, but some of those controls like CheckboxList; I must send as 1-to-many relationship.

I have table1 and table2. My main table is Table1 which gets record first, then Table2 can have multiple records relating to Table1 record.

I have a Stored Prc that insert records to Table1 and Table2 same time. Table2 gets the last @@identity from Table1.

I hope it is now clear.

Please assist.

CharlieMay, on 18 January 2011 - 06:16 AM, said:

If I'm understanding what you want to do, this should be a lot simpler. It iterates through the checkeditems and passes the string to the parameter.

Otherwise, could you elaborate a little more on what you're attempting to do with this code as there are numerous inconsistencies that can't be determined with this patch of code.

    For Each itm As String In CheckedListBox1.CheckedItems
            cmd.Parameters.AddWithValue("@OptionID", itm.ToString)
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Next

[CharlieMay]

I THINK I'm understanding.

You have a variable holding the PK from table 1

You want to insert the PK as the FK in table 2 along with the checked item

So an example would be

Table1
PK...Name
1....CharlieMay

Table2
PK...FK....Phone
1....1.....555-1212
2....1.....555-1213
Relating those two phone numbers to CharlieMay in Table1? Am I on the right track.

If so, can you show me more code with the SQL statements you're using and what variable you're storing the result of @@IDENTITY in?

[justicet]

Thanx again for your fast respond.

You got it CORRECTLY, that's exactly what I am looking for.I need to be able to insert more record on Table2 as you showed based on ID created on Table1.

Herewith some code, I just tried to make it simpler as it is a very long code.

CREATE PROCEDURE [dbo].[insertTdc]


@ReportRelatedtoPlanning varchar(255),
@RelatetoMatter varchar(255) ,
@IncRefID as int output,
@IncTime nchar(10)

AS

BEGIN TRY

BEGIN

SET NOCOUNT ON;

insert into [Table1]
(ReportRelatedtoPlanning,
RelatetoMatter ,
IncRef)

Values( @ReportRelatedtoPlanning ,
@RelatetoMatter ,
@IncRef,
@IncTime)

-- SELECT @IncRef as 'Ref#'

SELECT @IncRefID = @@identity

insert into [Table2](IncRefID,Reliability)
values(@IncRefID,@Reliability)


END

CharlieMay, on 18 January 2011 - 08:59 AM, said:

I THINK I'm understanding.

You have a variable holding the PK from table 1

You want to insert the PK as the FK in table 2 along with the checked item

So an example would be

Table1
PK...Name
1....CharlieMay

Table2
PK...FK....Phone
1....1.....555-1212
2....1.....555-1213
Relating those two phone numbers to CharlieMay in Table1? Am I on the right track.

If so, can you show me more code with the SQL statements you're using and what variable you're storing the result of @@IDENTITY in?

[CharlieMay]

OK, I'm not real strong with Stored Procedures but it would seem to me that you could use one stored procedure to insert into table1 and return the @@Identity back to VB. Then a second stored procedure to send the stored ID and the Related column, calling it for each checked item. As it stands right now, I don't see how you can preserve the @@Identity value any other way.

Here's a basic example
(First Procedure To Insert Into Table 1)

CREATE PROC Myproc
AS

INSERT INTO Table (SomeColumn) VALUES ('Whatever')

RETURN @@IDENTITY

in VB.Net you would handle this with the following code:

Dim con As New SqlConnection("connectString ...")
Dim cmd As New SqlCommand("Myproc", con)
Dim prm As New SqlParameter("RETURN", SqlDbType.Int)
Dim recordId As Integer
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(prm)
prm.Direction = ParameterDirection.ReturnValue
    con.Open()
    cmd.ExecuteNonQuery()
    recordId = CType(prm.Value, Integer)
    con.Close()

At this point you should have inserted the record into table 1 and now have the ID stored in the recordId variable.

This can then be passed into your second procedure where you insert into Table 2 only you will be sending 2 parameters, one will always hold the value of the recordID and the other will be the value of the checkedItem.

This post has been edited by CharlieMay: 18 January 2011 - 10:05 AM

[justicet]

Thanx Charlie,
I was returning the @@identity back to the form although I was not using it in a different S_Proc. It works fine.

Two more things that I need assistant with:
I have a form with different sections and I need users to be able to Save one section while they are still busy working on the form to avoid loosing data on the way; once done they can then Submit the data or come back at a later stage and be able to retrieve the incomplete form then complete it and Submit.

I have a record that I insert to the db, when I make changes/update to the record I need to be able to keep track of what the record was previously using the same recordid when user view it(Like the HelpDesk ticket).

Any help please.

CharlieMay, on 18 January 2011 - 10:03 AM, said:

OK, I'm not real strong with Stored Procedures but it would seem to me that you could use one stored procedure to insert into table1 and return the @@Identity back to VB. Then a second stored procedure to send the stored ID and the Related column, calling it for each checked item. As it stands right now, I don't see how you can preserve the @@Identity value any other way.

Here's a basic example
(First Procedure To Insert Into Table 1)

CREATE PROC Myproc
AS

INSERT INTO Table (SomeColumn) VALUES ('Whatever')

RETURN @@IDENTITY

in VB.Net you would handle this with the following code:

Dim con As New SqlConnection("connectString ...")
Dim cmd As New SqlCommand("Myproc", con)
Dim prm As New SqlParameter("RETURN", SqlDbType.Int)
Dim recordId As Integer
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(prm)
prm.Direction = ParameterDirection.ReturnValue
    con.Open()
    cmd.ExecuteNonQuery()
    recordId = CType(prm.Value, Integer)
    con.Close()

At this point you should have inserted the record into table 1 and now have the ID stored in the recordId variable.

This can then be passed into your second procedure where you insert into Table 2 only you will be sending 2 parameters, one will always hold the value of the recordID and the other will be the value of the checkedItem.

[CharlieMay]

The second one, pulling a record up to make changes should be fairly easy. I assume that you would pull this record from the database based on a list or something being entered to ensure you are pulling the correct record. At this point, you should have that number that you would hold in a variable to use for when you update back to the database. For example.

The first one would be pretty much the same thing but you would need to keep track as to whether this is the first save for this record (which would require an INSERT statement) or if it is a continuation/edit (which would require an UPDATE statement.