|
Sorry, I will try again. I have a Select statement that select some records. Then I want to send those record on another database. So I wanted to generate a Insert Script, that "Insert" those record.
My english is not so great, but I try to become better. Sorry.
sebastien.lachance.blogspot.com
|
|
|
|
|
|
Any tutorial/link on developing applications for Windows CE .NET using C#
|
|
|
|
|
I have a table with:
partId, location, quantity.
1 ,a ,12
1 ,b ,10
What is the query to make :
partId, quantity a, quantity b.
1 ,12 ,10
There are only two location, a and b.
Do you get it?
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Hi there. It depends on the database you're using. Here's one way in SQL Server:
select partId, sum(quantityA) as a, sum(quantityB) as b
from
(
select partId
,case (when location='a' then quantity else 0) as quantityA
,case (when location='b' then quantity else 0) as quantityB
from myTable
) x
group by partId
|
|
|
|
|
Thanks a lot
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Hi,I have a problem when I try to get resultset from a procedure.
if there is only select statement in my procedure,it works fine.
but if I add a insert statement in procedure,pRst->GetState() will return adStateClosed
develop environment:
vc6+MDAC2.7
sql server 2000
here is the procedure code:
create table tem(t_id int,t_date datetime)
go
insert tem select 1,dateadd(minute,1,getdate())
go
drop proc pTest
go
create proc pTest
as begin
--insert tem select 2,dateadd(minute,2,getdate()) --if I add this line,it won't work
select * from tem
end
go
here is the ado code:
_ConnectionPtr pConn;
_RecordsetPtr pRst;
_CommandPtr pCmd;
pConn.CreateInstance(__uuidof(Connection));
pCmd.CreateInstance(__uuidof(Command));
pRst.CreateInstance(__uuidof(Recordset));
....
try
{
pCmd->CommandText = "pTest";
pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc);
//pRst = pConn->Execute("pTest",NULL,adCmdText);
//both of above return adStateClosed
}
catch (_com_error &e)
{
....
}
thanks
|
|
|
|
|
Can someone please explain what the SET NOBROWSETABLE off / on value does?
This setting doesn't appear in the Microsoft books online.
Thanks,
K
Kerry Johnson
Business Systems Consultant
|
|
|
|
|
See KB 275483[^]. At the end of the article, it describes SET NO_BROWSETABLE ON.
There's some information for the SQL Server 2005 Beta in KB 885146[^].
Basically, the option is there to support client-side cursors. After a certain amount of experience my recommendation would be to avoid client-side cursors and indeed updateable recordsets in any form. Use static, forward-only recordsets and explicit INSERT, UPDATE and DELETE commands instead.
Be aware that SQL Server only supports a single forward-only resultset at a time on a given connection. ADO fakes multiple active resultsets by creating another connection under the covers - if you're not aware of this you can end up deadlocking with yourself. Also note that the second connection is not part of the same transaction.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
I am trying to copy a record from an existing record in the database and then save it as a new record. The dataset I am using has a couple of references to child tables which have primary key info as well. Does anyone know an easy way to do a copy, get a new primary key information and set the primary keys on the copy to the new one?
Thanks.
|
|
|
|
|
I have a simple C# app that uses an OleDBDataAdapter on an Access database. One of the fields is a date/time. I insert a new row into the table and the date portion of the date/time is there but the time is always "00:00:00" (the time fields are set properly in the DateTime object when I look at it in the debugger)
I tried setting the schema to us DBTimestamp and get an exception thrown during the database update 'type mismatch on criteria query'
any ideas on what could be causing or what I need to do to get both the date and time portions of a DateTime to trasfer to a database?
thanks!
|
|
|
|
|
I had to set all my Access date formats (in the table designer) to 'General Date'. The format seems to dictate the output format. Other formats are not fully understood by the ODBC driver (guessing).
|
|
|
|
|
Is there a way to do a query on 2 differents DB ?
Please help !
|
|
|
|
|
You can do this if you use linked servers.
Affordable and reliable hosting? Click here!
|
|
|
|
|
I am re-writing a control I was working on to use dynamically created template columns instead of using the designer for it. For one of my columns, I need a DropDown list, this worked in my old version fine. However, when writing the Databind event for the new control I am running into a problem where when I call the DataBind() on the listcontrol, it's causing an infinite loop.
It simply cuts out on the bind, and the method re-starts, I cant figure out why this is happening.
However, if I simply loop though all the items in the Dataset, and add them to the dropdown list that way, this works fine - however, much rather use databinding to do this.
private void TemplateControl_DataBindingDL(object sender,System.EventArgs e)
{
Debug.WriteLine("Starting DataBind:" + System.DateTime.Now);
DropDownList lc;
lc = (DropDownList) sender;
string strSqlCommand;
DataGridItem container = (DataGridItem) lc.NamingContainer;
SqlConnection sqlConnection1 = new SqlConnection();
DataSet dsTemp = new DataSet();
strSqlCommand = "SELECT " + fieldName + " FROM V_T_TASKS GROUP BY " + fieldName;
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(strSqlCommand,sqlConnection1);
sqlConnection1.ConnectionString = "...//connection string";
sqlConnection1.Open();
sqlDataAdapter1.Fill(dsTemp);
Debug.WriteLine("assigning datasource...");
lc.DataSource = dsTemp;
lc.DataTextField = fieldName;
lc.DataValueField = fieldName;
Debug.WriteLine("assignedg datasource... binding");
Debug.WriteLine("Rows count=" + dsTemp.Tables[0].Rows.Count);
lc.DataBind();
Debug.WriteLine("..bound");
dsTemp.Clear();
Debug.WriteLine("Cleared...");
lc.Items.Add( DataBinder.Eval(container.DataItem,fieldName,"{0}"));
Debug.WriteLine("Ending DataBind:" + System.DateTime.Now);
lc.SelectedIndex = 0;
sqlConnection1.Close();
}
When I run this, it'll just get to the last two debug statements, then jump back upto the start and keep looping - not throwing any errors or exceptions either.
If I take out the databind and replace it with a normal loop to add each item in the dataset, it works fine.
any ideas?
|
|
|
|
|
Alright everybody, hope you're all well. :->
Does anybody know how to remove the autonumber functionality from the id column in a table through a SQL Statement in SQL Server, it's obviously easy through Enterprise Manager .
I'm assuming it would be an ALTER TABLE statement but was wondering if anyone knows the syntax for this or if there is an easier method, like a stored procedure.
Help will be greatly appreciated.
Thanks
|
|
|
|
|
The following page has a some sample TSQL that does this.
Basically, it creates a new column of the same type as the identity, copies the data into the new column, drops the identity column and renames the new column to the name of the old identity column.
Affordable and reliable hosting? Click here!
|
|
|
|
|
Hi,
Thanks for the reply, however i think you may have forgotten to include the link.
Would be greatly appreciated if you could post it as i have found nothing in the way of doing this so far.
Thanks again,
|
|
|
|
|
|
Hello,
I have a problem with this code here.
cmd.CommandText = "UPDATE DBPROF SET (ID,NAME,PROF) = " +<br />
"(?,?,?) WHERE (rowguid = '"+strGUID+"')";<br />
<br />
cmd.Parameters.Add(@"ID",dID);<br />
cmd.Parameters.Add(@"NAME",strName);<br />
cmd.Parameters.Add(@"PROF",strPROF);<br />
Why this code isn't working for updating db ?
also values instead "?" don't work:
(@ID,@NAME,@PROF)
|
|
|
|
|
You don't say what database you are using - This is important information as there are subtle differences between each type of database (even from the same Vendor - MS Access has some differences with SQL Server)
Also, you have ommitted to describe what the cmd object is. Is it an OleDbCommand, a SqlCommand, an OracleCommand or what? There may be some slight differences in the way each of these work as a result of interacting with different databases. This information is also good to know as it helps diagnose the problem.
Why are you using parameters for the update values, but you are injecting the GUID into the string (this leaves you open to a SQL Injection attack)
Finally, a stab at the answer:
The syntax I would use is:
UPDATE DBPROF SET ID=@id, NAME=@name, PROF=@prof WHERE rowguid='GuidValueHere' This is valid for SQL Server.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
cmd.CommandText =
"UPDATE DBPROF SET ID = ?, " +
"NAME = ?, PROF = ? " +
"WHERE rowguid = ?";
cmd.Parameters.Add( "ID", dID );
cmd.Parameters.Add( "NAME", strName );
cmd.Parameters.Add( "PROF", strProf );
cmd.Parameters.Add( "rowguid", strGUID ); If you're using the SQL Server Managed provider, change the ? in CommandText for @param names, then supply the same @param names for the corresponding parameters:
cmd.CommandText =
"UPDATE DBPROF SET ID = @id, " +
"NAME = @name, PROF = @name " +
"WHERE rowguid = @rowguid";
cmd.Parameters.Add( "@id", dID );
cmd.Parameters.Add( "@name", strName );
cmd.Parameters.Add( "@prof", strProf );
cmd.Parameters.Add( "@rowguid", strGUID );
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thx, works
|
|
|
|
|
hi,
I have installed MSDE (sql2kdesksp3.exe) in my PC by following the guidelines given in Lars article
http://www.codeproject.com/database/ConfigureMSDE.asp[^]
but when i tried to connect using osql (C:\> osql –E –S localhost\myinstance),
it is giving the following error: "Server does not exist or access denied".
i dont have any idea what's wrong? i could see that the service is running.
i am running on a dual boot with Linux and Windows 2000 professional.
I installed it in Administrator login only without any password.
Can you please help?
|
|
|
|
|
Hello,
What is the best place to keep my SqlConnection object? Should I have only one application-wide? or one new every time I need it?
Where should I open it? every time I'm going to use it? or at the beginning of my application?
The application is what I consider more or less database intensive. It uses a lot of the database for some time, and then stands idle for some other time.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|