|
Hi, how can I get working the procedure, where only last 50 rows are returned: I have found something about SELECT TOP 50 in MSDN but I can't get it working. I have this code (procedure)
CREATE PROCEDURE dbo.GetGuestbookData
(
@SortOrder varchar(64)
)
AS
IF @SortOrder='ASC'
BEGIN
SELECT *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID ASC
END
ELSE
BEGIN
SELECT *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID DESC
END
RETURN
|
|
|
|
|
Hi,
Just Replace ur Query with This one
SELECT TOP 50 *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID ASC
Hope this will Help........................:->
Regards,
Ritesh
|
|
|
|
|
|
I am inserting string into Excel file Using ADO .Execute fn(). It copies d first 3 records frm .txt file which r of size 20-30 char but as soon d 4th record which is 150 char long with special char is encountered it gives (((error:The field is too small to accept the amount of data you attempted to add.Try inserting or pasting less data..)))
I manually changed the Format of Field frm text to General in Excel file and copy it so it worked but... how to go about programatically without changing from text to general the file format
Codes:
SQL.Format("INSERT INTO [Sheet1$] values('%s', '%s', '%s', '%s', '%s', '', '')", sJobName, sMID, sDT, sAN, sTitle);
m_pConXLS->Execute(_bstr_t(sSQL), NULL, adCmdText);
Thnx in Advance
Casper2080
|
|
|
|
|
hi,
when i put a string which is in unicode into my sqlcommand directly the command can not be executed, for example if i use this code in my application:
com.Commandtext = "select * from mytable where name='" + myString +"'";
where myString is in unicode(any coding except ASCII). after executing the command an exeption is thrown.
i want to know if there is a way rather than using sqlparameters to put a string in command?
|
|
|
|
|
|
Hello
I am unable figure out the following problem.I am using ODP.NET 9.2.0.4 and ORACLE 8i Server. While I am trying to call an Oracle function which returns a VARCHAR2 at the time of ExecuteNonQuery it generates the OracleExecption("ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1").
But No problem occurs while function returns Number.
Please help me.Thanks in advance
Arnab
Oracle Function:
<br />
<br />
FUNCTION Get_DATA1 (p_val IN VARCHAR2)<br />
RETURN VARCHAR2 IS<br />
<br />
v_val VARCHAR2(10);<br />
BEGIN<br />
v_val:=p_val;<br />
RETURN v_val;<br />
END Get_DATA1;<br />
VB.NET Function That accessing the Above function:
<br />
Public Function GetData1(ByVal strValue As String) As String<br />
<br />
Dim strConnection As String<br />
Dim objConnection As OracleConnection<br />
Dim objCommand As OracleCommand<br />
Dim objParameterReturn As New OracleParameter<br />
<br />
Dim strReturnValue As String = ""<br />
<br />
Try<br />
strConnection = "My Oracle connection String"<br />
objConnection = New OracleConnection(strConnection)<br />
objCommand = New OracleCommand("Get_DATA1", objConnection)<br />
objCommand.CommandType = CommandType.StoredProcedure<br />
objCommand.Parameters.Add("p_val", OracleDbType.Varchar2, 10, strValue, ParameterDirection.Input)<br />
'setting parameter for return value<br />
objParameterReturn = objCommand.Parameters.Add("retval", OracleDbType.Varchar2, 10, Nothing, ParameterDirection.ReturnValue)<br />
objCommand.Connection.Open()<br />
'Upto connection open is working fine<br />
'but ExecuteNonQuery casues the OracleException<br />
objCommand.ExecuteNonQuery()<br />
strReturnValue = CType(objParameterReturn.Value(), String)<br />
<br />
Catch ex As OracleException <br />
strReturnValue="Oracle Error"<br />
Catch ex As Exception<br />
strReturnValue="Other Error" <br />
Finally<br />
''Release All DB objects<br />
ReleaseDB(objCommand, objConnection)<br />
End Try<br />
<br />
Return strReturnValue<br />
End Function<br />
|
|
|
|
|
I tried to insert a record to database(in user register operation) using Access 2003 database and an exception appears("this operation must use updateable queries.").But when i use the same code with SQL2000 Database,every thing is OK.
Anyone can explain for me,and how to insert a record to Access database ? thanks.
the full code:
/////////////////////////////////////////////////
bool result = false;
//OLEDB
OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(CfgKeyOleConnString+CfgKeyOleDatabase);
string strSQL = "INSERT INTO Users (UserName,Name,UserPassword,Email,RoleID) VALUES (@UserName,@Name,@UserPassword,@Email,@RoleID)";
OleDbCommand dbComm = new OleDbCommand(strSQL,dbConnection);
dbComm.Parameters.Add(new OleDbParameter ("@UserName", OleDbType.VarChar, 50));
dbComm.Parameters["@UserName"].Value = userName;
dbComm.Parameters.Add(new OleDbParameter ("@Name", OleDbType.VarChar, 50));
dbComm.Parameters["@Name"].Value = displayName;
dbComm.Parameters.Add(new OleDbParameter ("@UserPassword", OleDbType.VarChar, 50));
dbComm.Parameters["@UserPassword"].Value = userPassword;
dbComm.Parameters.Add(new OleDbParameter ("@Email", OleDbType.VarChar, 50));
dbComm.Parameters["@Email"].Value = userEmail;
dbComm.Parameters.Add(new OleDbParameter ("@RoleID", OleDbType.Numeric , 8));
dbComm.Parameters["@RoleID"].Value = Convert.ToInt32(Role);
try
{
dbConnection.Open();
dbComm.ExecuteNonQuery();
result = true;
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
result = false;
}
finally
{
if (dbConnection.State==ConnectionState.Open)
dbConnection.Close();
}
return result;
...NLD...
|
|
|
|
|
Hi there. Your SQL statement begins with "INSERT Into Users ..." In your Access database, is "Users" a table or a query?
|
|
|
|
|
It's a table name.
...NLD...
|
|
|
|
|
Morning all,
Was wondering if anyone can help on replication as it's kind of urgent now and the help would be so greatly appreciated if anyone can.
What i have is a MSDE installation on several 100 clients and a SQL Server 2000 installation on the server.
I have set up merge replication on the local network and it work fine.
The problem is that several clients never login to the LAN and others don't even have windows user accounts (VPN is out of the window) but they also need to have their local databases updated.
All users need to be able to work locally and those changes need to be merged back into the server database without conflicts (or handled conflicts.)
Now, with FTP Merge Replication this would be a relatively simple process. However, the powers that be have made it quite clear that they are not going to let me use FTP, which i advised makes my job a lot harder and thanked them heartily.
Is there any way anyone can think of that this can be done through any other transfer method?
I was thinking i could mimic the process that Merge Replication goes through or maybe use parts of it. Please help....... Any suggestions would be a great start.
Thank you all.
"If i was king cigarettes would be free."
|
|
|
|
|
Hello folks,
I am using the following INSERT statement to add a new record in my table. I want to be able to get the Autonumber UserID field of the newly created record but I am getting the following error: "Characters found after end of SQL statement"
INSERT <br />
INTO tblUser <br />
(LoginName, UserPassword, EmailAddress, City)<br />
Values <br />
(@User, @UsrPassword, @EmailAddress, @City);<br />
SELECT @@IDENTITY As 'Identity'
What is wrong in my statement?
How do I retrieve the Autonumber field of the newly created record?
How do I handle if the LoginName and/or EmailAddress already exists?
I want to be able save this INSERT statement as an Access Query and call it from my C# code.
I would appreciate any help.
Thanks
Mike
|
|
|
|
|
Hi Mike. I don't think you can execute both statements in a single command with Access. You should though be able to execute the Select @@Identity statement as a second command, following the insert. Here's a link to another discussion thread[^] with information you may find helpful.
|
|
|
|
|
Thanks for the link Mike, it is very helpful.
|
|
|
|
|
Tell me please how ca I get over with table normal forms cause I am experiencing a great trouble with it.I have a table who contains repeteated city names,faxes,and states.I have to normalize this table but Im a beginner and I cant manage it.Can anyone show me any tricks about this ??I would be very grateful cause I really want to learn it.Thank you.
|
|
|
|
|
KORCARI wrote:
Can anyone show me any tricks about this ?
There are no tricks to normalisation.
IIRC, 1st normal form is to ensure that each column represents one distinct piece of data. e.g. If you currently have a Name column which contains, for example, Colin Mackay, that should be split into a Forename and Surname - "Colin" and "Mackay" (you may also want a middle name, and perhaps an additional surname (e.g. Spanish people have two surnames), title, and suffixes - it depends on what you want to do).
2nd Normal form is to ensure that each table has a primary key. This is normally a single column, but you can have compound primary keys with more than one column. A primary key must be unique. So, a person's name is not valid, but their National Insurance number is (or SSN, or a UserID, or whatever). Often you will have to create some sort of scheme to generate a unique value - most databases offer this facility.
3rd Normal form is to ensure that only data directly related to the primary key is stored in the table. So, for instance, if you were designing an application that held an electoral role that had each registered voter and their address in the database. You would split off the address into a separate table because it isn't directly related to the person (there may be more than one person living at the address). Now you have two tables, the person table will also have a foreign key (which is a copy of the primary key value for the address in the Address table).
Does this help? If you are still having problems perhaps you can post some details about your data and we can point you in the right direction using something you are familiar with. I know that when I first learned about normalisation I didn't get it the first time because the examples were all arbitrary. It was only when I had a real problem to solve that I started to understand what it was all about.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hey Colin, do you know about 4th and 5th Normal form?
I remembered learning them back in uni but they don't teach 'em anymore nowadays. I knew as I just taught my friend at uni about this and the examples are TERRIBLE.
Edbert P.
Sydney, Australia.
|
|
|
|
|
|
Here's what I know about 1st to 3rd NF:
1st NF: No repetition.
Make sure there are no repetition of data or multivalue in single column (e.g. 2 phone numbers in one column "9999 9998, 9999 9999"). That means you may have to split the table into several tables until there is no repetition of data.
2nd NF: No partial dependency.
Check tables with compound PKs (PKs consist of 2 or more column). If there is a column that depends only on a part of the compound PK then you have to put it in a new table e.g. table with columns
StudentID, ClassID, Grade, StudentName
has to be separated into
StudentID, ClassID, Grade
StudentID, Student Name
as StudentName is only dependant on StudentID, not both StudentID and ClassID (the composite PK) hence the partial dependency.
3rd NF: No transitive dependency.
Check that there are no columns (including the PK) that is dependant on a non-PK column e.g. table with columns
EmployeeID, EmployeeName, ManagerID, ManagerName
has to be separated into
EmployeeID, EmployeeName, ManagerID
ManagerID, ManagerName
as ManagerName is dependant on ManagerID (non-PK) hence the transitive dependency.
Hope it helps! 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Actually, I've solved my prior problem, but what I can't seem to do is get one query that does everything I want. I have two queries, that return the same columns, but one returns where a primary org is set, and one returns an arbitrary org for the contact where there is no primary. How do I turn these two tables, with the same columns, into one ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Use UNION?
e.g.
SELECT Column1, Column2<br />
FROM qry1<br />
UNION<br />
SELECT Column1, Column2<br />
FROM qry2
Edbert P.
Sydney, Australia.
|
|
|
|
|
I've heard that I can use SQL to run dynamically changing mathematical equations. I've got the equation stored in a string (i.e. "((100-75)/100)*100") with the values randomly changing. the formula will not stay constant either, but it is the example I am using. How do I get this to produce a result that I can store in another string? Thanks.
Nick
|
|
|
|
|
ColdWaterBlue wrote:
I've heard that I can use SQL to run dynamically changing mathematical equations.
It has the potential. You could create some dynamic SQL and execute it.
EXEC ('insert the dynamic sql between the apostrophes')
The final solution depends on the source of the data (which table(s) in the database, and which columns) and how dynamic you want your forumulas. For instance, you could set up a table to hold the formulas.
You are probably looking at writing some stored procedures to do the properly.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hello All, I wrote the code below planning to use it to pick out the newest database log file in a certian directory on a computer on our LAN.
<br />
Function NewestFile(Directory, FileSpec)<br />
' Returns the name of the most recent file in a Directory<br />
' That matches the FileSpec (e.g., "*.xls").<br />
' Returns an empty string if the directory does not exist or<br />
' it contains no matching files<br />
Dim FileName As String<br />
Dim MostRecentFile As String<br />
Dim MostRecentDate As Date<br />
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"<br />
FileName = Dir(Directory & FileSpec, 0)<br />
If FileName <> "" Then<br />
MostRecentFile = FileName<br />
MostRecentDate = FileDateTime(Directory & FileName)<br />
Do While FileName <> ""<br />
If FileDateTime(Directory & FileName) > MostRecentDate Then<br />
MostRecentFile = FileName<br />
MostRecentDate = FileDateTime(Directory & FileName)<br />
End If<br />
FileName = Dir<br />
Loop<br />
End If<br />
NewestFile = MostRecentFile<br />
<br />
End Function<br />
However, the directory that I need to pass to this function when called is a filepath on a computer on the LAN and the directory I need to access is password protected. Can anyone help me out on how to do authentication using VBA code in an access DB. Thanks is advance.
Frank
|
|
|
|
|
Hey guys...
I have a very simple question with probably a very difficult answer (for me  )
I have a view, containing three fields I want to create a crosstab with.
A Date (should be the 'row' header)
A Name (should be the 'column' header)
and a Total (number) field.
Can anyone 'introduce' me into SQL, cause i'm very very very new into these kind of queries (probably SP's)
I love it when a plan comes together
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
