-
Apr 24th, 2012, 06:05 AM
#1
Thread Starter
New Member
SQL query returning null values for few columns
Hi,
I am trying to use an excel worksheet as database.
The query to fetch records is working fine except for the fact that it is returning null values for few columns where data is present
The query I am using is
With cn
.Provider = "Microsoft.Jet.OLEDB.12.0"
.ConnectionString = "Data Source=N:\sneha\trial\BMT_3.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
Set rs = cn.Execute("SELECT DISTINCT * FROM [NLX_FIN$A:BU]WHERE FIN= '" & bpidPMR & "' and F4='" & accTypePMR & "' and F8='" & instIDPMR & "' ")
Do Until rs.EOF
-
Apr 24th, 2012, 07:10 AM
#2
Re: SQL query returning null values for few columns
yeah... that happens sometimes when the data changes causing a confusion of datatypes... Excel data isn't necessarily typed... so it has to infer the data type when you select it... I forget if it uses just the first row, or the first couple of rows... but the point is that the first couple lines of data ... so if it sees numbers in a column, it assumes it's a numeric column and will process it as such. If it encounters non-numeric data, it can't change the datatype in the datatable (it's too late) ... so it simply assigns NULL to the non-numeric cells.
-tg
-
Apr 24th, 2012, 07:44 AM
#3
Thread Starter
New Member
Re: SQL query returning null values for few columns
Thanks tg...I have already set the format of all cells as general.
So datatype should be an issue i guess
-
Apr 24th, 2012, 08:21 AM
#4
Re: SQL query returning null values for few columns
that jsut controls the format anyways... it doesn't affect the datatype... even if you format as a number of some kind... because in theory you can still enter "ABC" if you wanted to.
-tg
-
Apr 24th, 2012, 08:26 AM
#5
Thread Starter
New Member
Re: SQL query returning null values for few columns
yes.
got the problem... there was a row of merged cells above it..probably that was the reason of the problem
thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|