Database info saying missing but it is not

bluejaguar456 · February 5, 2009

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 :)

PlausiblyDamp · February 6, 2009

Any chance you could post the code you are using in both cases?

bluejaguar456 · February 6, 2009

Yes this is the part for the candidates which works correctly.


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.



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 :)

Edited February 8, 2009 by PlausiblyDamp

PlausiblyDamp · February 7, 2009

Which line(s) is it failing on?

bluejaguar456 · February 7, 2009

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

dsclient.Tables(vardatabaseloc).Rows(inc).Item(0)

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

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

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

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

Thanks

PlausiblyDamp · February 7, 2009

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.

bluejaguar456 · February 7, 2009

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

PlausiblyDamp · February 8, 2009

If you look at the dataset / datatable in the debugger are the column names the same as you would expect to see? Edited February 8, 2009 by PlausiblyDamp

bluejaguar456 · February 8, 2009

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?

PlausiblyDamp · February 14, 2009

When you are using the client table does the dataset look correct in the debugger? i.e expected column names and contained data.

bluejaguar456 · February 14, 2009

how can i check this?

PlausiblyDamp · February 14, 2009

Just put a breakpoint on the line that fails, then in the debugger you can inspect the variables contents.

Sign In

Database info saying missing but it is not

Recommended Posts

bluejaguar456

PlausiblyDamp

bluejaguar456

PlausiblyDamp

bluejaguar456

PlausiblyDamp

bluejaguar456

PlausiblyDamp

bluejaguar456

PlausiblyDamp

bluejaguar456

PlausiblyDamp

Join the conversation

Browse

Activity