Database

25-Mar-02 1:57

Do we have SQL Statement with 'AND' , 'OR',For example:

SELECT * FROM Table1 WHERE Column1='sth' AND Column2='sth' OR Column3='anotherthing'

Thanks

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Re: AND/OR

Mazdak25-Mar-02 4:14

25-Mar-02 4:14

I found it friends.Thanks Smile | :)

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Re: AND/OR

Philip Patrick29-Mar-02 2:23

Philip Patrick

29-Mar-02 2:23

Lol, just came to answer - yes, But late Smile | :)

Philip Patrick
Web-site: www.saintopatrick.com
"Two beer or not two beer?" Shakesbeer

Need ASP hosting? Check out 123HostNow.com

Re: AND/OR

Mazdak29-Mar-02 8:44

29-Mar-02 8:44

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Duplicated Value

Mazdak23-Mar-02 20:15

23-Mar-02 20:15

I have a database and a table in it,with primary key,the index of primary does not accept "duplicated value",So when I want add records to database for second time it gives me error that :

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index,or redefine the index to permit duplicate entries and try again.

What is duplicated value?

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Roger Wright23-Mar-02 20:21

Roger Wright

23-Mar-02 20:21

A primary key index cannot have two records that have matching values for the key field. They have to be unique for the table. If you are trying to Update a record with a primary index included, try deleting the record first, then adding the new one.

Mazdak23-Mar-02 20:38

23-Mar-02 20:38

hmmmm,What about the time I want to INSERT record?For the first time it is ok but second time it gives me that error,How can I avoid that one?

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Roger Wright24-Mar-02 5:10

Roger Wright

24-Mar-02 5:10

Is this happenning on non-duplicate values, or are you trying to add multiple records with the same value in the primary index field? It isn't clear what's going on...

Mazdak24-Mar-02 6:29

24-Mar-02 6:29

I have DataSet and DataGrid,in my application I add or delete some records from DataGrind,after all I check the dataset if anything change compare to the original datasource,if something change or add or delete,it add these change to the datasource.The code made these changes is here:

deletedRows =myDataSet.GetChanges(System.Data.DataRowState.Deleted);

if (((deletedRows) != (null))) 
{ 
//DataTable updTable = deletedRows.Tables["Companies"];
myOleDbAdapter.Update(deletedRows,"Companies");
}

// Get all of the updated rows and update the datastore
updatedRows = myDataSet.GetChanges(System.Data.DataRowState.Modified);
if (((updatedRows) != (null)))
{
myOleDbAdapter.Update(updatedRows,"Companies");
}
// Get all of the inserted rows and update the datastore
insertedRows =myDataSet.GetChanges(System.Data.DataRowState.Added);
if (((insertedRows) != (null)))
{
myOleDbAdapter.Update(insertedRows,"Companies");
}

Roger Wright wrote:
Is this happenning on non-duplicate values, or are you trying to add multiple records with the same value in the primary index field?

I don't know when I add non-duplicate or duplicate,I mean I don't what are they to know when I add which of them.The error tell me that I create multiple value in the index,primary key or relationship.This happend the SECOND time I call that function not for the first time.

Thanks

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

James T. Johnson25-Mar-02 16:40

James T. Johnson

25-Mar-02 16:40

I've read your thread with Roger and I'll try taking a scond approach at explaining the problem.

In a database you have tables; each table has one or more Primary Keys defined on it. A primary key is made up of one or more fields.

A primary key is a unique value in the table, there can only be one such key in the table as well. If you only have one field, called username then each username in the table must be unique. Now you can also have two fields be the primary key; in that case that same combination of values can only exist in the table once. If you have a primary key made up of UserID and OrderID then you can have multiple records with the same UserID and you can have multiple records with the same OrderID, but you can have only one record with the same combination of UserID AND OrderID.

The error you are receiving is because you are trying to add (or update) a record that would create a duplicate primary key.

HTH,

James

Sonork ID: 100.11138 - Hasaki
"Smile your little smile, take some tea with me awhile.
And every day we'll turn another page.
Behind our glass we'll sit and look at our ever-open book,
One brown mouse sitting in a cage."
"One Brown Mouse" from Heavy Horses, Jethro Tull 1978

Mazdak25-Mar-02 19:35

Problem using a Parameterized Command as the Select Command for a Data Adapter

25-Mar-02 19:35

