issue loading a tble

[dbsquared]

seems really dumb as I do it in other parts of my code but I have this class that is to query a database and put some data into a table for it and then just update the table. I have established all the database connections because it worked once and won't work anymore.
Can anyone see what I am doing wrong.
Thanks

SQL2 = "SELECT ID,Site,Current_Date,Color"
SQL2 += " FROM " + TableName

Dim Adp As New OleDb.OleDbDataAdapter(SQL2, conAccess)
Dim Tbl2 As New DataTable()

Adp.Fill(Tbl2)  'here is where i get the failure error E_FAIL 0X80004005 which when I looked it up was a general windows error.

To go where no programmer has gone before.

 

[jubble]

Is the 'TableName' variable OK?

Apart from that it looks fine. Either a problem with SQL or Windows.

 

[dbsquared]

Sorry about that yes it shows up as in the code

SELECT ID,Site,Current_Date,Color FROM tblDirectorComments

I have taken the SQL and copied it into the database and it returned what I wanted. I am just having trouble with the line Adp.fill(tbl2)

To go where no programmer has gone before.

 

[ThatRickGuy]

try this:

  '...
  try 
    Adp.Fill(Tbl2)
  catch exc as exception
    debug.writeline(exc.message)
    if not exc.innerexception is nothing then 
      debug.writeline(exc.innerexception.message)
    end if
  end try

Sometimes OLE errors get wrapped inside of a generic error message.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[dbsquared]

I got more info thank you very much for helping
Here are the new error messages:

The Application failed to Initialize properly (0xc0000142)

Unspecified error: E_FAIL(0x80004005)
An unhandled exception of type 'System.NullReferenceException' occurred in JITTER.exe

why am I getting that when everything is initialized? could it be that before I call this part of the class I call another sub in the class that queries the database to see if there is anything in it before I add data.
here is the code that calls the class I am doing eveything correct aint I?

 Dim dicom As New DirectorComments()'my class
 Dim Datatest As OleDb.OleDbDataReader
 Dim Check As Integer
 Datatest = dicom.GetExistingComments("tblDirectorComments", SwitchName(I), PegName, BeginDate)
 Do Until Datatest.Read = False
   If Not Datatest.IsDBNull(0) Then
     .Cells(RedRow, 3).Value = Datatest.GetValue(0)
     Check = 1  ' this is the test to see if this record already exiist in database.
   End If
 Loop
 dicom.CloseDatRead()
 Datatest.Close()
 Datatest = Nothing
 dicom.Dispose()'close all database connections 
 dicom = Nothing
 Dim dircom As New DirectorComments()
 If Check = 1 Then  ' If I have this record in database do this
   dircom.UpdateExistingPeg("tblDirectorComments", SwitchName(I), PegName, BeginDate, "R")' here is sub I get the error in other sub not tested yet.
 Else
   dircom.UpdateNewPeg("tblDirectorComments", SwitchName(I), PegName, BeginDate, "R")
 End If
 dircom.Dispose()
 dircom = Nothing

To go where no programmer has gone before.

 

[ThatRickGuy]

put a break point at the .fill line. Ensure that your connection is valid and open and that the SQL is properly formated.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[dbsquared]

I have put in the break point the SQL is correct, how do I make sure the connection is valid and active?

To go where no programmer has gone before.

 

[dbsquared]

The connection.objectstate=1
so I did a test : If conAccess.State = ConnectionState.Open Then
and it passed this test so that would make me think the database connection is open.

the if statement failed when the error occured.

If Not exc.InnerException Is Nothing Then
Debug.WriteLine(exc.InnerException.Message)
End If



To go where no programmer has gone before.

 

[dbsquared]

What seems strange that in another sub in the class I do almost the same SQL the only diferece is in the select line here are the 2 sql's

[sql]
SQL = "SELECT Initial_Date,Comments, Initital_Comment_Date, Comment_Update"
SQL2 = "SELECT ID,Site,Current_Date,Color"
[/sql]

The first SQL works everytime and returns the results I want. That SQL is part of the first sub in the class that I call to see if the specified record already exists.

in the first SQL i return a datareader and in the second I try to load a table why wouldn't that work?

To go where no programmer has gone before.

 

[ThatRickGuy]

Err, both of those SQL statements are missing the FROM clause... Also, double check and make sure you have all of your field names spelled correctly.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[dbsquared]

the from statements are the same as before
" FROM " + TableName ' tablename = tblDirectorComments

I have double checked the fields in the SQL and they are all correct.
I also double checked the whole SQL statement by debug printing the sql and taking that to the database and running it. This still works and returns what I want.

I have no idea why i can't load the data into a table the fields are only text and dates, nothing complex.

To go where no programmer has gone before.

 

[dbsquared]

I decided to do some more testing I have found why I get the error however I don't know the reason.
The cause of the error is I have to load the table before I load the data reader. even if I close the datareader first it doesn't matter it throws an error.

Anybody have a reason why this order would throw an error?
Thanks for all your help.

To go where no programmer has gone before.

 

[ThatRickGuy]

There's a datareader? All I saw was the data adapter and table.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[dbsquared]

never mind all that I found that one of my database fields, namely Color, is a reserved word and was causing all the errors.

To go where no programmer has gone before.

 

[jubble]

That's an interesting undocumented feature of SQL...allowing you to create a column using a keyword when it doesn't allow you to access it.
Another Microsoft moment.