Showing posts with label nvarchar. Show all posts
Showing posts with label nvarchar. Show all posts

Wednesday, March 28, 2012

Invalid syntax near nvarchar

Im trying to update and modify data in a grid view in my sql data base. I keep getting this error..What is the solution to this. Sorry, I have no programing experience. All help will be greatly appreciated. thank you
jdslim

Incorrect syntax near 'nvarchar'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.


Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +835
System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +162
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +118
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +175
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

It would help if you posted your code.

|||

Here is the new stack trace and the code that goes with it. I started a new page that just contains the gridview and the sql data source.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +638 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +71 System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +933 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1152 System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +191 System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

<%

@.PageLanguage="VB" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div> <asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [Trials] WHERE [Trial#] = @.original_column1 AND [Wet Date] = @.original_Wet_Date AND [Check Date] = @.original_Check_Date AND [Comments] = @.original_Comments"InsertCommand="INSERT INTO [Trials] ([Trial#], [Wet Date], [Check Date], [Comments]) VALUES (@.column1, @.Wet_Date, @.Check_Date, @.Comments)"OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT * FROM [Trials]"UpdateCommand="UPDATE [Trials] SET [Wet Date] = @.Wet_Date, [Check Date] = @.Check_Date, [Comments] = @.Comments WHERE [Trial#] = @.original_column1 AND [Wet Date] = @.original_Wet_Date AND [Check Date] = @.original_Check_Date AND [Comments] = @.original_Comments"><DeleteParameters><asp:ParameterName="original_column1"Type="String"/><asp:ParameterName="original_Wet_Date"Type="DateTime"/><asp:ParameterName="original_Check_Date"Type="DateTime"/><asp:ParameterName="original_Comments"Type="String"/></DeleteParameters><UpdateParameters><asp:ParameterName="Wet_Date"Type="DateTime"/><asp:ParameterName="Check_Date"Type="DateTime"/><asp:ParameterName="Comments"Type="String"/><asp:ParameterName="original_column1"Type="String"/><asp:ParameterName="original_Wet_Date"Type="DateTime"/><asp:ParameterName="original_Check_Date"Type="DateTime"/><asp:ParameterName="original_Comments"Type="String"/></UpdateParameters><InsertParameters><asp:ParameterName="column1"Type="String"/><asp:ParameterName="Wet_Date"Type="DateTime"/><asp:ParameterName="Check_Date"Type="DateTime"/><asp:ParameterName="Comments"Type="String"/></InsertParameters></asp:SqlDataSource></div><asp:GridViewID="GridView1"runat="server"AllowSorting="True"AutoGenerateColumns="False"DataKeyNames="Trial#"DataSourceID="SqlDataSource1"Style="z-index: 100; left: 538px; position: absolute; top: 391px"><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="Trial#"HeaderText="Trial#"ReadOnly="True"SortExpression="Trial#"/><asp:BoundFieldDataField="Wet Date"HeaderText="Wet Date"SortExpression="Wet Date"/><asp:BoundFieldDataField="Check Date"HeaderText="Check Date"SortExpression="Check Date"/><asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/></Columns></asp:GridView></form>

</

body>

</

html>|||

Hi

I just want to check if you ever solved the problem? I'm sitting with the same thing, but nothing will work...

I've tried the suggestions made, but it still doesn't work.

|||

Hi

I just want to check if you ever solved the problem? I'm sitting with the same thing, but nothing will work...

I've tried the suggestions made, but it still doesn't work.

|||you are better off starting a new thread and post your codes there

Monday, March 12, 2012

Invalid Cursor State

Hi all.
I have a procedure in my database whose code is more or less the
following:
CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
declare @.number1 int;
declare @.number2 int;
CREATE TABLE #Table1
(
ValueRet int,
)
set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
(select ref from table_set where num=@.Name)));
set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
(select ref from table_set where num=@.Name)));
insert into #Table values (@.number1);
insert into #Table values (@.number2);
select * from #Table;
if @.@.Error <> 0
begin
return @.@.error
end
GO
The procedure runs ok within the database. I have a problem when quering
using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
the SQLBind instruction and it successes again. However when running the
SQLFecth I get an error Invalid Cursor State (24000), while it should
return the query values (there are values to be returned ;)) )
Does anybody knows why that happens?
Any help is really welcome.
TA.
CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
SET NOCOUNT ON
declare @.number1 int;
declare @.number2 int;
"George" <george.news@.NOSPANgmx.net> wrote in message
news:Xns950AD84C3ED7newsgmxnet@.213.0.184.81...
> Hi all.
> I have a procedure in my database whose code is more or less the
> following:
> CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
> declare @.number1 int;
> declare @.number2 int;
> CREATE TABLE #Table1
> (
> ValueRet int,
> )
> set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> insert into #Table values (@.number1);
> insert into #Table values (@.number2);
> select * from #Table;
> if @.@.Error <> 0
> begin
> return @.@.error
> end
> GO
>
> The procedure runs ok within the database. I have a problem when quering
> using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
> the SQLBind instruction and it successes again. However when running the
> SQLFecth I get an error Invalid Cursor State (24000), while it should
> return the query values (there are values to be returned ;)) )
> Does anybody knows why that happens?
> Any help is really welcome.
>
> TA.

