|
Is there any way (besides triggers) to have an auto updating column in SQL Server? I'm looking for something like the "timestamp" column type from MySQL that automatically updates to the current date/time whenever an update/insert is performed. Is there anything like this type in SQL Server? Thanks Muchly.
- Aaron
|
|
|
|
|
It doesn't update to the current date and time, but SQL Server 2000 does have the rowversion type which is automatically updating. IIRC, the value for a changed row is only guaranteed to be different from the old value, not necessarily greater than the old value.
If you want date/time behaviour, you might want to consider using a trigger:
CREATE TRIGGER tg_timestamp ON MyTable
FOR INSERT, UPDATE
AS
UPDATE MyTable
SET TimeStampField = GETUTCDATE()
WHERE PrimaryKey IN
( SELECT PrimaryKey FROM inserted ) inserted is a pseudo-table (constructed from the transaction log) containing the new data for all the rows that were inserted or modified (there's also a deleted pseudo-table for the old version of modified rows, for an UPDATE trigger). You'll want to ensure that recursive triggers are not enabled, or you'll just end up going recursive.
I suggest using the UTC date and time so you don't get Daylight Savings Time effects.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
If there's no date/time updating field I guess I'll go with a trigger. Much thanks for the reply and the example.
- Aaron
|
|
|
|
|
Hi,
I'm using a little code to insert data in a Database.
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd
mStrSql = "INSERT INTO ZAP_CATEGORIE (CAT_Description, INS_USER, INS_DATTME) VALUES (@Cat_Desc, 'cde', getdate())"
' A SqlCommand object is used to execute the SQL statement.
mCmd.CommandText = mStrSql
With mCmd
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
End With
Try
mCmd.ExecuteNonQuery()
Catch exec As Exception
Return exec.message()
End Try
The first time I call this method, no problem.
The second time I got the following error message in the Excpetion :
The variable name '@Cat_Desc' has already been declared. Variable names must be unique within a query batch or stored procedure."
Can someone tell me where I'm wrong.
Thx
Sybux
|
|
|
|
|
SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
YOU MISSTYPED @ WITH LETTER a it should be @CAT_DESC 
ForgetAboutIt
|
|
|
|
|
You're caching the command object between runs, right? The first time you run through, the line
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Descadds the parameter to the command. When you run it again, the parameter @Cat_Desc is already there, so you get the exception.
There are two ways you can go: separate out setting the value from creating the parameter, and only creating the parameter once, or creating a new command object every time. I'd actually go for the second option, unless you need to run this command inside a tight, performance-critical block of code. In the long run, using less memory tends to perform better than caching lots of objects.
To do this, simply change
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd<pre>to<pre>Dim mCmd As New System.Data.SqlClient.SqlCommand
Secondly, never catch System.Exception. You can end up catching all sorts of critical errors other than those coming from the SQL layer. If you want to catch those, catch SqlException. From a design standpoint, don't catch an exception if all you're going to do is return a return code. Instead, allow the exception to propogate to the caller, and catch it there. It's OK to catch an exception and throw a new one of a different type to simplify the caller's exception code, but only if you can add more meaning (for example, throwing a user-defined BadFormatException if a file operation raised an IOException when you expected more data when reading a file). If you do this, remember to set the InnerException property.
The CLR will propogate some critical exceptions (such as ThreadAbortException or, IIRC, StackOverflowException) even if you do catch them.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thx for your answer but I will a third method becaus my SQLCommand is issue from my Database class so I can't make a new each time.
Here is my solution :
<code>
Dim mParDesc As New SqlParameter("@CAT_Desc", SqlDbType.NVarChar, 250)
With mCmd
.Parameters.Add(mParDesc).Value = Me.aCAT_Desc
End With
mCmd.ExecuteNonQuery()
mCmd.Parameters.Remove(mParDesc)
</code>
I don't know what is ths impact on perf. but I don't need an answer in the last 0.0001 seconds 
Regards
Sybux
|
|
|
|
|
In my home installation of VS.NET 2002, I now cannot create a new project (of any kind, WIn WEb etc.) because I get the error "Automation Sever cannot create object". I can run existing projects and even fill in the broken Solution/Project stub with files created at work. When I do that the projects will run fine.
The "stun" that is left behind consists of the sln and proj files without any other IDE generated files that are appropriate to the type of project selected. Un the simplest case (a Class Library project) there is now default class cs file generated.
This installation ran fine for a long time then broke. I had just finished deleting some projects. Perhaps I deleted something that I shouldn't have?
Any clues?
Gary Hyslop (at home)
|
|
|
|
|
This is Gary at work. Noone seems to have any suggestions. See my problem in Soapbox section. I thin the solution is to change my e-mail address in the Profile section of this site to the one of Garya at home. If Gary at home can edit this message from work then he can be the sole user of the site since he can read his home e-mail when he's not home.
Gary Hyslop (at work)
|
|
|
|
|
I have C# project that connects to a SQL server 2000 database , I want to deploy it with the project to the end user PC. can you help me please? [I failed to do it using Install Shield 9.0]
Mahmoud Hossam
|
|
|
|
|
For my last project I used the SQL Server Database Maintenance functions from http://www.installsite.org/pages/en/isp_db.htm[^]
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
I have no experience in this area, but have something to say anyway
SQL Server 2000 is a licensed software product, each copy of which must be legally licensed. Unless you have licenses from Microsoft for each distributed copy, it would be illegal to distribute it with your software.
MSDE (the stripped down version) is a different matter. That can be legally distributed in many cases. You may find more of what you are looking for if you focus your searches/enquiries on MSDE.
|
|
|
|
|
I'm using the following command to select all the columns from a table
SELECT * FROM table When the command executes it's not returning the columns in the same order they were added to the table. For example if I created the table with column1, column2, column3, column4, they're being returned as column1, column4, column2, column3 (for example). Is this normal? Is the best way around it to simply specify the columns in the order I want them in the query? Thanks. I'm using MSDE.
- Aaron
|
|
|
|
|
monrobot13 wrote:
Is the best way around it to simply specify the columns in the order I want them in the query?
You should always specify the columns you want. Mainly because it means that if the data model changes then any existing queries will still return the data in the form that you are expecting. (Assuming that no columns are deleted / moved to different tables etc.)
IIRC specifying the columns in a select is faster at execution time.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
Sounds like a good idea. Thanks for the information.
- Aaron
|
|
|
|
|
Where do i begin. I am very new to all of this. I have a class using visual studio.net where i have to creat a single record display that shows data one record at a time, on a form. I have to use ADO and SQL. I have the SQL it is:
Sort by Name
SELECT Customer.CustomerId, Customer.LastName, Customer.FirstName
FROM Customer
ORDER BY Customer.LastName, Customer.FirstName;
Non Zero Balance
SELECT Customer.LastName, Customer.FirstName, Customer.Balance
FROM Customer
WHERE (((Customer.Balance)>0))
ORDER BY Customer.LastName, Customer.FirstName;
Select by State
SELECT Customer.CustomerId, Customer.LastName, Customer.FirstName,
Customer.State
FROM Customer
WHERE (((Customer.State)=[@WhatState]))
ORDER BY Customer.LastName, Customer.FirstName DESC;
Sum Balance
SELECT DISTINCTROW Sum(Customer.Balance) AS SumBalance
FROM Customer;
Insert customer
INSERT INTO Customer ( LastName, FirstName, Balance )
VALUES ([@newlast], [@newfirst], [@newbalance
I also have my form code which is
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
Friend WithEvents MenuItem1 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem2 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem3 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem4 As System.Windows.Forms.MenuItem
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()
Me.MainMenu1 = New System.Windows.Forms.MainMenu
Me.MenuItem1 = New System.Windows.Forms.MenuItem
Me.MenuItem2 = New System.Windows.Forms.MenuItem
Me.MenuItem3 = New System.Windows.Forms.MenuItem
Me.MenuItem4 = New System.Windows.Forms.MenuItem
'
'MainMenu1
'
Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1, Me.MenuItem2, Me.MenuItem3, Me.MenuItem4})
'
'MenuItem1
'
Me.MenuItem1.Index = 0
Me.MenuItem1.Text = "Exit"
'
'MenuItem2
'
Me.MenuItem2.Index = 1
Me.MenuItem2.Text = "Customers"
'
'MenuItem3
'
Me.MenuItem3.Index = 2
Me.MenuItem3.Text = "MasterDetail"
'
'MenuItem4
'
Me.MenuItem4.Index = 3
Me.MenuItem4.Text = "StateQuery"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 206)
Me.Menu = Me.MainMenu1
Me.Name = "Form1"
Me.Text = "Form1"
End Sub
#End Region
Private Sub MenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem1.Click
End Sub
Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
End Sub
Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
End Sub
Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click
End Sub
Private Sub Exit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem1.Click
' Exit the Application
Application.Exit()
End Sub
Private Sub MenuItem2_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
Dim myform As New Customers
myform.ShowDialog()
End Sub
Private Sub MenuItem3_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
Dim myForm As New MasterDetail
myForm.ShowDialog()
End Sub
Private Sub MenuItem4_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem34.Click
Dim myForm As New StateQuery
myForm.ShowDialog()
End Sub
End Class
The file i am getting the information for the table to display the records is customer.mdb. From what i gather I think i have to change the button event to be something like me.bindingtxt(customer.mdb,"customer"). Is that right? I won't post my next question until i get a response, this one's long enough!
-ErinSue
|
|
|
|
|
I'm not sure exactly what problem you are having.
Have you connected to your database yet?
Have a read of http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingconnectiontoaccessdatabase.asp[^]
The other articles in the section should give you a clearer idea of how to proceed to bind your dataset to controls.
Alternatively, you could use the DataForm Wizard to generate an example form and study the code to get a better understanding.
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
i'm stuck
im trying to save a text file to sql server(in a 'text' column ); have no problem with unicode text, but with non-unicode strings after saving to the sql server i read garbage from the columns
here's my code
_bstr_t bstr_text = adoStream->ReadText(adoStream->Size);
// dumping bstr_text shows the text here in bstr_text is ok
recSet->Fields->GetItem(item)->Value = bstr_text;
// debugging down to Field20::PutValue shows that correct data is being put in the field
|
|
|
|
|
If your source data is 8-bit character-oriented, you should use a text column and ensure that the client's thread locale is the same as the locale used by the column.
If your source data is 16-bit character-oriented, use an ntext column. You should also use ntext if your client's locale will be different from the server's.
If you're just interested in storing and retrieving a file with no conversions at all, use an image column to treat it as binary. Obviously you can't do full-text search on an image column.
When you assign a char string to a _bstr_t, the conversion to Unicode is performed using the thread's default code page. When the opposite process occurs to store the data in the database, SQL Server uses the configured locale's code page. If the two settings don't match, the conversions may not be a round-trip.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thank you, but i know all of this already.
i think i found the problem: it turns out that whenever i call _bstr_t bstr_text = adoStream->ReadText(adoStream->Size);
no matter if the file is unicode or not, the data is saved to the wide char member of _bstr_t
so some hacking is needed to get correct results with C char* data
|
|
|
|
|
Yes, this is because ADO is an Automation interface designed for access from Visual Basic, and hence uses BSTRs throughout. It isn't really designed for use with C++.
You can access char-based data directly, without intervening conversions to and from Unicode, using the OLE DB interfaces. Visual C++ supplies the OLE DB Consumer Templates, which can simplify the task somewhat.
If you need to get char-oriented data back out of a BSTR, see WideCharToMultiByte.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thank you
WideCharToMultiByte is exactly what i used
|
|
|
|
|
I’m having a problem connecting my application to an MySQL server at an web host, have no problem on my own system.
The host doesn’t seem to know what’s wrong.
I’m using .Net Framework 1.1 and System.Data.ODBC and when connecting to the server get this error.
ERROR [S1000] [TCX][MyODBC]Can't connect to MySQL server on 'localhost' (10061)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr)
I have tried different connections strings, OleDB, SqlClient. But it just doesn’t seem to work.
If you have any ideas or know where I can find some information about this, I would be very happy since I can’t find any.
|
|
|
|
|
You must use System.Data.ODBC. SqlClient is only for Microsoft SQL Server databases.
First, try the MySQL client directly (if you can). This will isolate whether there actually is a MySQL server running.
Error number 10061 looks like a Windows Sockets error code, where it means WSAECONNREFUSED. Typically this means there wasn't a server listening on that port.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I am using System.Data.ODBC.
There is an MySQL server running since I have edit the MySQL database thru phpMyAdmin.
Im not declaring any port in my connectionstring.
Thx for trying.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
