Database

Re: Sudden cast problem using executescalar

6-Nov-13 1:22

Gennady Oster wrote:
catch(Exception e){ res = e.Message; }
return res;

There is an error in your query which is causing the ExecuteScalar method to throw an exception. Due to the incredibly poor decision to return the exception message as a valid result from your ExecuteScalar method, the calling code is trying to cast the string containing the error message to a byte[], which will never work. Remove the catch clause from your method to allow the exception to propagate to the calling code.

The fact that you're specifying a WHERE clause but not passing any parameters suggests that your code is susceptible to SQL Injection[^]. Update your code to use parameterized queries before you get a visit from little Bobby Tables[^].

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Gennady Oster6-Nov-13 1:46

Gennady Oster

6-Nov-13 1:46

Thanks, Richard.

Saying truth, I've already understand where I made a mistake.
I anyway want ExecuteScalar to return db error, so I simply added the test is byte for result before casting:

Object sqlResult = ExecuteScalar(sql);
if (!(sqlResult is byte))
{
    return sqlResult.ToString();
}

This is the internal module, and WHERE clause is generated programmatically, so I don't see the ability to SQL Injection.

Thanks once more.

Regards,
Gennady

My English is permanently under construction. Be patient !!

how can left join two table on multiple column???

mhd.sbt5-Nov-13 9:22

mhd.sbt

5-Nov-13 9:22

hi to all
i have two table same below :
tableA with this columns:
ID
CodeGuid
Name
Family

---------------------
and tableB with this columns:
ID
CodeGuid
mycomulns

----------------------------
i want to left join this two table on ID and CodeGuid but i dont want use code same this:

select * from tableA Left JOIN
tableB on (tableA.ID = tableB.ID)
UNION
select * from tableA Left JOIN
tableB on (tableA.CodeGuid= tableB.CodeGuid)
----------------------------
is there another way to handle this output ?
thanks in advance

Re: how can left join two table on multiple column???

Richard Deeming5-Nov-13 9:58

Mysql select query with multiple conditions

5-Nov-13 9:58

If both columns must match:

SQL

SELECT 
   ...
FROM 
   tableA
   LEFT JOIN tableB
   ON tableA.ID = tableB.ID
   AND tableA.CodeGuid = tableB.CodeGuid

If either column can match:

SQL

SELECT 
   ...
FROM 
   tableA
   LEFT JOIN tableB
   ON tableA.ID = tableB.ID
   OR tableA.CodeGuid = tableB.CodeGuid

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Member 102635195-Nov-13 1:50

5-Nov-13 1:50

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:

SQL

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 + "')";
           }

Richard Deeming5-Nov-13 2:36

5-Nov-13 2:36

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);
}
// Repeat for other fields...

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

Member 102635195-Nov-13 20:22

5-Nov-13 20:22

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.

Richard Deeming6-Nov-13 1:08

6-Nov-13 1:08

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

Member 102635196-Nov-13 1:38

6-Nov-13 1:38

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

Richard Deeming6-Nov-13 1:54

6-Nov-13 1:54

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

Member 102635196-Nov-13 18:10

6-Nov-13 18:10

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.

Member 102635196-Nov-13 19:41

unique random alphanumeric in MySQL

6-Nov-13 19:41

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 + "' ";
}

Jassim Rahma5-Nov-13 0:14

Jassim Rahma

5-Nov-13 0:14

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

Re: unique random alphanumeric in MySQL

thatraja5-Nov-13 2:17

thatraja

5-Nov-13 2:17

Googled? MySql also has RAND[^] function, using that you could generate random things. Do customize.

thatraja

Please update this article Useful Reference Books, it's urgent for Q/A section

Re: unique random alphanumeric in MySQL

jschell5-Nov-13 9:09

jschell

5-Nov-13 9:09

Jassim Rahma wrote:
unique random alphanumeric PIN

Why does it need to be unique?

Re: unique random alphanumeric in MySQL

Jassim Rahma19-Jul-14 7:03

Jassim Rahma

19-Jul-14 7:03

because it's a reservation code

Technology News @ www.JassimRahma.com

Using SQL LocalDB

GenJerDan29-Oct-13 3:56

GenJerDan

29-Oct-13 3:56

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?)

YouTube
and
My Mu[sic], Films and Windows Programs, etc.

Re: Using SQL LocalDB

Richard Deeming29-Oct-13 4:51

SQL connection in Visual studio 2012

29-Oct-13 4:51

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

Re: Using SQL LocalDB

GenJerDan29-Oct-13 4:57

GenJerDan

29-Oct-13 4:57

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.

YouTube
and
My Mu[sic], Films and Windows Programs, etc.

Member 1017436328-Oct-13 7:47

Member 10174363

28-Oct-13 7:47

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();

Eddy Vluggen28-Oct-13 8:44

Eddy Vluggen

28-Oct-13 8:44

Try removing the integrated security part of the connection-string.

What does the P in ISP stand for?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Member 1017436328-Oct-13 21:08

Member 10174363

28-Oct-13 21:08

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

Richard Deeming28-Oct-13 12:40

28-Oct-13 12:40

If you want to attach a specific database file with the "user instance" feature, you need to change your connection string:
http://www.connectionstrings.com/sqlconnection/using-an-user-instance-on-a-local-sql-server-express-instance/[^]

Otherwise, you need to attach the database file to the server before you can connect to it. You then specify the name of the database, not the path to the database file, in the connection string.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer