Hi! I am currently trying to insert some values into a table. I have a very strange problem. I get the error message that i am trying to insert a null value into one of my colums, the problem is that when I write the value to a label control before I runs the insert method on my SqlDataSource control the value is not NULL. If i push the insert button on my webpage once again everything is ok.
What am I doing wrong? Anyone seen this kind of problem before?
regards,
Lumsen
Hi,
You can use the SQL Profiler to see which sql statement runs on the database after you click the button. If the error message is an sql error then you can see the statement by using the profiler.
Eralper
|||Can't be sure without more details. It could depends on what data control you're using, if any, and posting your code may help people to get better idea.|||without more info, its hard to tell. One thing you can do is add a watch to that variable and see if it is NULL at the wrong time.|||
Thanks for trying to help me out!
Here is the code from my asp page:
<%@. Page Language="VB" AutoEventWireup="false" CodeFile="newassesment.aspx.vb" Inherits="newassesment" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Add a new assesment<br />
<br />
<asp:Label ID="Label1" runat="server" Text="assesment id"></asp:Label>
<asp:TextBox ID="text_assesment_id" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="Label2" runat="server" Text="primary language"></asp:Label>
<asp:DropDownList ID="prim_lang_DropDownList" runat="server" DataSourceID="SqlDataSource2"
DataTextField="language_id" DataValueField="language_id">
</asp:DropDownList>
<br />
<br />
<asp:Label ID="Label3" runat="server" Text="# of answer alternatives"></asp:Label>
<asp:TextBox ID="text_ans_alt" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="Label4" runat="server" Text="Dont know allowed"></asp:Label>
<asp:CheckBox ID="checkBox_dont_know_allowed" runat="server" /><br />
<br />
<asp:Label ID="Label5" runat="server" Text="Light level"></asp:Label>
<asp:CheckBox ID="checkBox_light_level" runat="server" /><br />
<br />
<asp:Label ID="Label6" runat="server" Text="Medium level"></asp:Label>
<asp:CheckBox ID="checkBox_medium_level" runat="server" /><br />
<br />
<asp:Label ID="Label7" runat="server" Text="Extensive level"></asp:Label>
<asp:CheckBox ID="checkBox_extensive_level" runat="server" /><br />
<br />
<asp:Button ID="button_submit" runat="server" Text="Submit" />
<asp:Label ID="label_error" runat="server" ForeColor="Red"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssesmentConnectionString2 %>"
DeleteCommand="DELETE FROM [assesment_definition] WHERE [assesment_id] = @.assesment_id"
InsertCommand="INSERT INTO [assesment_definition] ([assesment_id], [primary_language_id], [number_of_answer_alternatives], [dont_know_answer_accepted], [light_level], [medium_level], [extensive_level]) VALUES (@.assesment_id, @.primary_language_id, @.number_of_answer_alternatives, @.dont_know_answer_accepted, @.light_level, @.medium_level, @.extensive_level)"
SelectCommand="SELECT * FROM [assesment_definition]" UpdateCommand="UPDATE [assesment_definition] SET [primary_language_id] = @.primary_language_id, [number_of_answer_alternatives] = @.number_of_answer_alternatives, [dont_know_answer_accepted] = @.dont_know_answer_accepted, [light_level] = @.light_level, [medium_level] = @.medium_level, [extensive_level] = @.extensive_level WHERE [assesment_id] = @.assesment_id">
<DeleteParameters>
<asp:Parameter Name="assesment_id" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="primary_language_id" Type="String" />
<asp:Parameter Name="number_of_answer_alternatives" Type="Int32" />
<asp:Parameter Name="dont_know_answer_accepted" Type="Boolean" />
<asp:Parameter Name="light_level" Type="Boolean" />
<asp:Parameter Name="medium_level" Type="Boolean" />
<asp:Parameter Name="extensive_level" Type="Boolean" />
<asp:Parameter Name="assesment_id" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:FormParameter FormField="text_assesment_id" Name="assesment_id" Type="String" />
<asp:FormParameter FormField="prim_lang_DropDownList" Name="primary_language_id"
Type="String" />
<asp:FormParameter FormField="text_ans_alt" Name="number_of_answer_alternatives"
Type="Int32" />
<asp:FormParameter FormField="text_dontknowallowed" Name="dont_know_answer_accepted"
Type="Boolean" DefaultValue="" />
<asp:FormParameter FormField="text_lightlevel" Name="light_level" Type="Boolean" DefaultValue="false" />
<asp:FormParameter FormField="text_mediumlevel" Name="medium_level" Type="Boolean" DefaultValue="false" />
<asp:FormParameter FormField="text_extensivelevel" Name="extensive_level" Type="Boolean" DefaultValue="false" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AssesmentConnectionString2 %>"
SelectCommand="SELECT [language_id] FROM [language]"></asp:SqlDataSource>
<br />
<br />
<br />
<asp:TextBox ID="text_dontknowallowed" runat="server" CausesValidation="True"></asp:TextBox><br />
<asp:TextBox ID="text_lightlevel" runat="server"></asp:TextBox><br />
<asp:TextBox ID="text_mediumlevel" runat="server"></asp:TextBox><br />
<asp:TextBox ID="text_extensivelevel" runat="server"></asp:TextBox><br />
<br />
<br />
<asp:Label ID="testLabel" runat="server" ForeColor="Blue"></asp:Label></div>
</form>
</body>
</html>
And the code when the submit button is clicked:
Partial Class newassesment
Inherits System.Web.UI.Page
Protected Sub button_submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button_submit.Click
text_dontknowallowed.Text = checkBox_dont_know_allowed.Checked
text_lightlevel.Text = checkBox_light_level.Checked
text_mediumlevel.Text = checkBox_medium_level.Checked
text_extensivelevel.Text = checkBox_extensive_level.Checked
testLabel.Text = SqlDataSource1.InsertCommand()
Try
Dim recAff As String
recAff = SqlDataSource1.Insert()
label_error.Text = (recAff & " record inserted successfully!")
Catch SQLExp As System.Data.SqlClient.SqlException
label_error.Text = "An SQL Server Error Occurred: " & SQLExp.Message()
Catch except As Exception
label_error.Text = except.ToString()
End Try
End Sub
Protected Sub checkBox_dont_know_allowed_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles checkBox_dont_know_allowed.CheckedChanged
testLabel.Text = "HALLO"
End Sub
End Class
Do this help?
Thanks
Lumsen
|||
Why aren't you using one of the data controls (DetailsView or FormView) along with your SqlDataSource? They were meant to be used together. I suppose you could somehow make your code work properly, but it may prove difficult to get everything right. Also, FormView/DetailsView allow paging, edit, delete, insert to take place automatically and does data binding for you. By not using these controls, you're choosing to manually handle everything (sort of reinventing the wheel). Finally, I'm not sure if anyone will be willing to help you implement something so out of the ordinary.
So, my advice is for you to go with FormView control for Insert, Edit, Delete of your data. It'll make your project far easier.
|||You shouldn't be using formparameters to pull from ASP.NET controls to start. Either make them Parameters, and set them manually, or make them controlparameters to pull from an ASP.NET control, or change all your textboxes from ASP.NET controls to form controls "<INPUT TYPE=...>" and use formparameters.|||Thanks for guiding me! Works a lot better with the formview control!
Just one problem remains.. I want to make one of the fields in the formview a dropdownlist, where the items are selected from another table than the table associated with the formview. Is that possible?
Thanks!
Lumsen|||Using a DropDownList control within a FormView is possible. That topic has been covered many times before, so rather than describing the whole process again, try searching the forum for the keywords "formview dropdownlist".
