SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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>

  2. #2
    SitePoint Member
    Join Date
    Jul 2007
    Location
    Bangalore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi mukesh,
    In the real world environment, you need to check for null values Before inserting the row into backend database. For that, we need to check each and every field whether it is blank or not.

    With short span of time, I couldn't able to concentrate on your table, but no problem.
    My table is Dept1(deptno, dname, loc) and nothing is mandatory.
    please observe how i've checked for null values.

    My version of code is:

    objConn = new SqlConnection("Workstation ID=Rosesoft;User ID=sa; Password=secret ;Database=ASHOK");
    string intDeptNo = (Convert.ToInt32(TextBox1.Text)!=0)?TextBox1.Text : null;
    string strDName = (TextBox2.Text != null)? TextBox2.Text: null;
    string strLoc = (TextBox2.Text != null)? TextBox3.Text: null;
    SqlCommand objCmd = new SqlCommand("Insert into dept1 values(@deptno,@dname,@loc)",objConn);
    objCmd.Parameters.Add("@deptno",SqlDbType.Int).Value = intDeptNo;
    objCmd.Parameters.Add("@dname",SqlDbType.VarChar,20).Value = strDName;
    objCmd.Parameters.Add("@loc",SqlDbType.VarChar,20).Value = strLoc;
    objConn.Open();
    objCmd.ExecuteNonQuery();

  3. #3
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thanks its resolved

    hi thanks for all your help...

    i have resolved the above query...

    much appreciated


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •