|
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
|
|
|
|
|
|
Hey all, we've got a monster proc that does things like the following
-- create tmp table
-- for all fields in permanent table
-- for all parties involved
-- select somebig calculation
-- update tmp table
-- next party
-- update permanent table w/ tmp value
-- next field
It takes a little over 3000 lines of TSQL to get the whole thing done. What really sucks is that I need to call this from a web page (*.aspx) and allow our client to view the results of this procedure.
Eventually this will be a moot issue because it will run as a nightly job in Sql Server but while they test that it works I need to make sure that it runs smoothly from an aspx page.
Here are my questions:
1. What is the maximum timeout I can apply to both the SqlCommand and SqlConnection objects?
2. Can I run this in a separate process and do a callback to the aspx page to display results? Airline websites tend to do this quite a bit when you're looking for a flight. I'm thinking of using an asynchronous delegate but I'd have to guess how long it would take before redirecting the user to the results...
3. Any ideas on how I can get this to run smoothly and not worry about timeouts?
Thanks much,
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
I know this is not what you have asked but:
Have you tried removing the need for a cursor? Cursors are "very" rarely needed. It has been my experience that changing cursors to properly indexed set operations removes any significant time-out issues.
|
|
|
|
|
I know that the TOP operator can't be within the where clause, but is there a way to achieve this behavior in a single select call? For example, if I want to select all books where publisher = 3 or 5; but I want to limit the results to only getting the top 5 for publisher 3 and the top 10 for publisher 5. Is this achievable on a single select call?
Something along the lines of:
select * from books
where (publisher = 3 and top 5) or (publisher = 5 and top 10)
Thanks in advance if anyone knows.
Ron Ward
|
|
|
|
|
You could use a union...
SELECT TOP 5 FROM books WHERE publisher = 3
UNION
SELECT TOP 5 FROM books WHERE publisher = 5
|
|
|
|
|
That's a really good idea. Thanks for the quick response.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
