6 Replies - 1024 Views - Last Post: 31 July 2013 - 12:00 PM Rate Topic: -----

#1 raelkun00  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 99
  • Joined: 01-October 12

Error in loading empty field from DB to textbox

Posted 31 July 2013 - 11:09 AM

I am having error if one of the fields in MS Database is empty and I will load it in textbox
Here is my code.

 Dim ObjCon As New dbConnect
        ObjCon.con.Open()
        Dim da As New OleDbDataAdapter '("SELECT * FROM customer", ObjCon.con)
        Dim dt As New DataTable
        Try
            Dim cmd = New OleDbCommand("Select * from tbl_Customer where cust_company='" & cboSearch.Text & "'", ObjCon.con)
            da = New OleDbDataAdapter(cmd)
            da.Fill(dt)
            txtCstID.Text = dt.Rows(0).Item(0)
            txtCstFName.Text = dt.Rows(0).Item(1)
            txtCstLName.Text = dt.Rows(0).Item(2)
            txtCstCompany.Text = dt.Rows(0).Item(3)
            txtCstAddress.Text = dt.Rows(0).Item(4)
            cboCstCity.Text = dt.Rows(0).Item(5)
            txtCstTel.Text = dt.Rows(0).Item(6)
            txtCstEmail.Text = dt.Rows(0).Item(7)
            txtCstWeb.Text = dt.Rows(0).Item(8)
            txtCstFax.Text = dt.Rows(0).Item(9)
            ObjCon.con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try



Here is the error I am getting
Posted Image

This post has been edited by raelkun00: 31 July 2013 - 11:11 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Error in loading empty field from DB to textbox

#2 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Error in loading empty field from DB to textbox

Posted 31 July 2013 - 11:10 AM

What is the error you are getting? At what line does it occur?

Also I would recommend that you use Parameterized queries in your OleDbCommand. like so

Dim cmd = New OleDbCommand("Select * from tbl_Customer where [email protected]", ObjCon.con)
cmd.Parameters.AddWithValue("@cboSearch", cboSearch.Text)
da = New OleDbDataAdapter(cmd)
.
.
.



This makes the SQL statements easier to read as well as helping against SQL Injection

This post has been edited by ybadragon: 31 July 2013 - 11:14 AM

Was This Post Helpful? 0
  • +
  • -

#3 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Error in loading empty field from DB to textbox

Posted 31 July 2013 - 11:15 AM

Ok, we need to know the line that it is erroring on in the code you provided, from what it looks like you are trying to store a "Null" value into your textbox, which isn't allowed.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6541
  • View blog
  • Posts: 26,484
  • Joined: 12-December 12

Re: Error in loading empty field from DB to textbox

Posted 31 July 2013 - 11:18 AM

You haven't said which field it is :whistling: but you might add the following to a number of the fields, to handle DBNull:

txtCstFName.Text = If(IsDBNull(dt.Rows(0).Item(1)), "", CStr(dt.Rows(0).Item(1)))

This post has been edited by andrewsw: 31 July 2013 - 11:21 AM

Was This Post Helpful? 1
  • +
  • -

#5 raelkun00  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 99
  • Joined: 01-October 12

Re: Error in loading empty field from DB to textbox

Posted 31 July 2013 - 11:51 AM

I get error if I store a null value from Database to textbox. I can get error from any fields if its null and I will store it in textbox.
Still have error.
 Dim ObjCon As New dbConnect
        ObjCon.con.Open()
        Dim da As New OleDbDataAdapter '("SELECT * FROM customer", ObjCon.con)
        Dim dt As New DataTable
        Try
            Dim cmd = New OleDbCommand("Select * from tbl_Customer where cust_firstname='" & cboSearch.Text & "'", ObjCon.con)
            da = New OleDbDataAdapter(cmd)
            da.Fill(dt)
            txtCstID.Text = dt.Rows(0).Item(0)
            txtCstFName.Text = If(IsDBNull(dt.Rows(0).Item(1)), "", CStr(dt.Rows(0).Item(1)))
            txtCstLName.Text = dt.Rows(0).Item(2)
            txtCstCompany.Text = dt.Rows(0).Item(3)
            txtCstAddress.Text = dt.Rows(0).Item(4)
            cboCstCity.Text = dt.Rows(0).Item(5)
            txtCstTel.Text = dt.Rows(0).Item(6)
            txtCstEmail.Text = dt.Rows(0).Item(7)
            txtCstWeb.Text = dt.Rows(0).Item(8)
            txtCstFax.Text = dt.Rows(0).Item(9)

            ObjCon.con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try


Was This Post Helpful? 0
  • +
  • -

#6 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Error in loading empty field from DB to textbox

Posted 31 July 2013 - 11:54 AM

You need to tell us what line in the code you've provided the error occurs on. Put a breakpoint on da.Fill(dt) and step through the code to figure out where the error occurs.
Was This Post Helpful? 0
  • +
  • -

#7 raelkun00  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 99
  • Joined: 01-October 12

Re: Error in loading empty field from DB to textbox

Posted 31 July 2013 - 12:00 PM

Thank you, Your suggestion was right. I just put it in a wrong method.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1