|
I would recommend doing this from a stored procedure.
Really don't want users messing around with sysobjects unless there very knowlegeable.
Michael
|
|
|
|
|
If I had sql server on the back-end...I would. But we are trying to get at data in Informix...which as far as I know doesn't support sprocs.
Like I said before, I really a complete novice with this particular back-end so I was looking for a way to do this that generalized to all ado.net.
Your suggestion works great btw, even with the Informix back-end. Thanks again.
Bill
|
|
|
|
|
hi
how can i compare time part of datetime field, for exaple :
time of datetimefield between '12:24:36' and '18:50:25'
thank you ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Please Tell to me how can i make a progress bar control for showing the loaded data ine dataset or all data in any winforms.
cause this work is easy in the ado classic .
but i cant find any method or event or object for this problem .
so thanks
Saeed Sa_tabrizi@yahoo.com 
|
|
|
|
|
I cannot create a stored procedure like that:
CREATE PROCEDURE sp_GetLatestNews<br />
@nCount int<br />
AS<br />
Select top @nCount * from NEWS <br />
GO
But I CAN do like that:
CREATE PROCEDURE sp_GetLatestNews<br />
@nCount int<br />
AS<br />
Select top 5 * from NEWS <br />
GO
How can I throw a parameter to "Select TOP" command 
=======================
Nothing is perfect
|
|
|
|
|
CREATE PROCEDURE sp_GetLatestNews
@nCount int
AS
SET ROWCOUNT @nCount
Select * from NEWS
SET ROWCOUNT 0
GO
|
|
|
|
|
CREATE PROCEDURE sp_GetLatestNews
@nCount int
AS
declare @cCount varchar(10)
select @cCount = Convert(varchar(10),@nCount)
exec ('select top ' + @cCount + ' * from NEWS')
GO
onwards and upwards...
|
|
|
|
|
As the other two replies mentioned, you can use the SET ROWCOUNT statement or construct and execute a sql statement dynamically.
But I read the other day the Yukon will allow expressions to be used as an argument to the TOP statement.
This is from http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx[^]
"The T-SQL TOP option in Yukon has two significant enhancements. You can now specify as an argument to TOP an expression that can contain variables or even a self-contained query. You can also use the TOP option with modifying DML (INSERT, UPDATE, DELETE).
To specify an expression, you must enclose it in parentheses. The expression should be of the BIGINT datatype when not using the PERCENT option, and a float value in the range 0 through 100 when using the PERCENT option. The following code shows how to use an expression with a variable to return the requested number of earliest orders from the SalesOrderHeader in the AdventureWorks database:"
USE AdventureWorks
DECLARE @n AS BIGINT
SET @n = 5
SELECT TOP (@n) *
FROM SalesOrderHeader AS SOH
ORDER BY OrderDate, SalesOrderID
Searching the web without Google is like straining sewage with your teeth.
Userfriendly, 2003/06/07
|
|
|
|
|
I wondered that what is faster in executing?
CREATE PROCEDURE sp_GetLatestNews <br />
@nCount int <br />
AS <br />
SET ROWCOUNT @nCount <br />
Select * from NEWS <br />
SET ROWCOUNT 0 <br />
GO
or
CREATE PROCEDURE sp_GetLatestNews <br />
@nCount int <br />
AS <br />
declare @cCount varchar(10) <br />
select @cCount = Convert(varchar(10),@nCount) <br />
exec ('select top ' + @cCount + ' * from NEWS') <br />
GO
And what is T-SQL ? Help me !
=======================
Nothing is perfect
|
|
|
|
|
I believe that the first is faster
It gives SQL server the ability to comiple the SP and save it
but in second case ,, a query is constructed on the fly and then it will be parsed.
T-SQL = Transact SQL ( the language used in SQL Server )
|
|
|
|
|
It'd really make this project simple if i could, but this part doesn't appear to be as easy I thought.
|
|
|
|
|
You could use a combination of xp_cmdshell extended stored procedure and the dtsrun utility. Look in the BOL for the dtsrun utility, it's a command line program to execute DTS packages.
Jeff Martin
Triple20 Software
|
|
|
|
|
I need to know how can we grant permission to access the Msysobjects through C# coding.
Any kind of suggestion is highly appreciated!
|
|
|
|
|
Daminda wrote:
Any kind of suggestion is highly appreciated!
You won't get the answer by reposting and reposting each day.
Daminda wrote:
I need to know how can we grant permission to access the Msysobjects through C# coding.
I don't know if it helps but maybe you have to login with windows adminstrator account to do that.
Mazy
"Improvisation is the touchstone of wit." - Molière
|
|
|
|
|
Hi,
I have no idea anymore...
there is a file named 'fdax.txt' and a MS Access DB with an existing table 'data_table'. I have created a void 'ReadWriteData()' that should read out the data from the file which contains maybe hundreds of lines and insert them into the 'data_table'. My StreamReader works right, the SQL-Syntax is ok. but not more than 30 lines of data will be inserted into 'data_table' before there comes up an OleDbException: Overflow. After clicking OK it will be continued and some lines will be inserted. Then a new OleDbException: Overflow... OK... some lines... Overflow... OK... some lines till the end of loop.
Can anyone help me?
Here is the code of my 'ReadWriteData()':
private void ReadWriteData()<br />
{<br />
StreamReader datei;<br />
datei = new StreamReader("fdax.txt", Encoding.ASCII, true,1);<br />
<br />
string zeile;<br />
string[] felder;<br />
<br />
int id = 1;<br />
while (datei.Peek() != -1)<br />
{<br />
zeile = datei.ReadLine();<br />
felder = zeile.Split(new char[] {','});<br />
<br />
string datumzeit = felder[1] + felder[2];<br />
string strOpen = felder[3];<br />
string strHigh = felder[4];<br />
string strLow = felder[5];<br />
string strClose = felder[6];<br />
string strVolume = felder[7];<br />
<br />
string strJahr = datumzeit.Substring(0, 4);<br />
int jahr = Convert.ToInt16(strJahr);<br />
string strMonat = datumzeit.Substring(4, 2);<br />
int monat = Convert.ToInt16(strMonat);<br />
string strTag = datumzeit.Substring(6, 2);<br />
int tag = Convert.ToInt16(strTag);<br />
string strStunde= datumzeit.Substring(8, 2);<br />
int stunde = Convert.ToInt16(strStunde);<br />
string strMinute= datumzeit.Substring(8, 2);<br />
int minute = Convert.ToInt16(strMinute);<br />
string strSekunde= datumzeit.Substring(8, 2);<br />
int sekunde = Convert.ToInt16(strSekunde);<br />
convertdatumzeit = new DateTime(jahr, monat, tag, stunde, minute, sekunde, 0);<br />
string strDatum = Convert.ToString(convertdatumzeit);<br />
string strId = Convert.ToString(id);<br />
<br />
string strInsertIntoMDB = "insert into data_table(id, datum, [open], high, low, [close], volume) values(";<br />
strInsertIntoMDB += ToSQL(strId) + ", '";<br />
strInsertIntoMDB += ToSQL(strDatum) + "', ";<br />
strInsertIntoMDB += ToSQL(strOpen) + ", "; <br />
strInsertIntoMDB += ToSQL(strHigh) + ", "; <br />
strInsertIntoMDB += ToSQL(strLow)+ ", "; <br />
strInsertIntoMDB += ToSQL(strClose) + ", "; <br />
strInsertIntoMDB += ToSQL(strVolume) + ")";<br />
oleDbCommand1 = oleDbConnection1.CreateCommand();<br />
oleDbCommand1.CommandText = strInsertIntoMDB;<br />
try<br />
{<br />
oleDbConnection1.Open();<br />
oleDbCommand1.ExecuteNonQuery();<br />
oleDbCommand1.Dispose();<br />
oleDbConnection1.Close();<br />
}<br />
catch(Exception ed)<br />
{<br />
MessageBox.Show("Error in inserting! "+ed.ToString(), "Error");<br />
}<br />
id++;<br />
} <br />
<br />
}<br />
<br />
private string ToSQL( string input )<br />
{<br />
return input.Replace( "'" , "''" ); <br />
}
|
|
|
|
|
Hi,
Does it throw the exception on the same lines of the data file every time? If so, you might want to verify that you are parsing the line correctly and that the fields in the Access database are large enough to accept the value.
Bill
|
|
|
|
|
Hi Bill,
Thank you for your reply! I just found my mistake... and i was felt embarrassed! It's so simple...
One field of my MS Access table has not the right type. The Integer type of MS Access is not the same as in C# or other prog language. And so by inserting the Dataset comes the error at this field with a number > 30,000.
Sebastian.
|
|
|
|
|
Anyone have any ideas on how to store richtext in an sql database; then retreive the data and reload the richtext control without losing the formatting?
I've spent days looking on the web for answers and so far came up empty.
Any help will be greatly appreciated...
|
|
|
|
|
Just store the data in a text or image column. You can store any type of binary document data in the image column and text formatted data in text columns.
onwards and upwards...
|
|
|
|
|
I Want to List the Table Names Of a MS Access Database Using ADO Connection.
This is How I tried
ADODB.Connection myConnection = new ADODB.ConnectionClass();
ADODB.Recordset rsTblNames = new ADODB.RecordsetClass();
if (optAccess.Checked)
{
ConnStr= "Provider=Microsoft.Jet.OLEDB.4.0;User ID=;Data Source="+ txtDbPath.Text +";Mode=ReadWrite;Extended Properties='';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
myConnection.Open(ConnStr,"","",0);
StrSql= "SELECT Name FROM MSysObjects WHERE Type = 1";
rsTblNames.Open(StrSql, myConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly,0);
}
But This returns a Error
|
|
|
|
|
You might want to try ADOX instead, although I'm not sure if it works with Access.
Ian Darling
"The different versions of the UN*X brand operating system are numbered in a logical sequence: 5, 6, 7, 2, 2.9, 3, 4.0, III, 4.1, V, 4.2, V.2, and 4.3" - Alan Filipski
|
|
|
|
|
Try this:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
Source: http://www.sadeveloper.net/Forum/ShowPost.aspx?PostID=5153
(There are more info if you need it)
|
|
|
|
|
Is there any way we can get the column/field names of a table in access??
|
|
|
|
|
Hi,
Try this:
<br />
dbCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB;User ID=xxx;Password=xxx;"; <br />
<br />
dbCon.Open(); <br />
DataTable dt = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null); <br />
<br />
foreach(DataRow myRow in dt.Rows) <br />
{ <br />
if (myRow["TABLE_NAME"].ToString() == tabSel) <br />
{ <br />
ColList.Items.Add(myRow["COLUMN_NAME"].ToString()); <br />
} <br />
}<br />
This is also from the link that I posted above, here it is again:
http://www.sadeveloper.net/Forum/ShowPost.aspx?PostID=5153
HTH,
-Thea-
|
|
|
|
|
How do you write a stored procedure/function that returns recordsets?
Thanks
Man Learns from History that he never learns from History
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
