Database info saying missing but it is not

B

bluejaguar456

Well-known member
Joined
Aug 22, 2006
Messages
47
Hey guys,

I have run into a problem wth my database again :(

I cannot understand why this happens it is very strange.
When displaying the info from the databse in a label i can always get the first field becuase it is always named ref. i have 2 tables one called candidates and one called clients the candidate one works perfectly fine and always has done. when i have created the clients database i copied the fields from the candidate database and then renamed the fields to different names to suit the client needs. everytime i rename the fields my program alway throws back dbnull cannot be converted to string there is information in the database but it says this. if i keep the same column names as the candidate database it works fine.

Any help on this would be greatly appreciated :)
 
Yes this is the part for the candidates which works correctly.

Code: 
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & vardatabaseloc

con.open()

        If Not Trim(txtref.Text) = "" Then
            lbltotalresults.Text = "Search Results for: " & " " & Trim(txtref.Text) & " "
            sql = "SELECT * FROM " & table & "  WHERE [Ref] LIKE %" & Trim(txtref.Text) & "%"
        End If
        If Not Trim(txtfirstname.Text) = "" Then
            If sql = Nothing Then
                lbltotalresults.Text = "Search Results for: " & Trim(txtfirstname.Text) & " "
                sql = "SELECT * FROM " & table & " WHERE [FirstName] LIKE %" & Trim(txtfirstname.Text) & "%"
            Else
                lbltotalresults.Text = "Search Results for: " & Trim(txtfirstname.Text) & " "
                sql = sql & " AND  [FirstName] LIKE %" & Trim(txtfirstname.Text) & "%"
            End If
        End If
        If Not Trim(txtsurname.Text) = "" Then
            If sql = Nothing Then
                lbltotalresults.Text = "Search Results for: " & Trim(txtsurname.Text) & " "
                sql = "SELECT * FROM " & table & " WHERE [LastName] LIKE %" & Trim(txtsurname.Text) & "%"
            Else
                lbltotalresults.Text = lbltotalresults.Text & " " & Trim(txtsurname.Text) & " "
                sql = sql & " AND [LastName] LIKE %" & Trim(txtsurname.Text) & "%"
            End If
        End If
        If Not Trim(txtdob1.Text) = "" And Not Trim(txtdob2.Text) = "" And Not Trim(txtdob3.Text) = "" Then
            If sql = Nothing Then
                lbltotalresults.Text = "Search Results for: " & " " & Trim(txtdob1.Text) & "/" & Trim(txtdob2.Text) & "/" & Trim(txtdob3.Text) & " " _
                sql = "SELECT * FROM " & table & " WHERE [DOB] LIKE %" & Trim(txtdob1.Text) & "/" & Trim(txtdob2.Text) & "/" & Trim(txtdob3.Text) & "%"
            Else
                lbltotalresults.Text = lbltotalresults.Text & " " & Trim(txtdob1.Text) & "/" & Trim(txtdob2.Text) & "/" & Trim(txtdob3.Text) & " " _
                sql = sql & " AND [DOB] LIKE %" & Trim(txtdob1.Text) & "/" & Trim(txtdob2.Text) & "/" & Trim(txtdob3.Text) & "%"
            End If
        End If
        If sql = Nothing Then
            sql = "SELECT * FROM " & table
        End If

        \\Data adapter settings and then fills it.
        dacand = New OleDb.OleDbDataAdapter(sql, con)
        dacand.Fill(dscand, vardatabaseloc)

        \\close connection to the database.
        con.Close()

        \\Goes to the showallcandidates sub.
        showallcandidates()

        \\The following is the showallcandidates sub

        If sql = "SELECT * FROM " & table Then
            lblnofound.Text = "Total candidates for " & varcompanyname & ": " & maxrows
            lbltotalresults.Text = "Search Results for: All Candidates"
        Else
            lblnofound.Text = "Number of candidates found: " & maxrows
        End If

        \\Counts how many rows/records have been found.
        maxrows = dscand.Tables(vardatabaseloc).Rows.Count

        If inc = maxrows - 1 Then
            Exit Sub
        Else
            \\Clear all search lbls first
            clearsearchlbl()
        End If
           
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult1.Text = dscand.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dscand.Tables(vardatabaseloc).Rows(inc).Item(1) & " " & dscand.Tables(vardatabaseloc).Rows(inc).Item(2)
        Else
            Exit Sub
        End If
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult2.Text = dscand.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dscand.Tables(vardatabaseloc).Rows(inc).Item(1) & " " & dscand.Tables(vardatabaseloc).Rows(inc).Item(2)
        Else
            lnkresult2.Text = Nothing
            lnkresult3.Text = Nothing
            lnkresult4.Text = Nothing
            Exit Sub
        End If
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult3.Text = dscand.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dscand.Tables(vardatabaseloc).Rows(inc).Item(1) & " " & dscand.Tables(vardatabaseloc).Rows(inc).Item(2)
        Else
            lnkresult3.Text = Nothing
            lnkresult4.Text = Nothing
            Exit Sub
        End If
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult4.Text = dscand.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dscand.Tables(vardatabaseloc).Rows(inc).Item(1) & " " & dscand.Tables(vardatabaseloc).Rows(inc).Item(2)
        Else
            lnkresult4.Text = Nothing
            Exit Sub
        End If



