|
I don't know about Access, but SQL Server 2000 lets you specify the collation for comparisons. For example, for a general case-sensitive / accent-sensitive search, try:
SELECT *
FROM TableName
WHERE Description = 'A Description'
COLLATE SQL_Latin1_General_CP1_CS_AS To get a list of available collations, execute:
SELECT * FROM ::fn_helpcollations()
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Hello,
I have a problem that i cannot solve.
The problem shows up in XP, the code works fine in 98SE
Please if you know the answer help me
The code:
conITEMS.Open "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDb=" & "D:\work\old"
rsITEMS.CursorLocation = adUseClient
rsITEMS.Open "SELECT * FROM STOCK", conITEMS, adOpenStatic, adLockPessimistic
rsITEMS.MoveFirst
rsITEMS.Find ("number = '2004'")
This is where it fails with error = Run-time error '2147467259 (80004005)'
"data provider or other service returned an E_FAIL status"
Thank you in advance
Ilya
|
|
|
|
|
Hi
It is probably because VFP Drivers are not included in MDAC 2.6 and above.
You can download this drivers from Microsoft Visual FoxPro page. Here is the link
http://msdn.microsoft.com/vfoxpro/downloads/addons/odbc.asp
Greeting
Marek
|
|
|
|
|
Anyone know of a tool or interactive course that does something like SQLCourse2.com only for Sybase X?
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Hi! I'm in a work with a webapplication...
I made my own Sqlwrapper class to handle all of my database access querys.(To make sql connection closeing simplier. Useing the same connection/thread etc.)
I close the connection at the end of the usage, but at the performance monitor of SQL server userconnections statistics seems to grow!
Maybe the datagrid.?
May datagrid reopen my connections, or keeps it alive, while i'm visiting the page?
i close it really.
i tried to close it, referenced itt null, called the GC.Collect().
not helped.
The unused connections are dieing later. at the SQL server enterprisemanager there are .Net SqlClient DataProvivider sleeping threads, awaiting for command (Wait type: not waiting!). I called all of the close connections! There isn't deadlocked state. Some cache??
Can you help me?
klon
|
|
|
|
|
Hi!
I have a database project using MFC and ODBC. When we create a ODBC project, we obtain a CMyRecordView class derived from CRecordView. This class use a dialog template to represent data.
I want to use a CListCtrl to represent my data.
So in my class CMyRecordView.h I have declared
CListCtrl m_wndListCtrl;
In my CMyRecordView.cpp :
DDX_Control(pDX, IDC_LISTCTRL_VIEWDATA, m_wndListCtrl);
and :
void CMyRecordView::OnInitialUpdate()
{
m_wndListCtrl.InsertColumn(0,_T("Item"),LVCFMT_LEFT,100);
m_wndListCtrl.InsertColumn(1, _T("Value"),LVCFMT_LEFT, 100);
m_wndListCtrl.InsertColumn(2, _T("Time"), LVCFMT_LEFT, 100);
m_pSet = &GetDocument()->m_DataBaseManagerSet;
CRecordView::OnInitialUpdate();
}
In my dialog, my control style is set to VS_REPORT
The problem is that I dont see my columns.
I see a grey bar, but I dont see any text and I dont see any line break.
thanks for helping...
Everything's beautiful if you look at it long enough...
|
|
|
|
|
Hi All,
I'm trying to imlpement a helper .dll for managing some data on an Access DB using ADO. I found some sample code, that is working (sort of), but not too reliably. What I'm trying to figure out how to do is to populate some tables with new records, say 500+ at a time.
Basically, I open a recordset with the name of the table, then for each row, call AddNew, and set the values of the fields. After I'm done with all the rows, I call the Update method of the recordSet. Thing is, this works OK only if I do it kind of slow. I've got a Sleep(100) between each row, then I get all the data into my database. If I comment this out, I get nuthin'.
What's the deal? How would you do something like this:
struct Data
{
std::string Name;
double dWeight;
}
const Data arrData[] =
{
"Bob", 175.00,
"Tina", 130.00,
// lots more rows
};
Move all of this into an Access DB all at once.
Here's my actual code:
// To append a single row to the DB
HRESULT CHTDMBridge::AppendRow(/*const _bstr_t& bstrTable, */ValueMap& values)
{
// Must be connected
HRESULT hr;
if(m_pConnection == NULL) return E_FAIL;
if(m_pRecordSet == NULL) return E_FAIL;
// Going too fast for ACCESS??
// Sleep(100);
try
{
// Create a new record
hr = m_pRecordSet->AddNew();
if(FAILED(hr)) printf("\nOops!");
// For each field
ADO::FieldPtr pField;
_variant_t vField;
_variant_t vValue;
ValueMapIterator it;
for(it = values.begin(); it != values.end(); it++)
{
try
{
vField = (*it).first;
vValue = (*it).second;
// Set the value of the indicated fiels (it better be there)
m_pRecordSet->Fields->GetItem(vField)->Value = vValue;
}
catch(...)
{
printf("\nError updating fields in new record");
PrintProviderErrors(m_pConnection);
}
}
}
catch(_com_error e)
{
printf("\nError: %s, Desc: %s", e.ErrorMessage(), e.Description());
PrintProviderErrors(m_pConnection);
}
catch(...)
{
}
return S_OK;
}
// To append a lot of rows:
template<class _recordtype="">
void AppendRows(const char* szTableName, std::vector<_RecordType>& vct)
{
// Open the table
OpenTable(szTableName);
// For each record
std::vector<_RecordType>::iterator it;
ValueMap values;
_RecordType record;
for(it = vct.begin(); it != vct.end(); it++)
{
values = *it;
AppendRow(values);
}
// Update the database
HRESULT hr = m_pRecordSet->Update();
if(FAILED(hr)) printf("\nOops!");
m_pRecordSet->Close();
}
// To open the recordset to a given table
void OpenTable(const std::string& szTableName)
{
try
{
// Open the table
_variant_t vConnection = m_pConnection.GetInterfacePtr();
_bstr_t bstrTable = szTableName.c_str();
_variant_t vSource = bstrTable;
m_pRecordSet->Open(vSource, vConnection, ADO::adOpenForwardOnly, ADO::adLockPessimistic, ADO::adCmdTableDirect);
}
catch(_com_error e)
{
printf("\nCHTDMBridge::OpenTable(%s), Error: %s, Desc: %s", szTableName.c_str(), e.ErrorMessage(), e.Description());
PrintProviderErrors(m_pConnection);
}
catch(...)
{
}
}
Any help is appreciated.
Aaron
|
|
|
|
|
Hey everyone,
I am using an SQL Server 2000 database. It holds a table whose records have to be updated. I thought I had the query right, but it doesn't do anything. I am concatenating the sql string like this (I will be doing more validation later):
string sql = "UPDATE MYdotNET_addressbook" +
" SET [firstName] = '" + firstname.Text + "'," +
" [lastName] = '" + lastname.Text + "'," +
" [emailPrim] = '" + emailprim.Text + "'," +
" [emailSec] = '" + emailsec.Text + "'," +
" [company] = '" + company.Text + "'," +
" [jobTitle] = '" + jobtitle.Text + "'," +
" [businessPhone] = '" + bphone.Text + "'," +
" [homePhone] = '" + hphone.Text + "'," +
" [mobilePhone] = '" + mobilephone.Text + "'," +
" [businessFax] = '" + bfax.Text + "'," +
" [homeFax] = '" + hfax.Text + "'," +
" [website] = '" + website.Text + "'," +
" [msnAddy] = '" + msnaddy.Text + "'," +
" [birthday] = convert(datetime,'" + birthday.Text + "')," +
" [addressHomeSt] = '" + hst.Text + "'," +
" [addressHomeCity] = '" + hcity.Text + "'," +
" [addressHomeState] = '" + hstate.Text + "'," +
" [addressHomeZip] = convert(int,'" + hzip.Text + "')," +
" [addressBizSt] = '" + bstreet.Text + "'," +
" [addressBizCity] = '" + bcity.Text + "'," +
" [addressBizState] = '" + bstate.Text + "'," +
" [addressBizZip] = convert(int,'" + bzip.Text + "')," +
" [nickname] = '" + nickname.Text + "'" +
"WHERE (id = '" + Convert.ToInt32(Request.Params["id"]) + "')";
UpdateData(sql);
The UpdateData method looks like this:
private void UpdateData(string sql)
{
try
{
DataSource db = new DataSource();
SqlConnection conn = new SqlConnection(db.DbString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Response.Redirect("addressBook.aspx");
}
catch (Exception e)
{
Response.Write(e.Message + "<hr/>" + e.Source + "<hr/>" + e.StackTrace);
}
}
As it stands now, I don't get any errors, it just doesn't do the update. I can't figure out why.
Is my sql string wrong?
I know this isn't the asp.net forum, but I thought it would be more relevant here.
$TeVe McLeNiThAn
confused:
I know this isn't the asp.net forum, but I thought it would be more relevant here.
$TeVe McLeNiThAn
|
|
|
|
|
Steve McLenithan wrote:
"WHERE (id = '" + Convert.ToInt32(Request.Params["id"]) + "')";
Since this is an integer, don't use quotations, the string above should read:
"WHERE (id = " + Convert.ToInt32(Request.Params["id"]) + ")";
Note that I removed the single quotation marks (')
Philip Patrick
Web-site: www.stpworks.com
"Two beer or not two beer?" Shakesbeer
|
|
|
|
|
Thanks.
Ok I did that and now it does the method without errors, but when I check the database it has not been updated. It should be setting the new values but it doesn't.
Any ideas?
$TeVe McLeNiThAn
|
|
|
|
|
Make sure that the ID passed (in Request.Params["id"] ) is exists in the database. When running such query without errors but no changes, usually means that the WHERE clause didn't match to any records in the database.
Philip Patrick
Web-site: www.stpworks.com
"Two beer or not two beer?" Shakesbeer
|
|
|
|
|
(This reply is not related to your problem - but related to performance and security)
Performance:
You're doing about 75 malloc and memcpy operations by the way you are building that string. Every time you append something to the string (by using the + operator) you are forcing the application to malloc memory (which does a memset too), copy memory, delete the old memory.
Try using the stringbuilder class instead. Using the stringbuilder class greatly reduces the number of times you do expensive malloc and memcpy operations.
Security:
Using statements such as
<br />
"[firstName] = '" + firstname.Text + "'",<br />
leaves you vulnerable to SQL injection attacks. Essentially, any firstname that contains an apostrophe will cause your SQL to be malformed. If you're unlucky, this malformed SQL can do damage to your data.
To avoid this, always qualify your text fields by doubling your apostrophes as follows:
<br />
"[firstName] = '" + firstname.Text.Replace("'", "''") + "'",<br />
This not meant as a personal attack at you or your methodology - it's just meant as a friendly reminder to please take into consideration performance and security.
|
|
|
|
|
Reinout Hillmann wrote:
firstname.Text.Replace("'", "''")
Yes actually, I have done that, but took it out when posting to reduce code clutter.
I have actually changed the method in which I am trying to do the update. The following is the method I am using now:
private void update_Click(object sender, System.EventArgs e)
{
int prim = Convert.ToInt32(Request.Params["id"]);
string sql = "SELECT [id],[firstName],[lastName],[emailPrim],[emailSec],[company],[jobTitle],[businessPhone]," +
" [homePhone],[mobilePhone],[businessFax],[homeFax],[website],[msnAddy],[birthday],[addressHomeSt]," +
" [addressHomeCity],[addressHomeState],[addressHomeZip],[addressBizSt],[addressBizCity],[addressBizState]," +
" [addressBizZip],[nickname] FROM [MYdotNET_addressbook] WHERE [id] = " + prim;
DataSource db = new DataSource();
SqlConnection conn = new SqlConnection(db.DbString);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql,conn);
DataSet ds = new DataSet("addressBook");
da.FillSchema(ds,SchemaType.Source,"addressBook");
da.Fill(ds,"addressBook");
DataTable addressBook = ds.Tables["addressBook"];
DataRow drCurr = addressBook.NewRow();
drCurr = addressBook.Rows[0];
drCurr.BeginEdit();
drCurr["firstName"] = firstname.Text;
drCurr["lastName"] = lastname.Text;
drCurr["emailPrim"] = emailprim.Text;
drCurr["emailSec"] = emailsec.Text;
drCurr["company"] = company.Text;
drCurr["jobTitle"] = jobtitle.Text;
drCurr["businessPhone"] = bphone.Text;
drCurr["homePhone"] = hphone.Text;
drCurr["mobilePhone"] = mobilephone.Text;
drCurr["businessFax"] = bfax.Text;
drCurr["homeFax"] = hfax.Text;
drCurr["website"] = website.Text;
drCurr["msnAddy"] = msnaddy.Text;
drCurr["birthday"] = birthday.Text;
drCurr["addressHomeSt"] = hst.Text;
drCurr["addressHomeCity"] = hcity.Text;
drCurr["addressHomeState"] = hstate.Text;
drCurr["addressHomeZip"] = hzip.Text;
drCurr["addressBizSt"] = bstreet.Text;
drCurr["addressBizCity"] = bcity.Text;
drCurr["addressBizState"] = bstate.Text;
drCurr["addressBizZip"] = bzip.Text;
drCurr["nickname"] = nickname.Text;
drCurr.EndEdit();
SqlCommandBuilder sqlCB = new SqlCommandBuilder(da);
da.UpdateCommand = sqlCB.GetUpdateCommand();
da.Update(ds,"addressBook");
Response.Write("changes Successfull");
}
But it still appears to work, but doesn't update any data. I am not doing any data validation or security measure yet, I just want to get this working first.
I have checked the the ID in the query string is in the database. I have even tried directly embeding the id number right in the sql query above.
Any ideas on why this method isn't working?
$TeVe McLeNiThAn
|
|
|
|
|
I haven't done it this way before but could you be missing the call to AcceptChanges() on the dataset to commit the changes?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataSetClassAcceptChangesTopic.asp
|
|
|
|
|
|
I have almost figured it out. Turns out it isn't the way I am inserting it that causes it to not update, for some reason it is not pulling the newly entered values in the textboxes into the code-behind to insert.
I have a function that is called on Page_Load that sets existing values to the text boxes.
But when I hit the save linkButton it uses these values from the initial Page_Load function and NOT the ones the user has entered after that.
What is going on here? I tried turning ViewState off, but didn't work.
Any ideas?
Steve
McLenithan
Is Bert Evil?
|
|
|
|
|
|
Try using parameters instead of building the string dynamically:
string sql = @"UPDATE
MYdotNET_addressbook
SET
[firstName] = @FirstName,
[lastName] = @LastName,
...
WHERE
id = @ID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@ID", Convert.ToInt32(Request.Params["id"]);
cmd.Parameters.Add("@FirstName", firstname.Text);
cmd.Parameters.Add("@LastName", lastname.Text);
...
cmd.ExecuteNonQuery(); Also, make sure you only initialize the controls on the page is IsPostBack is false.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
|
I've got an MDB file with about 300 queries.
Is there any way to tell which of these queries is accessing a certain table. I don't fancy having to open each on to have a look.
There must be a search of some description.
thanks,
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
I have never tried this but in Access 2000 there are two tables where you can probably find the information you are looking for. The MSysQueries table appears to contain the tables used in individual queries. The table names have an Attribute of 5. This table should be joind on the MSQueries table by ObjectID. Something like:
<br />
Select b.Name as QueryName, a.Name1 as TableName<br />
From MSysQueries a<br />
INNER JOIN MSysObjects<br />
ON a.ObjectID = b.ID<br />
WHERE a.Attribute = 5<br />
Hope this helps.
Jeremy Oldham
|
|
|
|
|
Thanks for that.
I've found an alternative solution which is to use the Access Documentor on the Queries. Export the results into a text file and then do a good old Find in Notepad.
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
Hi, all:
I have a master-detail relationship in my MS Access DB. In my WinForm application, I also created same Master-Detail relationship in my dataset. When I delete a row in my datagrid, the entries in the detail table is also deleted in the dataset due to the relationship. After an user finishs editing the data, I call daMaster.Update and then daDetail.Update. I got the DBConcurrencyException when trying to update the detail table. I think the reason is as following: when the master table is updated, it deletes the row marked with "Deleted". Since the relationship is setup in the MS Access DB, it deletes the entries in the detail table of Access DB. When the second Update is called, the record is gone and exception is thrown.
How do I fix this problem? I can not change the order of update, otherwise it will break the new entries ( must exist in the Master table first ). I guess that if I take all the relationship out in the MS Access DB, it will work fine, but I think that probably is not good thing to do.
Please help. Thanks in advance.
Dion
|
|
|
|
|
I'm currently using ADO/Access with ATL and msjet sometimes crashes on some systems. I can't reproduce the damn bug and I'm starting to consider an alternative to Access, like XML or any other solution, where I could provide my own dll and make sure every user gets the current one.
Or is there a distributable dll available for ADO/Access?
Thanks!
---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com
|
|
|
|
|
anyone?
---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com
|
|
|
|
|