Incorrect syntax near 'sp_cursorclose'

I get this error message when using JDBC and JSP to access a sqlserver 2000 database. Trying to insert a record. Insert works perfectly when run from query analyzer. Fails with above error message when inserting from web app.
Any ideas?
Amy Thropp wrote:

> I get this error message when using JDBC and JSP to access a sqlserver 2000 database. Trying to insert a record. Insert works perfectly when run from query analyzer. Fails with above error message when inserting from web app.
> Any ideas?
Show the actual jdbc code you're running and also the whole stacktrace of the
exception.
thanks
Joe
|||inserting lock with {INSERT INTO record_locks (type, record_id, session_id, user_id, timestamp) VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1, getdate())}
connection: jdbc:JSQLConnect://tsps5.bha.biancohopkins.com:1433/database=conversion_db/sa barfed on update {INSERT INTO record_locks (type, record_id, session_id, user_id, timestamp) VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1, getdate())}
, error: com.jnetdirect.jsql.u: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
com.jnetdirect.jsql.u: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
at com.jnetdirect.jsql.at.a(Unknown Source)
at com.jnetdirect.jsql.ae.f(Unknown Source)
at com.jnetdirect.jsql.ae.new(Unknown Source)
at com.jnetdirect.jsql.ae.for(Unknown Source)
at com.jnetdirect.jsql.l.execute(Unknown Source)
at com.jnetdirect.jsql.ae.else(Unknown Source)
at com.jnetdirect.jsql.ae.executeQuery(Unknown Source)
at TestLock.main(TestLock.java:28)
"Joe Weinstein" wrote:

>
> Amy Thropp wrote:
>
> Show the actual jdbc code you're running and also the whole stacktrace of the
> exception.
> thanks
> Joe
>
|||here's the code. The other posting had the stacktrace messages
public static void main( String[] args)
{
String query =
"INSERT INTO record_locks (type, record_id, session_id, " +
"user_id) " +
"VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
try {
Class.forName( "com.jnetdirect.jsql.JSQLDriver");
Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
Statement stmt =
conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
print( "inserting lock with {" + query + "}");
ResultSet rs = stmt.executeQuery( query);
print( "and got back from update");
} catch (Exception e) {
print( "connection: " + DB + "/" + USER + " barfed on update {" +
query + "}, error: " + e.toString());
e.printStackTrace();
}
return;
}
"Joe Weinstein" wrote:

>
> Amy Thropp wrote:
>
> Show the actual jdbc code you're running and also the whole stacktrace of the
> exception.
> thanks
> Joe
>
|||Ok.
The problem is that you're doing an insert (not a query), and then calling
executeQuery() instead of executeUpdate().
Try this:
String insert =
"INSERT INTO record_locks (type, record_id, session_id, " +
"user_id) " +
"VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
Class.forName( "com.jnetdirect.jsql.JSQLDriver");
Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
Statement stmt = conn.createStatement();
print( "inserting lock with {" + insert + "}");
stmt.executeUpdate(insert);
Joe Weinstein at BEA
Amy Thropp wrote:
[vbcol=seagreen]
> here's the code. The other posting had the stacktrace messages
> public static void main( String[] args)
> {
> String query =
> "INSERT INTO record_locks (type, record_id, session_id, " +
> "user_id) " +
> "VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
> try {
> Class.forName( "com.jnetdirect.jsql.JSQLDriver");
> Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
> Statement stmt =
> conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
> print( "inserting lock with {" + query + "}");
> ResultSet rs = stmt.executeQuery( query);
> print( "and got back from update");
> } catch (Exception e) {
> print( "connection: " + DB + "/" + USER + " barfed on update {" +
> query + "}, error: " + e.toString());
> e.printStackTrace();
> }
> return;
> }
>
> "Joe Weinstein" wrote:
>

Incorrect syntax near 'COLLATE'

