|
TRy using parameterised queries, then you can be sure that your data is going to be presented in a format your source understands.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Thanks for ur co-operation but let me briefly describe what i want
my query is changing based on the filters used .
to accomodate this situation , i have made a function GetData() which is being called after the user clicks on the button (btnFind) and they query is decided based on the filters specified by the user which means the parameters also change .
---Function GetData()----
private void GetData(string selectCommand)
{
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
string currentDirectory = System.Environment.CurrentDirectory;
OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + currentDirectory + "\\dbLetters.mdb';Persist Security Info=False";
// Create a new data adapter based on the specified query.
dataAdapter= new OleDbDataAdapter(selectCommand, con);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
dataGridView1.DataSource = bindingSource1;
}
-------END--------
-----query specified-----
string command = "";
#region Sender of the letter is specified
else if (chkFrom.Checked == true && chkDate.Checked == false && chkSubject.Checked == false)
{
if (cmbFrom.SelectedItem.ToString() == "Others")
{
command = "Select * from tblIncomingLetters where From LIKE ='" + this.txtFrom.Text+ "'";
}
else
{
command = "Select * from tblIncomingLetters where From LIKE '" + cmbFrom.SelectedItem.ToString() + "'";
}
}
#endregion
#region date is specified
else if (chkDate.Checked == true && chkFrom.Checked == false && chkSubject.Checked == false)
{
command = "Select * FROM tblIncomingLetters where InwardDate Between " + dateTimePicker1.Value.Date+ " AND " + dateTimePicker2.Value.Date;
}
#endregion
#region subject is specfied
else if (chkDate.Checked == false && chkFrom.Checked == false && chkSubject.Checked == true)
{
command = "Select * from tblIncomingLetters where Subject LIKE '" + this.txtSubject.Text + "'";
}
#endregion
-------
Now plz tell me how to solve it or is there any other better way to achieve the same result.
I shall be really thankful 2 u
Sadaf
|
|
|
|
|
First of all, what I said stands. Use parameterised queries if you must do SQL in your presentation layer. I think it's inexcusable, but that's another discussion.
Second, read this[^]. Your code is a disaster, and a malicious user can easily comprimise your database, erase it, or gain access to any data they want from it.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
there is one thing I forgot to mention which is that
once the query to retrieve the data from database is decided (string command is formed) , I call the GetData function passing it the string command
GetData(command);
Kidnly correct me if you think this can done in other ways!
Sadaf
|
|
|
|
|
|
Hi All,
I want to list all the temporary tables for a perticular session in SQL Server 2005. I can see all the temporary tables in tempdb by using the following query
select * from tempdb..sysobjects
But i need to find out the tables which are created for a specific session.
Is there any way to do that?
Waiting for your reply.
Thanks in advance.
Regards,
Sulaman Riaz
|
|
|
|
|
|
Hi all
I am not sure to post this question is suitable or not.
How/Can I select the columns of entire database tables? I am going to clear about the question.
For select all columns of one tabble.
Select * from TableName.
Similarly, what is the query for select * from *(all tables inside a database).
Thanks
|
|
|
|
|
SQL Server 2005 : Select * from sys.columns
SQL Server 2000 : Select * from syscolumns
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Why do you want to do that ?
if you have lost the name of your table if, you are using SQl2005,2000 ,check on Enterprise manager and for 2005 Business intelligence, and tell me , if its an Application request, what was the thing that makes think its a right way to go by select all the Tables in the DB
?
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
Is it possible, with an SQL statement, to retrive a column's data type?
Note: working with MS Access 2000 database (OleDbCommand in C#).
Ron
|
|
|
|
|
Hi Ron,
You may try the following code snippet and see if this helps.
--------------------------------------------------------------------------
BEGIN CODE
using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {
public static void Main ()
{
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\DataBaseName.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
Console.WriteLine("Made the connection to the database");
String cmd = "SELECT * FROM YourTableName";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(cmd, con);
DataSet ds = new DataSet();
adapter.Fill(ds, "YourTableName");
DataTable item = ds.Tables[0];
Console.WriteLine("Table name: {0}", item.TableName);
Console.WriteLine("Its columns are:");
foreach (DataColumn col in item.Columns)
Console.WriteLine("{0}\t{1}", col.ColumnName, col.DataType);
con.Close();
}
}
END CODE
Hope this helps  .
--------------------------------------------------------------------------
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Thanks John!
I thought there might be an issue if no rows were returned, but it works great, thanks!
Ron
|
|
|
|
|
how to add 7 days to my date filter
i want to add 7 days to date the user will select
select employee_Id from attendance where [date]='2005-01-10' + 07
Ahmed hassan
|
|
|
|
|
Are you using SQL Server? If so, look at DateAdd.
|
|
|
|
|
try with following Query
select employee_Id from attendance where [date]= dateadd(day,7,'2005-01-10')
smile
|
|
|
|
|
I have a table i am querying, and a prety odd set i need to return.
the columns i am interested in are the following types:
int, nvarchar, datetime,datetime.
the int has a pretty normal progression, 1,2,3,4,5. the nvarchar contains only 2 possible values, we'll say red and blue. now, the data lines up something like this:
1 blue
2 blue
1 red
3 blue
2 red
3 red
ie, no particular order, but every number listed in the int coulmn has a red and a blue row, so a normal query would return
1 blue
2 blue
3 blue
1 red
2 red
3 red
if ordering by the colors
now, the two date times are a strat date and end date, and they line up weird. the blue and red of each int do not have the same range, but are considered related. however, all of the dates for a color tie together, so if the enddate for 1 blue is 4/25/2008, then the startdate for 2 blue is 4/26/2008. same goes for red, except what i end up with is something like this
1 blue 2/23/2008 3/12/2008
1 red 2/25/2008 3/20/2008
what i need to do is write a query that returns only 2 values, where they are both less than todays date, one is blue and one is red, and they both have the same int value.
now, the query i am using looks like this:
select top 2 * <br />
from [table] where enddate < cast('4/15/2008 12:59:59.999' as datetime)<br />
order by enddate desc
this particular one is right on the cusp of one of the transition dates, and the result i am getting is:
1 red
2 blue
when i need
1 red
1 blue
or
2 red
2 blue.
the int must always be the same and the nvarchar must always be different. is there a way to force this in a query?
what i end up needing is a query that returns
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Change your order by to include the number field as the first portion before the enddate, e.g. order by id, enddate.
|
|
|
|
|
Didn't actually try running this, but you can give it a whirl:
select top 2 a.* from [table] a
INNER JOIN [table] b ON a.int = b.int
AND a.nvarchar <> b.nvarchar
AND b.enddate < cast('4/15/2008 12:59:59.999' as datetime)
where a.enddate < cast('4/15/2008 12:59:59.999' as datetime)
order by enddate desc
|
|
|
|
|
your code is a thing of beauty
i modified it to run against the table i was using and was getting the same results i was getting before, whihc confused me because looking at it, it definatley said exaclty what i needed. so i opened a new view and plugged it in and started playing with it. the first thing i did was change it to show the results from a and b, and bingo, there it was. with this, i dont need the top 2 rows, just the top 1 with the nd date set to order by desc, and tada, i had the exact results i was looking for. so here is what i used:
SELECT TOP (1) a.nvarchar, b.nvarchar AS nvarchar2, b.int, a.int AS int2, a.StartDate, b.StartDate AS StartDate2, <br />
a.EndDate, b.EndDate AS EndDate2<br />
FROM [table] AS a INNER JOIN<br />
[table] AS b ON a.int = b.int AND a.nvarchar <> b.nvarchar AND <br />
b.EndDate < getdate() AND a.EndDate < GETDATE() <br />
ORDER BY int2 DESC
Thank you very much for this, you saved me a massive headache. And thank you to everyone that replied, i appreciate any input 
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
How can I avoid the error:?
-------------------------------------------------------------------------------------------------------
Run-time error '2749':
There isn't enough memory to complete the Automation object operation in the OLE object.
--------------------------------------------------------------------------------------------------------
Here is the code:
--------------------------------------------------------------------------------------------------------
Private Sub cmdLoadOLE_Click()<br />
<br />
Dim MyFolder As String<br />
Dim MyExt As String<br />
Dim MyPath As String<br />
Dim MyFile As String<br />
<br />
MyFolder = Me.SearchFolder__<br />
' Get the search path.<br />
MyPath = MyFolder & "\" & "*.jpg"<br />
' Get the first file in the path containing the file extension.<br />
MyFile = Dir(MyPath, vbNormal)<br />
<br />
Do While Len(MyFile) <> 0<br />
<br />
'[fldID] = CInt("4869")<br />
[fldID] = CInt(Replace(MyFile, ".jpg", ""))<br />
[fldOLE].Class = "MSPhotoEd.3"<br />
[fldOLE].OLETypeAllowed = acOLEEmbedded<br />
[fldOLE].SourceDoc = MyFolder & "\" & MyFile<br />
[fldOLE].Action = acOLECreateEmbed<br />
[fldOLE].Action = acOLEClose<br />
<br />
' Check for next OLE file in the folder.<br />
MyFile = Dir<br />
<br />
' For Access 97 only, use the following line of code:<br />
DoCmd.RunCommand acCmdRecordsGoToNew<br />
<br />
Loop<br />
<br />
End Sub
--------------------------------------------------------------------------------------------------------
Thanks in advance
|
|
|
|
|
Hi i am from the field of java and new in asp.net, can anybody explain me how can i execute multiple queries , means i am executing 5 queries in a line if any of query fails the transaction should rollback and if all 5 queries executed successfully then commit action should perform.
|
|
|
|
|
First of all, I suggest you read what the forum guidelines say about post titles. 'newbee ado.net' gives us no idea what your question is. Your question relates to ado.net database transactions and your title should reflect this.
Have a look at this CodeProject article about ADO.NET Transactions[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I am working on SQL Server 2005 and I have a doubt. Clinet application is in VC ++ and access SQL Server through ODBC API. If client application starts a transaction and locks some rows in a table and is terminated abnormally (crashed). Will SQL Server automatically rollback all those updates made by the client and release the locks on rows? If SQL Server is not handling this situation, how to handle it? .Please respond to my query.
Thanks in advance
modified on Tuesday, March 25, 2008 12:04 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
