Database

15-Mar-02 0:38

Whats wrong with this code?(it's in ASP.NET)

OleDbConnection myConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Inetpub\wwwroot\First\Testing.MDB");
myConnection.Open();

OleDbDataAdapter myAdapter=new OleDbDataAdapter("SELECT * FROM Table2",myConnection);

OleDbCommandBuilder myBuild=new OleDbCommandBuilder(myAdapter);

DataSet myDataSet=new DataSet();

myAdapter.Fill(myDataSet,"Table2");

DataColumn[] keys=new DataColumn[1];
keys[0]=myDataSet.Tables["Table2"].Columns["Username"];
myDataSet.Tables["Table2"].PrimaryKey=keys;

DataRow findRow=myDataSet.Tables["Table2"].Rows.Find(TextBox3.Text);

if(findRow==null)
{
    if(TextBox1.Text==TextBox2.Text)
    {
        DataRow myRow=myDataSet.Tables["Table2"].NewRow();
        myRow["Username"]=TextBox3.Text;
        myRow["Password"]=TextBox2.Text;
        myDataSet.Tables["Table2"].Rows.Add(myRow);

        myAdapter.Update(myDataSet,"Table2");

    }
}

I got an unhandled exception for this line:myAdapter.Update(myDataSet,"Table2");

Syntax error in INSERT INTO statement.

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. Johnson16-Mar-02 20:23

16-Mar-02 20:23

I'm not sure but you may have to assing the Insert, Update, and Delete Commands to the DataAdapter; at least thats what my Beginning ASP.NET book shows being done, but it was based on Beta 2.

If that doesn't work can you tell me the value of myAdapter.UpdateCommand.CommandText?

It also looks like for using the CommandBuilder you have to specify fields to get (including the primary key fields if they exist) because the other commands are built without accessing the database.

I'm basing this on what I see in the book, shame on me Blush | :O

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

Mazdak16-Mar-02 21:24

16-Mar-02 21:24

Hi James:
I learn ASP.NET like a blind persons,because in my country there is no books about .net topics and I have only some limited internet resources ,Any way.

James T. Johnson wrote:
I'm not sure but you may have to assing the Insert, Update, and Delete Commands to the DataAdapter;

How should I do it?

James T. Johnson wrote:
If that doesn't work can you tell me the value of myAdapter.UpdateCommand.CommandText?

I don't know how to debug ASP.NET application Frown | :(

In the windows form I put a breakpoint and use F5,But here it does not work Cry | :((

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. Johnson16-Mar-02 21:43

16-Mar-02 21:43

Mazdak wrote:
I learn ASP.NET like a blind persons,because in my country there is no books about .net topics and I have only some limited internet resources

I've always considered myself lucky in that regard Smile | :)

I only mentioned it came from the book because I hadn't actually tried it myself.

Mazdak wrote:
James T. Johnson wrote:
I'm not sure but you may have to assing the Insert, Update, and Delete Commands to the DataAdapter;

How should I do it?

OleDbDataAdapter myAdapter=new OleDbDataAdapter("SELECT Username, Field1, Field2, .... FROM Table2",myConnection);
OleDbCommandBuilder myBuild=new OleDbCommandBuilder(myAdapter);

myAdapter.InsertCommand = myBuild.GetInsertCommand();
myAdapter.UpdateCommand = myBuild.GetUpdateCommand();
myAdapter.DeleteCommand = myBuild.GetDeleteCommand();

DataSet myDataSet=new DataSet();
myAdapter.Fill(myDataSet,"Table2");

// these lines shouldn't be needed anymore because we explicitly defined the fields above
// DataColumn[] keys=new DataColumn[1];
// keys[0]=myDataSet.Tables["Table2"].Columns["Username"];
// myDataSet.Tables["Table2"].PrimaryKey=keys;

[....].

Mazdak wrote:
I don't know how to debug ASP.NET application,In the windows form I put a breakpoint and use F5,But here it does not work

At the top of your ASP.NET page change your Page tag to this <%@ Page Trace="true" ....>

Now when you want to output something for debugging purposes use, Trace.Write(strCategoryName, strValueToOutput); The category name is so that you can see what the value is for.

In your case you would put this before the myAdapter.Update(myDataSet,"Table2");

Trace.Write("myAdapter.InsertCommand.CommandText", myAdapter.InsertCommand.CommandText);

HTH, I'm was just on my way to bed so my brain may be fried :-P

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

Mazdak16-Mar-02 23:07

16-Mar-02 23:07

Good morning;)
James T. Johnson wrote:
OleDbDataAdapter myAdapter=new OleDbDataAdapter("SELECT Username, Field1, Field2, .... FROM Table2",myConnection);OleDbCommandBuilder myBuild=new OleDbCommandBuilder(myAdapter);myAdapter.InsertCommand = myBuild.GetInsertCommand();myAdapter.UpdateCommand = myBuild.GetUpdateCommand();myAdapter.DeleteCommand = myBuild.GetDeleteCommand();

I got really nice unhandled exception for last two line Smile | :)

:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

And after I use that still the same problem remain(it could not update)

James T. Johnson wrote:
// these lines shouldn't be needed anymore because we explicitly defined the fields above// DataColumn[] keys=new DataColumn[1];// keys[0]=myDataSet.Tables["Table2"].Columns["Username"];// myDataSet.Tables["Table2"].PrimaryKey=keys;

I need these lines because I use Find method and I need primary key for it.

James T. Johnson wrote:
At the top of your ASP.NET page change your Page tag to this <%@ Page Trace="true" ....>

Now when you want to output something for debugging purposes use, Trace.Write(strCategoryName, strValueToOutput); The category name is so that you can see what the value is for.

In your case you would put this before the myAdapter.Update(myDataSet,"Table2");

Trace.Write("myAdapter.InsertCommand.CommandText", myAdapter.InsertCommand.CommandText);

Frown | :(

I did as you say and nothing appear in output window,also something that I can't describe it mess up the background of page? 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

James T. Johnson17-Mar-02 14:50

17-Mar-02 14:50

Mazdak wrote:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Did your SELECT statement specify fields or just *?

When using the CommandBuilder you have to specify the fields that you are using, otherwise it has no way of generating the Update or Insert commands (since both require you to specify fieldnames and values).

Mazdak wrote:
I need these lines because I use Find method and I need primary key for it.

OK, I see what the code is actually doing now; I was thinking it was adding new columns to the data table when its just telling what the primary keys are. My bad Blush | :O

Mazdak wrote:
I did as you say and nothing appear in output window,also something that I can't describe it mess up the background of page?

When you enable tracing you should see a large table that shows what happened as the page was processed. I can't explain what the output should be like, but I can show you Smile | :)

I've uploaded a page that shows the output from a simple webform that sets the text of a label control inside the page_load. I've added Trace.Write's just before and just after I change the text.

http://www.takklesoft.com/dotnet/tracing_results.htm

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

Mazdak17-Mar-02 20:50

17-Mar-02 20:50

James T. Johnson wrote:
Did your SELECT statement specify fields or just *?

I specify fields Frown | :(

And after tracing InsertCommand it show me this:

myAdapter.InsertCommand.CommandText INSERT INTO Table2( UserName , Password ) VALUES ( ? , ? )

I use these code for inserting:

DataRow myRow=myDataSet.Tables["Table2"].NewRow();
myRow[0]=TextBox3.Text;
myRow[1]=TextBox2.Text;
myDataSet.Tables["Table2"].Rows.Add(myRow);

Then why it doesn't know the values for insertation?
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

James T. Johnson17-Mar-02 21:56

17-Mar-02 21:56

Very odd, you've done just about what the book is showing being done; except you've used text boxes for input and they used literal values (which won't make a difference since they are both string objects being assigned to the elements of myRow.)

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

Mazdak17-Mar-02 22:21

17-Mar-02 22:21

Soryy James,I can't understand what are you talking about?
(Forgive me for my poor english)
You mean I made mistake somewhere?You ask me a question or you give me advise;)???? 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

James T. Johnson17-Mar-02 22:28

17-Mar-02 22:28

Mazdak wrote:
You mean I made mistake somewhere?You ask me a question or you give me advise????

Neither Smile | :)

I just said that what you did is almost the same as the code in my ASP.NET book. The code *should* work, but obviously isn't.

Its starting to get that time though, so I'll have to take a look at it whenever I get up out of bed :-P

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

Mazdak17-Mar-02 22:41

How to connect to MicroSoft Access without creating a DSN in Control Panel?

17-Mar-02 22:41

hmmmm,Now I got what you say 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

14-Mar-02 22:48

(In VC6.0,using MFC & ADO)
I wrote a application based on MicroSoft Access.To let users use my application more easily,i want to save them from setting a DSN in Control Panel ODBC Administrator by themselves.Now suppose i have a Access file called List.mdb located in C:\,it contains just one table called Toys,and i created a ADO smart pointer in my code:

_ConnectionPtr m_pConnection;

Then,what the ConnectionString of m_pConnection shall look like? Or there's some other good method?

Thanks Much

God blessing me...

Re: How to connect to MicroSoft Access without creating a DSN in Control Panel?

Mazdak15-Mar-02 0:16

Re: How to connect to MicroSoft Access without creating a DSN in Control Panel?

15-Mar-02 0:16

Allis wrote:
Then,what the ConnectionString of m_pConnection shall look like?

m_pConnection->Open(
 _bstr_t(L"Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\file.mdb;"),
  _bstr_t(L""),
  _bstr_t(L""),
  adModeUnknown);

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

15-Mar-02 4:04

Thank you.It's OK now
But why does it work only when i cut the path,just write: L"...;Data Source=list.mdb;" ?Otherwise,it will append the path where the application locate,then cause a "can't find file" error.

God blessing me...

ADO.NET

Mazdak14-Mar-02 4:31

14-Mar-02 4:31

I have DataAdapter and DataSet,my dataadapter string is something like this:
"SELECT * FROM TABLE",and I fill dataset with this dataadapter,and I bound Datgrid to it and it's ok.
Now after this I want to set a filter for the recorsets that datagrid show.Can I use those dataadapter and dataset and set the filter in the way that I don't know it,
or I HAVE TO fill dataadapter and dataset from the beginning with new SQL statement?

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 Manggini16-Mar-02 16:39

Andres Manggini

16-Mar-02 16:39

To filter your table:

1) Get the DataView from the appropiate table.

MyTable.DefaultView //this is a property of the table.

2) Set the Filter.

