|
Are you using Microsoft SQL Server?
If yes, you could use the following query (you have to adopt it to your db):
;WITH MyTable_CTE(Name, Ranking)
AS
(
SELECT Name,Ranking = DENSE_RANK() OVER (PARTITION BY Name ORDER BY NEWID() ASC)
FROM MyTable
)
DELETE FROM MyTable_CTE
WHERE Ranking > 1
Hope this helps
Regards
Sebastian
|
|
|
|
|
I have this code written in PERl to connect to a MS-Access-2007 DB .
But my output does not print any of the contents. Could anybody figure this out?
use Win32::ODBC;<br />
$DB=new Win32::ODBC("perlDSN");<br />
if($DB->Sql("select no,cname from tab1"))<br />
{<br />
printf("Error reading\n");<br />
exit;<br />
}<br />
while ($DB->FetchRow())<br />
{<br />
($a,$b)=$DB->data();<br />
print("We have $a\n");<br />
}
|
|
|
|
|
hello every one
i m having error of converting datatype varchar to numeric while inserting
my query is
insert into des_mast (des_name,des_numb,des_recno.....)values('"me.textbox1.text"','"+me.combobox1.text+"','"+me.textbox2.text+"'.....)
and so on there are around 20 such fields in data base and all these fields like des_name,des_numb are set to allow null property.
now if i insert each and every values of textboxes and comboboxes at runtime there is no error of such type and query runs perfectly
but if i keep some textboxes or comboboxes with blank text this error is seen...
"Error converting data type varchar to numeric."
so please suggest what shall i do..
thank you in advance..
Smile Smile Smile
|
|
|
|
|
You are trying to convert and empty string '' to a numeric value, this cannot be done. I suggest you default the value of numeric textboxes to '0'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
u suggested to place default value of textboxes to '0'
but there are many such textboxes where there values are in nvarchar()
so if i place values to '0' it will create problem
and on this there are such around 30-35 such textboxes and comboboxes placed
i tried something like
insert into des_mast(pincode)values('"+ctype(me.txtpincode.text,integer)+"') there are such columns round about 30-35 in database this is just an example
but it too showed error message like:-"Conversion from string "','" to type 'Double' is not valid."
so please suggest some another way.
|
|
|
|
|
You have little choice in the matter, you need to give the database the correct datatype otherwise you are going to get an error!
Either manage your data or quit, there are many ways and places to set the default values, textboxes, method that reads the textboxes, stored proc that processes the data etc.
It's all about designing your application, currently you have a crappy design, you need to fix it. You know the cause of the problem it's your data so fix it!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As Mycroft says
I think the problem is when you try to insert "" in a numeric column, "" is not NULL
Besides, take care of sql injections, since you are concatenating strings
You shouls use sqlparameters, and there you can set a default value, as well as a condition to set empty string "" to null (At least in asp.net, i havent done it in windows forms, but it must be posible)
Alexei Rodriguez
|
|
|
|
|
Hi there.
I have the following stored procedure:
/**********************************************/
CREATE PROCEDURE sp_iet_ComplaintReport
@StartDate DATETIME,
@EndDate DATETIME
AS
select
co.ComplaintID,
pli.Description AS RegardingDD,
co.DateRaised,
c._accCustomercode,
rtrim(c.firstnames) + ', ' + rtrim(c.lastnames) as Name,
CASE
WHEN co.BeenRead = 0 THEN 'Open'
WHEN co.BeenRead = 1 THEN 'Completed'
END AS Status,
rtrim(u.fullname) as handler
from
complaint co
inner join
contact c on
co.contactid = c.contactid
inner join
adm_picklistitem pli on
co.regardingdd = pli.localcode and
pli.picklistname = 'complaintlist'
left outer join
adm_user u on
co.handler = u.userid
where
co.dateraised >= @startdate and co.dateraised <= @enddate
GO
/*********************************************/
This works fine but i want to be able to pass @Handler and map that to the complaint.Handler field. The thing that i want is if i pass NULL to the @Handler then it should bring back all Complaint.Handlers but if you pass an actual value to @Handler then this should bring back a matching value (if it exists).
Sorry if I have worded poorly.
Any help would be greatly appreciated.
|
|
|
|
|
In your where clause try the following
Where (Isnull(@Handler,'') = '') or Handler = @Handler
If the test for @handler = '' then no filter is applied, if @handler has a value then the filter is used.
Caveat - many of these in a single query can reduce performance!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
the problem that my code does the update during the same run while iam out and run again i find nothing of the updates i think it's a comminting problem anyway hope anyone help me!!
plz check the code:
public partial class Form1 : Form
{
static SqlCeConnection _connection = null;
public static SqlCeConnection Connection
{
get
{
if (_connection == null)
{
_connection = new SqlCeConnection(ConnectionString);
_connection.Open();
}
return _connection;
}
}
public Form1()
{
InitializeComponent();
}
public static DataSet ds = new DataSet();
public static int rowindex = -1;
private void menuExit_Click(object sender, EventArgs e)
{
Close();
}
#region File & Database Details
static string CurrentFolder
{
get { return Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase); }
}
const string _databaseLocalFileName = "DB1.sdf";
static string DatabasePathName
{
get { return Path.Combine(CurrentFolder, _databaseLocalFileName); }
}
const string _baseConnectionString = "Data Source =";
static string ConnectionString
{
get { return _baseConnectionString + DatabasePathName; }
}
#endregion
private void menuedit_Click(object sender, EventArgs e)
{
rowindex = dataGrid1.CurrentRowIndex;
if (dataGrid1.DataSource != null)
{
panel1.Visible = true;
txtcol1.Text = ds.Tables[0].Rows[rowindex].ItemArray[0].ToString();
txtcol2.Text = ds.Tables[0].Rows[rowindex].ItemArray[1].ToString();
txttblID.Text = ds.Tables[0].Rows[rowindex].ItemArray[2].ToString();
}
else
MessageBox.Show("should load data grid first", "error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
public static SqlCeDataAdapter adap;
private void btnsave_Click_1(object sender, EventArgs e)
{
try
{
SqlCeCommand cmdUpdate = Connection.CreateCommand();
SqlCeTransaction se = Connection.BeginTransaction();
cmdUpdate.CommandType = CommandType.Text;
cmdUpdate.CommandText = "UPDATE tbl SET Col1=@Col1, Col2=@Col2 where tblID = @tblID";
cmdUpdate.Parameters.AddWithValue("@Col1", txtcol1.Text);
cmdUpdate.Parameters.AddWithValue("@Col2", txtcol2.Text);
cmdUpdate.Parameters.AddWithValue("@tblID", txttblID.Text);
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
cmdUpdate.Transaction = se;
cmdUpdate.ExecuteScalar();
se.Commit(CommitMode.Immediate);
Connection.Close();
panel1.Visible = false;
menuItem1_Click(null, null);
}
catch (SqlCeException ex)
{
throw ex;
}
}
private void menuLoad_Click(object sender, EventArgs e)
{
try
{
SqlCeCommand com = new SqlCeCommand("Select * from Tbl ", Connection);
adap = new SqlCeDataAdapter(com);
if (ds.Tables.Count > 0)
{
ds.Tables["tbl"].Clear();
}
adap.Fill(ds, "tbl");
dataGrid1.DataSource = ds.Tables[0];
adap.Dispose();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
}
|
|
|
|
|
hello
I'm using "OPEN-FOR", not "EXECUTE IMMEDIATE" because I want to do multi rows query. However, "OPEN-FOR" won't take a variable "strSQL" in its FOR clause, it only takes fixed strings?
create or replace function fnEnumSystemUser
(
LogonFilter nvarchar2,
...
AdditionalWhereClause nvarchar2,
OrderByClause nvarchar2
)
RETURN xxxxx.cursorType
AS
strSQL nvarchar2(2000);
FormatAdditionalWhereClause nvarchar2(2000);
FormatOrderByClause nvarchar2(2000);
SystemUserCursor xxxxx.cursorType;
BEGIN
IF AdditionalWhereClause IS NULL THEN
FormatAdditionalWhereClause := ' ';
ELSE
FormatAdditionalWhereClause := TRIM(AdditionalWhereClause);
END IF;
IF OrderByClause IS NULL THEN
FormatOrderByClause := ' ';
ELSE
FormatOrderByClause := TRIM(OrderByClause);
END IF;
strSQL := 'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id ';
-- This won't work, simply says "Warning: compiled but with compilation errors" when I tried to create function
open SystemUserCursor FOR strSQL;
-- This won't either, on invoke "ORA-01006: bind variable does not exist"
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x :y ' using LogonFilter, OrderByClause ;
-- This also failed (on invocation, "ORA-00900: invalid SQL statement")
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x' || OrderByClause using LogonFilter;
RETURN SystemUserCursor ;
END;
This is how I invoked it:
declare
NumItemsSelected int;
lstResult xxxxx.cursorType;
TYPE SystemUserRecordType IS RECORD
(
Id numeric(19,0),
FirstName nvarchar2 (50),
MiddleName nvarchar2 (50),
LastName nvarchar2 (50),
PrimaryEmail nvarchar2 (190),
PersonType int,
CreateDate timestamp,
CreatedBy numeric(19,0),
LastUpdate timestamp,
LastUpdateBy numeric(19,0),
Logon nvarchar2 (75),
PasswdHash int,
IsSuspended char,
ExpiryDate timestamp
);
oUser SystemUserRecordType;
begin
lstResult := fnEnumSystemUser (... '%',... ' Person.PrimaryEmail like ''%a%'', ' order by Logon ASC',0,10,NumItemsSelected);
LOOP
fetch lstResult into oUser;
exit when lstResult%notfound;
dbms_output.put_line('Id: ' || oUser.Id);
END LOOP;
end;
I need ability to append to SQL string because in the end say the least I need to append:
1. ORDER BY clause
2. Call a FUNCTION in WHERE Clause
3. Paging and ROW_NUMBER() clause
What can I do? Thanks!
dev
|
|
|
|
|
Hi,
I would like to know your opinion on which software would be the best RAD around? Actually, I develop in Filemaker, when you are good it's damn fast to develop a solution, but it's quite limited in features.
Is there something similar in the open-source market?
Thank for your idea
|
|
|
|
|
I'm working on a project which will be completely contained on a 1 gig USB drive. I'm trying to determine whether I should use SQL Server CE or some other flavor of SQL. I remember there being some type of SQL that created an SDF file which seemed to be a standalone SQL database. I also remember that one including the ability to encrypt data. So, my questions are:
1) Can SQL Server CE work on a USB stick that's plugged into a computer or is it only for handheld devices?
2) Does SQL Server CE support encryption?
3) What was the version of SQL Server that produced the SDF file?
4) Does that version support encryption?
5) What do I use to create that version?
And finally (whew!)
6) What do you recommend I use and why?
I'd rather stay with SQL Server becuase the database this product will have to sync with is a SQL Server (full) database and I figure a SQL Server flavor will play nicer with it.
Thanks!!!
Denise "Hypermommy" Duggan
|
|
|
|
|
Hi,
I do recall a thread on "SQL Server Compact Edition", if was described as "just a couple of dlls that let you use a single .sdf file as a database. Similar limits to Express (4gig db limit etc) - but it is also missing stored procs."
Hope this helps.
modified on Tuesday, May 5, 2009 3:21 PM
|
|
|
|
|
Hypermommy wrote: 1) Can SQL Server CE work on a USB stick that's plugged into a computer or is it only for handheld devices?
You can run it from USB without any problems.
Hypermommy wrote: 2) Does SQL Server CE support encryption?
Yes, explained on MSDN here[^].
Hypermommy wrote: 3) What was the version of SQL Server that produced the SDF file?
I don't quite understand this question. The latest version of SQLCE is 3.5, you can get an overview of the different versions here[^].
Hypermommy wrote: 5) What do I use to create that version?
I'm mostly creating them from code. I tried using "SQL Server Management Studio", and it can handle .sdf files as well.
Hypermommy wrote: 6) What do you recommend I use and why?
SQLCE has the advantage that you needn't have a SQL Server instance installed on the local machine. If there's only one user accessing the data and the datamodel is relative simple, go for SQLCE. Keep in mind that SQLCE isn't a full database-server and that some features will be lacking.
There's a nice article on replication here[^]
I are troll
|
|
|
|
|
Hi, thanks for looking
Im devoloping a system (to buy and sell houses), and im having some isues with my current DB design and .net datasets, so i wonder how you guys make it, any advices will be appreciated, thanks
The table is.....Addresses
The thing is that many objects in y system can have an address, for example:
Customer
House
Notary
Employee
Office
And some others
So i decided to put the AddressID in the table corresponding to the object
Table Customers
CustomerID
...
AddressID
Table Houses
HouseID
...
AddressID
Table X
XID
...
AddressID
The thing is that im using .net datasets with foreing keys, wich wont let me add a house row if i havent created the address row, so i have to add automatically an address row before the house row, this has some isues, like i have to set all nonnullable values in address row to something before adding this row. It wouldnt make any sense to ask for the adress before the customer or house general info.
So im just wondering, what would be some other approach
Aditional Info:
Each object can only have one address
1 customer = 1 address
1 house = 1 address
...
Thanks in advance for any feedback
Alexei Rodriguez
|
|
|
|
|
Your initial design motivation was a good one, but is it really necessary to extract the House Address in your database to a separate table? (Same is true for Customer)
The idea behind making a foreign key reference is to eliminate storing duplicate data. In your situation, if you store the House and Customer Address in their respective tables, you wouldn't be storing duplicate data.
IMHO, This sounds like an "overdesign" of your DB schema. Oops.
|
|
|
|
|
I didn't get that from his explanation, I got that there is 1 address table and a FK to all the object table. This is a correct design and I would stick with it.
I would however change the FK constraint so there can be a customer without an address etc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft, glad to hear that the design is not that bad
I decided to go with this approach, because i didnt want to repeat columns (street, number, city...) in the tables for customers, houses, employees... so all of them have an AddressID column wich is a foreing key to the Address table
Thanks
Alexei Rodriguez
|
|
|
|
|
You're design is correct and good, I think your problem may be that the FK forces a constraint that an address record is required, relax this constraint and you are then able to have a cunstomer without an address record and everything works out nicely!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks David for the suggestion
Ill add the address info in the same table for some objects
And use the separate address table for others like customers, because one never knows... what if later they decide to add more than one address to a customer, employee...
Alexei Rodriguez
|
|
|
|
|
|
Hi,
I am trying to Export Data From SqlServer Table Into Multiple Excel Worksheets. I have more than 65000 records which can not fit into one Excel worksheet.
I am using BCP command in a stored procedure to export. I have searched in google and could not get solution to export to mulitple sheets from stored procedure. Please let me know if anyone can help me on this.
Thanks for helping.
|
|
|
|
|
What I would do is create ranges to export. For example, say the data is customer data, you could easily create 26 separate files by exporting customers by the first letter of their last name. Create an A file, B file, etc.
If your table is accounting in nature, use ranges of the account numbers.
Ex: Account: 1000-1999, 2000-2999, etc
|
|
|
|
|
Thank you for your answer. I am looking at the same Excel File with different sheets.
Please let me know.
|
|
|
|
|