|
I have tested the SQL query using SQL Server Enterprise Manager and it's working fine for me.
My guess is that n.dateentered is of type "smalldatetime" and GetDate actually returns "datetime" instead.
You might want to try one of the following:
1. Use CAST(x as y) e.g. CAST( (case when...end) AS smalldatetime) ->I've tested this with the code and it works
2. Use DateAdd() function instead of getDate()-3
3. Use DatePart to format your date ranges to yyyymmdd format
|
|
|
|
|
code for adding a new record,deleting a record that already exists and modifying an existing record needed in C++
ranjani
|
|
|
|
|
Is there a way to get the actual SQL commands that the OleDbDataAdapter.Update method is passing? I'm having a problem due to the fact that every time I try and run my Update command, I get an exception thrown by the db (access) claiming that there is a syntax error in my SQL command. Trouble is I can't seem to figure out how to get the actual text of the command (after the adapter has filled in the parameters). I'm pretty confident that the command that I set as the Update command is okay, so I want to see if the problem is with how the adapter has decided to fill in the parameters?
I tried hooking the RowUpdating event, but I still only get the command text without the parameters inserted?
|
|
|
|
|
The Update Command is an OleDBCommand. You can get the property CommandText from it...;)
Free your mind...
|
|
|
|
|
But the CommandText only has the command before the parameters have been filled in. I need the actual command that is sent to the database when I call the Update method of my OleDbDataAdapter. Is there a way to get this?
|
|
|
|
|
Okay, I tracked down the problem by systematically removing fields from my Update statement. The problem is caused by a date field. I have something like this (but with a lot more fields):
<br />
SQLUpdate = "UPDATE myTable SET Field1 = @Field1, Date = @Date, WHERE Key = @Key";<br />
Then I have this:
<br />
OleDbCommand myCmd = new OleDbCommand(SQLUpdate,myConnection);<br />
myCmd.Parameters.Add("@Field1", OleDbType.VarWChar, 20, "Field1");<br />
myCmd.Parameters.Add("@Date", OleDbType.Date, 0, "Date");<br />
<br />
myApt.UpdateCommand = myCmd;<br />
What's the right way to handle the date field in an Access database. Even the command builder seems to fail at this!! BTW, the Date field is bound to a DatePicker control on my form, perhaps something is getting lost in the translation?
|
|
|
|
|
I've written a program that uses MS DataGrid Control, but i've got problems with running it on computers without MS Visual Studio. I have registered msdatgrd.ocx, but when i try to run my app i get the error message "cannot initialise data bindings". What else should i install?
|
|
|
|
|
You need to make sure the target machine has COMCAT.DLL, MSSTDFMT.DLL and MSDATGRD.OCX, as well as any necessary database components.
Steve S
|
|
|
|
|
Does anyone knows how to extract the value of a query using FOR XML ??
All I want to do is assign the result value into a variable.
Thanks
Free your mind...
|
|
|
|
|
It basically comes out as a set of string fields.
If using classic ADO, use a Recordset to capture it. If using ADO.NET, use a SqlDataReader.
|
|
|
|
|
I know it works fine in the middle tier code. I just want to leave the result in the data tier (SQL Server). What I'm trying to do is a kind of audit trail, so when users update a record, I can take a snapshot of it (FOR XML RAW) and write it to another table.
Free your mind...
|
|
|
|
|
Hello all,
A quick question, I’m working on designing a SQL (7.0) database and for 37 of the tables I have 2 fields that are common between them and I’m using these 2 fields as the primary key for the 37 tables and also as the Foreign key to define the relations between these tables.
For example: <br />
Project_ID1: part of the Primary Key<br />
Project_ID2: part of the Primary key<br />
Both of them together form the primary key for the 37 tables because they are the only unique fields within each table. And when I create a relation between 2 of the tables I use these same 2 fields as the foreign keys
So, I would just like to get your opinion on if that is a correct design or not, I had some people telling me that this is a poor design?
Thanks in advance
|
|
|
|
|
I didn't really understand right.
Questions:
1. These 37 Tables do they all have the same Fields or what is different between them.
2. Do you store different Projects in different Tables?
3. Just show me the first 4 Fields of two Tables that I can understand.
4. What is the relation between the Tables?
Database Design is more difficult than many people think. A good structure may help the developers.
Please tell me more.
Stefan
|
|
|
|
|
confusedconfused:Can anybody suggest some online tutorials for ADO.Net? I've gone through several but they all tell you how to connect to a database, download the info and then Console.WriteLine() the results.
but i want some more realistic..In real world application we need to work with the results in datasets. Getting into the detail of evaluating data within the tables/datasets to determine what changes have been made?
Taking a subset of one of the tables and creating a new table and returning only that.
Just wondering if anybody can suggest some online tutorials.
phani.
|
|
|
|
|
This[^] may be helpful.
"Some people are like Slinkies... not really good for anything, but you still can't help but smile when you see one tumble down the stairs."
|
|
|
|
|
Hi,
I need to generate menus at runtime for a sample Windows application. I'm using vs.net winforms with c#.
Every user in the application does not have access to all menu options, so the menu items being displayed is as per user login.
I need to generate the form code at run-time. Any info you can provide OR links about this particular topic will be very helpful and appreciated with a zillion thanx...
It seems i need to following at runtime...
1) Retrieve data from the sql database about the menu options available to the current user
2) Declare each Menu item
private System.Windows.Forms.MainMenu mainMenu1;
private System.Windows.Forms.MenuItem menuItem1;
...
2) Initialize each menu item
this.mainMenu1 = new System.Windows.Forms.MainMenu();
this.menuItem1 = new System.Windows.Forms.MenuItem();
...
3) Declare Menu Collections - add list of menuitems
this.mainMenu1.MenuItems.AddRange(new
System.Windows.Forms.MenuItem[] {
this.menuItem1,
this.menuItem5});
...
4) Define each Menu Item
i.e. specify Index, Shortcut Key, Text (with hotkey) and declare eventhandler for each Menu item
this.menuItem2.Index = 0;
this.menuItem2.Shortcut=
System.Windows.Forms.Shortcut.CtrlN;
this.menuItem2.Text = "&New";
this.menuItem12.Click += new System.EventHandler(this.menuItem12_Click);
5) Assign the menu to Form as the current menu
this.Menu= mainMenu1;
Hope u guys will be keen to help. Thanx in advance.
Sunny.
|
|
|
|
|
What I usually do, is build the whole menu in the application. Then, when the user login in the application, I get an arraylist with all the menu options. After that, I execute a loop on the array list, and turn of all the options the user doesn't have access.
May be that could help you
Free your mind...
|
|
|
|
|
Hi Guillermo,
Thanx for your post. Your method seems to make much, much more sense than what i was trying to do. I just checked out the help files - i can either disable a menuitem or hide it completely.
>> menuitem3.enabled=false; // to disable a menuitem
>> menuitem3.Visible=false; // to hide a menuitem
I'll try out your method. Hope u can guide me, if i get stuck somewhere. Thanx a lot again.
Sunny
|
|
|
|
|
Concurrency violation: the UpdateCommand affected 0 records.
I get this error when I try to call Update() on an SqlDataAdapter using a DataSet which has been changed by another class. I've checked that the UpdateCommand.CommandText is properly set, all the parameters have the right SourceColumns, and that the DataSet has actually been changed. So what else could it be?
I'd post some code up here but since all those things are working OK I don't know where the error would be. Has anyone else solved this problem before? It's really baffling me.
|
|
|
|
|
I believe it means that someone has modified the underlying data from how it was before the data set was modified.
The DataSet object remembers what your original selection was. It then compares the rows in the data source to that original version, and if they've changed, it throws an exception rather than perform the update. This is to allow you to resolve the conflict between the change you're trying to make and the change that has already been made.
I seem to recall that there was an article in MSDN Magazine or on MSDN Online recently, but I can't seem to find it.
|
|
|
|
|
Hey, I think I found the article you mean. It didn't really apply to me since I'm not using the command builder, but it did point me in the right direction - I had to call GetChanges or else it wouldn't let me update at all (even though I know for a fact that the records couldn't have been changed). Still calling GetChanges (or refilling the dataset, my original tactic but an expensive one) makes the exception go away.
|
|
|
|
|
This may or may not relate to you situation, but I got the same exception when I had the wrong number of parameters added to my Update command using the OleDbDataAdapter. If you remove fields from the CommandText, but don't remove the related parameter from the commands ParameterCollection, it'll throw this exception for some strange reason. I would have thought it would just ignore the extra parameters, but it doesn't.
|
|
|
|
|
Thanks for your reply. I solved the problem - I just had to call GetChanges and get a new set before it would let me update. There are certainly a lot of quirks in this system! I'll keep your tip in mind if I get that error again - I can't imagine what you must have gone through to work it out.
|
|
|
|
|
I have a batch file where I am put a file into ftp site. The problem is that my file name are changing every day. How do I supply this file into batch file?
Or is any other way to do this?
open ftp.site
user test
pass test
lcd C:\
cd ..
put test.txt
bye
|
|
|
|
|
There are a couple of possible solutions:
1. Create a simple program that creates a fresh FTP script each day. Example BAT file:
@echo off<br />
echo open ftp.site > myftpscript.txt<br />
echo user test >> myftpscript.txt<br />
echo etc >> myftpscript.txt<br />
echo put %%1 >> myftpscript.txt<br />
echo bye >> myftpscript.txt<br />
ftp -n -s:myftpscript.txt
2. Use "mput *.txt" instead of "put test.txt" to transfer all matching files from your source directory.
Hope this helps.
Andy
PS: This was probably the wrong forum to post this question to.
|
|
|
|