|
And your QUESTION is!
I think Access has a macro function so step through the insert while recording a macro and inspect the SQL code generated.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys,
PLZ help me!
Scenario - My client has 2 server machine. one is database server with oracle 10g database 64-bit. Another server(new one) is windows 2008 server which has an .net 2.0+3.5 website hosted. This website make use of System.data.oracleClient dll . Problem is that this app is not able to connect to oracle 10g database server though it is able to connect to some other 11g R2 database server. On this app server we have also tried to install oracle 10g 32-bit client but still can't access. Tnsping and sqlplus is working fine. Pls. suggest.
Solution that we tried :-
- Gave permission and added various users to oracle directory and sub-directory like IIS_Iuser,network service, anonymous etc.
Possible culprit:-
- Earlier on this app server oracle 11g 32-bit client was installed. So my client has although worked on to remove it by manually deleting folder , deleting registeries , extra home directories. but it looks like a possible reason to me..
Note - we use some internal ddl for dataaccess , so im not able to see actual error just some OLEDB exception.
Pls help
|
|
|
|
|
abhi17_6 wrote: Problem is that this app is not able to connect to oracle 10g database server
though it is able to connect to some other 11g R2 database server.
A connectivity issue is not normally a code issue. Causes
- There is in fact no way to connect from server A to B. Perhaps because of firewall or IP routing.
- The host and/or port used on the client is wrong.
Simple test...
- Log in to the client box.
- Telnet to the Oracle box using the exact same host and port of the connection data.
If telnet connects then you probably have connectivity. If not then it is one of the above - don't look at code until the telnet test works.
It might work for you and not work for a server depending on the user the server runs under and how you logged in.
|
|
|
|
|
@jshell - we have installed oracle client 10g 32 bit also on web app server machine. and my client is able to do tnsping and use sqlplus here.
Questions-
- do i need to use telnet utility from my App server or from my oracle server machine? u mentioned client box, which one is it?
|
|
|
|
|
The "web app" would be the client machine.
|
|
|
|
|
Hello,
I am able to create a directory by using sql as
Exec master.dbo.xp_cmdshell 'C:\MyTest\<<date>>'
but when I tried to create director name with space as
Exec master.dbo.xp_cmdshell 'C:\My Test'\<<date>> then its throwing error.
can anybody please help me how to create directory with space in SQL
Samar
|
|
|
|
|
Surround the directory string with double quotes
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Thank you very much for your reply
but I already did as
Exec master.dbo.xp_cmdshell '"C:\Temp\My Test "'
but still getting facing problem
Samar
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