Hi,
I have an ACCESS ADP project that hooks to an SQL database. I have been
working on in may ws and it is ready to go. While testing yesterday, I
found that when I know try to open a table from ACCESS, I get the above erro
r
message. I can still see it OK in EM. Today, when I call one of my Stored
procs from ACCESS code, I get this message also. So I get it when trying to
open all tables, and so far when I call one stored Proc. I have done severa
l
searches on it, and they all talk about using DTS to import tables. This
isn't the case. I am not using COLLATE in my SQL.
Any thoughts?Try refreshing the linked tables.
Derek Davis
ddavis76@.gmail.com
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:8F0BDC8F-4BFF-4AE3-A4ED-DC172B041475@.microsoft.com...
> Hi,
> I have an ACCESS ADP project that hooks to an SQL database. I have been
> working on in may ws and it is ready to go. While testing yesterday, I
> found that when I know try to open a table from ACCESS, I get the above
> error
> message. I can still see it OK in EM. Today, when I call one of my
> Stored
> procs from ACCESS code, I get this message also. So I get it when trying
> to
> open all tables, and so far when I call one stored Proc. I have done
> several
> searches on it, and they all talk about using DTS to import tables. This
> isn't the case. I am not using COLLATE in my SQL.
> Any thoughts?
>|||Hi,
It is an ACCESS Data Project. You don't really link to tables like in an
.mdb. Unless I misunderstood you. I did disconnect from the SQL database
and reconnect.
Steve
"carion1" wrote:

> Try refreshing the linked tables.
> --
>|||More info.
When I try to run the stored proc via QA in debug I get the same message on
the first fetch in the proc. Worked fine the last couple ws and there
have been no changes to the code. I did set replication up for this databas
e
and successfully used in yesterday. So with ACCESS out of the picture, stil
l
having problems. Also updated to SP4 recently.
"SteveInBeloit" wrote:

> Hi,
> I have an ACCESS ADP project that hooks to an SQL database. I have been
> working on in may ws and it is ready to go. While testing yesterday, I
> found that when I know try to open a table from ACCESS, I get the above er
ror
> message. I can still see it OK in EM. Today, when I call one of my Store
d
> procs from ACCESS code, I get this message also. So I get it when trying
to
> open all tables, and so far when I call one stored Proc. I have done seve
ral
> searches on it, and they all talk about using DTS to import tables. This
> isn't the case. I am not using COLLATE in my SQL.
> Any thoughts?
>

Incorrect syntax near ?.

Hi Guys,

I have moved my asp.net app from access db over to MS SQL 2005 DB.

And I have got a slight problem when I go to view any product

for example if I type in the url ofhttp://domain.com/catalog/Details.aspx?AdNum=1

I get this error

Server Error in '/catalog' Application.
------------------------

Incorrect syntax near '?'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

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

I have attached the details.aspx.

I await for some suggestions.

Thanks

Matthew

------

1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>23<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">4Ad Detail - <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />5</asp:Content>67<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >89<script runat="server">10Sub Page_Load(ByVal SenderAs Object,ByVal EAs EventArgs)11If Not IsPostBackThen12 If Request.QueryString("AdNum") =""Then13 Response.Redirect("default.aspx")14End If15 EditLink.NavigateUrl ="confirm.aspx?AdNum=" & Request.QueryString("AdNum")16End If17 End Sub1819 Protected Sub DetailsView1_PageIndexChanging(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewPageEventArgs)2021End Sub22</script>2324  25 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"26 CellPadding="4" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" ForeColor="#333333"27 GridLines="None" Height="65px" Width="100%" Font-Names="Arial" Font-Size="8pt" OnPageIndexChanging="DetailsView1_PageIndexChanging">28 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />29 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />30 <Fields>31 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />32 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />33 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />34 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />35 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />36 <asp:TemplateField HeaderText="Email">37 <ItemTemplate>38 <asp:HyperLink ID="HyperLink1" runat="server" Text=Email NavigateUrl='<%# Eval("Email", "mailto:{0}") %>' />39 </ItemTemplate>40</asp:TemplateField>41 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />42 </Fields>43 </asp:DetailsView>44<p><i>To respond to this ad, just click the email address45above to send the poster46 a message.</i></p>47If you created this ad, you can48<asp:hyperlink id="EditLink" runat="server" >edit or delete it.</asp:hyperlink> <br>49 <asp:SqlDataSource ID="SqlDataSource1" runat="server"50 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"51 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"52 SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = ?)">53 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 </asp:SqlDataSource>5758</asp:content>

