Bill,
Thanks. ClientID isnt being passed as null, if i display the dataset on a
datagrid instead of updating the database, all the data looks correct. Im
not sure what you mean by the columnmapping - a primary key is defined
though.
I've attached some of the code that i think is prevelant. Let me know if
you need anythis else - This is bugging me and i cant figure it out. Thanks
again for the help.
Select Command:
SELECT I.[id], I.ClientID, I.casenum, I.[date], I.QID, I.Answer, IQ.Question
FROM Indicators I, IndicatorQuestions IQ WHERE (ClientID = @clientID) AND
(casenum = @casenum) and ([date]=@date) and I.QID=IQ.id
Update Command:
UPDATE Indicators SET ClientID = @ClientID, casenum = @casenum, date =
@date, QID = @QID, Answer =
@answer WHERE (id = @Original_id) AND (Answer =
@Original_Answer OR @Original_Answer IS NULL AND Answer IS NULL) AND
(ClientID = @Original_ClientID OR @Original_ClientID IS NULL AND ClientID IS
NULL) AND (QID = @Original_QID OR @Original_QID IS NULL AND QID IS NULL) AND
(casenum = @Original_casenum OR @Original_casenum IS NULL AND casenum IS
NULL) AND (date = @Original_date OR @Original_date IS NULL AND date IS
NULL);
SELECT id, ClientID, casenum, date, QID, Answer FROM Indicators WHERE (id =
@id)
This are both run through a SP and work fine independantly.
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[IndicatorsSelectCommand]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@clientID", System.Data.SqlDbType.Int,
4))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@casenum", System.Data.SqlDbType.Int,
4))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@date", System.Data.SqlDbType.DateTime,
8))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "[IndicatorsUpdateCommand]"
Me.SqlUpdateCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ClientID", System.Data.SqlDbType.Int,
4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@casenum", System.Data.SqlDbType.Int,
4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@date", System.Data.SqlDbType.DateTime,
8))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@QID", System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("
@answer", System.Data.SqlDbType.Bit, 1,
"Answer"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Answer",
System.Data.SqlDbType.Bit, 1))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ClientID",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_QID",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_casenum",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_date",
System.Data.SqlDbType.DateTime, 8))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.Int, 4))
On Page_Load
sqlDataAdapter1.SelectCommand.Parameters.Item("@casenum").Value =
Request.QueryString("id")
SqlDataAdapter1.SelectCommand.Parameters.Item("@clientID").Value
= Request.QueryString("case")
If ddlDates.SelectedValue <> "" Then
SqlDataAdapter1.SelectCommand.Parameters.Item("@date").Value
= ddlDates.SelectedValue
Else
SqlDataAdapter1.SelectCommand.Parameters.Item("@date").Value
= "1/1/1920"
End If
'Dim dsInd As New DataSet
SqlDataAdapter1.Fill(dsInd)
SqlDataAdapter2.Fill(dsInd)
dgIndicators.DataSource = dsInd
dgIndicators.DataKeyField = "QID"
Dim keys(2) As DataColumn
keys(0) = dsInd.Tables(0).Columns(0)
dsInd.Tables(0).PrimaryKey = keys
dgIndicators.DataBind()
Session("dsInd") = dsInd
On Save button:
dsInd = Session("dsInd")
Dim di As DataGridItem
Dim chkSelected As CheckBox
For Each di In dgIndicators.Items
chkSelected = di.FindControl("chkAnswer")
If di.ItemType = ListItemType.Item Or di.ItemType =
ListItemType.AlternatingItem Then
dsInd.Tables(0).Rows.Find(dgIndicators.DataKeys(di.ItemIndex))(2) =
chkSelected.Checked
End If
Next
SqlDataAdapter1.Update(dsInd)
W.G. Ryan eMVP said:
Check that the value you are passing in for ClientID isn't null (vs
DbNull.Value) and make sure that you have a columnmapping to it in yoru
client code. If you post the client code I can be of more help.
Cheers,
Bill
--
W.G. Ryan, MVP
www.tibasolutions.com |
www.devbuzz.com |
www.knowdotnet.com
Scott M said:
Ive tried and tried to fix this problem, I cannot seem to figure it out -
does anyone have any ideas?
I dragged and dropped my connection and had it create stored procedures for
all my SELECT, INSERT, UPDATE, DELETE commands for the dataadapter. I've
verified the changes do exisit in the dataset prior to running .update(ds),
but it fails and says:
Procedure 'IndicatorsUpdateCommand' expects parameter '@ClientID', which was
not
supplied.
This is caused because in profiler i see the command being run is:
exec [IndicatorsUpdateCommand] @ClientID = default, @casenum = default,
@date = default, @QID = default, @Answer = 1, @Original_id = default,
@Original_Answer = default, @Original_ClientID = default, @Original_QID =
default, @Original_casenum = default, @Original_date = default, @id =
default
Everything is default except what changed (@answer), but it should have the
values that are in the dataset.
Anyone know why it's doing this and/or howto fix it?
Thanks.
