|
Also, I know this is going to sound obvious and you already know this information - but it always amazes me that when I have something to do that I know extremely well, but under stress, I always forget something important. So, make sure you have a backup of the files you have now. If they get screwed up more you'll have even less to work with.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Thanks for your post. I know the problem you described and actually have experienced it several times . That why the first thing I did was to copy the data on a network drive somewhere down the road...
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hello! Anybody can help me to solve this problem?
I have a stored procedure like:
CREATE PROCEDURE PROC
@id int AS
SELECT 1 as tag,
0 as parent,
t1.name as 'PROC!1!name1',
t2.name as 'PROC!1!name2'
FROM Table1 as t1, table2 as t2
WHERE (t1.id = @id AND t1.id = t2.id)
FOR XML EXPLICIT
t1.id and t2.id are keys and there'is a relationship between them.
When I call this procedure an error occurs -
"FOR XML EXPLICIT query contains the invalid column name 'id'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] formatwhere TAGID is a positive integer"
When I change EXPLICIT with AUTO everything is ok
|
|
|
|
|
Hi , i have a small normalization problem.
imagine this:
table User
table City
table Country
each user can be related to a city , and each city can be related to a country
(NOTE: my real problem does not invlove neither users or regions this is just a exampel)
now to the problem.
an User MUST always have a country , but he may or may not have a city assigned
(incase the registration is incomplete... again , note the real world problem invloves products and different categories)
it might be easy to say "why do you want to use something that hasnt been registered 100%"
well.... i have no idea , this is just the way they work here, we sell products even if they havnt been completely registered..
so what would be the correct way to normalize such problem?
should user also relate to country , even though it makes it possible to relate the user to "hungary" and relate the same user to the city "london" , which in turn relates to "england".
OR
should i insert an "empty" element in the city table for each country.
so i can relate the user to an empty element that in turn relates to the correct country?
OR
is there any other "correct" way of doing this???
|
|
|
|
|
Does the database platform you are using support stored procedures?
Darien
C#, VB.NET, Oracle, Sq(uirre)l Server
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
its ms sql . but what has storedprocedures to do with table normalization rules?
//Roger
|
|
|
|
|
Well.... I was brainstorming and trying to think about different ways you could ensure the integrity of the data. i.e. Allowing London, U.K. and disallowing Los Angeles, Nigeria...
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
Speaking strictly from a normalization perspective, since any given user can only have one city or state, I would probably have a CityId and a CountryId element in the users table. I would make the countryId NOTNULLABLE and the cityId NULLABLE. I would then have those two id columns referenced to two lookup tables ( tblCountryLkp, tblCityLkp ? ) that would contain valid entries for their specific entities.
A stored procedure could then be used to reference another table ( tblValidCountryCities ? ) that would contain all valid combinations of cities and countries. Any insertion would be validated against this table and a non-conforming combination would result in an error.
Hope this helps,
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
Consider removing city from your user table, and placing it in an associated user_city table (user_city_id, user_id, city_id). This makes it clear that the city is optional information that is not (reliably) part of the core user information.
my blog
|
|
|
|
|
Hi everyone,
I am unable to use threads properly in an SQL Extended stored procedure.I am using CreateThread API, the Thread function is not called, but CreateThread doesnot fail neither WaitForSingleObject API works.
Please Help,Need the solution immedialetly.
NOTE: I am not allocating any memory during DLLMain.
|
|
|
|
|
Hi,
Which is better and than guarantees better rapidity and perfomance?
">>> SProcedure, SqlCommand, SqlReader, SqlDataAdapter <<<<"
I have a transaccional system and I am using SqlDataAdapter with DataSet and the times of process are very high. If somebody can help me, thanks.
Thanks,
|
|
|
|
|
First and foremost, I suppose that "SProcedure" is short for "stored procedure", since I know no object in the .NET framework with that name. The other three have their different purposes.
SqlReader: Lightweight, very fast. Read-Only, forward-read, server-side cursor. Good for populating lists or retrieving other data that you do not need to keep readily available for transactions.
SqlCommand: Pretty much a necessity if you are going to issue a transaction to a database. The only question is whether you are going to do it in a stored procedure or a text string. If the RDBMS you are using supports stored procedures, I would unconditionally support that. Not only do you wrap your insert values in parameter objects ( prevents SQL injection hacks - another column entirely ), but the stored procedure is already pre-compiled and should execute faster than a SQL-text insert.
SqlDataAdapter: Used to fill datasets / datatables with data resulting from a query. Very powerful, as you can use this object to populate relational data from your database and use the dataset object to maintain the information in memory. You just have to be very careful about how you construct your transaction logic, as it can become a major headache.
Also, check the layout of your database's table structure. Make sure that each table has ( at least ) one index defined and that you are utilizing it as often as possible when retrieving and updating table information. Finally, check your querys and check any joins that you may have. Full table joins ( joins without indexes ) and < gasp > Cartesian joins can stall even the best server if the tables are large enough.
Hope this helps,
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
There's got to be something wrong with what I'm doing. The database responds fine with the following code:
** Bracketed code is edited to protect the innocent **
<code>
Imports System
Imports System.Data.OracleClient
Public Class Driver
Public Shared Sub Main()
Dim conn as New OracleConnection
conn.ConnectionString = "Data Source=[DBName]; User Id=[User Id]; Password=[Password]"
Dim cmd as New OracleCommand
cmd.CommandText = "SELECT * FROM USERS WHERE USER_ID = '[USER_ID]'"
cmd.Connection = conn
cmd.CommandType = CommandType.Text
Try
Dim odr as OracleReader
odr = cmd.ExecuteReader()
Catch ex As Exception
Console.Writeline( ex.Message )
End
End Sub
End Class </code>
Now, upon replacing :
<code> cmd.CommandText = "SELECT * FROM USERS WHERE USER_ID = '[USER_ID]'" </code>
with a parameterized query like :
<code>
cmd.CommandText = "SELECT * FROM USERS WHERE USER_ID = :pUserId"
cmd.Parameters.Add( new OracleParameter( ":pUserId", OracleType.VarChar )).Value = "[USER_ID]" ) </code>
results in the return of a ORA-12571 : TNS:Packet Writer Failure
I've tried most everything I can think of and I can't around this.
The environment is Windows 2000 Server, Oracle 9i, and VS 2003.
Thanks in advance,
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
I'm working on application, that will recive multiple Excel files
and displaing it in a DataGrid.
The problem is, that when I select 2 or 3 files from OpenDialog, it
displays only the data from the first selected file, the other values
in DataGrid are null values. Why is that? And how can I solve this, that
the DataGrid will display all values. Thank you.
Here is the code:
private void btnOpen_Click(object sender, System.EventArgs e)<br />
{<br />
try<br />
{<br />
Stream myStream;<br />
OpenFileDialog openFileDialog1 = new OpenFileDialog();<br />
openFileDialog1.InitialDirectory = "E:\\" ;<br />
openFileDialog1.Filter = "txt files (*.txt)|*.txt|Allfiles(*.*)|*.*" ;<br />
openFileDialog1.FilterIndex = 2 ;<br />
openFileDialog1.RestoreDirectory = true ;<br />
openFileDialog1.Multiselect = true;<br />
<br />
if(openFileDialog1.ShowDialog() == DialogResult.OK)<br />
{<br />
if((myStream = openFileDialog1.OpenFile())!= null)<br />
{<br />
DataSet ds = new DataSet();<br />
<br />
foreach (string strFileName in openFileDialog1.FileNames)<br />
{<br />
<br />
string connStr = <br />
@"Driver={Microsoft Excel Driver (*.xls)};DBQ=" + strFileName;<br />
<br />
string sql = "SELECT * FROM [Sheet1$]";<br />
OdbcDataAdapter adapter = new OdbcDataAdapter(sql,connStr);<br />
adapter.Fill(ds,"[Sheet1$]");<br />
<br />
}<br />
gridPorocila.DataMember = "[Sheet1$]";<br />
gridPorocila.DataSource = ds.DefaultViewManager;<br />
<br />
myStream.Close();<br />
}<br />
}<br />
<br />
}<br />
catch (Exception ex)<br />
{<br />
MessageBox.Show(ex.Message.ToString());<br />
}<br />
<br />
}
|
|
|
|
|
I have the same problem with you but I can't
|
|
|
|
|
i am doing some project..and i am using vb.net language with access 2000 database. client wanna use backup function for database in case. how can i back up the database to another directory?? and wanna use save file dialog box. thanks.
|
|
|
|
|
Since Access is a file based DB, you can just copy it to a different folder:
<br />
<br />
'Gonna use FSO - need a reference to Windows Script Host object model<br />
Dim FSO as new FileSystemObject<br />
<br />
<br />
'Close any connections to the DB to ensure no access rights probs<br />
Conn.Close()<br />
<br />
'Use the FSO to copy the file<br />
FSO.CopyFile SrcMDB, DestMDB, bOverwrite<br />
<br />
Where SrcMDB is the DB to copy, DestMDB is the destination, and bOverwrite speaks for itself really - hope this helps
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hello,
I noticed that when I add a new row to my DB (MS Access 2002 with VB.Net), the row is inserted to a specific place according to the primary key and not at the end of the table - my primary key is client's ID.
I use a form with datagrid control for searching a row, but after adding a new row, its location in the dataset is different from the location in the database (?!?!), this form returns the row number to the main form...but the row number in incorrect...
In the searching form I use DataView and also in the main form I use the default view which sorted by the ID.
the main form gets the row number from the searching form.
Another thing is that after rebuilding the DB, the row number is correct but that's not a good solution...
I need to understand if I did something wrong with inserting or using the dataview objects. and how do I get the real row's number.
//**************************
//after double-clicking the row on the datagrid, get the default row number
RowNum = dbDsDB.Tables("clients").DefaultView.Find(DtGrd.Item(hti.Row, 2))
If you need more info, let me know - I'm stuck
thanks, Roy.
|
|
|
|
|
Although MS Access gives you a lot of flexibility, you really should not be departing so far from DDBMS principles.
-- Never rely upon storage order. You should consider that a black box and never be concerned with or rely on the order in which the rows are stored. There has never been a guarantee that the data will be stored in the order it was received. Therefore, don't use RowId!
You should use Primary Keys & Foreign Keys (referential integrity) to join tables and to match tables to forms. If enter order is important for you application, I suggest you add a time stamp attribute to your rows and reference it in your ORDER BY clause.
|
|
|
|
|
Hey Michael,
I guess I did make myself clear, I don't need the row's actual position in the DB.
I have thid code:
RowNum = dbDsDB.Tables("clients").DefaultView.Find(DtGrd.Item(hti.Row, 2))
the: DtGrd.Item(hti.Row, 2) is the current row in the datagrid(column 2).
(this is in the searching form that contains datagrid)
So, after this line of code, I suppose to get the row number in the default view:
Me.BindingContext(dbDsDB, "clients").Position = frmSrch.RowNum
(this is in the main form that calls the frmSrch).
But I won't seem to work and I get other row instead
..
Any Ideas??
thank Roy
|
|
|
|
|
I am writing a stored procedure where the WHERE clause may contain 100's of conditionals (i.e. UserID=1 OR UserID=2, etc). The field being searched will always be the same. It is just the number records to be returned I won't about. Is there anyways to pass the stored procedure an array or something similar to specify the records to return? Writing a statement that includes 100's of "UserID=1 OR UserID=2 OR..." breaks my layer abstraction so I'd rather just send the list of User ID's I want to get. Is there any way to do something like this?
|
|
|
|
|
Can you pass a comma-delimited string of values as a single varchar parameter? Then use that parameter as the list in an IN(...) operand?
|
|
|
|
|
You can try passing a string with the user id's and use it in the WHERE clause of your statement,
e.g.
SELECT *
FROM tblUsers
WHERE UserID IN (SELECT [Value] FROM dbo.Split(<code>@ListOfUserIDs</code>,','))
The code above uses the user defined function Split, which splits a string by the delimiter and converts it into a table.
Below is the user defined function that you can modify as needed.
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT
Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
There is another way using the EXEC statement in your stored procedure to execute the SQL, and you don't need the split function for this. Some users argued about the efficiency and safety of using EXEC statement, but it's up to you to decide.
DECLARE SqlStr varchar(1000)
SET SqlStr = 'SELECT * FROM tblUser WHERE UserID IN (' + @ListOfUserID + ')'
EXEC (SqlStr)
Both code will work even if you only pass one ID.
I hope it helps .
|
|
|
|
|
Thanks for the function. It works great.
|
|
|
|
|
Hello all .....
i m getting a Sql Error "Log file is not available"............when i m taking the backup....its both mdf and ldf are in its predefined location.....
let me know if any solution.....
******
|
|
|
|
|