And this is the client part as far as i can see because i have copied the code over it is all the same.

Code: 
If Not Trim(txtref.Text) = "" Then
            lbltotalresults.Text = "Search Results for: " & " " & Trim(txtref.Text) & " "
            sql = "SELECT * FROM " & table & "  WHERE [Ref] LIKE %" & Trim(txtref.Text) & "%"
        End If
        If Not Trim(txtfirstname.Text) = "" Then
            If sql = Nothing Then
                lbltotalresults.Text = "Search Results for: " & Trim(txtfirstname.Text) & " "
                sql = "SELECT * FROM " & table & " WHERE [Name] LIKE %" & Trim(txtfirstname.Text) & "%"
            Else
                lbltotalresults.Text = "Search Results for: " & Trim(txtfirstname.Text) & " "
                sql = sql & " AND  [Name] LIKE %" & Trim(txtfirstname.Text) & "%"
            End If
        End If
        If sql = Nothing Then
            sql = "SELECT * FROM " & table
        End If
        
        \\Data adapter settings and then fills it.
        daclient = New OleDb.OleDbDataAdapter(sql, con)
        daclient.Fill(dsclient, vardatabaseloc)

        \\close connection to the database.
        con.Close()

        \\Goes to the showallcandidates sub.
        showallclients()


        \\The following is the showallclients sub 

        If sql = "SELECT * FROM " & table Then
            lblnofound.Text = "Total clients for " & varcompanyname & ": " & maxrows
            lbltotalresults.Text = "Search Results for: All Clients"
        Else
            lblnofound.Text = "Number of clients found: " & maxrows
        End If

\\Counts how many rows/records have been found.
        maxrows = dsclient.Tables(vardatabaseloc).Rows.Count

        If inc = maxrows - 1 Then
            Exit Sub
        Else
            \\Clear all search lbls first
            clearsearchlbl()
        End If
        
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult1.Text = dsclient.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)
        Else
            Exit Sub
        End If
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult2.Text = dsclient.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)
        Else
            lnkresult2.Text = Nothing
            lnkresult3.Text = Nothing
            lnkresult4.Text = Nothing
            Exit Sub
        End If
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult3.Text = dsclient.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)
        Else
            lnkresult3.Text = Nothing
            lnkresult4.Text = Nothing
            Exit Sub
        End If
        If inc <> maxrows - 1 Then
            inc = inc + 1
            lnkresult4.Text = dsclient.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)
        Else
            lnkresult4.Text = Nothing
            Exit Sub
        End If

Thanks for your reply :)
 
Last edited by a moderator:
Code: 
lnkresult1.Text = dsclient.Tables(vardatabaseloc).Rows(inc).Item(0) & " / " & dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)


That is the line that displays the info and it diesplayed the
Code: 
dsclient.Tables(vardatabaseloc).Rows(inc).Item(0)

fine but this line is not displaying but not displaying an error message.

Code: 
dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)

it is as if it is never run, because if i delete the second part and change the first part to
Code: 
dsclient.Tables(vardatabaseloc).Rows(inc).Item(1)

that then it throws back the dnull to string error message.

Thanks
 
Does the column contain nulls? If so you need to check for null before assigning the contents to a label / variable?

Just as an aside I would tend to avoid using SELECT * and actually list the columns I need as this makes he code more readable and prevents strange errors if the underlying table changes ion the future; similarly I would tend to refer to the columns by name rather than an offset when accessing the data tables.

You might also want to look at parametrising the query rather than relying on string concatenation as this can sometimes introduce hard to track down issues.
 
yes i have tried to use the column names but then it throws back an error saying the column name does not exist in the table. No fields in the database are null they all contain something.

Thanks
 
If you look at the dataset / datatable in the debugger are the column names the same as you would expect to see?
 
Last edited by a moderator:
yes they are, if i change the table to the candidate table everything works fine again. do you think it is something to do with the database?
 

Similar threads

V
Why Add & Save Button on Navigation Toolbar of VS 2015 Database Programming is Not working?
Replies
0
Views
74
V.K.S.B.K
V
C
There’s already one coronavirus vaccine that has been approved, but does it actually work?
Replies
0
Views
92
Chris Smith
C
J
reader.hasrows is false
Replies
0
Views
57
J Vallee
J
R
DAO Database functionality in C++ called from a 64 bit app.
Replies
0
Views
911
Roy Holmberg
R
A
C# WCF service randomly crashing while loading data from SQL Server
Replies
0
Views
116
Alberto20
A
Back
Top