When you use SQLDataSource, you need to use the named parameter instead of the "?" which is correct when you were using Access db. Change your SelectCommand to: SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = @.AdNum)">|||

Thanks for your help, it now works.

|||

This piece of code is suppose to allow me to edit / delete records, I can update the info and press update but it doesn't update the database. And I can press Delete record and it doesn't delete the record out of the database.

I don't get any error messages.

Below is the code:

1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>2<%@. ImportNamespace="System.Data" %>3<%@. ImportNamespace="System.Data.SqlClient" %>45<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">6Edit Ad</asp:Content>78<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >910<script runat="server">11Protected Sub DetailsView1_ItemUpdated(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)12 Response.Redirect("default.aspx")13End Sub1415 Protected Sub DetailsView1_ItemDeleted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewDeletedEventArgs)16 Response.Redirect("default.aspx")17End Sub1819 Protected Sub DetailsView1_ItemCommand(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewCommandEventArgs)20If e.CommandName ="Cancel"Then21 Response.Redirect("default.aspx")22End If23 End Sub24</script>2526To make changes, click Edit, make your changes, then click Update.To delete27 this ad, just click the Delete button.28 <br />29 <br />30<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated" OnItemDeleted="DetailsView1_ItemDeleted" OnItemCommand="DetailsView1_ItemCommand">31 <Fields>32 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />33 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />34 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />35 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />36 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />37 <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />38 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />39 <asp:BoundField DataField="UserPassword" HeaderText="UserPassword" SortExpression="UserPassword" />40 <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />41 </Fields>42 <RowStyle BackColor="#FFFBD6" />43 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />44</asp:DetailsView>45 46<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"47 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"48 DeleteCommand="DELETE FROM [Ads] WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword"49 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"50 SelectCommand="SELECT [AdNum], [Title], [Category], [Description], [Price], [Phone],, [State], [UserPassword] FROM [Ads] WHERE ([AdNum] = @.AdNum)"51 UpdateCommand="UPDATE [Ads] SET [Title] = @.Title, [Category] = @.Category, [Description] = @.Description, [Price] = @.Price, [Phone] = @.Phone, = @.Email, [State] = @.State, [UserPassword] = @.UserPassword WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword">5253 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 <DeleteParameters>57 <asp:Parameter Name="original_AdNum" Type="Int32" />58 <asp:Parameter Name="original_Title" Type="String" />59 <asp:Parameter Name="original_Category" Type="String" />60 <asp:Parameter Name="original_Description" Type="String" />61 <asp:Parameter Name="original_Price" Type="Decimal" />62 <asp:Parameter Name="original_Phone" Type="String" />63 <asp:Parameter Name="original_Email" Type="String" />64 <asp:Parameter Name="original_State" Type="String" />65 <asp:Parameter Name="original_UserPassword" Type="String" />66 </DeleteParameters>67 <UpdateParameters>68 <asp:Parameter Name="Title" Type="String" />69 <asp:Parameter Name="Category" Type="String" />70 <asp:Parameter Name="Description" Type="String" />71 <asp:Parameter Name="Price" Type="Decimal" />72 <asp:Parameter Name="Phone" Type="String" />73 <asp:Parameter Name="Email" Type="String" />74 <asp:Parameter Name="State" Type="String" />75 <asp:Parameter Name="UserPassword" Type="String" />76 <asp:Parameter Name="original_AdNum" Type="Int32" />77 <asp:Parameter Name="original_Title" Type="String" />78 <asp:Parameter Name="original_Category" Type="String" />79 <asp:Parameter Name="original_Description" Type="String" />80 <asp:Parameter Name="original_Price" Type="Decimal" />81 <asp:Parameter Name="original_Phone" Type="String" />82 <asp:Parameter Name="original_Email" Type="String" />83 <asp:Parameter Name="original_State" Type="String" />84 <asp:Parameter Name="original_UserPassword" Type="String" />85 </UpdateParameters>8687 </asp:SqlDataSource>8889</asp:content>

I appricate your help

Thanks Matthew

|||

Hi,

You can get information through these links:

