|
I clearly am missing something (hopefull obvious to most) as how the adapter update function works. I have .net 1.1 and to test I just select the table name from a combobox and then refresh the data grid. The display works fine in showing the data and in showing changes but I fail when I want to submit the changes back.
I am using two functions 1) to get the data from the database
private void TableComboBox_SelectedIndexChanged(object sender, System.EventArgs e)
{
m_tableName = TableComboBox.SelectedItem.ToString();
if(m_tableName.Length<1) return;
m_dataGrid1.SetDataBinding(m_dsTables, "");
try
{
string sql = "Select * FROM " + m_tableName;
if(m_dataAdapter!=null) m_dataAdapter.Dispose();
m_dataAdapter = new OdbcDataAdapter(sql, m_Connection);
m_dsTables.Clear();
m_dataAdapter.Fill(m_dsTables, m_tableName);
m_dataGrid1.DataSource = m_dsTables.Tables[m_tableName].DefaultView;
}
catch(Exception ex)
{
string myval = ex.ToString();
}
}
and then press a button to "commit" the chagnes.
private void CommitButton_Click(object sender, System.EventArgs e)
{
string myval;
try
{
m_dsTables.AcceptChanges();
m_dataAdapter.Update(m_dsTables,m_tableName);
}
catch(Exception ex)
{
myval = ex.ToString();
}
}
I am new to this so any pointers to read are fine or a sample code segment.
Thanks.
|
|
|
|
|
private void CommitButton_Click(object sender, System.EventArgs e)
{
string myerrormes;
DataSet cdataSet=null;
int cnt=0;
try
{
cdataSet = m_dataSet.GetChanges();
cdataSet.WriteXml("C:\\gridC.xml");
m_dataSet.WriteXml("C:\\gridM.xml");
m_dataSet.AcceptChanges();
cnt = m_dataAdapter.Update(m_dataSet,m_tbname);
}
catch (Exception ex)
{
myerrormes = ex.ToString();
}
}
Well not the progress I had hoped for. When I write out the changed and complete data sets to an XML file they do have the correct updates. So I am missing something with the AcceptChanges and Update methods. The database does not get the updates. I have the same effect with Oracle, SQL Server and Jet.
|
|
|
|
|
Ok...Silly questions firt:
How did you generate the DataAdapter? Did you code it by hand or drag-n-drop from the ServerExplorer?
If you drag-n-dropped, you need to be sure that the table has a primary key defined if you want the update and delete commands to be generated correctly by VS.NET.
Another silly question: is that a windows app or a web app?
Hope this helps...
Bill
|
|
|
|
|
Bill Dean wrote:
How did you generate the DataAdapter?
Hand coded it. Just did a drag and drop with no differences. I am in both cases entering the connection string via code. The table I am experimenting with does have a primary key defined.
Bill Dean wrote:
Another silly question: is that a windows app or a web app?
My sample is a little windows app just to display a single table. The goal is to learn the correct code and then it will go into a Web Service.
I am sure my problem is something simple, just am overlooking something in the samples provided vs what I am doing.
I may try a Oracle or SqlServer Client vs the ODBC and see what differences I find.
Thanks for the help.
"Don't be so anti-american, would you?
KaЯl (to Paul Watson on Baseball Bats) 26 Nov '03 "
|
|
|
|
|
Hi Michael,
ok...so you dragged a dataadapter from the toolbox to the form? Or did you drag the table onto the form from the Server Explorer? I am guessing from your previous response that you dragged the blank dataadapter from the toolbox...yes?
Forgive me if I am being too basic here....
A dataadapter has four Command objects associated with it: select, insert, update and delete commands. If you construct the dataadapter in code by specifying the sql command in a string along with the connectionstring it ONLY generates the SELECT command.
I've never used the OdbcDataAdapter, but for the OLEDB and SQL dataadapters, there is a CommandBuilder class that will generate the other 3 commands for you (provided the table has a primary key).
Before you do the update, in your CommitButton_Click method:
OdbcCommandBuilder CB = new OdbcCommandBuilder(m_dataAdapter);
Creating CB should create the other three commands on m_dataAdapter, so the update should work.
Good luck,
Bill
ps: see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbccommandbuilderclasstopic.asp
|
|
|
|
|
Bill Dean wrote:
If you construct the dataadapter in code by specifying the sql command in a string along with the connectionstring it ONLY generates the SELECT command.
Before you do the update, in your CommitButton_Click method:
OdbcCommandBuilder CB = new OdbcCommandBuilder(m_dataAdapter);
For the record; this is exactly what I was missing.
Bill Again Thanks.
"Don't be so anti-american, would you?
KaЯl (to Paul Watson on Baseball Bats) 26 Nov '03 "
|
|
|
|
|
Hi,
the problem is in the order of these 2 statements:
> m_dsTables.AcceptChanges();
> m_dataAdapter.Update(m_dsTables,m_tableName);
You have to update before accepting the changes in the DataSet.
AcceptChanges means: dataset, forget the information you have collected about the modification of the data since you have received it from the DataAdapters or the last call of AcceptChanges.
So if you call this method before the Update of the DataAdapter, it appears to the adapter that there were do modifications at all and nothing has to be written back in the database.
Concerning this topic also have a look at the EndCurrentEdit-method of the BindingManager and the GetChanges-method of the dataset.
Hope I could help
|
|
|
|
|
Addition:
... and the dataadapter needs the proper statements or insert/update/delete from the commandbuilder.
IIRC odbc does not use the primary key for deciding if a row already exists and has to be updated or deleted like oledb does. odbc compares all fields and that generates very long and confusing statements.
|
|
|
|
|
I have,
1) Table A ( id INT )
2) Table B( id INT )
INSERT INTO B (id) VALUES (1)
INSERT INTO B (id) VALUES (2)
INSERT INTO B (id) VALUES (3)
3) DECLARE @TabVar TABLE (
old_id INT,
new_id INT
)
I populate the table variable like this.
INSERT INTO TabVar (old_id, new_id) VALUES (1, 11)
INSERT INTO TabVar (old_id, new_id) VALUES (2, 22)
Now I want to populate table A from table B replacing the B values from the table variable TabVar. i.e. Fetch the record from B, compare it in TabVar.old_id, if a match is found, get the TabVar.new_id and store it in A else simply write the record from B to A.
So the table A should have values 11, 22 & 3.
So, please write me the exact query to get this working.
Thanks,
- PRJ
|
|
|
|
|
How about doing a bit of joining:
INSERT INTO A
SELECT
ISNULL( tv.new_id, b.id )
FROM
B
LEFT JOIN
@TabVar tv
ON B.id = tv.old_idThe Cunning Plan is to match rows from B with rows in @TabVar; the LEFT JOIN gives us NULLs for new_id if there is no matching row in @TabVar.
This technique won't work if you have NULLs in your new_id column, but I generally recommend against storing NULLs in user tables. Few people can get to grips with the three-way truth tables involved in manipulating NULLs.
|
|
|
|
|
Hi guys I'm making a simple program in VB and thi is like one of my first attempts at using VB6 with SQL Server 2000. I've made a simple database in Sql server and a simple front end in VB6. I'm following a tutorial and the problem is that I get this error everytime I try to run my code.
Run time error '-2147467259' (80004005)
Login failed for user [Windows Login]. Reason Not associated with a trusted SQL Server COnnection
The code is the first simple just creating a simple connection object code..
Private Sub Command1_Click()
Dim dbConnection As New ADODB.Connection
dbConnection.ConnectionString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=FC_AIRLINES"
dbConnection.Open UserID,Pww 'UserID and Pww are my USerID and Pww
If dbConnection.State = adStateOpen Then
MsgBox ("CONNECTION OPEN SUCCESSFUL")
Else
MsgBox ("CONNECTION FAILED")
End If
dbConnection.Close
End Sub
I think theres some work tobe done at my SQL server databse end, but I have no idea what to do and how to do it  .
Its driving me nutz , I could use any help on what could be wrong guys
|
|
|
|
|
I don't know VB but I give you some hints. Put the username and password in your connection string , for the correct format of it seach in MSDN. And also add that login to yout database in the sqlserver database.
Mazy
"Improvisation is the touchstone of wit." - Molière
|
|
|
|
|
I think your server is configured in Windows Authentication mode. In this mode, SQL Server uses your Windows user account to work out who you are, ignoring any supplied user name and password.
I would recommend staying in this mode, and telling ADO to connect in this mode. To do this, change your connection string to
"Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=FC_AIRLINES;Integrated Security=SSPI" and eliminate the username and password parameters to dbConnection.Open. SQL Server will need a login configured for every user that will run your program, or for a Windows group that contains all your users. Add logins using Enterprise Manager by expanding the server in the tree, then going to server > Security > Logins. Right-click a blank area and choose New Login. See Help for filling in this dialog.
If you want to connect by supplying a user name and password, rather than using Windows authentication, consider using SQL Server Authentication. You must configure your server for Mixed Mode authentication, which permits both kinds of authentication. To do this, go to Enterprise Manager, right-click your server and choose Properties. On the Security tab, select SQL Server and Windows under Authentication. Click OK to apply the change; you will need to restart the server for it to take effect.
SQL Server authentication is generally considered less secure than Windows authentication, and you should protect your connection string. You should (almost) never connect to your database as sa from an application. Set up a less privileged account (by adding a login, see above).
|
|
|
|
|
How can i execute the query if the statement is an SQL statement that joins 2 tables? The Execute Command don't
work with this kind of query.
_bstr_t bstrQuery("SELECT Dept.*, Student.*
FROM Dept RIGHT JOIN Student ON Dept.DeptID = Student.Dept")
The two tables are named as Dept and Student. Dept has one-to-many relationship with Student, joined these two tables.
_RecordsetPtr pRecordSet;
CSampleDbase2Doc *pDoc;
pDoc = GetDocument();
pRecordSet = pDoc->m_pConnection ->Execute( bstrQuery, &vRecsAffected, adOptionUnspecified);
if (!pRecordSet->GetadoEOF())
{
int i = 0;
_variant_t vDeptID;
_variant_t vDeptName;
_variant_t vStudentID;
while (!pRecordSet->GetadoEOF())
{
vDeptID = pRecordSet->GetCollect(L"DeptID");
vDeptName = pRecordSet->GetCollect(L"Name");
vStudentID = pRecordSet->GetCollect(L"StudentNo");
i++;
pRecordSet->MoveNext();
}
pRecordSet->Close();
}
Since two tables are joined, can i consider it as one actual table?
Hope you can help me with this...
|
|
|
|
|
Generally speaking, a query does not return a table. More often, it returns a rowset*, which we tend to visualise as a table. You can quite happily use a join inside a query, and you'll get back one rowset.
On a stylistic note, I tend to prefer table.column1,table.column2 over table.*, since that way if someone modifies the schema by adding columns, existing code doesn't fall flat on it's backside.
Steve S
*Yes, I know that you can execute statements that don't return rowsets...
|
|
|
|
|
When I try:
INSERT INTO Product (ID, name, date) VALUES (123, NULL, NULL)
It works fine when I use NULL to 'name' and 'date' in the INSERT statement.
However, when I use a variable:
INSERT INTO Product (ID, name, date) VALUES (123, @myName, NULL)
And assign @myName.Value = null to the variable, I get an error message when I execute this statement.
(For example: myInsert.Parameters["@myName"].Value = null;)
My question is: If I use @myName in the INSERT statement, how I can assign NULL to it??
Thank you!!
|
|
|
|
|
You have to use DBNull.Value instead of null, e.g.
myInsert.Parameters["@myName"].Value = DBNull.Value
|
|
|
|
|
I use Win2000 Adv. Server for my server...
I want to configure SMTP for my custom send mail, so how do I do?;)
|
|
|
|
|
By default, IIS has an SMTP service, which can be configured by going to Internet Services Manager snapin and editing the PropertySheet for 'Default SMTP Virtual Server'
IIS will try to deliver the mails using its SMTP service by using its preconfigured DNS. You can override it by asking your IIS to relay all mails to SmartHost etc.
Did this help you?
Deepak Kumar Vasudevan
Personal Web: http://www24.brinkster.com/lavanyadeepak/default.asp
I Blog At: http://deepak.blogdrive.com/
|
|
|
|
|
Can you help me with exactly (will good better if it've a example;))? because I don't know how I do for exactly? 
|
|
|
|
|
|
Hi,
I have the following phenomenon: I have a database with several tables and created a union with a Like-Operator in the WHERE-Statement, like this:
<br />
SELECT Key, Value FROM MyTable WHERE Value Like 'RED*'<br />
UNION SELECT Key, Value FROM MySecondTable WHERE Value Like 'RED*';<br />
Now I'm trying to count the items in this view via ODBC, here are the main parts of my CRecordset-derived class doing it:
<br />
CHelperRecordset::CHelperRecordset(CDatabase* pdb)<br />
: CRecordset(pdb)<br />
{<br />
m_lFetchedCount = 0;<br />
m_nFields = 1;<br />
m_nDefaultType = forwardOnly;<br />
}<br />
<br />
void CHelperRecordset::DoFieldExchange(CFieldExchange* pFX)<br />
{<br />
pFX->SetFieldType(CFieldExchange::outputColumn);<br />
RFX_Long(pFX, _T("ZAEHLER"), m_lFetchedCount);<br />
}<br />
<br />
long CHelperRecordset::GetCount(const CString& strTableName, const CString& strIndexName) <br />
{<br />
CString strSQL = _T("");<br />
strSQL.Format("Select Count(%s) As ZAEHLER From %s", strIndexName, strTableName);<br />
Open(CRecordset::forwardOnly, strSQL);<br />
Close();<br />
return m_lFetchedCount;<br />
}<br />
If I'm executing this on my union I will get a count of 0, although all items are visible when I open the union in MS Access. There seems to be nothing wrong with the CHelperRecordset class, when the Like-operator is replaced by =, everythings works fine:
<br />
SELECT Key, Value FROM MyTable WHERE Value='RED'<br />
UNION SELECT Key, Value FROM MySecondTable WHERE Value='RED';<br />
And if I'm not using a union but a view only everything is fine as well, even with the Like-operator:
<br />
SELECT Key, Value FROM MyTable WHERE Value Like 'RED*';<br />
In these cases the correct count is retrieved.
On the PC of my colleague this problem doesn't occur. He is using WinNT4 SP6, Office 2000 (no SR), ODBC-Driver Version 3.520, Access-ODBC-Driver has the 4.00.5303.
I'm using WindowsXP SP1, Office 2000 (SR1, upgrading to SR3 didn't help), ODBC-Driver Version 3.525, Access-Driver is 4.00.6019.
Does anybody know whether there is some obscure option or setting I have to set somewhere or whether this is a MS Bug or whether I am doing something completely wrong? I could easily send the small test application and database with which I reproduced the problem along if anybody's interested.
As I'm no expert in databases, ODBC, SQL and stuff and our expert has left us I'm completely at loss. I wanted to have a look at some completely different problem of our application and cannot do it, because the application fails to see the database items on my pc in the first place.
Any hint or idea would be welcome. Thank you very much.
mexicanchili
|
|
|
|
|
Finally I found it in the MSDN knowledge base.
There are problems with the Jet engine version and MS Access, as stated in this article.
Like won't work with an asteriks '*': KB-Article
When using '%' instead of '*' my test gives the correct count via ODBC. BUT: when opening the view in Access, it is empty.
*sigh*
mexicanchili
|
|
|
|
|
hi all...
ok, ill get to the nuts and bolts of the problem...
i keep getting a C2259 error ('class' : cannot instantiate abstract class due to following members: )
now the class it is complaining about is one that instantiates IDataParameterCollection and ArrayList... specifically:
public __gc class NewSQLParameterCollection: public ArrayList, public IDataParameterCollection{};<br />
and the function it is complaining about is this one...
bool Contains(String *p)<br />
{<br />
if (this->IndexOf(p) >= 0)<br />
return true;<br />
else<br />
return false;<br />
}<br />
(umm, and before you say that i am missing the NewSQLParameterCollection:: indentifier, i should mention that i am inlining the call within the class header... )
now the problem occurs when in a subsequent class i call the following
function test::sometest()<br />
{<br />
NewSQLParameterCollection *p = new NewSQLParameterCollection(S"Name", Object);<br />
}<br />
it says that it can't find the function (as in bool Contains(...)) above, but as you can see, it's there...
any ideas?
this is for an open source C++.NET MySQL driver i am writing, so any help would be GREATLY appreciated...
cheers and thanks
nik
Nik Vogiatzis
PhD Candidate: University of South Australia
+++++++++++++++++++++++++++
Developing new generation Traffic Micro-simulation Tools for Traffic Engineers
em: nikolaos.vogiatzis@unisa.edu.au
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
