collection from gridview to SQL 1

[bobmmp]

This a copy of an unanwered post on asp.net forum

I have been hung up on this now for a while.

I have a gv that has multiple select checkboxes and a dropdownlist. In my codebehind, I captured the results and verified that they are collected.... see Part 1 in below code. I finally have this working from two tutorials.

Now for the problem that I am still having is getting this results into the another table in the database. SEE Part 2

I need to add the dropdownlist results to TestName
And the value of the PK (int) and the on/off (true/false) for the remaining checkbox

Field Properties are :

TestName varchar(50)
test1ID int
TestDefault Bit (True/False)

I know I need a For Each statement with the parameters value, but here is where I am totally lost. Do I need to repeat my IF Then statement for the TestDefault results. I have tried many ways since yesterday and I cannot get the info saved into the the table.... sql 2005. An example would be awesome.

I am running out of time on getting the test resolved.
Thanks in advance.

Regards!

Code below grabs the gridview check/selected items and a button1.click event handler.


Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

**************** PART1 **************************
Dim gvIDs As String = ""
Dim ddIDs As String = ""
Dim chkBox As Boolean = False
Dim chkBox1 As Boolean = False


'Navigate through each row in the GridView for checkbox items
ddIDs = DropDownList1.SelectedValue.ToString


For Each gv As GridViewRow In GridView1.Rows
Dim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)
Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox)

If addChkBxItem.Checked Then
chkBox = True
gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString
Response.Write(ddIDs + " " + gvIDs + " ")

If addChkBx1Item.Checked Then
chkBox1 = True
Response.Write("Defaut Item")
End If
Response.Write("<br />")
End If
Next

******************** Part 2 *****************************************************

Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)

If chkBox Then
Try
Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)"
Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn)
cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString)
cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString)
cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox))
cn.Open()
cmd.ExecuteNonQuery()
Catch err As SqlException
Response.Write(err.Message.ToString)
Finally
cn.Close()
End Try
End If



Bob B.

http://www.adigitalhost.com

 

[jbenson001]

What is the problem? Are you getting an error, no data in the DB? Ypu need to put your Insert statement within the For Loop so that each row's data gets inserted if it meets your criteria.

 

[bobmmp]

thanks for the reply jbenson001. Yes, I get all kinds of errors. Here is my insert that I am trying to get from that part1 code. I know my parameters are incorrect, I keep get scalar error messages and My when I do the For Each, vs tells me the varialbes used above are not declared.
I have written this atleast 50 different ways to this point, if I wasn't so far in the project, i would go back to asp and have this part done in 20 minutes.


If chkBox Then
Try
Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)"
Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn)
cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString)
cmd.Parameters.AddWithValue("@test1ID", gv.FindControl("TestID"))
cmd.Parameters.AddWithValue("@TestDefault", gv.FindControl("CheckBox1"))
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()
Catch err As SqlException
Response.Write(err.Message.ToString)
Finally
cn.Close()
End Try
End If

Bob B.

http://www.adigitalhost.com

 

[jbenson001]

Yes, I get all kinds of errors

It's hard to help you with a response like that. You will have to step through your code and see where the problems are and post here if you can't figure it out.

 

[bobmmp]

Thanks for the reply. Here is the error thrown.
Below is where it is thrown at.

System.ArgumentException: No mapping exists from object type System.Web.UI.WebControls.Label to a known managed provider native type. The line with the error is marked *** for easy id.

Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)
For Each gv1 As GridViewRow In GridView1.Rows
If chkBox Then
Try
Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)"
Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn)
cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString)
cmd.Parameters.AddWithValue("@test1ID", gv1.FindControl("TestID"))
cmd.Parameters.AddWithValue("@TestDefault", gv1.FindControl("CheckBox1"))
cn.Open()


cmd.ExecuteNonQuery() ******
GridView1.DataBind()
Catch err As SqlException
Response.Write(err.Message.ToString)
Finally
cn.Close()

End Try
End If
Next


Bob B.

http://www.adigitalhost.com

 

[jbenson001]

I belive this is your problem:

cmd.Parameters.AddWithValue("@test1ID",gv1.FindControl("TestID"))
cmd.Parameters.AddWithValue("@TestDefault", gv1.FindControl("CheckBox1"))

You are trying to add the control as a parameter value, and not the value of the control.
You need something like this:

dim tb as textbox
tb = gv1.FindControl("TestID")
cmd.Parameters.AddWithValue("@test1ID",tb.text)

Jim

 

[bobmmp]

Thanks Jim, I will give that a try. The value of the dropdownlist adds just fine. It is the other two values where the problem is at. One thing I did try and it failed to work was build an array up top where I was doing my output to screan to make sure I caught all my selected/checked values. But when I went to add them below in my parameters, It threw an error that the array was not declared. At that point, I tried the ddDropDown and it entered.

I appreciate you taking your time and I will work off of your solution. I have a small fortune in books, videos and so on, it is amazing how much is really not covered out there. I am embarrassed to say how long I have work on just this part of the application, and this is one of the simplest peices... I thought... everything else has been smooth.

Thanks again!

Bob.

Bob B.

http://www.adigitalhost.com

 

[bobmmp]

jbenson001 - Jim,

Thanks for your help. Your suggestions were very valuable.
This got me past a very big hurdle!


Bob B.

http://www.adigitalhost.com

 

[jbenson001]

Hey grea!t.. glad to help!.. and thanks for the star...