http://www.asp.net/learn/dataaccess/tutorial50vb.aspx?tabid=63

http://forums.asp.net/thread/1172520.aspx

Incorrect SET options all of a sudden?

We've been running a SQL Server based application for some time (Access
front-end). Suddenly, the application is reporting an error when running a
stored procedure to insert a new row in a specific table; there's an Exec
statement doing it. Here is the error:
--
Insert failed because the following SET options have incorrect settings:
ANSI_nulls
Quoted_identifier
Arith abort
--
Does anyone know what could have changed in SQL Server to cause this? We can
add a new record manually through Enterprise Manager. Thanks!!Is it possible that something in the app changed that issues different set
statments? Use SQL Profiler to take a look at what's being sent...
or... could it be that someone recompiled the procedure with different set
options in effect?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time (Access
> front-end). Suddenly, the application is reporting an error when running a
> stored procedure to insert a new row in a specific table; there's an Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this? We
can
> add a new record manually through Enterprise Manager. Thanks!!
>|||Hi,
I don't know if its possible. This is a production system that started to
exhibit this behaviour in the middle of the day. There's just one developer,
but he always works on a separate development database (same server
however). We'll keep checking. Any additional ideas are welcome.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:#TX7M5ydDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Is it possible that something in the app changed that issues different set
> statments? Use SQL Profiler to take a look at what's being sent...
> or... could it be that someone recompiled the procedure with different set
> options in effect?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> > We've been running a SQL Server based application for some time (Access
> > front-end). Suddenly, the application is reporting an error when running
a
> > stored procedure to insert a new row in a specific table; there's an
Exec
> > statement doing it. Here is the error:
> >
> > --
> > Insert failed because the following SET options have incorrect settings:
> > ANSI_nulls
> > Quoted_identifier
> > Arith abort
> > --
> >
> > Does anyone know what could have changed in SQL Server to cause this? We
> can
> > add a new record manually through Enterprise Manager. Thanks!!
> >
> >
>|||Perhaps someone created an index on a computed column or on a view that
references the table. This will require that the options listed be
turned on during update operations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time
(Access
> front-end). Suddenly, the application is reporting an error when
running a
> stored procedure to insert a new row in a specific table; there's an
Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect
settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this?
We can
> add a new record manually through Enterprise Manager. Thanks!!
>sql

inconsistent retrieval of data

I have created a sql 2000 db and imported my tables from an access database. My web site is using asp. I am getting inconsistent results when I retrieve data from the database. Even though I can see the data in the database some fields will not retrieve to the web site. When I go to the database and erase the field and then create a new field and then put data in, I will then be able to retrieve the data at the web site. The problem is some pages will be able to retrieve while others won't. Also some fields will return data while others won't. The web site worked fine with access.

Does anyone know what may be causing this?

Thank you in advance.Betcha got spaces in the column names...

or at least unique chars like ? in them

Can you show us some code?|||There are no spaces that I can see or unique chars.

RS.Open "Select * FROM tProjects where playCode = '" & playCode & "'", Conn

filePath = RS("filePath")
myVideo = Split(filePath, ",", -1, 1)

RS4.Open "Select * FROM tVideosViewed WHERE hitID="&hitID&" AND contentID="&myVideo(0), Conn

When the RS4.Open fires then I will receive an error and when I investigate it is because the array is blank and filePath is blank even though there is a value in the database.

Originally posted by Brett Kaiser
Betcha got spaces in the column names...

or at least unique chars like ? in them

Can you show us some code?|||Why not create and call stored procedures?

It'll be more effecient as well

Just curious though, do you have sql server client tools?

Can you type that same select in a query analyzer window?

What would it look like?

Also you can look at profiler to see what's being passed to the server..

my guess is the statement is malformed when it gets passed...

do you know how to start a trace?

I still thinks sprocs are the way to go in any case...|||We found the problem. On certain pages we did not have all the locktypes specified. So SQL server gave us random records back. Once That we specified the locktypes it worked fine.

Thanks for suggestions. Luckily it was an easy fix for us

Originally posted by Brett Kaiser
Why not create and call stored procedures?

It'll be more effecient as well

Just curious though, do you have sql server client tools?

Can you type that same select in a query analyzer window?

