Invalid column name error? - Database Discussion Boards

helelark1234-Aug-09 18:44

4-Aug-09 18:44

Private Function GetPathPicturesFromDb(ByVal Period As String, ByVal Location As String) As String
Dim ds As New DataSet("Pictures")
Dim strSQL As String = "SELECT FullFileName FROM PicturesTable " & _
"WHERE PERIOD='" & Period & "' " & _
"AND LOCATION='" & Location & "'"

Dim sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("MemberShipConnectionString").ConnectionString)
sqlConnection.Open()
Dim da As New SqlDataAdapter()
da.SelectCommand = New SqlCommand(strSQL, sqlConnection)
da.Fill(ds)

If ds.Tables(0).Rows.Count > 0 Then _
Return ds.Tables(0).DefaultView(0).Row("FullFileName").ToString.Trim

Return ""

End Function

The Location field contains hebrew words and when I am querying by changing the database content in english it works perfectly with the same code I post upstair. So I am sure that the problem is because the hebrew. I need to config anywhere that the Location column can contain hebrew characters. Again, I success to insert hebrew words in the databse but when I am querying no records are found
Thanks

Shay Noy

Re: SQL Server config

N a v a n e e t h4-Aug-09 17:52

N a v a n e e t h

4-Aug-09 17:52

shaynoy wrote:
The problem is when I query, I got 0 records and this is because the data is stored in hebrew.

How do you know that is the problem? AFAIK, if table has records, query will return irrespective of the type of data. Are you sure the table has records? What data type are you using for keeping values?

Navaneeth

How to use google | Ask smart questions

Re: SQL Server config

helelark1234-Aug-09 18:46

helelark123

4-Aug-09 18:46

This is a new table that I insert record in it and yes I am sure there are records and i am sure that my query is OK. I just need to know how to config the column that store hebrew characters to get abiility for query on it

Shay Noy

The ORA-01033: ORACLE initialization or shutdown in progress

vikash_singh4-Aug-09 5:59

vikash_singh

4-Aug-09 5:59

hi guys please help me.

while connecting to oracle database it shows the following error.

The ORA-01033: ORACLE initialization or shutdown in progress

i tried to login from command prompt..

c:\sqlplus/nolog
sql:\connect sys/manager as sysdba

but is throws error:- "insufficient privileges".
cannot connect to Database.....

i restarted all oracle services.
but the same error continues.....

from last 2 days it is showing the same error.

Re: The ORA-01033: ORACLE initialization or shutdown in progress

suresh.palghar1-Sep-09 23:31

suresh.palghar

1-Sep-09 23:31

Have you logged onto OS as owner of Oracle software?

Loading(Importing) Data into SQL Server 2005

sm_sadhik4-Aug-09 5:00

sm_sadhik

4-Aug-09 5:00

Hi,

I would like to load(import) data from my non-unicode database backup files to Unicode database instance in SQL Server 2005.

How to achieve the above requirement? if anyone knows the steps, it would be highly appreciated.

Thanks,
Sadhik

Re: Loading(Importing) Data into SQL Server 2005

i.j.russell5-Aug-09 11:42

i.j.russell

5-Aug-09 11:42

Try googling for Sql Server 2005 DTS

Invalid column name error?

cdietschrun4-Aug-09 4:25

cdietschrun

4-Aug-09 4:25

Error: saving tester - TST-000-341 - item :: System.Data.SqlClient.SqlException: Invalid column name 'TST'.

update tbl_swbom_checklist_testers set passfail='No' where package=992-700-358 and tester=TST-000-341

That is part of the exception thrown by my web app. There is no column named TST, the column is named TESTER, which is why the SQL statement is 'where tester=TST-000-341'. The PACKAGE column is the same syntax and it has no complaints. What am I missing here? Here is my entire SP:

CREATE PROCEDURE dbo.sp_UpdateTesterColumn 
(
@PACKAGE nvarchar(255),
@TESTER nvarchar(100),
@COLUMN nvarchar(100),
@VALUE nvarchar(100),
@LASTCHANGEBY nvarchar(100)
) 
AS 

declare @sql nvarchar(1024)
set @sql=''

