PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2013 write Null to sql server ?-VBForums
Results 1 to 12 of 12

Thread: write Null to sql server ?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2015
    Posts
    66

    write Null to sql server ?

    hello
    when i try write single user null to sql am getting nothing in my db sql

    my code
    Code:
     Private Sub RadioButton25_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton25.CheckedChanged
            AdminTextBox.Text = DBNull.Value.ToString
            SuperadminTextBox.Text = DBNull.Value.ToString
            SuperTextBox.Text = DBNull.Value.ToString
        End Sub
    i have other code for all user
    its work very well
    Code:
    Dim sql = <sql>
                UPDATE [dbo].[Table_infoname] SET [admin] = NULL
                UPDATE [dbo].[Table_infoname] SET [superadmin] = NULL
                UPDATE [dbo].[Table_infoname] SET [super] = NULL
              </sql>
    
            Using connection As New SqlConnection("Data Source=KANKON\SQLEXPRESS;Initial Catalog=kankon;Persist Security Info=True;User ID=aboahmed2;Password=12345"),
                  command As New SqlCommand(sql.Value, connection)
                connection.Open()
                command.ExecuteNonQuery()
            End Using

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,394

    Re: write Null to sql server ?

    How are you trying to use those values from your various TextBox controls? My guess is that you are not using prepared statements causing the value to be passed an empty String instead of NULL.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2015
    Posts
    66

    Re: write Null to sql server ?

    am using statements
    Attached Images Attached Images  

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,394

    Re: write Null to sql server ?

    Hmm, I still think that the value passed would be an empty String rather than NULL. Whenever you pass the parameter values to the statement, check if the value of your TextBox is an empty String and if so then pass the value of DBNull:
    Code:
    If(String.IsNullOrWhitespace(AdminTextBox.Text), DBNull.Value, AdminTextBox.Text)

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2015
    Posts
    66

    Re: write Null to sql server ?

    Quote Originally Posted by dday9 View Post
    Hmm, I still think that the value passed would be an empty String rather than NULL. Whenever you pass the parameter values to the statement, check if the value of your TextBox is an empty String and if so then pass the value of DBNull:
    Code:
    If(String.IsNullOrWhitespace(AdminTextBox.Text), DBNull.Value, AdminTextBox.Text)
    sorry did not understand you what do you mean , should add then code with my code like this ?
    Code:
    If(String.IsNullOrWhitespace(AdminTextBox.Text), DBNull.Value, AdminTextBox.Text) then
     AdminTextBox.Text = DBNull.Value.ToString
    end if
    there is error : Error ')' expected.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,409

    Re: write Null to sql server ?

    Quote Originally Posted by kankon View Post
    hello
    when i try write single user null to sql am getting nothing in my db sql

    my code
    Code:
     Private Sub RadioButton25_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton25.CheckedChanged
            AdminTextBox.Text = DBNull.Value.ToString
            SuperadminTextBox.Text = DBNull.Value.ToString
            SuperTextBox.Text = DBNull.Value.ToString
        End Sub
    How is that code writing anything to any database? All it's doing is displaying empty Strings in three TextBoxes. If you want to save something to a database then you have to open a connection and execute some SQL.
    Quote Originally Posted by kankon View Post
    i have other code for all user
    its work very well
    Code:
    Dim sql = <sql>
                UPDATE [dbo].[Table_infoname] SET [admin] = NULL
                UPDATE [dbo].[Table_infoname] SET [superadmin] = NULL
                UPDATE [dbo].[Table_infoname] SET [super] = NULL
              </sql>
    
            Using connection As New SqlConnection("Data Source=KANKON\SQLEXPRESS;Initial Catalog=kankon;Persist Security Info=True;User ID=aboahmed2;Password=12345"),
                  command As New SqlCommand(sql.Value, connection)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
    That code's a bit silly. Why execute three separate UPDATE statements? You only need one:
    vb.net Code:
    1. Dim sql = <sql>
    2.             UPDATE [dbo].[Table_infoname] SET [admin] = NULL, [superadmin] = NULL, [super] = NULL
    3.           </sql>
    If you ant to affect one record rather than every record then you need to add a WHERE clause to that SQL code that identifies that record, which usually means specifying a value for the primary key column(s).
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,409

    Re: write Null to sql server ?

    Quote Originally Posted by kankon View Post
    sorry did not understand you what do you mean , should add then code with my code like this ?
    Code:
    If(String.IsNullOrWhitespace(AdminTextBox.Text), DBNull.Value, AdminTextBox.Text) then
     AdminTextBox.Text = DBNull.Value.ToString
    end if
    there is error : Error ')' expected.
    That If expression produces a value that you can assign to a parameter or the like, e.g.
    vb.net Code:
    1. mySqlCommand.Parameters("@admin").Value = If(String.IsNullOrWhitespace(AdminTextBox.Text), DBNull.Value, AdminTextBox.Text)
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Feb 2015
    Posts
    66

    Re: write Null to sql server ?

    Quote Originally Posted by jmcilhinney View Post
    How is that code writing anything to any database? All it's doing is displaying empty Strings in three TextBoxes. If you want to save something to a database then you have to open a connection and execute some SQL.
    am using button save data
    Code:
     Private Sub Button40_Click(sender As Object, e As EventArgs) Handles Button40.Click
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.Table_infonameTableAdapter.Update(Me.KankonDataSet.Table_infoname)
        End Sub

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Feb 2015
    Posts
    66

    Re: write Null to sql server ?

    Quote Originally Posted by jmcilhinney View Post
    That If expression produces a value that you can assign to a parameter or the like, e.g.
    vb.net Code:
    1. mySqlCommand.Parameters("@admin").Value = If(String.IsNullOrWhitespace(AdminTextBox.Text), DBNull.Value, AdminTextBox.Text)
    sorry mySqlCommand is this mean my connection ?
    or i need make new connection
    Code:
    dim mySqlCommand as new New SqlConnection("Data Source=KANKON\SQLEXPRESS;Initial Catalog=kankon;Persist Security Info=True;User ID=aboahmed2;Password=12345")

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,409

    Re: write Null to sql server ?

    Why are you calling ExecuteNonQuery on a SqlCommand if you have a typed DataSet? If you're going to use a typed DataSet then use it, i.e. only use table adapters to retrieve and save data.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Feb 2015
    Posts
    66

    Re: write Null to sql server ?

    am still get nothing

    isn't like this ?
    Code:
     'Admin
        Private Sub RadioButton1_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton1.CheckedChanged
            AdminTextBox.Text = "admin"
            SuperadminTextBox.Text = DBNull.Value.ToString
            SuperTextBox.Text = DBNull.Value.ToString
    
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KankonDataSet)
        End Sub
    
        Private Sub RadioButton2_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton2.CheckedChanged
            AdminTextBox.Text = DBNull.Value.ToString
    
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KankonDataSet)
        End Sub
    
        'Superadmin
        Private Sub RadioButton4_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton4.CheckedChanged
            AdminTextBox.Text = DBNull.Value.ToString
            SuperadminTextBox.Text = "superadmin"
            SuperTextBox.Text = DBNull.Value.ToString
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KankonDataSet)
        End Sub
    
        Private Sub RadioButton3_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton3.CheckedChanged
            SuperadminTextBox.Text = DBNull.Value.ToString
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KankonDataSet)
        End Sub
    
        'Super
        Private Sub RadioButton6_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton6.CheckedChanged
            AdminTextBox.Text = DBNull.Value.ToString
            SuperadminTextBox.Text = DBNull.Value.ToString
            SuperTextBox.Text = "super"
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KankonDataSet)
        End Sub
    
        Private Sub RadioButton5_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton5.CheckedChanged
            SuperTextBox.Text = DBNull.Value.ToString
            Me.Validate()
            Me.Table_infonameBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KankonDataSet)
        End Sub
    Attached Images Attached Images   

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,409

    Re: write Null to sql server ?

    Stop doing this:
    vb.net Code:
    1. AdminTextBox.Text = DBNull.Value.ToString
    You appear to think that you're storing NULL in a TextBox but you're not. You're calling ToString so you're obviously storing a String. If you had read the documentation for that ToString method then you'd have seen this:
    Returns an empty string (String.Empty).
    All you're doing is this:
    vb.net Code:
    1. AdminTextBox.Text = String.Empty
    Now, having said that, Bindings are usually configured such that an empty String in the control causes a DBNull.Value in the data source and vice versa. I would suggest that you check the Binding for those TextBoxes and specifically the NullValue and DataSourceNullValue properties.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width