hmmmmm,now I got the point . Smile | :)

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Paulo23-Mar-02 7:17

Paulo

23-Mar-02 7:17

Seems that using a parameterized query as a SelectCommand for an OleDbDDataAdapter to fill a DataSet doesn't work!
The following code is how I'm doing that. All the code is inside an try/catch block. The language i'm using is C#

// Using a UDL file for the connection string
string strDBConn = "FileName=D:\Contacts.udl;";

// Sets up the connection
OleDbConnection conn = new OleDbConnection(strDBConn);
conn.Open(strDBConn);

// Creates a new command
OleDbCommand cmd = new OleDbCommand(
"select * from dbo.Contacts where contact_id = ?", conn);

// Sets up the Data Adapter
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;

// Sets the Command parameters
param = da.SelectCommand.Parameters.Add (
"@contact_id", OleDbType.Numeric, 9, "entid_id");
param.Value = 1; // The parameter value

// creates a new DataSet
DataSet dsResult = new DataSet()

// Fills the DataSet
da.Fill(dsResult);

Somehow in the last instruction it generates an exception that even 'catch (Exception e)' can't get exactly because in spite of falling in this catch block the 'e' (the exception) is null!
But the reason he is throwing some weird exception is because the parameters are not being declared first before querying the database with the parameterized select command and consequently the string that is being sent is only "select * from dbo.Contacts where contact_id = @P1" and nothing else.
I'm using a .UDL file to set my connection string which is set with valid parameters because it's being used everywhere else in the app and it works perfectly.
My Database is SQL Server 2000... Yeah, of course I could use SqlDataAdapter and all the other classes specific for SQL Server but the fact is that I want to use an .UDL file to set my datasource and the only way to do this is using the OleDb... classes. UDL files doesn't work with SqlConnection in case you didn't know.

So... am I doing something wrong here in setting the Data Adapter to a parameterized query? Is this a bug in the .NET framework? Can you help me out with this? Thanks in advance Smile | :)

Best Regards,
Paulo

ASP DLL PROBLEM....HELP!!!!

22-Mar-02 11:18

I am having a problem with a server side component (DLL) i am developing for an ASP page.
Im not really sure what the problem is. I tested the code in a vbproject and it worked perfectly but
when I try it in an ASP page, it doesnt work.

**************************************************************
My ASP page looks like this

<%@ Language=VBScript %>

<%
dim obj
set obj = server.CreateObject("Danny.Lopez")
Response.Write(obj.GetTeam(1))

%>

***************************************************************

This is the error im getting

Error Type:
Danny (0x800A01A8)
Object required
/Project1/Registration.asp, line 10

***************************************************************

This is some of the the code in my ActiveX DLL

Public Function GetTeam(TeamID As Integer) As String
Dim sql As String
sql = "SELECT Name FROM Teams WHERE TeamID = " & TeamID
GetTeam = ExecuteQuery(sql, True)(0)
End Function

Private Function ExecuteQuery(sql As String, Optional CloseConnection As Boolean = False) As ADODB.Recordset
Dim state As Integer
state = DataEnvironment1.Connection1.state

If state = 0 Then
DataEnvironment1.Connection1.Open
End If

Dim rs As ADODB.Recordset
Set rs = DataEnvironment1.Connection1.Execute(sql)
Set ExecuteQuery = rs

If state = 0 And Not CloseConnection Then
DataEnvironment1.Connection1.Close
End If
End Function

***************************************************************

To make sure the DLL worked at all in the ASP page I created a simple function that takes a
string and returns it.

Public Function Repeat(str As String) As String
Repeat = str
End Function

This function worked in an ASP pef

***************************************************************
I feel the error has to do with the data types im passing to my DLL, but im not sure.
Please Help!!!!

