a number of time I have come accross developers using functions like
GetProduct()
GetProductTitle()
GetProductCategory()
to get the value of the data instead of just using queries / stored procedures
I have not understood why
can you please point to some good forum messages / blogposts / articles on this ?
Whats your take on this ?
HiI'm having problems following the tutorial on creating a data access layer - http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - when I try to compile in Visual Studio 2005 I get namespace could not be found. I followed exactly the tutorial - I created a dataset and added this code in my aspx page. <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle"> <HeaderStyle CssClass="HeaderStyle" /> <AlternatingRowStyle CssClass="AlternatingRowStyle" />In my C# file I added these lines... using NorthwindTableAdapters; <<<<<this is the problem - where does this come from? protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProducts(); GridView1.DataBind(); }Thanks in advance
View 6 Replies View RelatedHello, everybody.
In my web application, i'm using 2 tabels; Users(Username(PK), Pwd, Name, Deptid(FK)) n Dept(Deptid(PK), Deptname)).
For creating a Data Access Layer 4 my project, I added dataset as new item n followed the wizard 2 create the required functions.
I have a function GetUser(@uname, @pwd), which takes username n password as input. M using this for authentication purpose.
While executing it poping an ConstrainException.
Plz help me out.
I've tried 2 as clear as possible here. OR u may ask me any other questions for clear picture of the scenario.
Thanks and Regards,
Sankar.
I request you plz tell how to create Data Access Layer. I mean DataAccess.dll. So that I can call stored procedure from dataaccess.dll as below.
DataAccess.SqlHelper.ExecuteDataset(DataAccess.DSN.Connection("DBConnectionString"), CommandType.StoredProcedure, "SP_GetEmpIds");
I request you how can I add this stored procedures to DataAccess.dll and function. I am not having any idea in this area. I request you plz give me some suggestions to work with task.
I have some code below which is a function in an Access database, the function is a field in a query which find the initial non numeric charceters from a postcode (eg CV8 6UJ returns CV) then returns the region manager. So findPCR("CV8 6UJ") = "Bob Thomas".
I have a view in SQL Server and I want to the exact same thing (create a new field for the view which has as its source the findPCR() function.
So firstly i)can i do this in SQL Server and ii) how do I go about doing it?
Function findPCR(pcode As String)
Dim startpos As Integer
Dim CurChar, sstring As String
startpos = 1
CurChar = "a"
Do While Not IsNumeric(CurChar) And startpos <= Len(pcode)
CurChar = Mid(pcode, startpos, 1)
startpos = startpos + 1
Loop
sstring = "%" & Mid(pcode, 1, startpos - 2) & "%"
If InStr(1, "%B%CV%DE%DY%LE%LN%NG%NN%PE%ST%SY%TF%WR%WS%WV%", sstring) > 0 Then
findPCR = "Bob Thomas"
Else
findPCR = "Unknown"
End If
End Function
Hi,
Below is my scenario:
Table name: "Items"
Columns:
Item Id,
Item name,
Item Description
I am implementing a search feature.There is SP for the same.
The item Id is passed as a parameter and the records are fetched.
If the Item id is passed as a integer value all works fine.
For eg. @ItemId=1.
Now the user would like to enter rangevalues on the front end screen.
Like for eg.Item Id = 1-10 or user can enter Item Id=1-10 , 20-25, 30-50
In such a case i am passing the contents as it is.
I.e my @ItemId='1-10 , 20-25, 30-50'
Now i need a function will will decode these appropriately and will return the result for these group of items.
Kindly give me some directions in how to go about this scenario.
Thanks
Renu
Can someone please explain what the following function does?
Use Master
go
create function dbo.concatenate(@a1 as int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
declare @b1 varchar(1000)
set @r = ''
declare c cursor for
select Notes from heatdb..Accs where ID = @a1
open c
fetch next from c into @b1
while @@FETCH_STATUS = 0
begin
set @r = @r + ' ' + @b1
fetch next from c into @b1
end
close c
deallocate c
return @r
end
go
Well, I just downloaded SQL 2005 Express to have a play. Good old Billsays "up and running in 20 minutes"Well 5 hrs, 5 reboots and 4 full un-install and re-install later I havefinally got it working. About what I expected really.Anyway, my question relates to creating user defined functions. I've 10yrs exp in MS Access and VB, and user ent Manager to create views onthe work server, but havnt done much with functions.I'm using the Management Studio Express.I've opened a new view and copied the following sample code but it wontlet me save it.-------------------------IF OBJECT_ID(N'dbo.GetWeekDay', N'FN') IS NOT NULLDROP FUNCTION dbo.GetWeekDay;GOCREATE FUNCTION dbo.GetWeekDay -- function name(@Date datetime) -- input parameter name and datatypeRETURNS int -- return parameter data typeASBEGIN -- begin body definitionRETURN DATEPART (weekday, @Date) -- action performedEND;GO------------------------------------------When I try to save it it says"incorrect syntax near the key work 'IF'incorrect syntax near 'GO'CREATE FUNCTION must be the first statement in a query batchmust declair the scalar variable "@Date"Whats wrong with the syntax???Thanks in advanceGrant
View 4 Replies View RelatedHi all,I'm trying to create a layout of our website for Marketing to review,and though I know how I want it presented, I'm not sure how to writethe SQL code to make it work. Here's a sample of the View I've writtenwith all our content:Level Title ID ParentID1 Clinics 11 Services 22 Surgery 1 22 ER 2 22 Radiology 3 22 Clinic 1 4 12 Clinic 2 5 12 Clinic 3 6 13 Heart 1 13 Lung 2 13 Physicians 3 43 Physicians 4 53 Physicians 5 6And the output would basically be this:Clinics- Clinic 1-- Physicians- Clinic 2-- Physicians- Clinic 3-- PhysiciansServices- Surgery-- Heart-- Lung- ER- RadilogyIs there anyway to do this in a SQL statement, or maybe Crystal orAccess? I even tried a Pivot table in Excel, but it wants to sumstuff, and I'm not working with numbers (sums, counts, etc). Alsosince I'm not using any aggrate functions MS SQL is complaining when iuse Group By.Thanks for any suggestions. Also though the layout is similar, theitems listed above are pulled outta the air. Hopefully I typed upsomething that makes since :)Alex
View 2 Replies View RelatedWe have few C# binaries that contains definition of CLR functions, and one stored procedure to install / create them in our database. Recently when dropping and recreating the CLR function using our stored procedure, it failed with below error:
Error/info returned: [Microsoft][ODBC SQL Server Driver][SQL Server]This server is Information Protection configured. Only the sql server web application can be used for this function. ([URL])[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction ended in the trigger. The batch has been aborted.
Basically our stored procedure has standard steps to create asymmetric key for each of our C# binary and create login for that key and then grant unsafe assembly permissions to those logins.
It sets show advanced options, clr enabled, and ole automation procedures options. It then using create assembly (for each c# binary with permission_set = unsafe) and create function constructs to create clr functions in SQL server.
I usually access stored procedures using SQL data source. But now I need a string returned from the database. If I write a function in SQL how do I access it from an aspx.vb file?
View 3 Replies View RelatedI'm upsizing MS-Access to SQL Server 2005.
I need to convert the following functions:
TRANSFORM
PIVOT
FORMAT
MID
Are there any similar functions in SQL Server?
Also I have a query as follows:
SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate])," ",Format([statusdate],"yyyymmdd")) AS [Date]
FROM tblResult;
How do I convert this in SQL Server query?
Before page 12, step by step instructions work!
Then there is code for AllProducts.aspx that doesn't work if one inserts the code
into the DataTutorial project started on page 1. Yes I changed the name of the CodeFile!
The code given for AllProducts.aspx.cs doesn't compile.
I was doing better without the tutorial!
I can gleen out some concepts but that is all.
If that is all, why have a tutorial?
We have an Access application using Jet. I added some new indexes yesterday and now they are being blamed for poor Access application performance. I then dropped the new indexes. The poor performance continued until the Access application was re-linked to the SS2000 database. Then things returned to normal.
Question, does Access/Jet persist SqlServer schema info in its MDB (or elsewhere?) I am told that the MDB is copied from a share to the local PC where it grows during its use. Some people are saying the MDB persists schema info about the SS2000 schema which influences how Jet accesses the SS2000 database. Is that true? Is there a link where I can read about this? I am a dba and am not an Access developer . . .
Thanks!
Michael
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View Relatedwhat is the best way to create a MS SQL 2000 database from an existing MS ACCESS 2003 database
with all datas in it ?
thank you
I would like to write a custom mining function, which takes a string, queries the database, and returns an answer based upon those queries. So the basic function is then:
[MiningFunction("Performs Foo")]
public string Foo(string param)
{
// process parameters
// query database
// calculate answer from query results
// return query results
}
And is executed from the client using:
SELECT Foo("X Y Z") FROM FooModel
This arrangement is so that resource-intensive calculations are performed server-side.
My question is: what is the preferrable method for executing the database query from within the custom mining function?
I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?
View 8 Replies View RelatedI am trying to create a MS Access datasource to use it in a IS dataflow. I've done the following things:
1.I've created an ODBC datasource - System DSN, pointing to the Access database.
2.I've created an ODBC connection manager pointing to the ODBS datasource that I created in the previous step.
3.I've created a DataReader Source that uses as its Connection Manager the connection manager that I created in the previous step.
I get the following error message: "Cannot acquire a managed connection from the run-time connection manager." What can I do to solve this error?
Pedro Martins
Hi all,
I'm in a situation where I need to accumulate values from several columns in each of the rows returned in a result set and to then display each accumulation value for each row at the end of each row.
For example, if I had the following...
------------------------------------------------------
name - car - van - bus - total
------------------------------------------------------
Terry - 55 - 34 - 12 - 101
John - 01 - 23 - 05 - 029
etc etc
------------------------------------------------------
Where 'total' column is an accumuliation of the 'car', 'van', and 'bus' columns. The values for the columns 'car', 'van', and 'bus' are all values that have been derived from COUNT function. So what I want is to somehow genarate the values for the 'total' column (car + van + bus).
Now I have tried the following SQL, but to no avail
Code:
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(COUNT(pveh.name1) + SUM(COUNT(pveh.name2))
FROM planvehicles AS pveh
... and I also tried...
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(Car + Van)
FROM planvehicles AS pveh
Thanks
Tryst
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
HowTo: Import data to MS SQL 2008 from password protected Access DB ?
View 2 Replies View RelatedI'm trying to create a linked server to an Access database that resides on a separate machine.
On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.
If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.
The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.
I create the linked server using the following command:
exec sp_addlinkedserver
@server = 'AccLinkedServer',
@provider = 'Microsoft.jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\DatabaseServerAccessDatabase.mdb'
If I run the SQL statement:
SELECT * FROM AccLinkedServer...AnyTableYouLike
I get this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\DatabaseServerAccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.
Anyone got any other advice, before I go totally insane with this.
Cheers.
Hi!
This is quite weird.
When I first wanted to create assably using:CREATE ASSEMBLY hashfrom 'C:hash.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS... Server returned error:CREATE ASSEMBLY failed because it could not open the physical file "C:TESTS_Run_This_In_SQL2.dll": 2(The system cannot find the file specified.).Then I tried to find file with exec Master.dbo.xp_fileexist 'C:hash.dll'File exists - falseFile is directory - falseParent directory exists - trueConclusion: Server knows that I have hard drive but cannot see the file (by the way file IS THERE 10000%,alright)
2nd TRY:Copy file to a Server PC named 'pluto' in a directory... i try to create an assably:CREATE ASSEMBLY hashfrom '\plutoProjektiECDLhash.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS...SAME THING: The system cannot find the file specified.i try to find file.... SAME THING!
3rd TRY:I copy file to another Server PC, this time i try to find file first:exec Master.dbo.xp_fileexist '\Depoprojekti2ECDLSRCBackuphash.dll'IT WORKS!!! file is found!!! now everything should work....right!CREATE ASSEMBLY hashfrom '\Depoprojekti2ECDLSRCBackuphash.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS.....CREATE ASSEMBLY failed because it could not open the physical file "\Depoprojekti2ECDLSRCBackuphash.dll": 5(Access is denied.).
Now this is fun.
The whole point of this is can somebody tell me what permissions I (or Management Studio or whatever) have to have in order to create an assably from dll?
Hi
I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.
Try
Dim cat As Catalog = New Catalog()
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Seans VBAppGeneratorSystemAppGen.mdb;" & _
"Jet OLEDB:Engine Type=5;" & _
"User Id=user;" & _
"Password=pass;")
Console.WriteLine("Database Created Successfully")
cat = Nothing
Catch ex As Exception
Console.WriteLine("Failed to create database")
End Try
How do you create a link to MS Access DB in SQL Server 2005. Thanks
View 3 Replies View RelatedHi, Folks!
I'm receiving Access Violation Error when I'm trying to create a clustered index on a datetime field on a table that have around 4 million records, if I create the index nonclustered, no problem, but clustered the system raise this error!
Any help will be appreciate a lot!
Thanks in advance!
Armando Marrero
Cti. Miami
Dear Readers,
Is it possible to create a query that access multiple sql express databases? Or is it possible to link a table in one database from another?
Hi,
How do I create a user that has full access to a database using only SQL statements?
I've create very basic database called "sampledb".
Then I've done:
CREATE LOGIN sampleuser WITH PASSWORD = '123sampleuser', DEFAULT_DATABASE = sampledb;
USE sampledb;
CREATE sampleuser FOR LOGIN sampleuser WITH DEFAULT_SCHEMA = 'db_owner';
Then I logon to SQL server express 2005 management studio using this user, first of all I do not see the sampledb database listed under "databases" even though it is selected in the "available databases" toolbar (how do I list it there? Because going through "attach..." tells me I do not have the privileges to browse for the database to attach).
I then open up a new query (sampledb is selected), and type (products is one of the table in sampledb):
select * from products;
And I get:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Products', database 'sampledb', schema 'dbo'.
Shouldn't the chosen schema (db_owner) give me the right to do this? And how do I fix this from the query window (without going through the GUI, just using SQL statements)? And how do I list sampledb under the databases when I log in as this user?
Thank you!
-Yannick
I need to create graphs in access in a lick of a button all the data will be in the sql transfered from the access.
If anybody did this before ar having Idea please let me know
I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts???
View 1 Replies View RelatedI have been using the .NET Enterprise Library for DataAccess patterns for some time now. I'm going back to a bunch of old legacy ADO code written in VB6/VB5 and would like to try and create the same "centralized" class that all of my code can instantiate and call to open database objects, call stored procedures, etc.
Does anyone know of any samples of papers which might describe this?
Thanks very much,
...Andrew