|
hi,
i have a very simple problem....i have a ms access database. I have a windows form, which has two textboxes(name and depid) and a combobox(department),i want to write a query that will search all these using WHERE and LIKE.ie i need to create a function by the name search and use the query so that this will search all these...i am new to sql...expecting your help
Thanks in advance,
JAN
|
|
|
|
|
select * from <tableName> where name ='deptname' and depid='1' <br />
-- instead value 1 on depid set another id
for more help describe your problem much more.
I Love SQL
|
|
|
|
|
hi,
i have three layers.
1)form.cs
2)datalayer
3)database(forget about this since it is used to open and close the connections form the ms access)
i have two text and a single combobox.
basic idea is i want a query that will search even if any one of these fields are selected.
also do the same if two are selected.
also do the same if one is selected.
the code to search will have to be on my btn click for serach event(ie is in layer form1.cs).
it will have to call my query that will be in a fn: that is in datalayer ( public DataTable refinesearch(string searchstr1, string s2, string s3)
so i think i need to use "like" condition.
something like
"select * from Employee(name of the table) where name like "string" where empid like "s2" etc........
this is my problem .i do not know the query.
thanking you and expecting your reply....
J
|
|
|
|
|
if datatype of column EmpId is integer then you can not use like statement just
>,<,=,<> but if column EmpId is varchar then you can use like statement
--if empid is integer
select * from Employee where name like '%string%' and empid=1
--if empid is varchar
select * from Employee where name like '%string%' and empid like '%1%'
-- modified at 11:38 Saturday 13th October, 2007
I Love SQL
|
|
|
|
|
Hi,
Thankyou Blue_Boy .it worked fine.
Thanks a lot for your time....
Regards
Jan
|
|
|
|
|
You are welcomed.
I Love SQL
|
|
|
|
|
Guys im having a problem with this statement..
ALTER TABLE tblSample
ADD SampleField nvarchar (30) NOT NULL
CONSTRAINT SampleField PRIMARY KEY
im just trying to set my newly added column and at the same time setting it as a primary key.. but it wont work..
Is there any other way?
or can it set an existing non primary column to a primary one.. if it can then how.. thanks..
|
|
|
|
|
Widgets wrote: but it wont work..
Please supply error messages etc as "won't work" is an interpretation and not an observation of what actually happened.
Have you checked that SampleField contains unique values?
Primary keys can only be assigned to columns with unique values.
You always pass failure on the way to success.
|
|
|
|
|
Several problems:
- You haven't specified a default value for SampleField and have disallowed NULLs so the database doesn't know what value to give that field for existing rows in the table (it would normally use NULL but you've disallowed it);
- Even when you do add a default value or allow NULLs primary keys must be built on unique values, and every row in the table will then have the same value in that column;
- The SQL syntax for ALTER TABLE doesn't allow you to do both at the same time anyway;
- The SQL syntax for adding a primary key is:
ALTER TABLE tblSample
ADD CONSTRAINT PK_tblSample PRIMARY KEY
( SampleField ) PK_tblSample is the name I've given to the constraint, which you need if you later want to drop it. If using CREATE TABLE, you can set the primary key to a single column simply by stating PRIMARY KEY after the column definition, but the name is generated by SQL Server making it harder to use later.
You can define a primary key on a table that doesn't already have one by using the syntax above. You can create compound keys - keys consisting of multiple column values - by including all the key columns between the parentheses.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
thanks guy i really appreaciate your help..
|
|
|
|
|
hey how do i filter rows in a datagridview which have its datasource from a dataset?
the situation is like this:
I use c# , i got a dataset which contains a full table from my database , and i want to view on the datagridview a filtered table which result by the table.select() method, or maybe with other similar method , i couldnt manage to do that :/ i dont know how to view the viltered results only the full table  , please help on the subject any help will be great , thanks in advance to all!
Net
|
|
|
|
|
What I suggest is that you create a dataview based on your datatable.
Bind this dataview to your datagrid.
You can then filter, sort etc on the dataview without changing the contents or base data in your datatable.
Below is an example in VB .NET on how I have filtered and sorted using a dataview:
<br />
dvFileList = New DataView(tblFileList)<br />
dvFileList.Sort = "bytes desc"<br />
filter = "filetype = '" & DataGrid1.Item(DataGrid1.CurrentCell) & "'"<br />
dvFileList.RowFilter = filter<br />
DataGrid1.DataSource = dvFileList<br />
You always pass failure on the way to success.
|
|
|
|
|
thanks , i will look into it
Net
|
|
|
|
|
The first 3 columns of my report are dynamic, depending entirely on parameters passed to the stored procedure. The result set from the stored procedure is used as the dataset in my Reporting Services report. Sometimes there are fields that are not returned in the result set and I wanted to display blanks if they weren't there. I tried all the tricks using iif(isNothing(Fields!field1.Value, "",Fields!field1.Value) and using Fields(Parameters!parm1.value).Value in my argument list. Neither of these worked.
I created 3 textboxes in my report (table) and placed the three different values in them, i.e., Fields!field1.Value, Fields!field2.Value, and Fields!field3.Value. In certain cases, one or two of these fields would not be returned in the result set. I made them hidden. Then in my other textbox, where I wanted to display the value, I added as the expression, Code.DetermineFieldValue(Parameters!parm1.value, ReportItems!txtTextBox1.Value, ReportItems!txtTextBox2.Value, ReportItems!txtTextBox3.Value)
I did this for each of the 3 textboxes, just replacing parm1 with parm2 or parm3.
In the embedded code, the function was defined as:
public function DetermineField(value1 as object, value2 as object, value3 as object, value4 as object) as object
I just refered to my fields as value1 through value4 and it worked. I have been trying this for about a week and couldn't get anything to work. Apparently, it will accept the textbox not having a value, but cannot handle the dataset field directly if it is not returned. It never got to the code. Hope this helps someone.
Dove11
|
|
|
|
|
Your post is difficult to read. To make it more understandable, you should use the <code> and <pre> tags to format your code.
You can't bind a report control to a field in your data source and then not return the field. I had the same issue a couple of years ago when developing reports that allowed the user to pick up to 3 fields to group the data on. If less than 3 grouping fields were selected, some of the grouping columns needed to be hidden. I got round this problem by writing my stored procedures to pad out the resultset with extra 'dummy' fields if less than 3 grouping fields were selected. This way, the resultset would always have the same 'shape' and my data binding expressions would always work. In the report, I would then hide the grouping columns that were not required.
Paul Marfleet
|
|
|
|
|
Hi Friends,
My database has emergency mode. I tried to following commands using alter database to online,
ALTER DATABASE 'MYDATABASENAME' SET ONLINE
But it has processing when it has taking more time.
How to solve this?
Thanks in advance,
Rameshkumar Thirumalaisamy
-- modified at 13:25 Friday 12th October, 2007
|
|
|
|
|
|
Is there a way to make a database connection more dynamic, so that the database could be moved to a different directory and still function without requiring the need to change the path in the database code itself? The code I have so far is static and requires changing each time the MS Access file moves or is moved to a different directory.
Dim strDBPath As String
strDBPath = "J:\GELCO DATABASE\Headcount Database\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)
|
|
|
|
|
usually, connection strings, paths, etc are stored in configuration files so changing them does not require changing code.
You should adopt this practice in general.
|
|
|
|
|
Could you explain where the configuration file is in Microsoft Access? I understand in a web application, there is a configuration file, but what about in a networked version of MS Access?
|
|
|
|
|
I inherited a job including updating website. the website has a login page. When a client logs in the page will show all there orders. Our staff has to also go in and view there orders. The way it is set up know is the staff member has to look up each clients user id and password in order to view orders. I want a quick and dirty way to allow the staff member to use a global password to get into any account. How would I do this. The page offers a SQL statement that grabs the info. Found here
"SELECT * FROM WSUSER WHERE U_ID='"+ REQUEST.QueryString("U_ID") +"' AND U_PASSWORD ='"+ REQUEST.QueryString("U_PASSWORD") +"'"
Is there a way to change this to also grab the record if U_PASSWORD is a Global password like PASSWORD?
|
|
|
|
|
Hi Cory
Try entering
' or '' = ' as the password .... then read Angus' article about SQL Injection attacks[^]. If you still want a global password then tack the following to the end of your SQL.
OR U_PASSWORD = 'MyGlobalPassword'
Regards
Andy
|
|
|
|
|
When I put OR U_PASSWORD = 'MyGlobalPassword' at the end of the SQL statement it allows all passwords to work even blank ones. In the following where do I place this at?
SQLS="SELECT * FROM WSUSER WHERE U_ID='"+ REQUEST.QueryString("U_ID") +"' AND U_PASSWORD ='"+ REQUEST.QueryString("U_PASSWORD") +"'"
|
|
|
|
|
Hi Cory
Try:
SQLS = "SELECT * FROM WSUSER " & _
"WHERE U_ID = @U_ID " & _
"AND @U_PASSWORD IN (U_PASSWORD, 'MyGlobalPassword') then setup a SqlCommand with SqlParameters for @U_ID and @U_PASSWORD. That will remove your SQL injection problem.
You will probably find that posting to the page using Request.Form will be more secure that Request.QueryString too.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
void CSerialRecieveDlg::OnBtnAutosend()
{
// TODO: Add your control notification handler code here
if(!m_ctrlMSCommCOM1.GetPortOpen())
AfxMessageBox("please open the port!");
else
{
CCreateName Dlg;
CString TB_Name;
CString mstr;
if(Dlg.DoModal()==IDOK)
{
TB_Name=Dlg.m_TableName;
}
m_pRecordset->Close();
//// create a new table in SQL
mstr.Format("IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = '");
mstr+=TB_Name;
mstr+="') CREATE TABLE ";
mstr+=TB_Name;
mstr+=" (ID INT,FangWei TEXT,FuYang TEXT,QingXie TEXT)";
try
{
m_pRecordset->Open(mstr.AllocSysString theApp.m_pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
SetTimer(1,500,NULL);
}
}
when dibug to the end, appear an ERROR about "stackoverflow"
what is the problem??
wuhuaiji
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