Thanks Danny Cry | :((

Re: ASP DLL PROBLEM....HELP!!!!

Loki27-Mar-02 22:47

Loki

27-Mar-02 22:47

DanielLopez wrote:
Public Function GetTeam(TeamID As Integer) As String
Dim sql As String
sql = "SELECT Name FROM Teams WHERE TeamID = " & TeamID
GetTeam = ExecuteQuery(sql, True)(0)
End Function

Rewrite the above function as ...
Public Function GetTeam(TeamID As Integer) As String
Dim sql As String
Dim rs as ADODB.Recordset

sql = "SELECT Name FROM Teams WHERE TeamID = " & TeamID
set rs = ExecuteQuery(sql, True)

GetTeam = rs.Fields(0)
End Function
Smile | :)

Grant @ Loki

Another problem in ADO.NET

Mazdak22-Mar-02 9:30

22-Mar-02 9:30

I have a datagrid and its connected to database ,I use this funcion to save changes to
datasource from datagrid:

private void btnSave_Click(object sender, System.EventArgs e)
{
    deletedRows=null;
    updatedRows=null;
    insertedRows=null;

    try
    {

        deletedRows =myDataSet.GetChanges(System.Data.DataRowState.Deleted);
        if (((deletedRows) != (null)))
        {
            myOleDbAdapter.Update(deletedRows,"Companies");
        }


        // Get all of the updated rows and update the datastore
        updatedRows = myDataSet.GetChanges(System.Data.DataRowState.Modified);
        if (((updatedRows) != (null)))
        {
            myOleDbAdapter.Update(updatedRows,"Companies");

        }


        // Get all of the inserted rows and update the datastore
        insertedRows =myDataSet.GetChanges(System.Data.DataRowState.Added);
        if (((insertedRows) != (null)))
        {
            myOleDbAdapter.Update(insertedRows,"Companies");
        }
        myDataGrid.ReadOnly=true;

    }
    catch(Exception myE)
    {
        MessageBox.Show("Problem!!" + myE.ToString());

    }




}

For the first time it works ok but second time I unhandle error thrown:

---------------------------

---------------------------
System.Data.OleDb.OleDbException: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

at WindowsApplication3.Form1.btnSave_Click(Object sender, EventArgs e) in f:\visual studio projects\projectc#\windowsapplication3\form1.cs:line 219
---------------------------
OK
---------------------------

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

ADO.NET

Mazdak22-Mar-02 7:44

22-Mar-02 7:44

I want to check if my connection is close or not but Close() does not return any value. Confused | :confused:

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Andres Manggini22-Mar-02 8:20

Andres Manggini

22-Mar-02 8:20

Do you mean, check if Close() has REALLY closed the connection, or check at any given time if a particular connection object is open ?

in the latter, what about setting the connection object to null after calling Close(), and then check if the object is null when you are about to use it ?

Andres Manggini.
Buenos Aires - Argentina.

Mazdak22-Mar-02 9:34

22-Mar-02 9:34

Andres Manggini wrote:
Do you mean, check if Close() has REALLY closed the connection, or check at any given time if a particular connection object is open ?

No,I want to open it if I close it in another places in my program.

Andres Manggini wrote:
in the latter, what about setting the connection object to null after calling Close(), and then check if the object is null when you are about to use it ?

This is good idea.

I looked into MDSN and found ConnectionState,but I couldn't find out how to work with it,the example in MSDN is not good one,like always;)

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

Andres Manggini22-Mar-02 10:06

Andres Manggini

22-Mar-02 10:06

Yeah, that work too..
That property can take to values:
The allowed state changes are:

Property Value
A bitwise combination of the ConnectionState values. The default is Closed.

From Closed to Open, using the Open method of the connnection object.
From Open to Closed, using either the Close method or the Dispose method of the connection object.

[C#]
public void createOleDbConnection()
{
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;";
myConnection.Open();
MessageBox.Show("Connection State: " + myConnection.State.ToString());
myConnection.Close();
}

this helps ?

Andres Manggini.
Buenos Aires - Argentina.

Mazdak22-Mar-02 18:26

22-Mar-02 18:26

Andres Manggini wrote:
public void createOleDbConnection()
{
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;";
myConnection.Open();
MessageBox.Show("Connection State: " + myConnection.State.ToString());
myConnection.Close();
}

This is the one MSDN,and I said I see this before,the problem is that how to check the condition state not convert to string.

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975

AndyG22-Mar-02 18:44

AndyG

22-Mar-02 18:44

I think you're looking for the System.Data.ConnectionState enum.

Andy Gaskell, MCSD MCDBA

Mazdak22-Mar-02 19:11

22-Mar-02 19:11

AndyG wrote:
I think you're looking for the System.Data.ConnectionState enum.

Yes,If you read all the thread you figure out that the problem is that I need an example for that. Smile | :)

Mazy

"So,so you think you can tell,
Heaven from Hell,
Blue skies from pain,...
How I wish,how I wish you were here."
Wish You Were Here-Pink Floyd-1975