I need to be able to drop a table after a user is done with it. I have
tried something like,
DROP TABLE USER.tblEducation_SAP1
but I get an error.
Can someone suggest the way I should be using the USER value in this
instance?
Thanks!
I am working with Telerik's new rad grid that is helping us speed up our development process. I also have a VB code behind page for the aspx form that does my sql database connection and statements. From the aspx form I also have a user control. The user control allows me to format my edit area within the rad grid in html and asp.net which allows me full control. This user control is an ascx page referenced in my aspx form. I know this is getting confusing so let me get to the point. My control page has an button with CommandName="Update". The vb codebehind for this page looks like:
Imports SystemImports System.DataImports System.CollectionsImports System.Web.UIImports System.Data.SqlClientImports Telerik.QuickStartImports Telerik.WebControlsPartial Class UserDetailsInherits System.Web.UI.UserControlSub Btn_Update(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.ClickDim MySqlConnection As New SqlConnection("Data Source=")Dim UserAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM WebUsers", MySqlConnection)Dim UserDT As DataTable = New DataTable()UserAdapter.UpdateCommand = New SqlCommand("UPDATE [Users] SET [UserName] = @OldUserName, [Comments] = @OldComments WHERE [UserID] = @OldUserID", MySqlConnection)UserAdapter.UpdateCommand.Parameters.Add("@UserID", SqlDbType.Int, 5, "UserID")UserAdapter.UpdateCommand.Parameters.Add("@OldUserName", SqlDbType.Char, 5, "UserName")UserAdapter.UpdateCommand.Parameters.Add("@OldComments", SqlDbType.Char, 5, "Comments")UserAdapter.Fill(UserDT)MySqlConnection.Close()TryMySqlConnection.Open()UserAdapter.Update(UserDT)Catch ex As ExceptionFinallyMySqlConnection.Close()End TryEnd SubEnd Class
The line of code for the button is:
<asp:button id="btnUpdate" text="Update" runat="server" CommandName="Update" OnClick="Btn_Update"></asp:button>
I know there is some simple logic error I have. The page isn't erroring, and there doesn't look like a problem but we aren't able to update the database. I know the button click event gets to the code because we plugged a msg box in there and it appeared, twice I might add, which i don't know about either. Can anyone help us make this darn thing update??? What logic are we missing?
Thanks!!!
Using Transact SQL how can I retrieve the network user name by accessing the API apiGetUserName.
I would like to some how create a stored procedure that will retrieve the user name when a user tries to access the data in a table(s).
One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks
GO
DROP TABLE LI_Users
GO
DECLARE @d1DATETIME
, @d2DATETIME
, @d3DATETIME
, @d4DATETIME
, @d5DATETIME
, @d6DATETIME
, @d7DATETIME
, @d8DATETIME
SELECT @d1 = GetDate()
CREATE TABLE LI_Users (
uidINT
PRIMARY KEY (uid)
)
SELECT @d2 = GetDate()
CREATE TABLE LI_UserLinks (
uid_fromINT
CONSTRAINT XFK01LI_UserLinks FOREIGN KEY (uid_from)
REFERENCES LI_Users (uid)
, uid_toINT
CONSTRAINT SFK01LI_UserLinks FOREIGN KEY (uid_to)
REFERENCES LI_Users (uid)
CONSTRAINT XPKLI_UserLinks PRIMARY KEY (uid_from, uid_to)
)
ALTER TABLE LI_Userlinks
ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to)
SELECT @d3 = GetDate()
INSERT INTO LI_Users (
uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5
FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z2
CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z3
CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z4
CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z5
SELECT @d4 = GetDate()
INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 0 AND 99
AND uid != 100 * uid + n0 + 10 * n1
SELECT @d5 = GetDate()
INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 100 AND 9999
SELECT @d6 = GetDate()
SELECT u.uid, r1.uid_to, r2.uid_to
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
WHERE 1 = u.uid
SELECT @d7 = GetDate()
SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to)
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
SELECT @d8 = GetDate()
SELECT
DateDiff(ms, @d1, @d2)
, DateDiff(ms, @d2, @d3)
, DateDiff(ms, @d3, @d4)
, DateDiff(ms, @d4, @d5)
, DateDiff(ms, @d5, @d6)
, DateDiff(ms, @d6, @d7)
, DateDiff(ms, @d7, @d8)
, DateDiff(ms, @d1, @d8)-PatP
Cannot insert the value NULL into column 'PortfolioID', table 'SazamaBuilders.dbo.Portfolio'; column does not allow nulls. INSERT fails.The statement has been terminated.
Looking for help with the correcting this.
This is the complete error message I am receiving. When debugging and I try to save something the error comes up right next to objportfolio.Save(). The following is my code for the save button:Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
If Page.IsValid ThenDim objportfolio As New BusinessObjects.Portfolio
objportfolio.LoadByPrimaryKey(ViewState("ItemID"))
If objportfolio.es.HasData = False Then
objportfolio.AddNew()
End IfWith objportfolio
.portfolioName = Me.txtportfolioName.Text
' See if the directory is there, if not, create one
If IO.Directory.Exists(Server.MapPath("../Gallery/")) = False ThenIO.Directory.CreateDirectory(Server.MapPath("../Gallery/"))
End If
If Me.FileUpload1.HasFile ThenMe.FileUpload1.SaveAs(Server.MapPath("../Gallery/" & Me.FileUpload1.FileName))
.Filename = Me.FileUpload1.FileName
End If
End With
'Now save the portfolio
objportfolio.Save()
'Cleanup
objportfolio = NothingResponse.Redirect("./manage_portfolio.aspx")
End If
End Sub
It tells me the it's ArticleCatID which is one of the columns that I added to my table. I'm able to add new articles fine and all the database gets written to the database. This error is a result of clicking on the "edit" link and the program goes and retriees the data for the article based on the articleID. Why is it not able to recognize this parameter? Since the article_insert stored procedure works, and the update is the same:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO------------------------------------------------------------------------------ Update a single record in Article----------------------------------------------------------------------------ALTER PROC [dbo].[sp_Article_Update] @ArticleID int, @UserID int = NULL, @WebSiteID int = NULL, @ArticleCatID int = NULL, @ArticleTitle nvarchar(400) = NULL, @Author nvarchar(200) = NULL, @ShortDesc nvarchar(200) = NULL, @ArticleText ntext = NULL, @Keywords nvarchar(200) = NULL, @AnchorText nvarchar(400) = NULL, @ShowInDirectory bit = NULL, @Active bit = NULL, @DateAdded datetime = NULLASUPDATE tblArticleSET UserID = @UserID, WebSiteID = @WebSiteID, ArticleCatID = @ArticleCatID, ArticleTitle = @ArticleTitle, Author = @Author, ShortDesc = @ShortDesc, ArticleText = @ArticleText, Keywords = @Keywords, AnchorText = @AnchorText, ShowInDirectory = COALESCE(@ShowInDirectory, 1), Active = COALESCE(@Active, 0), DateAdded = @DateAddedWHERE ArticleID = @ArticleIDWhy would it give me these errors?
View 2 Replies View RelatedI need to convert user's input to SQL statement.
How to code to chage user's input:123, 234 to '123','234'
Hi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code?
---------------------
Here is part of the cursor I have created.
IF @@FETCH_STATUS = 0
BEGIN
IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1
IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1
IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1
FETCH curs_Briefs INTO @txtPrintSuit
END
------------
How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?
THanks in advance
I would like to have more info on UserInstances concept of SQLExpress 2005.
Can i find any code samples for this feature in MSDN.
I have tried using the following links..
http://msdn2.microsoft.com/en-us/library/ms143401(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms143684(SQL.90).aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
http://msdn2.microsoft.com/en-us/library/ms254504.aspx
http://msdn2.microsoft.com/en-us/library/ms165690(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms143446(SQL.90).aspx
any other links will be helpful for me in this regard.
Thanks,
Vaishu
When I use sa to log into one of my database , it give me the following error: The user is not associated with a trusted conncections. error code: 18452, please tell me how to solve this problem.
I appreciate your help.
Hello,I am getting a SqlException with title "SqlException was unhandled by user code" and then it says "Invalid object name 'Access Table'.here is my code (this is from my login page:)<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT [Password] FROM [AccessTable] WHERE" & _
" (([Username] = @Username) AND ([Password] = @Password))"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
Response.Write("Invalid credentials")
End If
myReader.Close()
End Sub
</script> The error is comming from the myReader = cmd.Execute(CommandBehavior.CloseConnection)Thanks for any suggestions or ideas.
== I asked this question directly to Remus and wanted to share the response to all of those people using this forum ==
We recently moved our database server from SQL Server 2000 to SQL Server 2005. All applications on our intranet development server stay the same [VS.NET 2003], but recently resources in our Dev DB server ran out of space. While doing a thorough investigation, I noticed ERRORLOG file was occupying about 35 Gig of HDD space. I immediately checked SQL Server error log and noticed an entry which says €“
===========================================================================================
Date 7/7/2006 4:45:37 PM
Log SQL Server (Current - 7/7/2006 4:45:00 PM)
Source spid77s
Message
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-5eaf8465-d0cb-4be7-93b6-44bb979dd41c] running on queue BW_Content.dbo.SqlQueryNotificationService-5eaf8465-d0cb-4be7-93b6-44bb979dd41c output the following: 'Could not obtain information about Windows NT group/user 'BWCINCHoffK', error code 0x534.'
===========================================================================================
What is this SqlQueryNotificationService in my database? Is it a SQL Server 2005 thing? Why the same kind of stored procedure does not exist in other databases, but BW_Content? This error is getting repeated most probably every second and is filling up our server.
I believe our corporate IT people removed our domain accounts from BWCINC domain to BWCORP domain and probably some application which is using BWCINCHoffK credential is getting errored out. I tried to locate this application and was not successful.
Is there anyway that I can stop this ERRORLOG from growing? How can I delete these log entries so that I can make space on our Hard Drive? Is there an easy way in SQL Server 2005 to locate which application is creating this error?
Response from Remus:
The 'SqlQueryNotificationService-...' is the service created by SqlDependency when you call SqlDependency.Start (). The problem you describe appears because the 'dbo' user of the database is mapped to the login that originally created this database. The SqlDependency created queue has an EXECUTE AS OWNER clause, owner is 'dbo' and therefore this is equivalent to an EXECUTE AS USER = 'dbo'. The error you see is reported by the domain controller when asked to give information about the original account 'dbo' mapps to (that is, BWCINCHoffK'): Error code: (Win32) 0x534 (1332) - No mapping between account names and security IDs was done.
To solve the issue, change 'dbo' to match a correct login, using either sp_changedbowner or ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somavalidlogin]
To find the databases that have this problem, run this query:
select name, suser_sname(owner_sid) from sys.databases
The databses that have the problem will show NULL on the second column.
A similar problem is described here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1
To remove the entries, use sp_cycle_errorlog to force a new errorlog file, then delete the huge log file.
---------------------------------------
I executed ALTER AUTHORIZATION ON DATABASE::[BW_Content] TO [sa];
I got this error in SQL Error Log once and the growth of ERRORLOG was stopped.
===============================================================
Date 7/10/2006 1:16:55 PM
Log SQL Server (Current - 7/10/2006 1:17:00 PM)
Source spid20s
Message
The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a' because it does not exist.</Description></Error>'.
===============================================================
Should I be concerned about this error?
Thanks
-Binoy
hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using System.Data.OleDb;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
IDTSConnectionManager100 connMgr;
OleDbConnection sqlConn = null;
OleDbDataReader sqlReader;
public override void AcquireConnections(object Transaction)
{
connMgr = this.Connections.MyConnection;
sqlConn = (OleDbConnection )connMgr.AcquireConnection(null);
//sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
OleDbCommand cmd = new OleDbCommand("SELECT CustomerID,TerritoryID,AccountNumber,CustomerType FROM Sales.Customer", sqlConn);
sqlReader = cmd.ExecuteReader();
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();
//SqlDataAdapter oLead = new SqlDataAdapter();
DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable();
//DataRow row = new DataRow();
oLead.Fill(dt,this.Variables.ObjVariable);
foreach (DataRow row in dt.Rows)
{
{
Output0Buffer.AddRow();
Output0Buffer.CustomerID = (int)row["CustomerID"];
Output0Buffer.TerritoryID =(int)row["TerritoryID"];
Output0Buffer.AccountNumber = row["AccountNumber"].ToString();
Output0Buffer.CustomerType = row["CustomerType"].ToString();
}
}
}
}
the error
Script component has encountered an exception in user code
Object is not an ADODB.RecordSet or an ADODB.Record.
Parameter name: adodb
at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String
srcTable, Boolean multipleResults)
at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs,
Int32[] outputIDs, PipelineBuffer[] buffers)
thanks
kedarnath
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?
I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.
After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
View 2 Replies View RelatedWhen I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies View Related
i'm using the Enterpirse library logger to write logs into a database.
When choosing connection string i choose the database i want in the "connection properties" dialog box and
push 'Test connection' button.
everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things,
from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore....
it only work when i restart the computer.
why ?
Hi:
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8462</Code>
<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>
</Error>
Why am i gettting this error after the conversation.
Thanks,
Pramod
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. 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 open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies View Related Hi,
I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!
Regards,
Kenneth Lai
Programmer
Error Pic
Message Box
Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I would be grateful if you can help me
Hi,
I have a copy of VS2005 and i have installed the Microsoft SQL Server Developer Edition
I am trying to do my first connection using ASP.net (yay for me...) and it has failed...with this error..Login failed for user 'testuser'. The user is not associated with a trusted SQL Server connection.
can someone please point out why? yes i know that the error seems pretty straight forward but how do i fix it? like how do i make the user a trusted server connection? where do i do it? and what are the steps? the user is an administrator and by rights should have all access to everything....
connection string
SqlConnection MySQLConnection; MySQLConnection = new SqlConnection("server=cmptrname\databsename;uid=testuser;password=password;database=mytest");
btw while your reading do you have any examples of opening a db with the connection string in the web.config file? because i get some error "connection string has not been initialised" using the
system.Configuration.ConfigurationManager.AppSettings.Get
i though i would stick to the top code first before i tackled the web config error first....
Thanks heaps
I need help. The security principal is the correct DomainUser, but the error message says I am not authenticated.
So here is my error Message:
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Me.User.Identity.Name=CompanyDomainRyan; System.Security.Principal.WindowsIdentity.GetCurrent=CompanyDomainRyan; HttpContext.Current.User.Identity.Name=CompanyRyan
Generated by:
Dim secPrinc As System.Security.Principal.WindowsIdentity = System.Security.Principal.WindowsIdentity.GetCurrent
Me.lblError.Text = ex.Message & vbCrLf & vbCrLf & "Me.User.Identity.Name=" & Me.User.Identity.Name & "; System.Security.Principal.WindowsIdentity.GetCurrent=" & secPrinc.Name & "; HttpContext.Current.User.Identity.Name=" & HttpContext.Current.User.Identity.Name
Here is the Connection String:
Private ConnString As String = "Server=10.144.162.111;Database=DNS;Trusted_Connection=True"
IIS has 4 websites hosted on it. The parent level allows anonymous with windows auth off, but the website level has anonymous off with widnows auth and digest.
In IIS, is there a problem with having the parent level anonymous and website level windows auth?
All the research I found dealing with this problem is that the solution is to set SQLserver to mix mode. I have SQL server already set to mix mode. I am not sure what else to do. Has anybody run into this problem?
my connection string: <connectionStrings>
<add name="ConnectionString" connectionString="Data Source= server name ;
Initial Catalog=FILESHAREDB.MDF;
Integrated Security=false;"providerName="System.Data.SqlClient"/>
</connectionStrings>
Server Error in '/SendItNow' Application.
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: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Source Error:
Line 41:
Line 42: 'open the connection and execute the stored procedure
Line 43: mConnection.Open()
Line 44: mResourceID = mCommand.ExecuteScalar()
Line 45: mConnection.Close()Source File: H:MIS DepartmentIntranetSendItNowApp_CodeDalResourceDB.vb Line: 43 Stack Trace:
[SqlException (0x80131904): Login failed for user ''. The user is not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +172
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +357
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +494
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ResourceDB.SaveResource(String filename, String fromContactEmail, String toContactEmail, String message, Int32 ID) in H:MIS DepartmentIntranetSendItNowApp_CodeDalResourceDB.vb:43
Resource.SaveResource(String filename, String fromContactEmail, String toContactEmail, String message) in H:MIS DepartmentIntranetSendItNowApp_CodeBllResource.vb:31
ContentFiles_Default.ImageButton1_Click1(Object sender, ImageClickEventArgs e) in H:MIS DepartmentIntranetSendItNowContentFilesDefault.aspx.vb:33
System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +86
System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115
System.Web.UI.WebControls.ImageButton.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) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Hi all,
I am new in asp.net. In my web.config file, I wrote the connection strings as below and then I got the above error. I already changed SQL server Authentication mode to "SQL server and Windows". Any idea? Thanks in advance.
<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=xxx;Initial Catalog=mydb;User ID=test;Password=test;Persist Security Info=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>