What would it look like?

Also you can look at profiler to see what's being passed to the server..

my guess is the statement is malformed when it gets passed...

do you know how to start a trace?

I still thinks sprocs are the way to go in any case...|||Help me out here...what did you do exactly?|||Originally posted by Brett Kaiser
Help me out here...what did you do exactly?

We didn't have the locktype and cursor type set for all the recordsets that we had open. There was multiple recordsets on each page. When I added:

RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic

RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic

For each recordset then we received consistent results from SQL server. It was a small thing but access let us get away with it but SQL wouldn't.

Looks like we have found most of the differences from access to SQL but we need to test it for about a week.

In use by another user..?

Hi all,
I have a liked SQL-server 2000 table in an Access database.
This table have worked fine for 6 months.
But now it start to say: 'In use by another user..' when I try to edit a
record. The table looks like this:
=====================================
CREATE TABLE [dbo].[Tbl_IS_Flagga](
[Orgnr] [nvarchar](11) NOT NULL,
[Flagga1] [bit] NOT NULL,
[Flagga1Ben] [nvarchar](max) NULL,
[Flagga2] [bit] NOT NULL,
[Flagga2Ben] [nvarchar](max) NULL,
[Flagga3] [bit] NOT NULL,
[Flagga3Ben] [nvarchar](max) NULL,
[FtgBeskrivning] [nvarchar](max) NULL,
[FtgLnk] [nvarchar](max) NULL
) ON [PRIMARY]
======================================
It seems to me that the problems are related to the records in the
table. What can I do about it?
Kent J.
My guess is that Access is acting up for any of below reasons:
The primary key for the table was removed.
Someone created a trigger on the table,. which does some strange things (like returning "rows
affected" messages).
Only guesses...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kent J" <kent.johnson@.telia.com> wrote in message news:5LtLj.5924$R_4.4639@.newsb.telia.net...
> Hi all,
> I have a liked SQL-server 2000 table in an Access database.
> This table have worked fine for 6 months.
> But now it start to say: 'In use by another user..' when I try to edit a record. The table looks
> like this:
> =====================================
> CREATE TABLE [dbo].[Tbl_IS_Flagga](
> [Orgnr] [nvarchar](11) NOT NULL,
> [Flagga1] [bit] NOT NULL,
> [Flagga1Ben] [nvarchar](max) NULL,
> [Flagga2] [bit] NOT NULL,
> [Flagga2Ben] [nvarchar](max) NULL,
> [Flagga3] [bit] NOT NULL,
> [Flagga3Ben] [nvarchar](max) NULL,
> [FtgBeskrivning] [nvarchar](max) NULL,
> [FtgLnk] [nvarchar](max) NULL
> ) ON [PRIMARY]
> ======================================
> It seems to me that the problems are related to the records in the table. What can I do about it?
> Kent J.
|||What about locks or transaction log?
If I create new table with 'old' and new records then I have problem
with the old ones but not with the new records. Strange!
Kent J.
Tibor Karaszi skrev:
> My guess is that Access is acting up for any of below reasons:
> The primary key for the table was removed.
> Someone created a trigger on the table,. which does some strange things
> (like returning "rows affected" messages).
> Only guesses...
>
|||Have you tried to "edit a record" using an UPDATE statement in a query
window?
Why does the table not have a key? How do you identify a single row?
"Kent J" <kent.johnson@.telia.com> wrote in message
news:5LtLj.5924$R_4.4639@.newsb.telia.net...
> Hi all,
> I have a liked SQL-server 2000 table in an Access database.
> This table have worked fine for 6 months.
> But now it start to say: 'In use by another user..' when I try to edit a
> record. The table looks like this:
> =====================================
> CREATE TABLE [dbo].[Tbl_IS_Flagga](
> [Orgnr] [nvarchar](11) NOT NULL,
> [Flagga1] [bit] NOT NULL,
> [Flagga1Ben] [nvarchar](max) NULL,
> [Flagga2] [bit] NOT NULL,
> [Flagga2Ben] [nvarchar](max) NULL,
> [Flagga3] [bit] NOT NULL,
> [Flagga3Ben] [nvarchar](max) NULL,
> [FtgBeskrivning] [nvarchar](max) NULL,
> [FtgLnk] [nvarchar](max) NULL
> ) ON [PRIMARY]
> ======================================
> It seems to me that the problems are related to the records in the table.
> What can I do about it?
> Kent J.
|||Yes, I can use UPDATE instead.
I have primary key on the first field.
CONSTRAINT [PK_Tbl_IS_Flagga] PRIMARY KEY CLUSTERED
(
[Orgnr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Aaron Bertrand [SQL Server MVP] skrev:
> Have you tried to "edit a record" using an UPDATE statement in a query
> window?
> Why does the table not have a key? How do you identify a single row?
>
> "Kent J" <kent.johnson@.telia.com> wrote in message
> news:5LtLj.5924$R_4.4639@.newsb.telia.net...
>
|||Hi all,
I figured it out!
[bit] NOT NULL,
...if it's null and you're trying to edit you'll get: "Edited by another
user".
Kent J.
Kent J skrev:
> Hi all,
> I have a liked SQL-server 2000 table in an Access database.
> This table have worked fine for 6 months.
> But now it start to say: 'In use by another user..' when I try to edit a
> record. The table looks like this:
> =====================================
> CREATE TABLE [dbo].[Tbl_IS_Flagga](
> [Orgnr] [nvarchar](11) NOT NULL,
> [Flagga1] [bit] NOT NULL,
> [Flagga1Ben] [nvarchar](max) NULL,
> [Flagga2] [bit] NOT NULL,
> [Flagga2Ben] [nvarchar](max) NULL,
> [Flagga3] [bit] NOT NULL,
> [Flagga3Ben] [nvarchar](max) NULL,
> [FtgBeskrivning] [nvarchar](max) NULL,
> [FtgLnk] [nvarchar](max) NULL
> ) ON [PRIMARY]
> ======================================
> It seems to me that the problems are related to the records in the
> table. What can I do about it?
> Kent J.

In use by another user..?

Hi all,
I have a liked SQL-server 2000 table in an Access database.
This table have worked fine for 6 months.
But now it start to say: 'In use by another user..' when I try to edit a
record. The table looks like this:
===================================== CREATE TABLE [dbo].[Tbl_IS_Flagga](
[Orgnr] [nvarchar](11) NOT NULL,
[Flagga1] [bit] NOT NULL,
[Flagga1Ben] [nvarchar](max) NULL,
[Flagga2] [bit] NOT NULL,
[Flagga2Ben] [nvarchar](max) NULL,
[Flagga3] [bit] NOT NULL,
[Flagga3Ben] [nvarchar](max) NULL,
[FtgBeskrivning] [nvarchar](max) NULL,
[FtgLänk] [nvarchar](max) NULL
) ON [PRIMARY]
======================================
It seems to me that the problems are related to the records in the
table. What can I do about it?
Kent J.My guess is that Access is acting up for any of below reasons:
The primary key for the table was removed.
Someone created a trigger on the table,. which does some strange things (like returning "rows
affected" messages).
Only guesses...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kent J" <kent.johnson@.telia.com> wrote in message news:5LtLj.5924$R_4.4639@.newsb.telia.net...
> Hi all,
> I have a liked SQL-server 2000 table in an Access database.
> This table have worked fine for 6 months.
> But now it start to say: 'In use by another user..' when I try to edit a record. The table looks
> like this:
> =====================================> CREATE TABLE [dbo].[Tbl_IS_Flagga](
> [Orgnr] [nvarchar](11) NOT NULL,
> [Flagga1] [bit] NOT NULL,
> [Flagga1Ben] [nvarchar](max) NULL,
> [Flagga2] [bit] NOT NULL,
> [Flagga2Ben] [nvarchar](max) NULL,
> [Flagga3] [bit] NOT NULL,
> [Flagga3Ben] [nvarchar](max) NULL,
> [FtgBeskrivning] [nvarchar](max) NULL,
> [FtgLänk] [nvarchar](max) NULL
> ) ON [PRIMARY]
> ======================================> It seems to me that the problems are related to the records in the table. What can I do about it?
> Kent J.|||What about locks or transaction log?
If I create new table with 'old' and new records then I have problem
with the old ones but not with the new records. Strange!
Kent J.
Tibor Karaszi skrev:
> My guess is that Access is acting up for any of below reasons:
> The primary key for the table was removed.
> Someone created a trigger on the table,. which does some strange things
> (like returning "rows affected" messages).
> Only guesses...
>|||Have you tried to "edit a record" using an UPDATE statement in a query
window?
Why does the table not have a key? How do you identify a single row?
"Kent J" <kent.johnson@.telia.com> wrote in message
news:5LtLj.5924$R_4.4639@.newsb.telia.net...
> Hi all,
> I have a liked SQL-server 2000 table in an Access database.
> This table have worked fine for 6 months.
> But now it start to say: 'In use by another user..' when I try to edit a
> record. The table looks like this:
> =====================================> CREATE TABLE [dbo].[Tbl_IS_Flagga](
> [Orgnr] [nvarchar](11) NOT NULL,
> [Flagga1] [bit] NOT NULL,
> [Flagga1Ben] [nvarchar](max) NULL,
> [Flagga2] [bit] NOT NULL,
> [Flagga2Ben] [nvarchar](max) NULL,
> [Flagga3] [bit] NOT NULL,
> [Flagga3Ben] [nvarchar](max) NULL,
> [FtgBeskrivning] [nvarchar](max) NULL,
> [FtgLänk] [nvarchar](max) NULL
> ) ON [PRIMARY]
> ======================================> It seems to me that the problems are related to the records in the table.
> What can I do about it?
> Kent J.|||Yes, I can use UPDATE instead.
I have primary key on the first field.
CONSTRAINT [PK_Tbl_IS_Flagga] PRIMARY KEY CLUSTERED
(
[Orgnr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Aaron Bertrand [SQL Server MVP] skrev:
> Have you tried to "edit a record" using an UPDATE statement in a query
> window?
> Why does the table not have a key? How do you identify a single row?
>
> "Kent J" <kent.johnson@.telia.com> wrote in message
> news:5LtLj.5924$R_4.4639@.newsb.telia.net...
>> Hi all,
>> I have a liked SQL-server 2000 table in an Access database.
>> This table have worked fine for 6 months.
>> But now it start to say: 'In use by another user..' when I try to edit
>> a record. The table looks like this:
>> =====================================>> CREATE TABLE [dbo].[Tbl_IS_Flagga](
>> [Orgnr] [nvarchar](11) NOT NULL,
>> [Flagga1] [bit] NOT NULL,
>> [Flagga1Ben] [nvarchar](max) NULL,
>> [Flagga2] [bit] NOT NULL,
>> [Flagga2Ben] [nvarchar](max) NULL,
>> [Flagga3] [bit] NOT NULL,
>> [Flagga3Ben] [nvarchar](max) NULL,
>> [FtgBeskrivning] [nvarchar](max) NULL,
>> [FtgLänk] [nvarchar](max) NULL
>> ) ON [PRIMARY]
>> ======================================>> It seems to me that the problems are related to the records in the
>> table. What can I do about it?
>> Kent J.
>|||Hi all,
I figured it out!
[bit] NOT NULL,
..if it's null and you're trying to edit you'll get: "Edited by another
user".
Kent J.
Kent J skrev:
> Hi all,
> I have a liked SQL-server 2000 table in an Access database.
> This table have worked fine for 6 months.
> But now it start to say: 'In use by another user..' when I try to edit a
> record. The table looks like this:
> =====================================> CREATE TABLE [dbo].[Tbl_IS_Flagga](
> [Orgnr] [nvarchar](11) NOT NULL,
> [Flagga1] [bit] NOT NULL,
> [Flagga1Ben] [nvarchar](max) NULL,
> [Flagga2] [bit] NOT NULL,
> [Flagga2Ben] [nvarchar](max) NULL,
> [Flagga3] [bit] NOT NULL,
> [Flagga3Ben] [nvarchar](max) NULL,
> [FtgBeskrivning] [nvarchar](max) NULL,
> [FtgLänk] [nvarchar](max) NULL
> ) ON [PRIMARY]
> ======================================> It seems to me that the problems are related to the records in the
> table. What can I do about it?
> Kent J.