hiiii all,
plz , I need help:
i want to know relations between tables stored in sql server database :
1-parent column.
2-parent table.
3-child coulmn.
4-child table.
using c# ado.
thanks.
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,
[Code] ...
HiI have to make export of SQL Server Database to MS Access and I havedone it with the tables but now I need to transfer(export) therelations, keys and indexes. Can any1 tell me how to read relations,keysand indexes from SQL Server and convert them to MS Access.(it seams thatfor creation of indexes in MS Access ADOX is the only way but ADOXcannot read all information about keys,indexes from SQL Server.)All I can see is that I have to use SQLDMO to enumarate allrealtion/keys/indexes from SQL Server database and use ADOX to createthis relations/keys/indexes in MS Access database, but I don't knowexact matching attributs of relations/keys/indexes from SQL to Access.Thanks
View 3 Replies View RelatedHi There,Im trying to display a list of results and have extra information pulled from another table. Please read on as this isnt what you think, just hard to explain but I will try.In Table 1 I have a set of Orders, example data below.Order 1, Line 1Order 1, Line 2Order 1, Line 3Now I have another table to record what lines a user would like to monitor, for this example the user will monitor these lines.Line 1Line 2but not Line 3I need to display a single grid view using the least amount of queries possible to diplay the following resultOrder 1, Line 1, MonitoredOrder 1, Line 2, MonitoredOrder 1, Line 3, NOT MonitoredI have tried viewed, but with the relationships it only displayes what is avialable in both tables, I have also thought about using a DataSet relationship with two queries through the code, but am really wondering if anybody knows of a simple and low overhead way of producing this result? Mayby stored procedures?Kind Regards,Gareth
View 2 Replies View Relatedi want to write program that will return
the relations between the tables and its type (one to many,.....etc)
any help
Thanks in advance
Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.
for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]
I tried it by using information_scheme but I was unsuccesfull.
The result should be:
A
C
D
B
E
Thank you,
Tom.
hi! I've been using sql server for a while but until recently have kept things pretty simple. now I'm trying to expand my horizons by trying to tackle some more complex applications, and one I'm really struggling with is nested relations. I hope this is in the right forum; if it is not, please feel free to move it, thank you!
here is my problem: I'm desigining a simple "Downloads" page in asp.net, and I have two tables set up. One is Downloads and the other is DownloadCategories. here is a simplified layout of the tables:
Downloads:
ID PK
Title
Description
CatID FK
DownloadCategories
ID PK
Name
Description
ParentID FK
basically CatID in downloads is a foreign key to the ID in downloadcategories, and ParentID is a foreign key to the ID in the same table, downloadcategories. This is set up because I want to support an infinite number of categories, each being able to support their own subcategories, which can go deeper into more subcats, and so on...
the problem is that I can't seem to get them to fill into the dataset I've created in vs 2005's designer. I have a procedure SelectAll which retrieves all rows, and SelectMain which retrieves only the topmost categories (where ParentID=Null). If I fill the datatable with SelectMain, I don't get any of the child categories, and if I call SelectAll, I get just a single table with all the rows, but no relations.
I have defined a relation in the datatable that mirrors that of the database, but no matter what I try, I cannot get it to show the relationship in the datatable when I fill it. am I doing something wrong? this is kind of how I have it setup:
dataset with Downloads and Categories datatables, relations from Categories to Downloads, and from Categories to Categories. I have the two queries added to the table adapter, and I call the SelectAll query to fill it, but all it does is fill the table with rows; it doesn't create any relations.
I hope this explanation of my problem makes sense. as I said I'm still very new to this complex stuff, and I'm hoping to get my head around it soon, because I really need the functionality (not to mention the skills!) so if you can take a moment to go over what I've explained and point out where my flaw might be, I would really appreciate it!!
if you need any more information to help, please let me know and i'll get right back to you. thanks a bunch!
-SelArom
Hi all,
In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations
Right now my code looks like this
foreach(object obj in Objects){
int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
}
The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
Thanks
Regards
Prasad.
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link:
[URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
Hi everyone,
I am new to SSIS and I thought maybe someone would give me tips for solving the problem I am facing.
Overview:
I want to insert data contained in a flat file into several DB tables, which have N-M relations.
For illustration, I would explain the problem on a very simple DB:
1. The database contains the following 3 tables:
EMPLOYEE (EMP_ID, EMP_NAME)
PROJECT (PROJ_ID, PROJ_NAME)
EMP_PROJ (EMP_ID, PROJ_ID) , where EMP_ID and PROJ_ID are foreign keys referencing records in the EMPLOYEE and PROJECT tables respectively.
2. Each entry in the falt file contains the following data:
EMP_ID, EMP_NAME, PROJ_ID, PROJ_NAME
3. In SSIS, I have created a Data Flow Task containing:
- a path from a Falt File Source to an SQL Server Destination (Table: Employee)
- a path from a Falt File Source to an SQL Server Destination (Table: Project)
- a path from a Falt File Source to an SQL Server Destination (Table: Emp_proj)
Note: I used SQL Server Destination, because I need to import a huge amount of data and I read that this component performs better than the OLE DB Destination!
Questions:
1. I would like to eliminate EMP_ID and PROJ_ID from the Flat File Source. Instead, I would like these fields to be generated automatically upon insertion.
a. How can I do this and propagate the generated key among the different paths, which I have explained previously?
b. Can I first generate the two keys somehow then the parallel insertions into the different tables should start using the generated keys?
2. Is my solution correct in the first place? Or is there another better way for inserting data which belong to N-N relations?
Thanks in adavance,
Samar
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link:Â [URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- Query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM Â [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]Â
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2
Hi,How to get list of all relations of certein database's table?
View 7 Replies View RelatedHi,
I've been trying to export a whole database from one server to another (connected through a local network) but the keys always get lost: when I open the diagrame I can actually see them just before the tables names appear. I've tried inserting the primary keys for each table manually with the export wizard, which took me some time, but no relations were exported. Any way of doing this automatically?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Hayyy
I have just made a database with 3 tables, they are now connectet with relationships.
My problem is when I write my select statement:
select * from table1 a, table2 b where a.ID=2.
I will get all the ´records from both the tables, I just want One record. Then I addes some too my select statement so it now looks like this:
select * from table1 a, table b where a.ID=2 AND a.No=b.No
Now I got the right result, but if this is the only way to do it, there is no recent to make all the relations i the DB between the tables.
Thank You... Jonas
Hi,after i exported my DataBase From my local computer to host, all tables and views have exported, but my tables Realations dosent exported and i have'not any realation in my database, i can't create new realation , when i want to do this i get an error:" You do not have sufficient privelege to create n new database diagram" What this mean??how i can export my database to my host whit my realations?plz help me i you can!thanks.
View 6 Replies View Relatedhello dear friends? l want to take values from my table via a stored procedure and appoint this value,that l take from table via stored procedure, to Textbox1.TExt or LAbel1.Text table(id,name,surname,adress) id is primary key and select query will do this ;select name which id =4 and then this value will appoint to label or textbox text thanks indeed for help
View 1 Replies View RelatedI want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.
View 6 Replies View RelatedHi all,
I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.
For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?
what's the easiest way to script all table,views and stored procedures?
I would like to script them like from ssms i right click on each one and choose script table as --- but how can I do it for all of them at once?
Hi to all,
As I am going to deal with a huge number of database tables, I thought that drawing their diagrams will be the most professional way of keeping track of what I am doing. So In Enterprise Manager using the Wizard I made it to be drawn nicely.
But I have a problem now. I see that the relations defined among the tables are represented correctly but the line connecting the two table is drawn randomly. I mean the starting point of the line doesnt start from the column having the primary key and doesnt end near to the column having the foreign key. Viewing my diagram I want to see the lines to start and end showing the 2 related key columns.
Is there a way to accomplish this like setting an option, clicking somewhere or should I try to drag the lines to manually?
Thanks in advance
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
Hi,
I was just wondering if something could be explained to me.
I have the following:
1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.
How come i need to specify data type and length in three different places? Am i doin it wrong?
Any information is greatly appreciated.
Thanks
Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..
hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards
Ali
I am a beginer in SQL Server and if someone knows I would like to knowwhere are stored columns description - am refering at that columndescription that can be write for each column when you create a tablein a SQL Server Database using SQL Server Enterprise Manager.I want to get this information from the database and display it in aC# application, I 've tryed COLUMNPROPERTY function and sp_columnsprocedure, but this are not giving me the column definition, onlytype, name, etcThanksMaria
View 4 Replies View RelatedThere are two ways to create a temporary tables in stored procedures
1: Using Create Table <Table Name> & then Drop table
ex. Create Table emp (empno int, empname varchar(20))
at last : drop table emp
2. Using Create table #tempemp
( empno int, empname varchar(20))
at last : delete #tempemp
---which one is preferrable & why.
what are the advantages & disadvantages of the above two types.
I am trying to create a SQL data adapter via the wizard, however, I get
the error "Invalid object name #ords" because the stored procedure uses
a temp table. Anyway around this? Thanks.
I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
i have more tables
exmaple:
table1(id,title,content,date)
table2(did,type,title,text1,text2,requestdate)
table3(subid,value1,value2,value3,value4)
.......................15 tables left,some exmaple
now i want use stored procedure do search for asp.net
asp.net send search keyword to sql server,sql server get keywords execute search and return results to web application(asp.net)
i don't konw how what write t-sql,please friends help me.
because table colums is different,so need write t-sql put the temp DATA TO Temp DataTable,last return Temp DataTable to asp.net,asp.net execute split pager display data,eventable colums is different,so on asp.net links is different,need t-sql programming,hope friends can help me complete this effect(i need code,C#/VB.NET +T-SQL).
i at ago like this do.
<%@ Page Language="C#" ValidateRequest="false"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
private void page_init()
{
if (!IsSecure())
{
Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
}
}
private void page_load(object sender, EventArgs e)
{
string CurrentPageValue = "";
string Qu = "";
Double PageValue = 0;
CurrentPageValue = Request.QueryString["PageN"];
Qu = Request.QueryString["QuKey"];
Qu = ReplaceStr(Qu);
if (Qu != null)
{
if (IsNumber(CurrentPageValue))
{
PageValue = Convert.ToDouble(CurrentPageValue.Trim());
Init_Data(PageValue,Qu);
}
else
{
PageValue = 1;
Init_Data(PageValue,Qu);
}
}
else
{
ExecuteError();
}
}
private void Init_Data(Double CurrentPage,string QuKey)
{
try
{
string ConStr = GetConnectionString();
string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%' order by date desc";
//expmale for test,i have 17 tables,so write some table do test
System.Data.DataTable ResultsTable = new System.Data.DataTable();
ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
ResultsTable.Columns.Add("title", System.Type.GetType("String"));
ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
ResultsTable.Columns[0].Unique = true;
ResultsTable.Columns[0].AutoIncrement = true;
ResultsTable.Columns[0].AutoIncrementStep = 1;
ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
System.Data.DataSet Ds = new System.Data.DataSet();
SearchConnect.Open();
ada1.Fill(Ds, "table1");
ada2.Fill(Ds, "table2");
ada3.Fill(Ds, "table3");
ada4.Fill(Ds, "table4");
string ReTitle = "";
string ReUrl = "";
string ReIntro = "";
DateTime ReDate = System.DateTime.Now;
if (Ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[0].Rows[1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url1";
ReIntro = Ds.Tables[0].Rows[1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
if (Ds.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[1].Rows[1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url2";
ReIntro = Ds.Tables[1].Rows[1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
//........loop to table end
//................................
Ds.Dispose();
ada1.Dispose();
ada2.Dispose();
ada3.Dispose();
ada4.Dispose();
SearchConnect.Dispose();
if (SearchResults.Rows.Count > 1)
{
DisplayData(CurrentPage, QuKey, SearchResults);
}
else
{
SearchResults.Dispose();
ExecuteError();
}
}
catch
{
ExecuteError();
}
}
private void DisplayData(Double PageNumber, string Keywords, System.Data.DataTable CurrentData)
{
Double TotalPage = 0;
Double TotalRow = 0;
Double TempPage1 = 0;
decimal TempPage2 = 0;
int StartValue = 0;
int EndValue = 0;
int PageSize = 30;
TotalRow = CurrentData.Rows.Count;
TempPage1 = TotalRow/PageSize;
TempPage2 = Convert.ToDecimal(TotalRow / PageSize);
if (Convert.ToDouble(TempPage2 - TempPage1) > 0)
{
TotalPage = TotalRow / PageSize + 1;
}
else
{
TotalPage = TotalRow / PageSize;
}
if (TotalPage < 1) { TotalPage = 1; }
if (PageNumber > TotalPage) { PageNumber = TotalPage; }
if (PageNumber == TotalPage)
{
EndValue = TotalRow;
}
else
{
EndValue = PageNumber * PageSize;
}
StartValue = PageNumber * PageSize - PageNumber;
for (int j = StartValue; j < EndValue; j++)
{
System.Web.UI.WebControls.TableRow Tr = new TableRow();
System.Web.UI.WebControls.TableCell Td1 = new TableCell();
System.Web.UI.WebControls.TableCell Td2 = new TableCell();
System.Web.UI.WebControls.TableCell Td3 = new TableCell();
Td1.Controls.Add(new LiteralControl(CurrentData.Rows[j][1].ToString()));
Td2.Controls.Add(new LiteralControl(CurrentData.Rows[j][2].ToString()));
Td3.Controls.Add(new LiteralControl(CurrentData.Rows[j][3].ToString()));
Tr.Cells.Add(Td1);
Tr.Cells.Add(Td2);
Tr.Cells.Add(Td3);
SearchResults.Rows.Add(Tr);
}
System.Web.UI.WebControls.TableRow StateTr = new TableRow();
System.Web.UI.WebControls.TableCell StateTd = new TableCell();
StateTd.ColumnSpan = 3;
if (PageNumber - 1 > 0)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber - 1) + "&qu="+Keywords+">prev</a>"));
}
if (PageNumber !=TotalPage)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber + 1) + "&qu="+Keywords+">Next</a>"));
}
StateTr.Cells.Add(StateTd);
SearchResults.Rows.Add(StateTr);
CurrentData.Dispose();
SearchResults.Dispose();
//rows count > 200,runtime at 1.6 seconds left,
//rows count > 1000,runtime at 8 seconds left,
//rows count >10000,runtime at 12 seconds left
//execute speed is very bad,so i want use sql stored procedure search,but i don't write t-sql,hope friends hope me,thanks
}
private void ExecuteError()
{
SearchResults.Controls.Add(new LiteralControl("we're sorry,we unable find even contain your key data,please try other keywords.suppot to ...."));
SearchResults.Dispose();
}
private bool IsSecure()
{
if (!Page.Request.IsSecureConnection)
{
return true;
}
else
{
return false;
}
}
private bool IsNumber(string Restr)
{
try
{
if (Restr != null)
{
string TempStr = Restr.Trim();
if (TempStr != "")
{
System.Text.RegularExpressions.Regex MyTry = new Regex("@[0-9]*$");
if (MyTry.IsMatch(TempStr))
{
Double valueR;
Double = Convert.ToDouble(TempStr);
if (valueR >= 1 && valueR <= 10000)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
catch
{
return false;
}
}
private string GetConnectionString()
{
try
{
string ConnectionString = "datasource=......,initial catalog=databasename,trusted_connection=yes;integrate security info=true;";
return ConnectionString;
}
catch
{
return "!";
}
}
private string ReplaceStr(string k)
{
try
{
if (k != null)
{
string r = k.Trim();
if (r != "")
{
r = r.Replace("~", "");
r = r.Replace("!", "");
r = r.Replace("@", "");
r = r.Replace("#", "");
r = r.Replace("$", "");
r = r.Replace("%", "");
r = r.Replace("^", "");
r = r.Replace("&", "");
r = r.Replace("*", "");
r = r.Replace("(", "");
r = r.Replace(")", "");
r = r.Replace("\", "");
r = r.Replace("|", "");
r = r.Replace("[", "");
r = r.Replace("]", "");
r = r.Replace("{", "");
r = r.Replace("}", "");
r = r.Replace(":", "");
r = r.Replace(";", "");
r = r.Replace("'", "");
r = r.Replace("", "");
r = r.Replace("<", "");
r = r.Replace(">", "");
r = r.Replace("!", "");
r = r.Replace(",", "");
r = r.Replace(".", "");
r = r.Replace("?", "");
r = r.Replace("/", "");
r = r.Trim();
return r;
}
}
else
{
return "";
}
}
catch
{
return "";
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Do Search SQL Server Page</title>
</head>
<body>
<asp:Table runat="server" ID="SearchResults">
</asp:Table>
</body>
</html>
MyTest:
SQL SERVER 2005+WINDOWS SERVER 2003+.NET 2.0
I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!
View 1 Replies View RelatedIs there a fast way to see, which relations a table has for his PK?
View 2 Replies View Related