|
Thanks. I'm a developer and know some SQL but not the mechanics of SQL Server. I leave that to the experts. Can I change my Auto-Increment field to numeric and if so does that buy me anything?
E=mc2 -> BOOM
|
|
|
|
|
Suppose DataSet myDataSet has a DataTable myDataTable. Do the follwing:
myDataSet.Tables.Remove(myDataTable);
bool tf=myDataSet.HasChanges(); //tf will be false
DataSet tbl=myDataSet.GetChanges(); //tbl will be null
I understand the datatable is removed, thus all the DataRow are removed from the dataset. So dataset does not detect any change and does not get any changes. But what is the common way to track the fact that a whole table is removed from the dataset? Thanks.
webx10
|
|
|
|
|
i am using vs2002 and want to enter data in a sql server 2000 database through vb interface. i am able to enter data if the vb application and sql server both are on same node but i want to enter data to database on other node running sql server. computers are networked under same workgroup and there is no domain controller.
i used the following code for connection.
Dim constudent As SqlConnection
Dim cmdadd As SqlCommand
Dim conn As String
conn = "server=.; UID=sa;PWD=secret;database=student"
constudent = New SqlConnection(conn)
cmdadd = New SqlCommand("insert into student values(@name,@roll,@stream)", constudent)
cmdadd.Parameters.Add("@name", TextBox1.Text)
cmdadd.Parameters.Add("@roll", Val(TextBox2.Text))
cmdadd.Parameters.Add("@stream", TextBox3.Text)
Try
constudent.Open()
cmdadd.ExecuteNonQuery()
constudent.Close()
MessageBox.Show("Data Added", "added", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
Catch ex As Exception
MessageBox.Show("error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
End Try
may any one hlp plz to modify the connection string or tell me how to establish such connection
|
|
|
|
|
server=TheMachineName
or
server=TheMachineName\TheSQLInstanceName
Oh... And logging in as sa through your application is a huge security risk - Don't do it or you could get 0wn3d.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks Colin Angus Mackay for reply
but it is not working
I even tried giving port no 1433 wit the ip address of machine but no success.
As there is no domain controller and all the systems are under same workgroup, if your solution works under these circumstances or not????
Reply.........
|
|
|
|
|
I don't think it's a connection string problem.
If the database is on server 2003 or XP SP 2, you needs to change some configuration values for it to work. You need to add sql server to the windows firewalls exceptions, open the ports, and change some registry settings.
It gets more complicated is you use the MSDTC but Microsoft provides step by step procedures. Search under SP 2 for SQL Server.
|
|
|
|
|
Please , can any one confirm that a table type or collection type parameter can be passed to stored procedure in oracle using ADO.NET ?
I , browsed several forum and site but without success
Thanks
love2code
|
|
|
|
|
You need to use, ODP.Net , Data Provider for Oracle supplied by Oracle. Assuming you have that here is the example from Oracle
/********* Example Start *************/
using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace ODPSample
{
class AssocArray
{
static void Main(string[] args)
{
Console.WriteLine("Demo: PL/SQL Associative Array");
// Connect
string connectStr = "User Id=scott;Password=tiger;Data Source=oracle";
// Setup the Tables for sample
Setup(connectStr);
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand cmd = new OracleCommand("begin MyPack.TestVarchar2(:1, :2, :3);end;",
connection);
OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2);
OracleParameter param3 = cmd.Parameters.Add("param3", OracleDbType.Varchar2);
// Setup the direction
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.InputOutput;
param3.Direction = ParameterDirection.Output;
// Specify that we are binding PL/SQL Associative Array
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Value = new string[3]{"Input1",
"Input2",
"Input3"};
param2.Value = new string[3]{"Inout1",
"Inout2",
"Inout3"};
param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative
// Array
param1.Size = 3;
param2.Size = 3;
param3.Size = 3;
// Setup the ArrayBind Size for param1
param1.ArrayBindSize = new int[3]{13,14,13};
// Setup the ArrayBind Status for param1
param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBind Size for param2
param2.ArrayBindSize = new int[3]{20,20,20};
// Setup the ArrayBind Size for param3
param3.ArrayBindSize = new int[3]{20,20,20};
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("Results:");
Display(cmd.Parameters);
}
public static void Setup(string connectStr)
{
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand("", connection);
try
{
connection.Open();
command.CommandText = "drop table T1";
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
command.CommandText = "CREATE TABLE T1(COL1 number, COL2 varchar2(20))";
command.ExecuteNonQuery();
StringBuilder hdr = new StringBuilder();
hdr.Append("CREATE or replace PACKAGE MYPACK AS ");
hdr.Append("TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;");
hdr.Append(" PROCEDURE TestVarchar2(");
hdr.Append(" Param1 IN AssocArrayVarchar2_t,");
hdr.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
hdr.Append(" Param3 OUT AssocArrayVarchar2_t);");
hdr.Append(" END MYPACK;");
command.CommandText = hdr.ToString();
command.ExecuteNonQuery();
StringBuilder body = new StringBuilder();
body.Append("CREATE or REPLACE package body MYPACK as ");
body.Append(" PROCEDURE TestVarchar2(");
body.Append(" Param1 IN AssocArrayVarchar2_t,");
body.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
body.Append(" Param3 OUT AssocArrayVarchar2_t)");
body.Append(" IS");
body.Append(" i integer;");
body.Append(" BEGIN");
body.Append(" -- copy a few elements from Param2 to Param1\n");
body.Append(" Param3(1) := Param2(1);");
body.Append(" Param3(2) := NULL;");
body.Append(" Param3(3) := Param2(3);");
body.Append(" -- copy all elements from Param1 to Param2\n");
body.Append(" Param2(1) := Param1(1);");
body.Append(" Param2(2) := Param1(2);");
body.Append(" Param2(3) := Param1(3);");
body.Append(" -- insert some values to db\n");
body.Append(" FOR i IN 1..3 LOOP");
body.Append(" insert into T1 values(i,Param2(i));");
body.Append(" END LOOP;");
body.Append(" END TestVarchar2;");
body.Append("END MYPACK;");
command.CommandText = body.ToString();
command.ExecuteNonQuery();
command.CommandText="Commit";
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(command.CommandText + ":" + e.Message);
}
}
public static void Display(OracleParameterCollection collection)
{
foreach(OracleParameter p in collection)
{
Console.Write(p.ParameterName + ": ");
for(int i=0;i<3;i++)
{
if (p.Value is OracleString[])
Console.Write((p.Value as OracleString[])[i]);
else
Console.Write((p.Value as string[])[i]);
Console.Write(" ");
}
Console.WriteLine();
}
}
}
}
/********* Example End *************/
Regards
|
|
|
|
|
I found a really cool function at http://www.devx.com/tips/Tip/20009.
You pass in in a string with what it's deliminated by and it returns a temp table variable of all of the data split into rows..
i.e
select * from fn_SplitByID('oranges|apples|pears|lemons','|')
1 Oranges
2 Apples
3 Pears
4 Lemons
All really nice and pretty. The trouble is I have a table products (around 2000 rows). Each has a product_code (varchar(255) primary key), stock level (int), with a description field that contains deliminated text 'blue|green|pink' (varchar(255)), etc.
I want to extract and split the data into a whole new table:
product_id product_code stock_level description
1 ABC NULL BLUE
2 ABC NULL GREEN
3 ABC NULL PINK
4 DEFG NULL SILVER
5 DEFG NULL GOLD
...
Yeah I am not worried about the stock_level, just need a null value into the new table, as the stock would have to be recounted.
I just keep getting stuck on how to do this. Maybe I've been looking at too many different ways and the answer is straight in front of me. If you have any suggestions I would be grateful.
Many thanks
Kev
|
|
|
|
|
hi all,
In our Application ,we have to use 175 SQL Functions.So there are many dependancies between each other.
That mean ,If we creating a function,we have to use several function to create it.
So I need to create a function relation ship between each other.(like relatinship between Tables)
Please kindly tell me,Is there a way to get dependancies and relation in functions?
thanks in advance.
|
|
|
|
|
You could use sp_depends[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hi everyone,
I want to update a row in a table in SQL with values from another row in the same table,
i wrote this:
UPDATE Income_Codes IC
SET IC.DateLeave = IC1.DateLeave
FROM Income_Codes IC1
WHERE IC.Cm = 420 AND IC1.Cm = 911
but it didn't work.
I tried this:
update Income_Codes
set DateLeave = (select DateLeave from Income_Codes where Cm = 911)
where Cm = 420
it worked, but if i have to update 20 fields, i have to write 20 selects, any other solution?
Thanks
|
|
|
|
|
delete the first row, and insert second row just changing the primary key
love2code
|
|
|
|
|
If you want both rows, then use the following
UPDATE table
SET (col1, col2, col3,..., col20) = (SELECT col1, col2, col3,..., col20
FROM table
WHERE <your_where_clause1>)
WHERE <your_where_clause_2>;
|
|
|
|
|
I tried it, but it gives an error,
any other solutions.
Thanks anyway.
|
|
|
|
|
Hi,
Anybody knows how to know if an ADO connection has been disconnected even before you execute anything? I have an application which has a global connection object and when it was disconnected from the server for whatever reason, it raises an error when you execute something even if the connection has been restored. Is there a way which I could know if the connection is still valid before I even execute anything? Thanks
SDE
|
|
|
|
|
alex1205 wrote: know if an ADO connection has been disconnected even before you execute anything?
You can check the connection state.
State Property (ADO)[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
Thanks for the response. I've tried that and it doesn't work. I also tried looking at the error count but it doesn't report any. 
SDE
|
|
|
|
|
alex1205 wrote: Thanks for the response. I've tried that and it doesn't work.
Oh, sorry about that. I take it the Connection State is showing the connection is still active?
alex1205 wrote: it raises an error when you execute something even if the connection has been restored.
In the original post you mention the next time you execute a query it throws an error. What is the error message it throws when this happens?
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi all,
I have a question on sql writing
for a table schema as below
Table :
(studentID, studentName, courseID, classID, Age)
What is the correct sql of " select studentID from table where classID = "101" and group by courseID and order by age " ? (ie. Group by courseID, and within the same courseID, the studentID are ordered by age)
Thanks
|
|
|
|
|
SELECT studentID
FROM table
WHRERE classID = "101"
GROUP BY courseID
ORDER BY age
By default, ORDER BY will go in ascending order, if you want descending order use ORDER BY age DESC
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
i've used ASP.NET with Microsoft Access Database before.
Now i would to learn how use (create database, create table, create references, add values, update, delete...) SQL databases with ASP.NET (c#).
Can you indicate me a good web tutorial?
|
|
|
|
|
SQL Server BOL [Books On-Line] (i.e. the built in online-help that comes with SQL server and it available on MSDN) have some excellent examples at the bottom of the pages on each of these T-SQL statements. All you have to do in C# is remember that you can run any T-SQL you like through the SqlCommand object.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
We recently had a fire on campus. So we had to move all our data to new servers. The servers are supposed to be identical. I built a large DTS package to move some data into a database. I also made it a scheduled job, and a vb app that calls the scheduled job so our end users can run it by themselves. It was working fine in the previous servers. Now, if I run the DTS package, it works fine. If I try to run the Scheduled Job through Enterprise Manager or through the VB app it fails. Here is the error I get:
Executed as user: server\user. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: ActiveX Scripting encountered a Run Time Error during the execution of the script. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: ActiveX Scripting encountered a Run Time Error during the execution of the script. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I am not sure why it will work if the DTS package is started by me, but not through the scheduled job. I have been working on this for days and I really don't know what else to try, any help would be GREATLY appreciated.
|
|
|
|
|
I have a DTS package, it works fine if I execute it in Enterprise manager. I set up a scheduled job for it, but if I try to start that job it fails. So it works if I run the DTS package, but not if I try to run the job that calls the package. I read the error message and it says it is trying to execute the DTS package as a local user instead of a domain user. In the DTS I have some files moving to another server, so that is what is causing the failure. How can I get the scheduled job to run as a different user?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
