Re: Sudden cast problem using executescalar - Database Discussion Boards

Re: How to create directory in SQL with Space

Richard Deeming7-Nov-13 8:33

7-Nov-13 8:33

For SQL 2000, you'll need to stick with xp_cmdshell, but you need to put mkdir in front of the path you want to create:

SQL

Exec master.dbo.xp_cmdshell 'mkdir "C:\My Test\<<DATE>>"'

http://www.sqlservercentral.com/Forums/Topic604168-8-1.aspx[^]

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

Re: How to create directory in SQL with Space

Samarjeet Singh@india21-Nov-13 0:57

Samarjeet Singh@india

21-Nov-13 0:57

Thank you very much it working for me

Re: How to create directory in SQL with Space

jschell7-Nov-13 9:26

jschell

7-Nov-13 9:26

You might want to tell everyone that SQL 2000 is well past the end of life - including extended support.

http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL[^]

Re: How to create directory in SQL with Space

Minghao Xue20-Nov-13 22:03

Minghao Xue

20-Nov-13 22:03

below procedure should be the solution

create procedure sp_create_directory
(
@full_path varchar(500)
)
as
set nocount on

declare @command varchar(1000)
set @command = 'mkdir ' + @full_path

exec master..xp_cmdshell @command , no_output

set nocount off
GO

Re: How to create directory in SQL with Space

Samarjeet Singh@india21-Nov-13 0:57

Samarjeet Singh@india

21-Nov-13 0:57

Thanks for your great help

Sudden cast problem using executescalar

Gennady Oster5-Nov-13 22:25

Gennady Oster

5-Nov-13 22:25

Hi !

Here is the snip of code in question:

.......
                sql = "select " + picField + " from " + table + " where " + where;
                conn = new SqlConnection(connectionstring);
                byte[] picture = (byte[])ExecuteScalar(sql);  // Shows error
                Image source = Image.FromStream(new MemoryStream(picture));
                Image th = source.GetThumbnailImage(w, h, null, IntPtr.Zero);
.......
        public object ExecuteScalar(string strCommand)
    {
        object res=null;
        conn.Open();
        SqlCommand cmd = new SqlCommand(strCommand, conn);
        try
        {
            res = cmd.ExecuteScalar();
        }
        catch(Exception e)
        { res = e.Message; }
        finally
        {
            conn.Close();
        }
        return res;
    }

It worked like a sharm some time ago.
Code was not changed. The table definition also was not changed and field declaration is as follows:
[Picture_Image] [image] NOT NULL

But now I get error:
unable to cast object of type 'system.string' to type 'system.byte '
I suppose that something may be changed in the server and/or DB settings. But what ??

Can somebody please shed a light on this issue?

Thanks in advance.
Regards,
Gennady

My English is permanently under construction. Be patient !!

Re: Sudden cast problem using executescalar

Richard Deeming6-Nov-13 1:22

Richard Deeming

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

Re: Sudden cast problem using executescalar

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

Richard Deeming

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

Mysql select query with multiple conditions

Member 102635195-Nov-13 1:50

Member 10263519

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

Re: Mysql select query with multiple conditions

Richard Deeming5-Nov-13 2:36

Richard Deeming

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

Re: Mysql select query with multiple conditions

Member 102635195-Nov-13 20:22

Member 10263519

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.

Re: Mysql select query with multiple conditions

Richard Deeming6-Nov-13 1:08

Richard Deeming

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

Re: Mysql select query with multiple conditions

Member 102635196-Nov-13 1:38

Member 10263519

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

Re: Mysql select query with multiple conditions

Richard Deeming6-Nov-13 1:54

Richard Deeming

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

Re: Mysql select query with multiple conditions

Member 102635196-Nov-13 18:10

Member 10263519

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.

Re: Mysql select query with multiple conditions

Member 102635196-Nov-13 19:41

Member 10263519

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

unique random alphanumeric in MySQL

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

Richard Deeming

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.