|
An INSERT statement can only target one table.
It is possible to target an INSERT statement to a view in SQL Server if you're prepared to create an INSTEAD OF trigger on the view, but this trigger will have to be coded to perform an INSERT into each table separately.
The general way of handling this is to use the SCOPE_IDENTITY() function to discover the identity value of the last row inserted.
You could consider creating a stored procedure:
CREATE PROC uspInsertWebContentLocalized
(
@contentName varchar(n),
@contentPage varchar(n),
@contentCulture varchar(n),
@contentHtml varchar(n)
)
AS
INSERT INTO web_Content ( ContentName, ContentPage )
VALUES ( @contentName, @contentPage )
INSERT INTO web_ContentLocalized
( ContentId, ContentCulture, ContentHtml )
VALUES
( SCOPE_IDENTITY(), @contentCulture, @contentHtml )
|
|
|
|
|
Hi,
I want to search a DataSet (dataset contains several tables) for a specific value. (Writing a login method that shall verify if the user exists and has the correct password)
I am bit confused about; DataSet, BindingSource, DataTable etc.
thanx in advance
Regards
/Christoffer
|
|
|
|
|
What specifically are you asking?
You seem to know what a DataSet is...It is a collection of DataTables.
Or are you asking about connecting to a Data Source like an access database or something? In that case you would need to research command objects, connection objects, and data adapters. I'm sure you can find a bunch of articles about connecting to a datasource here at codeproject.
|
|
|
|
|
Hi,
Thanx for your reply.
What I am a bit confused about is once you have "imported" data into the DataSet how do you search for a value in the dataset? Is there a common/standard way to do this? I think of a DataSet as an interface to the datastorage and therefor expect methods like getDataByUniqueKey(), searchForData(DataTable table) etc. I now understand that this is not the case. So should I write an interface to the DataSet? Maybe the problem is that I do not fully understand the relations between TableAdapter, BindingSource, DataSet?!
:-/
Hope this clarify the issue/question?!
cheers
/Christoffer
|
|
|
|
|
I think you've probably had trouble finding information on this because you don't really search a DataSet...You would do the search on a DataTable. There are a number of ways to perform a search on a DataTable object, I'll tell you about one. First of all, since a DataSet is a collection of DataTable objects, you can reference a table IN a DataSet like this:
dsMyDataSet.Tables("myTableName")
or, if for example you knew the table you wanted was the first table inserted into the DataSet:
dsMyDataSet.Tables(0)
So one way to search the DataTable requires that you first have a primary key constraint on the table. If you got the data for this DataSet from a data base by using a DataAdapter and doing a .Fill the constraints are not necessarily carried over into the DataSet. You may have to do something like this:
With dsMyDataSet.Tables("dtMyTable")<br />
.Constraints.Add("pk", .Columns("PrimaryKeyColumnName"), True)<br />
End With
The "pk" parm gives a name to the new contraint for referencing it later, the .Columns("PrimaryKeyColumnName") parm specifies which column in the DataTable is the primary or unique key, and the True parm specifies that this is a primary key. This works when just one column makes up your primary key, if you have more than one column as a primary key the .Columns("PrimaryKeyColumnName") parm should be replaced by an array of DataColumns from the DataTable that compose the Primary Key.
Once you are sure that your DataTable has a primary key constraint you can search for a row like this:
Dim rowFound As DataRow<br />
rowFound = dt.Rows.Find("myKeyValue")
or if your primary key is a number:
Dim rowFound As DataRow<br />
rowFound = dt.Rows.Find(1003)
I hope this helps.
|
|
|
|
|
Hi again,
That helped!
Thank you very much for taking your time and explain the solution of the problem of mine.
cheers
/Christoffer
|
|
|
|
|
I m trying to execute the following store procedure
create Procedure sp_sys_getMax
@FieldId int,
@tablename varchar(255)
as
select Max(@FieldId) from @tablename
the following error is occuring:
Must declare the table variable "@tablename".
i want one store proc for all tables. plz help me in this.
|
|
|
|
|
create Procedure sp_sys_getMax<br />
@FieldId int,<br />
@tablename varchar(255)<br />
<br />
as<br />
<br />
select Max(@FieldId) from tablename
|
|
|
|
|
You can't pass table names as parameters. What you want to do will not work.
|
|
|
|
|
I'm not sure what you are passing as your first parameter, but if you make the following procedure and pass in the Field Name and Table Name, you should get what you're after.
create Procedure sp_sys_getMax
@FieldName varchar(50),
@tablename varchar(255)
as
declare @sql varchar(300)
set @sql = 'select Max(' + @FieldName + ') from ' + @tablename
exec(@sql)
GO
Be sure to read this article: SQL Injection Attacks and Some Tips on How to Prevent Them[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hello,
In my application i am Encrypting the mail ID in Dotnet using RijndaelManaged class and ICryptoTransform and storing it in SQL 2000 table.
I want to decrypt the same data(i.e Encrypted data in dotnet) in SQL 2000.
Is it possible to do to this. If so Can anybody suggest me on this.
With regards
Prashanth B.V
|
|
|
|
|
if i have three condition like a=b ,c=d ,y=z?
How i write in SQL code ?
if i write
SELECT
FROM
WHERE a=b AND c=d AND y=z
?
is it correct?
thanks
Best regards,
Chee ken
|
|
|
|
|
i.e
select * from tablename where column1= 'condition1' and column2= 'condition2' and column3= 'condition3'
|
|
|
|
|
Say for example you have two companys using the same database...but each table in the database has a company id so you can keep track of which company ownes the data...now the question is, I need to insert a record and return the id for that record, but IDENT_CURRENT is going to give me the current ID for that table...not respective of which company inserted the ID...so what is the best way of getting the correct id? Here is the wraper class so far...I dont mind if you re-use it in your projects, but help if you can please 
Thanks in advance guys(and girls  )
Public NotInheritable Class winSQLite
#Region " Public Types "
Public NotInheritable Class UpdateResultParam
#Region " Variables "
Private m_IDs As New ArrayList
Private m_NumAdded As Integer
Private m_NumDeleted As Integer
Private m_NumUpdated As Integer
Private m_Failed As Boolean
#End Region
#Region " Constructors "
Public Sub New()
m_NumAdded = 0
m_NumDeleted = 0
m_NumUpdated = 0
m_Failed = False
End Sub
Public Sub New(ByVal ids As ArrayList, ByVal numAdded As Integer, ByVal numDeleted As Integer, ByVal numUpdated As Integer, ByVal failed As Boolean)
m_IDs = ids
m_NumAdded = numAdded
m_NumDeleted = numDeleted
m_NumUpdated = numUpdated
m_Failed = failed
End Sub
Public Sub New(ByVal id As Integer, ByVal numAdded As Integer, ByVal numDeleted As Integer, ByVal numUpdated As Integer, ByVal failed As Boolean)
m_IDs = New ArrayList
m_IDs.Add(id)
m_NumAdded = numAdded
m_NumDeleted = numDeleted
m_NumUpdated = numUpdated
m_Failed = failed
End Sub
Public Sub New(ByVal numAdded As Integer, ByVal numDeleted As Integer, ByVal numUpdated As Integer, ByVal failed As Boolean)
m_NumAdded = numAdded
m_NumDeleted = numDeleted
m_NumUpdated = numUpdated
m_Failed = failed
End Sub
#End Region
#Region " Propertys "
Public ReadOnly Property IDs() As Integer()
Get
Return m_IDs.ToArray(GetType(Integer))
End Get
End Property
Public ReadOnly Property NumAdded() As Integer
Get
Return m_NumAdded
End Get
End Property
Public ReadOnly Property NumDeleted() As Integer
Get
Return M_NumDeleted
End Get
End Property
Public ReadOnly Property NumUpdated() As Integer
Get
Return m_NumUpdated
End Get
End Property
Public ReadOnly Property Failed() As Boolean
Get
Return m_Failed
End Get
End Property
#End Region
#Region " Functions "
Public Sub Combine(ByVal p2 As UpdateResultParam)
m_IDs.AddRange(p2.m_IDs)
m_NumAdded += p2.m_NumAdded
m_NumDeleted += p2.m_NumDeleted
m_NumUpdated += p2.m_NumUpdated
m_Failed = m_Failed Or p2.m_Failed
End Sub
#End Region
#Region " Shared "
Public Shared Function Combine(ByVal p1 As UpdateResultParam, ByVal p2 As UpdateResultParam) As UpdateResultParam
Dim theIDs As New ArrayList
theIDs.AddRange(p1.m_IDs)
theIDs.AddRange(p2.m_IDs)
Return New UpdateResultParam(theIDs, p1.m_NumAdded + p2.m_NumAdded, p1.m_NumDeleted + p2.m_NumDeleted, p1.NumUpdated + p2.NumUpdated, p1.Failed Or p2.Failed)
End Function
#End Region
End Class
#End Region
#Region " Variables "
Private Shared m_DefConStr As String = Nothing
#End Region
#Region " Propertys "
Public Shared Property DefaultConnectionString() As String
Get
Return m_DefConStr
End Get
Set(ByVal value As String)
m_DefConStr = value
End Set
End Property
#End Region
#Region " Constructor Lock Out "
Private Sub New()
End Sub
#End Region
#Region " User Routines "
#Region " With Con Str "
#Region " Update "
Public Shared Function ID_UpdateRows(ByVal conStr As String, ByVal ds As DataSet) As UpdateResultParam
Dim retParam As New UpdateResultParam
For Each r As DataRow In ds.Tables(0).Rows
retParam.Combine(ID_UpdateRow(conStr, r))
Next r
Return retParam
End Function
Public Shared Sub UpdateRows(ByVal conStr As String, ByVal ds As DataSet)
For Each r As DataRow In ds.Tables(0).Rows
UpdateRow(conStr, r)
Next r
End Sub
Public Shared Function ID_UpdateRow(ByVal conStr As String, ByVal r As DataRow) As UpdateResultParam
Dim retParam As UpdateResultParam
Select Case r.RowState
Case DataRowState.Added
retParam = New UpdateResultParam(ID_InsertRow(conStr, r), 1, 0, 0, False)
Case DataRowState.Deleted
r.RejectChanges()
DeleteRow(conStr, r)
retParam = New UpdateResultParam(0, 1, 0, False)
Case DataRowState.Modified
ExecuteNonQuery(conStr, UpdateRowSQL(r))
retParam = New UpdateResultParam(0, 0, 1, False)
Case Else
retParam = New UpdateResultParam(0, 0, 0, False)
End Select
Return retParam
End Function
Public Shared Sub UpdateRow(ByVal conStr As String, ByVal r As DataRow)
Select Case r.RowState
Case DataRowState.Added
InsertRow(conStr, r)
Case DataRowState.Deleted
DeleteRow(conStr, r)
Case DataRowState.Modified
ExecuteNonQuery(conStr, UpdateRowSQL(r))
End Select
End Sub
#End Region
#Region " Insert "
Public Shared Sub InsertRows(ByVal conStr As String, ByVal ds As DataSet)
For Each r As DataRow In ds.Tables(0).Rows
InsertRow(conStr, r)
Next r
End Sub
Public Shared Function ID_InsertRows(ByVal conStr As String, ByVal ds As DataSet) As Integer()
Dim retIDs As New ArrayList
For Each r As DataRow In ds.Tables(0).Rows
retIDs.Add(ID_InsertRow(conStr, r))
Next r
Return retIDs.ToArray(GetType(Integer))
End Function
Public Shared Sub InsertRow(ByVal conStr As String, ByVal r As DataRow)
ExecuteNonQuery(conStr, InsertRowSQL(r))
End Sub
Public Shared Function ID_InsertRow(ByVal conStr As String, ByVal r As DataRow) As Integer
ExecuteNonQuery(conStr, InsertRowSQL(r))
Return GetCurrentID(conStr, r.Table.TableName)
End Function
#End Region
#Region " Delete "
Public Shared Sub DeleteRows(ByVal conStr As String, ByVal ds As DataSet)
For Each r As DataRow In ds.Tables(0).Rows
DeleteRow(conStr, r)
Next r
End Sub
Public Shared Sub DeleteRow(ByVal conStr As String, ByVal r As DataRow)
Dim pk As String = r.Table.PrimaryKey(0).ColumnName
DeleteID(conStr, pk, r.Item(pk), r.Table.TableName)
End Sub
Public Shared Sub DeleteID(ByVal conStr As String, ByVal pk As String, ByVal pkv As Integer, ByVal tbl As String)
ExecuteNonQuery(conStr, "Delete From " & tbl & " Where " & pk & " = " & pkv)
End Sub
#End Region
#Region " GetDatasets "
#Region " Typed "
Public Shared Function DR_GetTypedDataset(ByVal conStr As String, ByVal qry As String, ByVal dsName As String) As DataSet
Dim theCmd As SqlClient.SqlCommand = New SqlClient.SqlConnection(conStr).CreateCommand
Dim reader As IDataReader
Dim ds As DataSet
theCmd.CommandText = qry
theCmd.Connection.Open()
reader = theCmd.ExecuteReader(CommandBehavior.CloseConnection)
ds = DataReaderToDataSet(reader, dsName)
reader.Close()
Return ds
End Function
Public Shared Function GetTypedDataset(ByVal conStr As String, ByVal qry As String, ByVal dsName As String) As DataSet
Dim theCmd As SqlClient.SqlCommand = New SqlClient.SqlConnection(conStr).CreateCommand
Dim ds As New DataSet
theCmd.CommandText = qry
theCmd.Connection.Open()
With New SqlClient.SqlDataAdapter(theCmd)
.Fill(ds, dsName)
End With
theCmd.Connection.Close()
Return ds
End Function
#End Region
#Region " UnTyped "
Public Shared Function DR_GetDataset(ByVal conStr As String, ByVal qry As String) As DataSet
Dim theCmd As SqlClient.SqlCommand = New SqlClient.SqlConnection(conStr).CreateCommand
Dim reader As IDataReader
Dim ds As DataSet
theCmd.CommandText = qry
theCmd.Connection.Open()
reader = theCmd.ExecuteReader(CommandBehavior.CloseConnection)
ds = DataReaderToDataSet(reader, "DataSet")
reader.Close()
Return ds
End Function
Public Shared Function GetDataset(ByVal conStr As String, ByVal qry As String) As DataSet
Dim theCmd As SqlClient.SqlCommand = New SqlClient.SqlConnection(conStr).CreateCommand
Dim ds As New DataSet
theCmd.CommandText = qry
theCmd.Connection.Open()
With New SqlClient.SqlDataAdapter(theCmd)
.Fill(ds, "DataSet")
End With
theCmd.Connection.Close()
Return ds
End Function
#End Region
#End Region
#Region " Executes "
Public Shared Function Execute(ByVal conStr As String, ByVal qry As String) As DataSet
Return DR_GetDataset(conStr, qry)
End Function
Public Shared Function DR_Execute(ByVal conStr As String, ByVal qry As String) As DataSet
Return GetDataset(conStr, qry)
End Function
Public Shared Function ExecuteNonQuery(ByVal conStr As String, ByVal qry As String) As Integer
Dim ret As Integer
With New SqlClient.SqlConnection(conStr).CreateCommand
.CommandText = qry
.Connection.Open()
ret = .ExecuteNonQuery()
.Connection.Close()
End With
Return ret
End Function
Public Shared Function ExecuteScalar(ByVal conStr As String, ByVal qry As String) As Object
Dim ret As Object
With New SqlClient.SqlConnection(conStr).CreateCommand
.CommandText = qry
.Connection.Open()
ret = .ExecuteScalar
.Connection.Close()
End With
Return ret
End Function
#End Region
Public Shared Function GetCurrentID(ByVal conStr As String, ByVal tbl As String) As Integer
Return ExecuteScalar(conStr, "SELECT IDENT_CURRENT('" & tbl & "')")
End Function
#End Region
#Region " Without Con Str "
#Region " Update "
Public Shared Function ID_UpdateRows(ByVal ds As DataSet) As UpdateResultParam
Return ID_UpdateRows(m_DefConStr, ds)
End Function
Public Shared Sub UpdateRows(ByVal ds As DataSet)
UpdateRows(m_DefConStr, ds)
End Sub
Public Shared Function ID_UpdateRow(ByVal r As DataRow) As UpdateResultParam
Return ID_UpdateRow(m_DefConStr, r)
End Function
Public Shared Sub UpdateRow(ByVal r As DataRow)
UpdateRow(m_DefConStr, r)
End Sub
#End Region
#Region " Insert "
Public Shared Sub InsertRows(ByVal ds As DataSet)
InsertRows(m_DefConStr, ds)
End Sub
Public Shared Function ID_InsertRows(ByVal ds As DataSet) As Integer()
Return ID_InsertRows(m_DefConStr, ds)
End Function
Public Shared Sub InsertRow(ByVal r As DataRow)
InsertRow(m_DefConStr, r)
End Sub
Public Shared Function ID_InsertRow(ByVal r As DataRow) As Integer
Return ID_InsertRow(m_DefConStr, r)
End Function
#End Region
#Region " Delete "
Public Shared Sub DeleteRows(ByVal ds As DataSet)
DeleteRows(m_DefConStr, ds)
End Sub
Public Shared Sub DeleteRow(ByVal r As DataRow)
DeleteRow(m_DefConStr, r)
End Sub
Public Shared Sub DeleteID(ByVal pk As String, ByVal pkv As Integer, ByVal tbl As String)
DeleteID(m_DefConStr, pk, pkv, tbl)
End Sub
#End Region
#Region " GetDataset "
#Region " Typed "
Public Shared Function DR_GetTypedDataset(ByVal qry As String, ByVal dsName As String) As DataSet
Return DR_GetTypedDataset(m_DefConStr, qry, dsName)
End Function
Public Shared Function GetTypedDataset(ByVal qry As String, ByVal name As String) As DataSet
Return GetTypedDataset(m_DefConStr, qry, name)
End Function
#End Region
#Region " UnTyped "
Public Shared Function DR_GetDataset(ByVal qry As String) As DataSet
Return DR_GetDataset(m_DefConStr, qry)
End Function
Public Shared Function GetDataset(ByVal qry As String) As DataSet
Return GetDataset(m_DefConStr, qry)
End Function
#End Region
#End Region
#Region " Executes "
Public Shared Function Execute(ByVal qry As String) As DataSet
Return Execute(m_DefConStr, qry)
End Function
Public Shared Function DR_Execute(ByVal qry As String) As DataSet
Return DR_Execute(m_DefConStr, qry)
End Function
Public Shared Function ExecuteNonQuery(ByVal qry As String) As Integer
Return ExecuteNonQuery(m_DefConStr, qry)
End Function
Public Shared Function ExecuteScalar(ByVal qry As String) As Object
Return ExecuteScalar(m_DefConStr, qry)
End Function
#End Region
Public Shared Function GetCurrentID(ByVal tbl As String) As Integer
Return GetCurrentID(m_DefConStr, tbl)
End Function
#End Region
#End Region
#Region " Helpers "
Public Shared Function DataReaderToDataSet(ByVal rdr As IDataReader, ByVal tblName As String) As DataSet
Dim tbl As New DataTable(tblName)
Dim count As Integer = rdr.FieldCount
Dim items(count - 1) As Object
Dim ds As New DataSet
For i As Integer = 0 To count - 1
tbl.Columns.Add(rdr.GetName(i), rdr.GetFieldType(i))
Next i
While rdr.Read
rdr.GetValues(items)
tbl.LoadDataRow(items, True)
End While
ds.Tables.Add(tbl)
Return ds
End Function
Public Shared Function InsertRowSQL(ByVal r As DataRow) As String
Dim tbl As DataTable = r.Table
Dim qry As String = "INSERT INTO " & tbl.TableName & "("
For Each c As DataColumn In tbl.Columns
If Not c.AutoIncrement Then qry &= c.ColumnName & ","
Next c
qry = qry.TrimEnd(",") & ") VALUES("
For i As Integer = 0 To r.Table.Columns.Count - 1
Dim c As DataColumn = r.Table.Columns(i)
If Not c.AutoIncrement Then
If c.DataType Is Type.GetType("System.String") Then
qry &= "'" & r.Item(i) & "',"
Else : qry &= r.Item(i).ToString & ","
End If
End If
Next i
Return qry.TrimEnd(",") & ")"
End Function
Public Shared Function UpdateRowSQL(ByVal r As DataRow) As String
Dim qry As String = "UPDATE " & r.Table.TableName & " Set "
For i As Integer = 0 To r.Table.Columns.Count - 1
Dim c As DataColumn = r.Table.Columns(i)
If Not c.AutoIncrement Then
If c.DataType Is Type.GetType("System.String") Then
qry &= c.ColumnName & "='" & r.Item(i) & "',"
Else : qry &= c.ColumnName & "=" & r.Item(i).ToString & ","
End If
End If
Next i
Return qry.TrimEnd(",")
End Function
Public Shared Function IsDSEmpty(ByVal ds As DataSet) As Boolean
If Not ds Is Nothing Then
If ds.Tables.Count > 0 Then
If ds.Tables(0).Rows.Count > 0 Then
Return False
End If
End If
End If
Return True
End Function
#End Region
End Class
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
How to excute parameterized storeprocedure in LinkedServer.
Rickey
|
|
|
|
|
Hi guys,
Got this problem that's really annoying me as I can't find an answer and I know it's going to bug the hell out of me when a simple solution comes up.
Anyway, I'm using C# to connect to an Access database using the following code:
cmdFindLenders.CommandText = "SELECT ID FROM Company WHERE CompanyNameID='" + strCompany + "'";<br />
SqlDataReader rsFindLenders = cmdFindLenders.ExecuteReader();
However, due to Access and the crappy proprietory SQL is uses, it needs the string (the company name) to be passed through using double quotes, not single. So the SQL should read SELECT ID FROM Company WHERE CompanyName="Microsoft" rather than SELECT ID FROM Company WHERE CompanyName='Microsoft'
If I use the \" escape character in my SQL string, then the command text passed to Access isn't what I intended and it comes out like this: SELECT ID FROM Company WHERE CompanyName=\"Microsoft\"
Grrrrrr!!!!  Any ideas any one?
Baz
|
|
|
|
|
Use '' to replace ' quote and access does not use \ as the escape sequence
|
|
|
|
|
Baz wrote:
SqlDataReader
First of all, shouldnt you be using an OledbDataReader rather than an SqlDataReader? The SqlClient namespace classes are intended only for use with SQL Server, and it is surprising to me that they work at all with Access. Not sure how you are constructing the string that caUses the escape sequence to be ingored (you're not prefixing with an @ are you?), but try this:
public const string sQuote = "\"";
public string QuoteString(string strToQoute)
{
return sQuote + strToQuote + sQuote;
}
...
x.CommandText = "Select ID from Company where CompanyName = " + QuoteString(strCompany);
....
|
|
|
|
|
Thanks for that Rob - yeah my bad! I am actually using the OleDbDataReader and not the SqlDataReader class in my code. I was trying to get this question up there before CSI came on the TV!!! (it comes on at 9pm here in the UK)
Anyway, the method you have presented there is the same method I am using, and the CommandText that gets passed to Access is actually:
SELECT ID FROM Company WHERE CompanyName=\"Microsoft\" and obviously not what I want!
EDIT: Woah! After rebooting my machine, it now seems to pass through the correct string... i.e SELECT ID FROM Company WHERE CompanyName="Microsoft"
Not sure what happened there... anyway, the query never finds the company regardless of the name supplied, even though the company is in the database! Any ideas on this one? I have seen a similar situation when searching on Google, but couldn't find a fix
|
|
|
|
|
Can't you use SqlParameter instead? I'm not 100% sure whether it works with Access but it's safer than using concatenated Sql strings and you don't need to worry about the quotes.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
I think you might be onto a winner here... it was the next thing to try on my list, if I didn't get this working
Cheers!
Baz
|
|
|
|
|
char qoute ='"';<br />
<br />
cmdFindLenders.CommandText = "SELECT ID FROM Company WHERE CompanyNameID=" + quote + <br />
<br />
strCompany + quote;
|
|
|
|
|
I’m having a problem updating a record in my AAH_CNTRCT_EVNT_LOG table. I know its because of the datetime. I’m using datetime as one of my keys. The Dates I physically put in the table work just fine but when I add a record I use a store procedure that adds the date using DECLARE @NOW datetime SET @NOW = GETDATE()and those are the ones that seem to give me the problem when updating. Dose anyone know how to fix this problem? 
Here's the store procedure for the update:
(
@iAahCntrctId int,
@sAahCntrctEvntNM varchar (50),
@dAahCntrctEvntLogDT datetime,
@sAahCntrctEvntLogCmnt varchar (500),
@sAahCntrctSignLN1NM varchar (20),
@sAahCntrctSignLN2NM varchar (20),
@sAahCntrctSignLN3NM varchar (20),
@sUpdtUserID varchar (8)
)
AS
DECLARE @EMPTY INT
DECLARE @BLK CHAR
DECLARE @BLKDT DATETIME
DECLARE @NOW datetime
SET @NOW = GETDATE()
SET @EMPTY = 0
SET @BLK = ''
SET @BLKDT = ''
UPDATE AAH_CNTRCT_EVNT_LOG
SET AAH_CNTRCT_EVNT_LOG_CMNT = @sAahCntrctEvntLogCmnt,
AAH_CNTRCT_SIGN_LN_1_NM = @sAahCntrctSignLN1NM,
AAH_CNTRCT_SIGN_LN_2_NM = @sAahCntrctSignLN2NM,
AAH_CNTRCT_SIGN_LN_3_NM = @sAahCntrctSignLN3NM,
UPDT_USER_ID = @sUpdtUserID,
UPDT_DTTM = @NOW
WHERE AAH_CNTRCT_ID = @iAahCntrctId
and AAH_CNTRCT_EVNT_NM = @sAahCntrctEvntNM
AND AAH_CNTRCT_EVNT_LOG_DT = @dAahCntrctEvntLogD
|
|
|
|
|
This should work. What error are you getting? (You are missing a "T" at the end of the UPDATE statement. I assume that is a copy/paste error on Code Project).
Does UPDT_DTTM have a unique constraint? If so, are you adding records so fast that you are violating the constraint?
|
|
|
|
|
We had populated the date field using the GetDate() function in SQL during the insert of the record. We were using a datetime type variable in C# to hold the date until we updated the record by calling the SQL statement. When we tried to update the record using the held variable and passing it to the SQL statement to the where clause, it could not find the record to update it.
We resolved the whole issue by changing the way the date field was poulated when it was inserted into the database. We removed the GetDate() function from the SQL and passed a date variable as a string to the datetime type field on the SQL statement. When we updated the record this time, it worked.
Thank you for your help in trying to resolve this.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
