|
hi,
am facing problem with the following;
my table is having 6 columns.
i want to select the data based on "from" and "to" dates,and also if i enter value in any one /two/three/.../all of the fields data should be displayed from the database.
am using this:
if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == ""))
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
}
else if(R_Tag.Text != "")
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "') AND tag_id='" + R_Tag.Text + "' ";
}
else if (R_Category.Text != "" && R_Tag.Text != "")
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')";
}
|
|
|
|
|
Start by reading about SQL Injection[^], and then fixing your code to use parameterized queries.
Your current approach will need 64 different queries to satisfy every possible combination. A simpler approach would look something like:
var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);
queryBuilder.Append("AND (1 = 1");
if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = @RTag");
command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
queryBuilder.Append(" OR category_id = @RCategory");
command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks for reply.
but am getting fatal error at da.Fill(ds).
and :You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1 = 1) OR tag_id = 'E2002076990B019322902641')' at line 1
here is my code: in button click;
i entered tag_id dynamically
// Connect to Mysql
String query;
System.Data.DataTable dt = new System.Data.DataTable();
String conString = "Server=localhost;Database=asset;Uid=root;pwd=root";
MySqlConnection con = new MySqlConnection(conString);
MySqlCommand command = new MySqlCommand();
command .Connection = new MySqlConnection(conString);
command.CommandType = CommandType.Text;
//command.CommandText =query;
con.Open();
MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", con);
string fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd");
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "')";
if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == ""))
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
command.CommandText = query;
da = new MySqlDataAdapter(command);
ds = new DataSet();
da.Fill(ds);
//System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
}
else
{
try
{
var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);
queryBuilder.Append("AND (1 = 1");
if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = @RTag");
command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
queryBuilder.Append(" OR category_id = @RCategory");
command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
if (!string.IsNullOrEmpty(R_Product.Text))
{
queryBuilder.Append(" OR product_id = @RProduct");
command.Parameters.AddWithValue("@RProduct", R_Product.Text);
}
if (!string.IsNullOrEmpty(R_ProName.Text))
{
queryBuilder.Append(" OR product_name = @RProName");
command.Parameters.AddWithValue("@RProName", R_ProName.Text);
}
if (!string.IsNullOrEmpty(R_Gate.Text))
{
queryBuilder.Append(" OR gate_id = @RGate");
command.Parameters.AddWithValue("@RGate", R_Gate.Text);
}
queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();
// command.CommandTimeout = 600;
da = new MySqlDataAdapter(command);
ds = new DataSet();
da.Fill(ds);
da.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
} }
modified 6-Nov-13 7:17am.
|
|
|
|
|
Member 10263519 wrote: queryBuilder.Append("AND (1 = 1)");
You've put an extra closing bracket in that line. It needs to be:
queryBuilder.Append("AND (1 = 1");
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
eventhough am getting same error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1=1OR tag_id = 'E2002076990B019322902641')' at line 1
|
|
|
|
|
I don't have a copy of MySQL to test, but why does the error message include a specific value rather than a parameter name? I would expect it to read near '(1 = 1 OR tag_id = @RTag)'.
There seems to be contradicting information on how to pass parameters to a MySQL query. You might want to try using ?name instead of @name to see if that makes any difference:
if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = ?RTag");
command.Parameters.AddWithValue("?RTag", R_Tag.Text);
}
If it still doesn't work, try running the query in the MySQL Workbench to see if that gives you any more information.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
if i keep , ? instead of @. i got error as:
Fatal error encountered during command execution .at da.Fill(ds) i have placed "Allow User Variables=True" in connection string.plz help me . how to run this query in workbench.
|
|
|
|
|
thanks for the help:i solved it in another way:
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "'";
if(R_Tag.Text != "")
{
query=query+ " AND " + "tag_id=" + "'" + R_Tag.Text + "' ";
}
if (R_Category.Text != "")
{
query = query + " AND " + "category_id=" + "'" + R_Category.Text + "' ";
//query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')";
}
if (R_Product.Text != "")
{
query = query + " AND " + "product_id=" + "'" + R_Product.Text + "' ";
}
if (R_ProName.Text != "")
{
query = query + " AND " + "product_name=" + "'" + R_ProName.Text + "' ";
}
if (R_Gate.Text != "")
{
query = query + " AND " + "gate_id=" +"'" + R_Gate.Text + "' ";
}
|
|
|
|
|
hi,
I want to know how can I created a unique random alphanumeric PIN in MySQL? I have job_order_code field and I want everytime to set a unique random alphanumeric code?
Thanks.
Technology News @ www.JassimRahma.com
|
|
|
|
|
Googled? MySql also has RAND[^] function, using that you could generate random things. Do customize.
|
|
|
|
|
Jassim Rahma wrote: unique random alphanumeric PIN
Why does it need to be unique?
|
|
|
|
|
because it's a reservation code
Technology News @ www.JassimRahma.com
|
|
|
|
|
Works great on my development machines.
Not so much on any "real" machines.
Microsoft says it needs .NET 3.5 (or 4, depending on where you look)
But loading .NET 4 on the "real" machines still doesn't make it work*.
Loading .NET 4.5 does make it work. Even if the application is compiled for version 4, not 4.5
*"Doesn't work" meaning can't attach the database file, mostly. Sometimes it can't even find it.
So what was introduced in .NET 4.5 that makes all these problems go away? My development machines are still on .NET 4 and manage to work in or outside of VS. And can this piece be added separately so my uses won't be forced to install v4.5 just to make v4 applciation work? (I'm thinking it might be because I have the more full versions of SQL Server on the dev machines, too. Yes? Is there a teeny part that I can install separately to fix it?)
|
|
|
|
|
According to this[^], you'll need to install the .NET 4.0.2 update[^] as well as SQL 2012 Express[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks. I'll give that update a try on another "clean" machine and see if it solves the "invalid key name" error when trying to do an AttachDBFilename.
|
|
|
|
|
I'm trying to connect to northwnd database by using Visual studio 2021 and c++. I have installed "Microsoft SQL Server 2012 - 11.0.2100.60 (X64)"
I get the error:
'System.Data.SqlClient.SqlException' in System.Data.dll
Cannot open database "E:\C++\Projects\DB\Debug\NORTHWIND" requested by the login. The login failed."
with the following code. Can anybody help me??
String ^sqlServerInstance = ".\\SQLEXPRESS";
String ^dbase = "E:\\C++\\Projects\\DB\\Debug\\NORTHWIND";
String ^UID = "myUID";
String ^PWD = "myPWD";
String ^ISP = "true";
String ^myConnectString = "Database=" + dbase + ";Server=" + sqlServerInstance + ";Integrated Security=" + ISP + ";";
SqlConnection ^myConnection = gcnew SqlConnection(myConnectString);
myConnection->Open();
|
|
|
|
|
Try removing the integrated security part of the connection-string.
What does the P in ISP stand for?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I tried to remove the integrated securit from connection string but i got the same error.
ISP is just a self-definend variabile used ro build the connection string.
Thanks again eddy
|
|
|
|
|
|
There are some issues with your connection string.
"Database" is the *name* of the database in SQL Server (as you can see it e.g. in SQL Server Management Studio), not the path to the database file.
"Integrated Security=true" means that you login with your Windows Credentials. That means, your Windows user must have access rights to the databse.
If you want to use a specific (database defined) user, do not use Integrated Security, but set username and password instead.
For more information, look at http://www.connectionstrings.com/sql-server/[^].
|
|
|
|
|
Hi
Below query is taking 10 seconds to return 90 rows.
Using cte to get the dates of the last 90 days, then summing the production for each day (Alloocated is production qty) and DateCreated is a DateTime value that the units were produced.
So the output is the date of the last 90 days, and the total production for each day
Can anyone point out what I am doing wrong?
;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)
SELECT convert(nvarchar(10),DateValue,121) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(nvarchar(10),DateCreated,121) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(nvarchar(10),DateCreated,121))
as trk on trk.TrkDate = convert(nvarchar(10),DateValue,121)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)
|
|
|
|
|
I don't think you can blame the CTE, I would look into all the convert dates, are you storing your dates as strings in vektronix?
Try changing all the string joins to date joins (eliminating the time component).
Try creating a temp table with the 90 dates instead of the CTE if that makes no difference. I use CTEs only as a last resort and this does not require one.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft.
The dates are stored as dates in the database. How can I do the joins eliminating the time component because that is the reason I did all that converting to nvarchar(10) was to eliminate the time part of the dates
|
|
|
|
|
If you are using 2008 then cast/convert to DATE instead of DATETIME
DECLARE
@D DATETIME
SET @D = GETDATE()
SELECT @D
SELECT CONVERT(DATE,@D)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Mycroft = a bit faster but still slow for a mere 90 rows.
Bellow returns 10000 rows in under as second - so the problem must be in the second part
;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-10000,GETDATE())
)
select convert(date,DateValue) from Dates
OPTION (MAXRECURSION 32747)
This executes in under a second (This is basically the output I want, but I also want days where there was zero production hence the CTE)
select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)
With the change to date as opposed to a datestring nvarchar(10) as you suggested, below has reduced from about 17 second to 5 seconds. But to be that seems terribly slow for 90 rows?
;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)
SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated))
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