MyTable.DefaultView.RowFilter = "Name = 'Mazdak'";

Andres Manggini.
Buenos Aires - Argentina.

Mazdak16-Mar-02 19:59

16-Mar-02 19:59

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

Mazdak16-Mar-02 20:40

16-Mar-02 20:40

I used this code :

string str="CompanyName LIKE '%"+DropDownList1.SelectedItem.Text+"'";
ds.Tables["Customers"].DefaultView.RowFilter=str; //ds is dataset
DataGrid1.DataSource=ds;
DataGrid1.DataBind();

I have a drop down list box,it's item form A to Z,when user choose one,data grid must filter by the words that their first character are like that.when I use this code nothing
happen,did I miss something?

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. Johnson16-Mar-02 21:20

16-Mar-02 21:20

According to the code above you are matching results where the last letter is the one selected :-P

string str="CompanyName LIKE '"+DropDownList1.SelectedItem.Text+"%'"; is the correct version Smile | :)

Hopefully that is what is causing the problem, because according to the docs what you have should work. Eek! | :eek:

Mazdak16-Mar-02 21:30

16-Mar-02 21:30

James T. Johnson wrote:
Hopefully that is what is causing the problem, because according to the docs what you have should work.

No ,that was not the problem. Poke tongue | ;-P

It seems that datagrid does not update. Dead | X|

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. Johnson16-Mar-02 21:44