|
I have some sql Update command error.
I get the following error message when i run my project.
" ERROR [42000] [Microsoft] [ODBC Microsoft Access Driver] Syntax error in UPDATE statement "
some of my code is following....
Dim CW As Integer
Dim RCW As Integer
------------------------------------------------------------------------------------------------------------------------
Dim level As String = CStr(ComboBox1.SelectedItem.ToString)
Dim exercise As String = CStr(ComboBox2.SelectedItem.ToString)
Dim FWds As String = CStr(lblFalseWords.Text)
Dim SpanTime As String = CStr(hour )
"UPDATE tblSave SET Level=' " & level & " ',Exercise=' " & exercise & " ',CWords=" & CW & ",FWords=' " & FWds & " ',WPM=" & RCW & ",SpanTime=' " & SpanTime & " ' WHERE tblSave.UserName=' " & CurrentUser & " ' "
Please tell me some suggestion.....
|
|
|
|
|
Well my first suggestion is to read up on SQL injection attacks, to understand why you should never allow any system to use this code, ever. Next, I would point out that CW and RCW are used without you giving them a value. However, I'd say your core issue is simply that you're doing your own bit of SQL injection, that values in the strings you're passing through are what's causing this SQL to break. Did you go into the debugger to look at the final SQL string and see what the final SQL line looks like ? Perhaps if you copied and pasted it into SQL Server, it would explain it even better, but the solution is to use stored procs and pass your parameters in a sane way, not by string mashing
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
In "tblSave" table, if CWords and WPM columns are of VarChar Datatype then you have to alter update query as below:
'UPDATE tblSave SET Level='" & level & "',Exercise='" & exercise & "',CWords='" & CW & "',FWords='" & FWds & "',WPM='" & RCW & "',SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"
And For Level, Excercise, FWords and SpanTime columns ur giving one space after single quote, if the query runs successfully that space will also insert into the database.
|
|
|
|
|
Now i get success by adding [] in Level field.
Now my success code is
"UPDATE tblSave SET [Level]='" & level & "',Exercise='" & exercise & "',CWords=" & CW & ",FWords='" & FWds & "',WPM=' & RCW & ",SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"
Plese tell me how do u think for above metter?
|
|
|
|
|
sounds like "Level" is a keyword and has to be put into [] if used as column.
|
|
|
|
|
how to get row values as columns.
Ismail
Software Engineer
|
|
|
|
|
I believe that's called a 'pivot table'. If you google that, you should find the help you need.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Add the following code in your stored Procedure
DECLARE @V1 VARCHAR(1000) --Increase the size depending on your row count
DECLARE @V2 VARCHAR(500)
DECLARE @V3 VARCHAR(500)
SET @V1 = ''
DECLARE C1 CURSOR FOR
SELECT country_name FROM t_master_country
OPEN C1
FETCH C1 INTO @V3
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH C1 INTO @V2
IF @@FETCH_STATUS <> 0
PRINT ' '
ELSE
SET @V1 = @V1 + ' ' + @V2
END
SET @V1 = @V3 + ' ' + @V1
CLOSE C1
PRINT @V1 -- Return @v1
|
|
|
|
|
This i know but I want to take row as coulum and next coulum values as row values
Ismail
Software Engineer
|
|
|
|
|
i dint get you...can u give an example
|
|
|
|
|
I a C# app that queries against an accessDB having the following tables:
tblItems<br />
int ItemID string ItemName ...<br />
1 Item1 ...<br />
2 Item2 ...<br />
3 Item3 ...<br />
...<br />
<br />
tblEvents<br />
int EventID string EventName<br />
1 Event1<br />
2 Event2<br />
3 Event3<br />
4 Event4<br />
....<br />
<br />
<br />
lnkItemEvents<br />
int ItemID int EventID DateTime TimeStamp ...<br />
1 1 Jan 1 ...<br />
1 2 Jan 3 ...<br />
1 4 Jan 4 ...<br />
1 1 Jan 8 ...<br />
1 2 Jan 13 ...<br />
2 1 Jan 2 ...<br />
2 4 Jan 3 ...<br />
2 3 Jan 8 ...<br />
...<br />
<br />
<br />
What I need is a query to return something that looks like this.
ItemName EventName TimeStamp ... about 10 more fields from a half dozen tables.<br />
Item1 Event2 Jan 13 ...<br />
Item2 Event3 Jan 8 ...<br />
...<br />
Based on a bit of googling I believe that to make it work I'll need to use sub queries. One ran on lnkItemEvents to get the most recent time stamp for each item. And a second using the first as a sub query and adding the ItemName, EventName, etc fields to create the final result. I have the first working:
SELECT MAX(TimeStamp) AS Expr1, ItemID<br />
FROM lnkItemEvents<br />
GROUP BY ItemID
But when I try and add it to the WHERE clause of the second I get a syntax parsing error at the WHERE and a second error "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." If I remove ITemID from the sub query, I only get the single most recent event. Not the most recent event for each item.
To keep it simple while trying to debug, in my truncated main query I haven't added the SQL to use the ID fields to get the corresponding Names, or the other fields of data I need.
<br />
SELECT ItemID, EventID, TimeStamp<br />
FROM lnkItemEvents<br />
WHERE (SELECT MAX(TimeStamp) AS Expr1, ItemID<br />
FROM lnkItemEvents<br />
GROUP BY ItemID)<br />
GROUP BY ItemID, EventID, TimeStamp
If i remove the WHERE clause it does return the selected fields of every row in the table. I do have a otherwise complete, working, version of the main query that gets everything except for the data linked via lnkItemEvents.
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
Try this query
SELECT ItemID, EventID, TimeStamp
FROM lnkItemEvents
INNER JOIN tblItems ON tblItems.ItemID = lnkItemEvents.ItemID
INNER JOIN tblEvents ON tblEvents .EventID = lnkItemEvents.EventID
WHERE TimeStamp = (SELECT MAX(TimeStamp)FROM lnkItemEvents)
I guess this will work
|
|
|
|
|
With a few formatting tweaks (below) to make the DB happy it ran, but it only returns a single row with the single most recent event, not the most recent event for each item.
SELECT tblItems.ItemID, tblEvents.EventID, TimeStamp
FROM ((lnkItemEvents
INNER JOIN tblItems ON tblItems.ItemID = lnkItemEvents.ItemID)
INNER JOIN tblEvents ON tblEvents .EventID = lnkItemEvents.EventID)
WHERE TimeStamp = (SELECT MAX(TimeStamp)FROM lnkItemEvents)
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
Putting the subquery as it's own entity so that I could access it like a table in the main one let me get what I needed working.
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
I am new to C# and I am trying to convert some old VB6 code to the new platform.
I am importing a text file ".txt" and want to put the data into a database.
In VB6 you could create a recordset for the database in ram and store it there while you are using it and then delete it.
I would like to accomplish the same task in C# but I am having some problems.
The code I am using is as follows:
private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();
public frmMainAlarm()
{
InitializeComponent();
rs.Fields.Append ("ID", ADODB.DataTypeEnum.adInteger, 4, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
rs.Fields.Append("Alarm", ADODB.DataTypeEnum.adChar, 255, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
rs.Fields.Append("Message", ADODB.DataTypeEnum.adChar, 255, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
//rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;
//rs.LockType = ADODB.LockTypeEnum.adLockOptimistic;
rs.Open(null, null, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
}
I get an error at the "rs.Fields.Append" statement saying it has the wrong variables or data types.
Any help would be greatly appreciated.
Keith Mabey 
|
|
|
|
|
If you are using the same code in C# then it will not compile you have to write C# equivalent code to this vb6 code
1 - write code to read from file and then
2 - make a SqlConnection object
3 - make a SqlCommand object
4 - through SalCommand object call the ExecuteNonQuery method
|
|
|
|
|
I am tring to create a virtual database in RAM (Memory).
Fill the database table with data from a text file.
Then manipulate the data and rewrite it back out as an XML file.
|
|
|
|
|
|
write code
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
vinodh.s wrote: can you plz give me an example.so tat i can get a claer view.
An example of what? You haven't explained what you want to do.
I suggest you read the forum guidelines on message etiquette. Asking vague questions isn't likely to appeal you to the users of this forum. State clearly what your problem is and what you want to achieve and perhaps someone will help you.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
need an example of creating a table with a column name declared of datetime datatype.......
|
|
|
|
|
Don't delete your messages on a thread. It's rude.
Have you tried to solve the problem yourself? What code have you written?
Documentation for the CREATE TABLE[^] statement is easily available on the web. I suggest you read it and attempt to solve the problem yourself before asking for help again.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
Most frustrating. The format of the website cannot be changed:
I have 5 web pages each with a form. These page forms have content the user can edit & redit.On the top of each page is a menu bar affording the user to toggle back and forth to any of the 5 pages viewing the respective content. Now say the user toggles to page 2, makes changes to the content, via a RadEditor, toggles to page 5, then retoggles to page 2 to see the changes he just made.
Here's the delema: During these edits/redits, the revised content CANNOT be posted as an update to the database. Only AFTER the user is satisfied with all the edits made, does he click button 6 for "Update All My Changes".
I cannot have one page with all the form elements listed via visible/not visible panels. I cannot use session objects, or cookies - there is just too much data. I heard about the cache object / application object but I do not know if it plays a part here. Also note there could be thousands of concurrent users taking this initially identical data content then editing it to each's own unique wants.
I know this may sound more of a .Net question but I thought as DB ppl here, you must have more experience on this need then others.
So in short, how can I have several editable web pages of form elements that allow the user to see the changes they made, even after clicking off and then back to that form without using a database update or items mentioned above. Remember, They will not let me hit the database until aff the edits on the 5 web page forms are complete. Thanks.
Update:
I have reevaluated the entire process.
I've decided to utilize the file stream object and have them edit their entire page.
Now, I have to figure out how to reiterate thru these modified forms to place the data into the db.
modified on Tuesday, February 26, 2008 4:39 PM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
