insert query fails (if form fields left empty)

Dear All,

I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".

As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?

<script language="VB" runat="server" >

Sub Page_Load(Src As Object, e As EventArgs)


If Page.IsPostBack Then

Dim ConLath As SqlConnection
Dim comLath As SqlCommand
Dim insertcmd

conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password")
ConLath.Open()
insertcmd = "Insert into His_set values (@.t_d,@.s_p,@.p_s,@.v_oq,@.i_oq,@.v_qn,@.i_qn,@.v_qw,@.i_qw)"

comLath = New SqlCommand(insertcmd, ConLath)


comLath.Parameters.Add(New SqlParameter("@.t_d", SqlDbType.DateTime, 12))
comLath.Parameters("@.t_d").Value = trade_date.Text
comLath.Parameters.Add(New SqlParameter("@.s_p", SqlDbType.Decimal, 8))
comLath.Parameters("@.s_p").Value = sett_price.Text
comLath.Parameters.Add(New SqlParameter("@.p_s", SqlDbType.Decimal, 8))
comLath.Parameters("@.p_s").Value = post_close.Text
comLath.Parameters.Add(New SqlParameter("@.v_oq", SqlDbType.Int, 8))
comLath.Parameters("@.v_oq").Value = vol_oq.Text
comLath.Parameters.Add(New SqlParameter("@.i_oq", SqlDbType.Int, 8))
comLath.Parameters("@.i_oq").Value = oi_oq.Text
comLath.Parameters.Add(New SqlParameter("@.v_qn", SqlDbType.Int, 8))
comLath.Parameters("@.v_qn").Value = vol_qn.Text
comLath.Parameters.Add(New SqlParameter("@.v_qw", SqlDbType.Int, 8))
comLath.Parameters("@.v_qw").Value = vol_qw.Text
comLath.Parameters.Add(New SqlParameter("@.i_qn", SqlDbType.Int, 8))
comLath.Parameters("@.i_qn").Value = oi_qn.Text
comLath.Parameters.Add(New SqlParameter("@.i_qw", SqlDbType.Int, 8))
comLath.Parameters("@.i_qw").Value = oi_qw.Text

Try
comLath.ExecuteNonQuery()

Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try

comLath.Dispose()
ConLath.Close()




End If
End Sub

</script>

I'm not surprised if it fails when you leave the mandatory field empty. But I assume that's not what you meant, right?

The problem here relates to casting. Your empty text box returns an empty string. This would be fine for a varchar column, but if you try this with a column of type int, it will fail. You need to explicitly insert a null value in this case.

You could try something like this:

comLath.Parameters("@.v_oq").Value = (vol_oq.Text =="" ? DBNull.Value : vol_oq.Text);

|||

thanks for your prompt reply.

apparently the conditional operator ? works if u are using C#.

I am using the language vb, this implies i will have to use the if and then conditonal block for each, right?

|||

VB has the tertial operator IIF which is similar to ? operator, although it behaves somewhat differently (VB evaluates all parameters). 

comLath.Parameters("@.v_oq").Value = IIF(vol_oq.Text =="", DBNull.Value, vol_oq.Text)

|||

thanks so much it worked :)

however, the issue now is when i try to display the columns with null values, it reports an error -

i have explicitly casted these values with their corresponding data types to defualt value other than null. But the problem is like for eg, in case of any integer type it i set it to default of "0", for our business purpose its misleading as they would be expecting the sell of items for that day to be "0".

this is my code:

Public Function CheckDBNull(ByVal obj As Object, _
Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object
Dim objReturn As Object
objReturn = obj
If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then
objReturn = ""
ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then
objReturn = 0
ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then
objReturn = 0.0
End If
Return objReturn
End Function

|||

Then what do you want to display if an integer column is null? If you want to leave that field blank, simply return an empty string...

|||hi thanks for all your help... i have resolved the above query... much appreciated