Invalid Cursor State

Hi all.
I have a procedure in my database whose code is more or less the
following:
CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
declare @.number1 int;
declare @.number2 int;
CREATE TABLE #Table1
(
ValueRet int,
)
set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
(select ref from table_set where num=@.Name)));
set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
(select ref from table_set where num=@.Name)));
insert into #Table values (@.number1);
insert into #Table values (@.number2);
select * from #Table;
if @.@.Error <> 0
begin
return @.@.error
end
GO
The procedure runs ok within the database. I have a problem when quering
using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
the SQLBind instruction and it successes again. However when running the
SQLFecth I get an error Invalid Cursor State (24000), while it should
return the query values (there are values to be returned ;)) )
Does anybody knows why that happens?
Any help is really welcome.
TA.CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
SET NOCOUNT ON
declare @.number1 int;
declare @.number2 int;
"George" <george.news@.NOSPANgmx.net> wrote in message
news:Xns950AD84C3ED7newsgmxnet@.213.0.184.81...
> Hi all.
> I have a procedure in my database whose code is more or less the
> following:
> CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
> declare @.number1 int;
> declare @.number2 int;
> CREATE TABLE #Table1
> (
> ValueRet int,
> )
> set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> insert into #Table values (@.number1);
> insert into #Table values (@.number2);
> select * from #Table;
> if @.@.Error <> 0
> begin
> return @.@.error
> end
> GO
>
> The procedure runs ok within the database. I have a problem when quering
> using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
> the SQLBind instruction and it successes again. However when running the
> SQLFecth I get an error Invalid Cursor State (24000), while it should
> return the query values (there are values to be returned ;)) )
> Does anybody knows why that happens?
> Any help is really welcome.
>
> TA.

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Friday, March 9, 2012

Invalid column error

Hello all,
Does anyone see anything wrong with the sql query below


DECLARE @.BUILDINGLIST nvarchar(100)
SET @.BUILDINGLIST = 'ALABAMA'

DECLARE @.SQL nvarchar(1024)

SET @.SQL = 'SELECT id, CLOSED, building AS BUILDING FROM
requests WHERE building = (' + @.BUILDINGLIST + ')'

EXEC sp_executesql @.SQL

I keep on getting the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ALABAMA'.

Thanks in advance.
Richard M.you need an xtra set of quotations.


SET @.SQL = 'SELECT [id], CLOSED, building AS BUILDING FROM
requests WHERE building = ('' + @.BUILDINGLIST + '')'

hth|||actually... one more than that.

(''' + @.BuildingList + ''')'

or SET @.BUILDINGLIST = '' + @.BUILDINGLIST + ''

Then run the original way.

'' would cancel into one '.

Friday, February 24, 2012

Into cursor problem