set @sql=@sql+'update tbl_swbom_checklist_testers set '+@COLUMN+'='''+@VALUE+''' '
set @sql=@sql+'where tester='+convert(nvarchar(100),@tester)+' and package='+convert(nvarchar(255),@package)

print @sql

exec sp_executesql @sql
GO

Obviously the print is when I uncomment the print statement. What am I missing here?

Re: Invalid column name error?

Blue_Boy4-Aug-09 4:44

Blue_Boy

4-Aug-09 4:44

Change

cdietschrun wrote:
package=992-700-358 and tester=TST-000-341

with

package='992-700-358' and tester='TST-000-341'

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Re: Invalid column name error?

cdietschrun4-Aug-09 4:50

cdietschrun

4-Aug-09 4:50

I thought so, but that code is generated by the SP, and I am having trouble with the quotation/apostrophes that properly encase each one. Could you help me and show me how to edit the SP itself?

Re: Invalid column name error?

cdietschrun4-Aug-09 4:58

cdietschrun

4-Aug-09 4:58

set @sql=@sql+'update tbl_swbom_checklist_testers set '+@COLUMN+'='''+@VALUE+''' '
set @sql=@sql+'where tester='''+convert(nvarchar(100),@tester)+''' and package='''+convert(nvarchar(255),@package)
set @sql=@sql+''''

Ended up looking like that, if anyone was curious.

Re: Invalid column name error? [modified]

Blue_Boy4-Aug-09 5:40

Blue_Boy

4-Aug-09 5:40

Instead using four single quotes then use CHAR(39)

set @sql=@sql+'update tbl_swbom_checklist_testers set '+@COLUMN+'='+CHAR(39)+@VALUE+CHAR(39)<br />
set @sql=@sql+' where tester='+CHAR(39)+convert(nvarchar(100),@tester)+CHAR(39)+'  and package='+CHAR(39)+convert(nvarchar(255),@package)+CHAR(39)

Hope this helepd you.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

modified on Tuesday, August 4, 2009 11:56 AM

How to edit Dataset value (NOT DATA GRID)

sacr834-Aug-09 0:12

sacr83

4-Aug-09 0:12

Hi All,

i want to edit DataSet value before binding to GridView. How can i do that..?

conn.open();

string result="select name,address,age";
sqlCommand cmnd=new sqlCommand(result);
sqlDataAdapter ad=new sqlDataAdapter(result,conn);
DataSet ds=new DataSet();
result.Connection=conn;
ad.SelectCommand=result;

// in here i want to edit (divide age value by 2) before bind data to gridview.

ad.fill(ds);
GridView1.DataSource=ds;
GridView1.DataBind();

conn.close();

i want to divide age value by 2 before bind data to gridview.How can i do that...???

Thank you very much .....

CheeN

Re: How to edit Dataset value (NOT DATA GRID)

Kschuler4-Aug-09 3:14

Kschuler

4-Aug-09 3:14

A DataSet object is a collection of DataTable objects. You could loop through the table and update the age with something like this:

For Each row As DataRow In ds.Tables(0).Rows<br />
   row("age") = row("age") / 2<br />
Next

Or you could just do it in the select by changing your select statement to this string:
"select name, address, age/2 as age"

Hope this helps.

Re: How to edit Dataset value (NOT DATA GRID)

sacr835-Aug-09 20:21

sacr83

5-Aug-09 20:21

Thank u 4 your post. I fixed my problem..Thank u all...

CheeN

Re: How to edit Dataset value (NOT DATA GRID)

Andy_L_J4-Aug-09 9:38

Andy_L_J

4-Aug-09 9:38

For Each dr As DataRow In ds.Tables(0).Rows

  dr("ColumnName") = (dr("ColumnName") / 2)

Next

Where ColumnName is your data column in the DataSet Table.

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

sql server comparing two huge tables

ps_prakash023-Aug-09 21:26

ps_prakash02

3-Aug-09 21:26

Hi Guys,

I'm in need of your help.

my problem is, i have two tables namely MASTER and LINECONTENT.

"MASTER" table contains 5,00,000 records,
The columns in MASTER table is
masterid int, masterdesc varchar(1000).

"LINECONTENT" table contains more than 1000 records &
its columns are pageno int, lineno int, content varchar(2000).

I want to compare each row of "MASTER" table, masterdesc column value with the "LINECONTENT" table content column,
If any records matched i want to take the matched master record and store it in another table.

for this i used the below query.

select b.* from linecontent a
join master b on a.content like '%'+b.masterdesc+'%'

but this query is taking much. more than 15 minutes
i want to decrease the time give me some suggestions handle this problem

thank you
regards,
prakash

Re: sql server comparing two huge tables

Mycroft Holmes3-Aug-09 23:18

Mycroft Holmes

3-Aug-09 23:18

Throw an index on MasterDesc

Try and use charindex in the where clause - it may not help, just an idea.

Never underestimate the power of human stupidity
RAH

Re: sql server comparing two huge tables

ps_prakash023-Aug-09 23:50

ps_prakash02

3-Aug-09 23:50

Hi,

Thanks for your reply.

sorry i forgot tell you that i have created nonclustered index in both tables. In "MASTER" i have created non-clustered index for masterdesc column similarly in "LINECONTENT" table i have created non-clustered index for "content" field. even though its taking time.

thanks & regards
P.Prakash

Re: sql server comparing two huge tables

David Skelly4-Aug-09 2:10

David Skelly

4-Aug-09 2:10

Putting an index on masterdesc will not make any difference to this query because you are using a leading wildcard: LIKE '%abcde%'. The query optimiser will not even try to use an index for this: it will just go through the rows one by one checking each in turn, because that's all it can do.

So basically the query will take every row in LINECONTENT and test it against every row in MASTER. That's why it's taking so long.

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 2:26

ps_prakash02

4-Aug-09 2:26

Hi,

but my functionality is like that. i want to compare "master" table with "linecontent" table and matching rows should be taken. so i have written like that but its taking nearly 15 minutes. i want to decrease the time to some 2 to 3 minutes. please tell me the solution

Thanks & Regards,
P.Prakash

Re: sql server comparing two huge tables

David Skelly4-Aug-09 6:20

David Skelly

4-Aug-09 6:20

I understand what you are trying to do. The problem is that a leading wildcard search is a performance killer, end of story. There is no quick fix or easy answer.

Depending on which version of SQL Server you are using, you may be able to use Full Text indexing. I can't remember whether that was added at SQL Server 2005 or SQL Server 2008 off the top of my head.

Otherwise, I would recommend looking at something like Lucene or Microsoft Search Server to see how they work, as they do something similar to what you are trying to achieve.

Re: sql server comparing two huge tables

Mycroft Holmes4-Aug-09 16:12

Mycroft Holmes

4-Aug-09 16:12

I wonder if he tested charindex and if it give a better performance. I would be surprised if it did but needs testing.

Never underestimate the power of human stupidity
RAH

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 17:44

ps_prakash02

4-Aug-09 17:44

Hi,

I could not understand what you have said.
Could me please explain me deeply.

Thanks & Regards,
P.Prakash

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 17:39

ps_prakash02

4-Aug-09 17:39

Hi,

thanks for your answer,
we are using sql server 2005 i'll look for those two options you have specified.

regards,
P.Prakash

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.