Not Permitted In This Context
Jul 19, 2006
Hi,
I am having a SQL Server 2005 problem with my Insert statement. I am
sending a command via my website to my database. It comes up with an
error I'll put below. The code is here:
"INSERT INTO tblUsers (userName) VALUES ( userNameTest)"
This is the error I get:
The name "userNameTest" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.
Now, userName is a varchar field in the database. What is wrong?
Kivak
View 2 Replies
ADVERTISEMENT
Nov 6, 2006
To all,
I have previously read from and saved to Access databases, but now decided to attempt to learn more about using SQL databases. I took an ASP.NET page I had previously used to save data to an access database and attempted to repurpose it to be used with a SQL database with the same content. I get an error that I have spent the past 3 hours researching on the internet. I am unable to find a solution that fits my code formatting. Any help that could be given would be greatly appreciated.
When I run my ASP.NET page I get the following error:"The name 'newsID' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
The code I am using is shown below:---------------------------------------------------------------------------------------------------------------------Dim objCommand As OleDbCommandDim strSQLQuery As String
strSQLQuery = "INSERT INTO news " _& "(ID, newsDate, newsComp, newsInfo) " _& "VALUES (newsID, newsDate, newsComp, newsInfo)"
objCommand = New OleDbCommand(strSQLQuery, objConnection) objCommand.Parameters.Add(New OleDbParameter("newsID", OleDbType.Integer))objCommand.Parameters.Add(New OleDbParameter("newsDate", OleDbType.DBTimeStamp))objCommand.Parameters.Add(New OleDbParameter("newsComp", OleDbType.VarChar))objCommand.Parameters.Add(New OleDbParameter("newsInfo", OleDbType.LongVarChar))
objCommand.Parameters("newsID").Value = "1"objCommand.Parameters("newsDate").Value = "11/06/2006"objCommand.Parameters("newsComp").Value = "Company Name"objCommand.Parameters("newsInfo").Value = "A description of the news items here"
objConnection.Open()objCommand.ExecuteNonQuery()objConnection.Close()---------------------------------------------------------------------------------------------------------------------
View 5 Replies
View Related
Oct 5, 2007
I'm pulling data from one database in which the headerID is the primary key which automatically increases by one on each insert. Modifying all of the other fields from one data type to another and then inserting them into another database after the convertion is done. I keep getting this error.
The name 'HeaderID' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
INSERT INTO [DatabaseName].[dbo].[InvoiceHeaderConverted]
([HeaderID], [InvoiceNumber], [StoreNumber], [DeliveryDate], [DueDate],
[TotalInvoice], [TotalTax], [ManifestNumber])
VALUES(HeaderID = @HeaderID, InvoiceNumber = @InvoiceNumber, StoreNumber = @StoreNumber,
DeliveryDate = @DeliveryDate, DueDate = @DueDate, TotalInvoice = @TotalInvoice,
TotalTax = @TotalTax, ManifestNumber = @ManifestNumber)
Can anyone point me in the right direction on this one. The headerid isn't autogenerated in the second database but is still the primary key.
View 4 Replies
View Related
Sep 25, 2007
Hi Everyone,
I've run into a problem I have not seen before, and a Google search did not turn up any useful info. So, I'm back to ask for help here! I have created an INSERT statement and several columns are boolean. When I try to execute the INSERT, I get the following error:
The name "False" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I've found that this is Error MSG 128. Also, I'm executing the command as a transaction. The first command is a DELETE followed by an INSERT. The DELETE is working fine, but the INSERT is causing this error. Here is the code for assembling the 2 SQL statements. string[] SQLCommands = new string[RecordCount+1];
int SQLCount = 0;
StringBuilder stb = new StringBuilder();
stb.Append("DELETE FROM UserRights WHERE UserIDPtr = '");
stb.Append(TargetUserID);// Guid value
stb.Append("';");
SQLCommands[SQLCount++] = stb.ToString();
stb.Remove(0, stb.Length);
foreach (MyApp.UserRightsRow row in TargetUserRights)
{
stb.Append("INSERT INTO [UserRights] ");
stb.Append("([UserIDPtr], [AllCustomersFlag], [Customer], [CustomerFlag], [Region], [RegionFlag], [Plant], [PlantFlag], ");
stb.Append("[Building], [BuildingFlag], [Area], [AreaFlag], [Active], [UpdatedBy], [Updated]) ");
stb.Append("VALUES ('");
stb.Append(row.UserIDPtr);// Guid value
stb.Append("', ");
stb.Append(row.AllCustomersFlag.ToString());// bit
stb.Append(", ");
stb.Append(row.Customer.ToString());// int
stb.Append(", ");
stb.Append(row.CustomerFlag.ToString());// bit
stb.Append(", ");
stb.Append(row.Region.ToString());// int
stb.Append(", ");
stb.Append(row.RegionFlag.ToString());// bit
stb.Append(", ");
stb.Append(row.Plant.ToString());// int
stb.Append(", ");
stb.Append(row.PlantFlag.ToString());// bit
stb.Append(", ");
stb.Append(row.Building.ToString());// int
stb.Append(", ");
stb.Append(row.BuildingFlag.ToString());// bit
stb.Append(", ");
stb.Append(row.Area.ToString());// int
stb.Append(", ");
stb.Append(row.AreaFlag.ToString());// bit
stb.Append(", ");
stb.Append(row.Active.ToString());// bit
stb.Append(", '");
stb.Append(row.UpdatedBy);// Guid value
stb.Append("', ");
stb.Append(row.Updated);// DateTime value
stb.Append("); ");
SQLCommands[SQLCount++] = stb.ToString();
stb.Remove(0, stb.Length);
}
The method that performs the SQL Transaction is: public bool DoTransaction(string[] SQLStatements)
{
int i;
int SQLStatementCount = SQLStatements.Length;
using (SqlConnection MyConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("cnMyApp")))
{
using (SqlCommand command = MyConnection.CreateCommand())
{
SqlTransaction transaction = null;
try
{
MyConnection.Open();
transaction = MyConnection.BeginTransaction();
command.Transaction = transaction;
for (i = 0; i < SQLStatementCount; i++)
{
command.CommandText = SQLStatements[i].ToString();
command.ExecuteNonQuery();
}
transaction.Commit();
return true;
}
catch
{
transaction.Rollback();
throw;
}
finally
{
MyConnection.Close();
}
}
}
} Any help with this would be greatly appreciated!! Thanks!
View 2 Replies
View Related
Jan 23, 2013
Am getting error:- Subquery returned more than one value,that is not permitted when sub query follows
=,<,>.................................Below is my query
declare @WorkOrder as nvarchar(10)
Set @WorkOrder =( Select X.PrjCode from OPRJ X Where X.PrjCode='[%1]')
SELECT L.Project ,l.U_PINo as [Indent No], M.DocNum AS 'GRN No', M.DocDate as 'Date',
M.CardName as 'Vendor Name',L.ItemCode,l.Dscription,l.Quantity,l.unitMsr as'UOM',l.Price,l.DiscPrcnt ,L.LineTotal,
isnull((SELECT (TaxSum) FROM PDN4 where statype=-90 and DocEntry=M.DocEntry and L.LineNum =PDN4.LineNum),0) +
[Code] .....
View 1 Replies
View Related
Jan 21, 2002
Can anyone tell me how to UPDATE a column w/ an aggregate value (sum) w/in an UPDATE statement?
I receive the error: "An aggregate may not appear in the set list of an UPDATE statement" in the follwoing code:
update TabAAA set A.col_777 = sum(A.Salary)
from TabAAA A TabBBB B,
where (A.col_1 = B.col_1)
and (A.col_2 = B.col_2)
View 2 Replies
View Related
Jan 31, 2008
I've done some programming before, but i'm pretty much a novice in C#. I recently installed Visual Studio 2008 Express Edition. For some reason, when i came to a point to make a connection with an SQL Sample database "NORTHWIND.SDF".... An error message show up saying "access to database file is not permitted". What causes it.???
View 1 Replies
View Related
Mar 2, 2006
when attempting to execute the following query from within 2005 Mgmt Studio:
sp_dropextendedproperty
@name = 'test_me'
, @level0type = 'schema'
, @level0name = 'dbHSPS.dbo'
, @level1type = 'table'
, @level1name = 'Conflict'
/*[ , [ @level2type = 'column'] { 'level2_object_type' }
, [ @level2name = ] { 'level2_object_name' }
] */
I receive:
"Object is invalid. Extended properties are not permitted on 'dbHSPS.dbo.Conflict', or the object does not exist."
However I can SELECT * FROM dbHSPS.dbo.Conflict and it returns results, and I know the test_me extended property exists for table "Conflict" in dbHSPS.dbo ...
View 4 Replies
View Related
Apr 9, 2007
ok so this is my code:
private void iDListBox_SelectedIndexChanged(object sender, EventArgs e)
{
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Table1 WHERE Naam=@Naam", connection);
cmd.Parameters.AddWithValue("@Naam", iDListBox.SelectedValue);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
naamTextBox.Text = reader.GetString(1);
}
reader.Dispose();
connection.Dispose();
}
}
static private string GetConnectionString()
{
return "Data Source=(local);Initial Catalog=AdventureWorks;"
+ "Integrated Security=SSPI;";
}
but when I change the selected index of the listbox, then this error comes up:
System.Data.SqlClient.SqlException was unhandled
Message="An error occured while setting up a connection with the server. At making a connection with SQL server 2005, this jamming is possibly caused by the fact that at the standardinstitutions of SQL server no external connections have been permitted (provider: Provider of named pipes, error: 40 - no connection with SQL could open server)"
If the error sounds a bit weird, sry I tried to translate as most of my errors come in my language
someone knows what the problem is and a solution to it?
(btw sry if this is in the wrong part of the sql forum, but there are so much parts, I didn't know which to choose lol )
thx in advance
View 1 Replies
View Related
Sep 15, 2011
I have a table that I am an dbo owner and when I try to move a column from the last to the first position in Design table mode and click to save, I am getting the following warning:"Saving changes is not permitted. The changes you have made require the following tables to be dropped or re-created.  You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to the re-created"..how to deal with this error so that I can move my column in a table Design mode. I am on SS 2008 R2.
View 5 Replies
View Related
May 14, 2007
Hi.. I tried to setup MSSQL2000 trans replicatiom using push subscription method. My Publisher is also my distributor. But whenever I started the dstribution agent, I got the following error. It has problem connecting to subscriber's DB with the following error. I had tried that the login is 100% fine. But why this error appear ? Any help?
The name ' ' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
(Source: Subsriber (Data source); Error number: 128)
View 1 Replies
View Related
Aug 22, 2006
Hi There,
I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.
=================
Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
===============
Can anybody please suggest me what I should do?
Thanks.
View 19 Replies
View Related
Aug 25, 2015
I would like to configure the permitted hosts setting for the Report Server. According to MSDN, the Syntax is:
 <PermittedHosts>
         <HostName>Adventure-Works.com</HostName>
         <HostName>hotmail.com</HostName>
    </PermittedHosts>
In case of having only one host, is the <HostName> tag mandatory? I was not able to find more information regarding the xsd on which the configuration file is based. To be more specific, is it possible to define the PermittedHost like this or will this result in an error?
 <PermittedHosts>Adventure-Works.com</PermittedHosts>
View 3 Replies
View Related
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Apr 29, 2003
Hi,
I am trying to create a block of sql statements that will read the sysdatabases table, and store the value of the database in a variable.
I want to use a cursor to step through all these databases (excep of course the control databases).
For each of the databases I want to run a backup log followed by dbcc shrinkfile. For dbcc shrinkfile, I need to change the database context.
What I am finding is that I cannot use the variable to hold the database name.
here is the code
-------------------------
SET NOCOUNT ON
DECLARE @db_name varchar(100)
DECLARE @db_filename varchar(100)
DECLARE list_dbs Cursor
FOR select [name] from master..sysdatabases
OPEN list_dbs
FETCH NEXT FROM list_dbs into @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
select name = @db_filename from @db_name..sysfiles
backup log @db_name with no_log
use @db_name
dbcc shrinkfile (@db_filename, 25)
FETCH NEXT FROM list_dbs into @db_name
END
CLOSE list_dbs
DEALLOCATE list_dbs
-----------------------
I get a syntax error where ever the @db_name is used (on the select and on the use).
Any suggestions?
Thanks,
Jim
View 7 Replies
View Related
Apr 11, 2008
Hi,
I need to build up dynamically cursor and fetch variables in a script. I use exec(@sql) to declare the cursor and my hope was I could use the same method for doing the fetch. Unfortunately I run into a
Must declare the scalar variable "@c1".
Here's the part of the code:
... snippet
DECLARE @c1 varchar(max)
DECLARE @c2 char(3)
DECLARE @c3 char(3)
DECLARE @c4 char(3)
DECLARE @c5 char(3)
DECLARE @c6 char(3)
DECLARE @c7 char(3)
DECLARE @c8 char(3)
DECLARE @c9 char(3)
DECLARE @c10 char(3)
DECLARE @c11 char(3)
DECLARE @c12 char(3)
DECLARE @c13 char(3)
DECLARE @c14 char(3)
DECLARE @c15 char(3)
DECLARE @c16 char(3)
DECLARE @c17 char(3)
DECLARE @c18 char(3)
DECLARE @c19 char(3)
DECLARE @c20 char(3)
DECLARE @c21 char(3)
DECLARE @c22 char(3)
DECLARE @c23 char(3)
DECLARE @c24 char(3)
DECLARE @sql VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @fetch varchar(max)
SET @sql ='DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET(' +
@apo+@int_provider_name+@apo+ ',' +
@apo+@int_provider_string+@apo + ',' +
@apo+@int_query_string+@apo +
') FOR READ ONLY'
PRINT @sql
EXEC(@SQL)
OPEN c1
set @fetch = 'FETCH c1 INTO '+@int_fetch
print @fetch
EXEC (@fetch)
... snippet
Output:
DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:work2007schenker.xls','SELECT * FROM [Datei$]') FOR READ ONLY
FETCH c1 INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@c1".
How can I declare the dynamically built 'fetch into' variables (@c1 etc.) so they are in the context of dynamic sql ?
Thanks: Peter
View 1 Replies
View Related
Jul 6, 2006
Hello,
I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"
I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?
Thanks
View 6 Replies
View Related
Sep 28, 2007
There are some concept about context switch block me .
---executor: dbo
create user u1 without login
go
create table t
(
col int
)
go
create proc proc1
with execute as 'u1'
as
insert into t values(1)
go
exec proc1
I think proc1 can't be run successfully beacuse the executor of proc1 is u1 and
I didn't grant any permission to user u1. So it will return a error message like The INSERT permission was denied on the object 't', database 'tempdb', schema 'dbo'
To my surprise, the "exec p1" command could run successfully..
Why?
View 12 Replies
View Related
Oct 5, 2006
I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.
View 5 Replies
View Related
Sep 21, 2004
I was trying out the Building an End-to-End Application (VB.Net) exercises on Web Matrix Guided Tour and encountered the mentioned errors. Please help. Thanks.
The code is as follows :
<%@ Page Language="VB" Debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Welcome.Text = "Hello, " + User.Identity.Name
If Not Page.IsPostBack Then
' Databind the master grid on the first request only
' (viewstate will restore these values on subsequent postbacks).
MasterGrid.SelectedIndex = 0
BindMasterGrid()
BindDetailGrid()
End If
End Sub
Sub MasterGrid_Select(Sender As Object, E As EventArgs)
BindDetailGrid()
End Sub
Sub MasterGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
If MasterGrid.SelectedIndex <> -1 Then
' unset the selection, details view
MasterGrid.SelectedIndex = -1
BindDetailGrid()
End If
MasterGrid.CurrentPageIndex = e.NewPageIndex
BindMasterGrid()
End Sub
Sub BindMasterGrid()
' TODO: Update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
Dim CommandText As String = "select OrderID, OrderDate, CustomerName from Orders"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds)
MasterGrid.DataSource = ds
MasterGrid.DataBind()
End Sub
Sub BindDetailGrid()
' get the filter value from the master Grid's DataKeys collection
If MasterGrid.SelectedIndex <> -1 Then
' TODO: update the ConnectionString value for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
' TODO: update the CommandText value for your application
Dim filterValue As String = CStr(MasterGrid.DataKeys(MasterGrid.SelectedIndex)).Replace("'", "''")
Dim CommandText As String = "select OrderDetailID, ProductName, Quantity, UnitPrice from OrderDetails where OrderID = '" & filterValue & "'"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)
myConnection.Open()
DetailsGrid.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End If
DetailsGrid.DataBind()
End Sub
</script>
And this is the compilation error :
Cannot generate SSPI context.
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: Cannot generate SSPI context.
Source Error:
Line 49:
Line 50: Dim ds As New DataSet()
Line 51: myCommand.Fill(ds)
Line 52:
Line 53: MasterGrid.DataSource = ds
Source File: C:WorksWebWISHDefault.aspx Line: 51
Stack Trace:
[SqlException: Cannot generate SSPI context.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +311
System.Data.SqlClient.SqlConnection.Open() +383
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
ASP.Default_aspx.BindMasterGrid() in C:WorksWebWISHDefault.aspx:51
ASP.Default_aspx.Page_Load(Object Sender, EventArgs E) in C:WorksWebWISHDefault.aspx:14
System.Web.UI.Control.OnLoad(EventArgs e) +55
System.Web.UI.Control.LoadRecursive() +27
System.Web.UI.Page.ProcessRequestMain() +731
View 1 Replies
View Related
Mar 15, 2001
Hi all,
I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:
Error 5701
Severity Level 10
Message Text:
Changed database context to '%.*ls'.
However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).
Any ideas why this would start happening?
View 1 Replies
View Related
Oct 16, 1998
I am writing a trigger to audit changes to certain columns. In the trigger I
would like to record to the "Audit" table as much information about the
current execution context as possible (current user, login, nt user,
inputbuffer, etc.). I couldn`t find the way to find out what is the
currently executed* stored procedure. @@PROCID returns object id for the
trigger itself. Any help would be highly appreciated as this is an urgent
production environment issue.
regards,
Anatol
View 1 Replies
View Related
Feb 2, 2004
Hi,
We have a sqlserver 7 on windows nt 4.
We'd just change a startup account for sqlserver agent to a
not domain admin for security reason. The startup account for sqlserver service is
still domain admin. We are now getting the following error every 5 second
in our application event log.
"SQLServeragent security context does not have server-autorestart privileges"
Is there a way to get rid of this error without putting a domain admin as
a startup account for sqlserver agent?
Ted
View 1 Replies
View Related
Feb 4, 2014
I have two questions about date formatting in MS SQL 2008.
1. I have column name called DATETIME which have the value like this: 2013-11-12 05:00 what function should I have to use to get only 05:00?
2. I have some rows as below picture:
how can I get the maximum value of "Active PDP Context" and the Time column becomes only 2014-02-03 00:00:00.000?
View 2 Replies
View Related
Feb 5, 2007
All of a sudden I cannot connect to sql server from Visual Studio. Nor I can't conenct to Sql server through IIS and it gives me the same error saying "Login failed: Cannot Generate SSPI Context". Lots of my development projects got stuck because of the SSPI Issue. I know there was a network upgrade from Windows NT (users) to Active directory. I can only access the sql server from the host machine.if someone can guide or give me a quick run down on things to look for that would be wonderful.
View 2 Replies
View Related
Feb 20, 2007
Um, still trying to transpose MySQL into T-SQL.
Inserting info into a table, where one of the columns meets a certain criteria.
insert into employee (/*emp_id,*/ fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (/*null,*/ 'Michael', 'Smith', '2001-06-22',
(select dept_id from department where name = 'Administration'),
'President',
(select branch_id from branch where name = 'Headquarters'));
But I'm getting this error:
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Any help would be greatly appreciated.
View 2 Replies
View Related
Jul 9, 2007
I have a SSIS package developed by a different user which does a lot of DML. This package sits on the server.
The package needs to be executed on a regular basis.
I have given RO access for a regular user on production DB, he is executing the package from his client desktop.
I was expecting this execution fail, since the package is doing lot of INSERTS , where the user has ONLY RO access.
I understand from the above experience that there is an “execution context� for SSIS execution. Can someone tell me how can I define the execution context for SSIS?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Jul 23, 2005
HiMy company uses a Windows 2000 server with MS SQL Server 2000.We have many clients with administrative software that use the MS SQLserver, the problem is that we experimented randomly the error"Cannot Generate SSPI Context" and the only way to work with DB is logout the client.I'm looking the MS Technical sheet but there's no resolving hints thatcan help.Anyone has a similar problem ?Thanks.
View 1 Replies
View Related
Feb 6, 2007
Win Server 2003
SQL Server 2000 SP4
SharePoint Server
Win Server 2000 (Domain Controller / Active Drectory)
Clients: Win XP / Access XP
I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.
The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified:
1) date and time
2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2))
3) Not using cached credentials
we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.
Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?
View 1 Replies
View Related
Jun 18, 2006
Hi There
I am trying to loop through databases to gather information.
However if i loop though datbases in a cursor, by using a select from sysdatabases, once i am in the cursor and SET @Sql = 'use ' + DBName and EXEC(SQL), the database context never changes from the one the cursor is executing in.
Is there a way to loop through database contexts and execute sql ?
Thanx
View 3 Replies
View Related
Feb 24, 2006
Hello Guys,
I need some some clarifications on how Context connections and transactions inter operate in CLR.
The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.
SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);
I guess my ambiguity comes from the fact that the Transaction is not specifically specified.
In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.
Thank you,
Lubomir
View 1 Replies
View Related
May 5, 2006
Hi
I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.
If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.
The event log shows me:
The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".
Running a SQL Profiler trace shows me:
Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.
This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150
I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.
TIA . . . Ed
View 6 Replies
View Related
Oct 6, 2006
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean
Dim success As Boolean = False
emailLog = emailLog & System.DateTime.Now.ToString & " - bulk transfer2 - " & _tblName & vbCrLf
Dim insertStr As String = "INSERT INTO " & _tblName & "("
Dim values As String = ") Values("
Dim drow As DataRow = Nothing
Dim dCol As DataColumn = Nothing
'add the column names
For Each dCol In _dt.Columns
insertStr = insertStr & dCol.ColumnName.ToString & ", "
values = values & "@" & dCol.ColumnName.ToString & ", "
Next
'remove the last comma & form the final string
insertStr = insertStr.Substring(0, insertStr.Length - 2)
values = values.Substring(0, values.Length - 2)
insertStr = insertStr & values & ")"
Dim connStr As String = "context connection = true"
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Using conn
Try
conn.Open()
For Each drow In _dt.Rows
cmd = New SqlCommand(insertStr, conn)
For Each dCol In _dt.Columns
cmd.Parameters.AddWithValue("@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))
Next
SqlContext.Pipe.ExecuteAndSend(cmd)
Next
success = True
Catch ex As Exception
emailLog = emailLog & System.DateTime.Now.ToString & " " & ex.ToString & vbCrLf
success = False
Finally
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
success = False
End Try
End Try
End Using
Return success
End Function
View 4 Replies
View Related