My code in SP
DECLARE @.sql nvarchar(4000)
SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from #myTable '
print @.sql
EXEC @.sql
I got the error Invalid object name '#temptable' , Does anyone know the
reason ? thanks a lotUnless #temptable is declared within the container SP, it only exists within
the scope of the dynamic sql and will be destroyed when it completes:
DECLARE @.sql nvarchar(4000)
CREATE TABLE #temptable ...
SELECT @.sql = 'INSERT #tempTable SELECT ... from #myTable'
print @.sql
EXEC @.sql
Mr Tea
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23HgFUx7GFHA.3352@.TK2MSFTNGP10.phx.gbl...
> My code in SP
> DECLARE @.sql nvarchar(4000)
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from #myTable
> '
> print @.sql
> EXEC @.sql
> I got the error Invalid object name '#temptable' , Does anyone know the
> reason ? thanks a lot
>|||Try this
SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
#myTable '
print @.sql
EXEC (''+@.sql +'')
Madhivanan|||> Try this
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
> #myTable '
> print @.sql
> EXEC (''+@.sql +'')
?
Does that will anyhow solve the problem?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109396430.717724.313510@.z14g2000cwz.googlegroups.com...
> Try this
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
> #myTable '
> print @.sql
> EXEC (''+@.sql +'')
> Madhivanan
>|||Thomas
I tested that in the query analyser. The text displayed is (9 row(s)
affected). But actually the table was not created. Can you tell me
where the prblem is?
Madhivanan|||Madhivanan,

> I tested that in the query analyser
Tested what? The code you posted?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109401722.488986.74000@.o13g2000cwo.googlegroups.com...
> Thomas
> I tested that in the query analyser. The text displayed is (9 row(s)
> affected). But actually the table was not created. Can you tell me
> where the prblem is?
> Madhivanan
>|||Yes thomas
Madhivanan|||The code you posted will not work on its own.
(It was even missing the declare statement )
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109402883.603761.286740@.f14g2000cwb.googlegroups.com...
> Yes thomas
> Madhivanan
>|||Thomas,
Run this and see the result
create table #temp (no int, name varchar(100))
insert into #temp values(1,'Name1')
insert into #temp values(2,'Name2')
insert into #temp values(3,'Name3')
insert into #temp values(4,'Name4')
insert into #temp values(5,'Name5')
Declare @.sql varchar(100)
SELECT @.sql = 'SELECT no,name INTO #tempTable from #temp'
print @.sql
EXEC (''+@.sql +'')
drop table #temp
Actually it is running without any error. But the table #tempTable is
not created.
Can you find out the reason?
Madhivanan|||Madhivanan,
The #tempTable is not visible because EXEC() has its own scope.
The following are the limitations of EXEC
a.. Within the SQL batch you cannot access local variables or parameters
of the calling stored procedure.
b.. Any USE statement will not affect the calling stored procedure.
c.. Temp tables created in the SQL batch will not be available to the
calling procedure since they are dropped when the block exits - just like
when you exit a stored procedure. The batch can however access tables
created in the calling procedure.
d.. If you issue a SET command in the dynamic SQL batch, the effect of the
SET command lasts for the duration of the dynamic SQL batch only.
e.. The query plan for the batch is not part of the plan for the calling
procedure. Cachewise the query is just as good as a bare SQL statement sent
from the client.
f.. If the batch results in an condition that leads to abortion of the
batch, for instance rollback in a trigger, not only the batch of dynamic SQL
is terminated, but also the calling procedure (and its caller and so on).
Its taken from this excellent article by Erland Sommarskog.
http://www.sommarskog.se/dynamic_sql.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109413962.850048.149990@.z14g2000cwz.googlegroups.com...
> Thomas,
> Run this and see the result
>
> create table #temp (no int, name varchar(100))
> insert into #temp values(1,'Name1')
> insert into #temp values(2,'Name2')
> insert into #temp values(3,'Name3')
> insert into #temp values(4,'Name4')
> insert into #temp values(5,'Name5')
> Declare @.sql varchar(100)
> SELECT @.sql = 'SELECT no,name INTO #tempTable from #temp'
> print @.sql
> EXEC (''+@.sql +'')
> drop table #temp
> Actually it is running without any error. But the table #tempTable is
> not created.
> Can you find out the reason?
> Madhivanan
>