|
I have a question about using ExecutiveNonQuery() in C# (using Access).
The following code would return 0, when I specify "@myMax".Value = 1 (see below).
However, if I changed @myMax to a constant (ie. not using a variable), everything worked fine and it returned 1 as the result.
The problem is that since it doesn't throw an exception, and only returns 0, I have no idea what's wrong with the "Update" SQL statement. Thank you and appreciate any feedbacks!!
<br />
OleDbCommand myUpdate = new OleDbCommand("UPDATE Product SET Prod_max = @myMax, Prod_desc = @myNote WHERE Prod_id = @myID", oleDbConnection1);<br />
<br />
myUpdate.Parameters.Add("@myNotes", OleDbType.LongVarWChar);<br />
myUpdate.Parameters.Add("@myID", OleDbType.VarChar);<br />
myUpdate.Parameters.Add("@myMax", OleDbType.SmallInt);<br />
<br />
myUpdate.Parameters["@myNotes"].Value = Notestxtbox.Text.ToString();<br />
myUpdate.Parameters["@myID"].Value = ProdIDtxtbox.Text.ToString();<br />
myUpdate.Parameters["@myMax"].Value = 1;<br />
<br />
try<br />
{<br />
textBox1.Text = myUpdate.ExecuteNonQuery().ToString();<br />
}<br />
catch (Exception ex)<br />
{<br />
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);<br />
}
|
|
|
|
|
HI,
Can someone help me with following SQL?
I have an array of about 200 string IDs in my program (C++).
I need to delete these IDs from a database table.
I would like to use the most efficent method of doing this.
Any suggestions on the best SQL query?
Thanks
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
If your id columns is numeric, try this...
DELETE FROM [Table] WHERE [ID_Column] IN (1,2,3,...,n)
If it's string...
DELETE FROM [Table] WHERE [ID_Column] IN ('1','2','3',...,'n)
Free your mind...
|
|
|
|
|
Use the In Clause to execute the query.
DELETE FROM [TableName]
WHERE ID IN (1,2,3,4,5,6,etc...200)
I know Sysbase 11 had a limit of 255 values in the In Clause. However, I do not think SQL Server or Access has that limitation.
Michael
|
|
|
|
|
Otherday I installed SQL Server on my personal computer, but now I forgot the password for 'sa' userid. How to retrive it? or How can I reset it?
Promise only what you can do. And then deliver more than what you promised.
This signature was created by "Code Project Quoter".
|
|
|
|
|
Do you have Enterprise Manger? As much as I think it logged in automaticlly and in Security-->Login node you can change password(I don't remembr if it asked old pass),if it asks, you can backup from your databases there and uninstall-install SQLServer again.
Mazy
No sig. available now.
|
|
|
|
|
Thanks for your tip.  It didn't ask for the old password.
Promise only what you can do. And then deliver more than what you promised.
This signature was created by "Code Project Quoter".
|
|
|
|
|
Hi
I have a question of database.
If you have some solutions, please tell me.
Now I'm developing tool getting and filing trigger_body from database using ODP.NET. As a ODP.NET case, I can set fetch size value, so I set value. But Trigger_body I'd like to get is heavy, so I couldn't get it all( I could get 80%).
I investigated ODP.NET, I noticed there is maximum fetch size is exist(32KB).
So I am looking for how to get it using .NET Framework and etc.
I usually use Toad, this have a function display trigger_body. So I tried to display trigger_body over 32KB. I got it. I noticed that how to get trigger_body over 32KB was exist using program.
thank you in advance for your kindness.
best regards,
yu-yu
|
|
|
|
|
The trigger body is saved in a LONG field, I guess you are using the LONG Type included in the ODP.NET.
Can you send the code you are using to extract the trigger_body from DB ?
Free your mind...
|
|
|
|
|
hi,
thank you for your mail.
The code getting trigger_body from DB using ODP.NET is the following.
------------------------------------------------------------
string sSQL = "select trigger_body from user_triggers where trigger_name='" + sName + "'";
OracleCommand com = conn.CreateCommand();
com.CommandText = sSQL;
com.InitialLONGFetchSize = 32767; // Maxvalue
OracleDataReader reader = com.ExecuteReader();
string sResultString = string.Empty;
reader.Read();
if (!reader.IsDBNull(0))
{
sResultString = reader.GetOracleString(0);
}
reader.Close();
-------------------------------------------------------------
best regards,
yu-yu
|
|
|
|
|
Try using this approach...
First, on the DB run this to create a Stored Procedure
CREATE OR REPLACE
PROCEDURE get_trigger(iName IN VARCHAR2, iBody OUT LONG) IS
BEGIN
SELECT TRIGGER_BODY INTO iBody FROM USER_TRIGGERS WHERE TRIGGER_NAME=iName;
END;
/
Then try this...
string GetTriggerBody()
{
OracleCommand vCommand;
OracleParameter vParameter;
OracleString vString;
string vTriggerBody;
vCommand = new OracleCommand();
vCommand.Connection = this.mConn;
vCommand.CommandText = "get_trigger";
vCommand.CommandType = CommandType.StoredProcedure;
vCommand.Parameters.Add(new OracleParameter("iName", OracleDbType.Varchar2, ParameterDirection.Input));
vCommand.Parameters["iName"].Value = "TR_AC_DOCUMENTOS_FECHAFIN";
vParameter = new OracleParameter("iBody", OracleDbType.Long);
vParameter.Direction = ParameterDirection.Output;
vParameter.Size = 80000;
command1.Parameters.Add(vParameter);
command1.ExecuteNonQuery();
vString = (Oracle.DataAccess.Types.OracleString)vParameter.Value;
vTriggerBody = vString.Value;
return vTriggerBody;
}
Hope this helps...
Free your mind...
|
|
|
|
|
hi,
I tried to do it, but I couldn't.
I have a question.
command1.Parameters.Add(vParameter);
command1.ExecuteNonQuery();
what is "command1" ?
I thought it's vCommand, so I changed it.
but Error occuered at pl/sql Ora-06502
ORA-06512: 1 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(
Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure, String[] args)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String[] args)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at ConsoleApplication2.Class1.test() in d:\testing\consoleapplication2\class1
.cs:line 162
at ConsoleApplication2.Class1.Main(String[] args) in d:\testing\consoleapplication2\class1.cs:line 37
best regards,
yu-yu
|
|
|
|
|
Sorry, it's vCommand, not command1.
The Oracle Error happens because the trigger you are trying to get doesn't exist.
Free your mind...
|
|
|
|
|
Really?
Thanks, I will try again.
After I will announce the result for you.
Best Regards,
yu-yu
|
|
|
|
|
Hi
I tried it, but same situation occuered.
The test code is the following.
----------------------------------------------------------
OracleConnection conn = new OracleConnection("Data Source=hora9;user id=aaa;password=aaa");
conn.Open();
OracleTransaction trn = conn.BeginTransaction();
OracleCommand vCommand = new OracleCommand();
OracleParameter vParameter;
OracleString vString;
string vTriggerBody;
vCommand.Connection = conn;
vCommand.CommandText = "get_trigger";
vCommand.CommandType = CommandType.StoredProcedure;
vCommand.Parameters.Add(new OracleParameter("iName", OracleDbType.Varchar2, ParameterDirection.Input));
vCommand.Parameters["iName"].Value = "T_T1URIA";
vParameter = new OracleParameter("iBody", OracleDbType.Long);
vParameter.Direction = ParameterDirection.Output;
vParameter.Size = 80000;
vCommand.Parameters.Add(vParameter);
vCommand.ExecuteNonQuery();
vString
= (Oracle.DataAccess.Types.OracleString)vParameter.Value;
vTriggerBody = vString.Value;
---------------------------------------------------------
I will try again,
If you see the code and notice some cause, please tell me.
Best regards,
yu-yu
|
|
|
|
|
What is the Oracle Error you are receiving ?
Usually, Oracle errors has this pattern.. ORA-#####. Need the number to check what is happening.
Free your mind...
|
|
|
|
|
Hi
I found the error cause.
It is the volume of trigger_body.
The procedure parameter of trigger_body is LONG.
This style miss, I think.
Because I tried to get short volume trigger_body, succeeded it.
But I think about it. Do you know Toad ? This is database tool.
As a this tool case, this can get heavy trigger_body without procedure.
Best regards,
yu-yu
|
|
|
|
|
Yes, I've used Toad. But I really prefer SQL Navigator.
This style miss, I think.
Because I tried to get short volume trigger_body, succeeded it.
I don't understand what you are saying here friend. Sorry 
Free your mind...
|
|
|
|
|
hi,
Sorry, my explanation is luck.
When We run "select trigger_body from user_triggers" using Toad and after check the result, we usually get the contents of trigger_body, do you know ?
This get not only short volume trigger_body but also heavy volume trigger_body. So I think we can get heavy trigger_body without procedure, but I don't know how to get it, use database control.
As a short volume trigger case, I could get it using ODP.NET library.
best regards,
yu-yu
|
|
|
|
|
Sorry friend, but I don't know what you mean with short and heavy volume triggers.
When I run "select trigger_body from user_triggers" on SQL Navigator, I get a result with a LONG field containing the trigger boddy.
This worked fined, but I don't have such a big trigger.
I have a policy, that is when the trigger is getting too big, just fragment it into Stored Procedures or Packages. That's why I don't have such a big trigger.
I would really like to know what you mean with short and heavy triggers, and I'm willing to help you solve the "big-trigger-pain"
Free your mind...
|
|
|
|
|
Whats the best way to get an empty Typed DataTable ? I've been using an SQL statement that returns zero rows, but the overhead of such a statement is going to get too big once the SQL table gets large.
|
|
|
|
|
Using the SQL statement is proably the easiest way. There shouldn't be much overhead regardless of the SQL statement if no rows are returned and the WHERE clause is set if an Key column not have a value in the table.
SELECT * FROM Employees WHERE EmployeeID = 0
Regardless of how many table are in the SQL statement, just make sure they are all joined correctly.
After you obtain the DataTable the 1st and only time, just cache it or use a singleton.
Michael
|
|
|
|
|
Here's a statement that uses less overhead since it won't require any kind of table-scan or index lookup:
SELECT * FROM Employees WHERE 1=2
|
|
|
|
|
we are working in a project in vb.net2003 using msaccess database...
we want to know the code to search or rowfilter by letters
(or by word match)
if any one can help us please connect us at this e-mail
masdotnet@yahoo.com
|
|
|
|
|
Are you talking about a query or filtering a datatable?
SQL in .Net use the '%' wildcard
e.g. Select LastName From tblUsers Where LastName Like '%bri%'
This will retrieve all rows with 'bri' in the last name.
DataTable in .Net the '%' wildcard is used as well.
Dim dv as DataView
Dim dt as DataTable
Dim dr as DataRow
Dim r as Integer 'loop for each row
'Populate the DataTable
...
...
'Now take the populated dt and make it a DataView and Filter it.
dv = dt.DefaultView
dv.RowFilter = 'LastName Like '%bri%'
For r = 0 to dv.Count-1 'Rows in the dataview
dr = dv.item(r).Row
'Print the values for LastName column
System.Diagnostics.debug.WriteLine(dr.Item("LastName"))
Next
Now this was taken off the top of my head so I am sure there may be some syntax errors, but you should get the idea.
Hope this is what you were looking for.
Michael
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
