|
Samarjeet Singh@india wrote: Exec master.dbo.xp_cmdshell 'C:\My Test'\<<DATE>> then its throwing
error.
What's that? always include those details in your question.
|
|
|
|
|
For example if I tried to run in sql
Exec master.dbo.xp_cmdshell '"C:\Temp\My Test"'
Then I am getting following 3 rows of messages
1- 'C:\Temp\My' is not recognized as an internal or external command
2-operable program or batch file.
3- NULL
Thanks
|
|
|
|
|
|
Thank u very much !!!!!
its working in sql2008 but client is running in older version(Sql2000) and in older version its unable to find the reference and producing following msg
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure
is any other method so that its working in SQL2000 as well
Thanks in advance
|
|
|
|
|
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:
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
|
|
|
|
|
Thank you very much it working for me
|
|
|
|
|
|
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
|
|
|
|
|
Thanks for your great help
|
|
|
|
|
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);
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 !!
|
|
|
|
|
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
|
|
|
|
|
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 !!
|
|
|
|
|
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
|
|
|
|
|
If both columns must match:
SELECT
...
FROM
tableA
LEFT JOIN tableB
ON tableA.ID = tableB.ID
AND tableA.CodeGuid = tableB.CodeGuid
If either column can match:
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
|
|
|
|
|
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?
|
|
|
|
|