|
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in the string of info that this company sends back ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info ( or not ) and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
|
In your stored procedure you can do something like this:
IF NOT EXISTS (SELECT * FROM IPN_received WHERE Invoice = @Invoice)
BEGIN
-- The row does not exist already. Insert the new row here!
END
|
|
|
|
|
Hi Colin
What I really need is the procedure simply returning TRUE if a given invoice is there or FALSE if it is not.
What lines of code should I add after “IF NOT EXISTS (SELECT * FROM IPN_received WHERE Invoice = @Invoice)” ?
( Sorry if my question is too elementar. I am still learning SQL. )
|
|
|
|
|
tedhill13 wrote: What I really need is the procedure simply returning TRUE if a given invoice is there or FALSE if it is not.
You can use something like:
SELECT 1
or
SELECT 0
Then you can pick up the value from your application with ExecuteScalar()
int existsAlready = (int)theSqlCommand.ExecuteScalar()
tedhill13 wrote: Sorry if my question is too elementar. I am still learning SQL.
Then you may wish to lean more about constraints so that SQL Server can handle this scenario itself - that is what it is good at.
|
|
|
|
|
I would likely have a ProcessState column. Initialized to something like a "Pending" code.
When I receive a response (approve or decline), if the transaction is still "Pending" I'd set the ProcessState as appropriate. Then other methods would take the transactions from there.
Technical note: Use integers for the code, not strings; have a separate table to map the values to strings. Then, if you like, add referential integrity.
Extra credit: If you don't know about finite state machines, you might want to do some reading.
|
|
|
|
|
Can someone post a link for some sample app. in vb2005 and with sql db (mdf)
Thanks!
|
|
|
|
|
There are plenty of samples at Microsoft's web site including their "coding4fun" samples. Make a search there.
modified 1-Aug-19 21:02pm.
|
|
|
|
|
Hi All,
I'm calling a SQL2000 SP from my VB.NET app with the following code:
<br />
Dim sqlCmd As SqlCommand<br />
sqlCmd = New SqlCommand("JHU_SAS_IDOC_Process", conn)<br />
sqlCmd.CommandType = CommandType.StoredProcedure<br />
<br />
Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlCmd)<br />
<br />
adapter.Fill(tableReturned)<br />
This SP performs a SELECT that causes the tableReturned (a DataTable) to be filled. The SP also returns an INT (0 or 1, depending on whether an error occurred). The SP code is basically this (much simplified for this question):
<br />
CREATE PROCEDURE dbo.JHU_SAS_IDOC_Process<br />
<br />
AS<br />
<br />
BEGIN<br />
<br />
SELECT * FROM JHU_SAS_IDOC<br />
<br />
RETURN 1 -- or 0, depending on an error condition<br />
<br />
END<br />
My question is: How do I read that return value in the app? I think I could read it using the SqlCommand.ExecuteScalar() func, but that wouldn't work to fill my table. Any ideas?
Thanks!
Justin
|
|
|
|
|
Justin Cooke wrote: How do I read that return value in the app? I think I could read it using the SqlCommand.ExecuteScalar() func, but that wouldn't work to fill my table.
And it wouldn't work to get the value because ExecuteScalar just retrieves the value in the first column of the first row in the result set and discards anything else.
You need to add a SqlParameter[^] to your SqlCommand object to get the ReturnValue.
For example:
Dim sqlCmd As SqlCommand
sqlCmd = New SqlCommand("JHU_SAS_IDOC_Process", conn)
sqlCmd.CommandType = CommandType.StoredProcedure
Dim parameter As New SqlParameter("ReturnValue", SqlDbType.Int, 4)
parameter.Direction = ParameterDirection.ReturnValue
sqlCmd.Parameters.Add(parameter)After you've got the data you can then check the value of the parameter with parameter.Value
DISCLAIMER: I typed that off the top of my head, and I'm also not a VB.NET developer, so there may be errors
|
|
|
|
|
hi fellows
i'm trying to create triggers in Sql Server 2000 and my query doesn't work.
can anyone plz tell me the query syntax to create trigger for the following operations:
1. Insert
2. Update
3. Delete
awaitng ur replies
Saira
|
|
|
|
|
Saira Tanwir wrote: can anyone plz tell me the query syntax to create trigger
The syntax is here[^]
|
|
|
|
|
Saira Tanwir wrote: 1. Insert
CREATE TRIGGER blah_trigger ON blah1 FOR INSERT AS INSERT INTO blah2 ...
|
|
|
|
|
I have a database that a user can search for using the begins with, exact match, or contains option. I did a request.querystring to pull in that information from the previous page where the user selects one of those options and types is what he is searching for. The problem is that I cannot figure out how to display the users result when they select to search by exact match, begins with, or contains. The following is the code that I used. Can anyone tell me what is wrong with it and give me some leads on how to get it fixed.
<title>Table Display
<%Dim FieldHead, SearchMethod, TextBox, Table, DB, RS, strSql%>
<%'Request the information from the previous page'%>
<%FieldHead = Request.QueryString("fieldhead")
SearchMethod = Request.QueryString("SrchMthd")
TextBox = Request.QueryString("textbox")
Table = Request.QueryString("Table")
%>
<%'search method if the user selects begins with%>
<%If SearchMethod = "begin" Then%>
<%Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb")
Set RS = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '" & TextBox & "%'"
RS.Open (strSql), DB
End IF
%>
<%'search method if the user selects contains%>
<%If SearchMethod = "contain" Then%>
<%Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb")
Set RS = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '%" & TextBox & "%'"
RS.Open (strSql), DB
End IF%>
<%'search method if the user selects exact match%>
<%If SearchMethod = "exact" Then%>
<%Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb")
Set RS = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & = TextBox
RS.Open (strSql), DB
End IF%>
Ty
|
|
|
|
|
Your code should use:
... " WHERE " & FieldHead & " LIKE '" & Replace(TextBox, "'", "''") & "%' Instead of:
... " WHERE " & FieldHead & " LIKE '" & TextBox & "%'" Otherwise it will break if the user enters a single-quote into his search value.
The normal way to show the results is to iterate through each row and column in the result set. However, you might find the following faster:
'Open the recordset and render the data in HTML format.
For Each objField In objRS.Fields
Call Response.Write("<td nowrap><b>" & _
Server.HtmlEncode(objField.Name) & "</b></td>" & vbCRLF)
Next
Call Response.Write("</tr>" & vbCRLF)
Call Response.Write("<tr>")
If Not objRs.eof Then
strData = objRS.GetString( , , "#<#/td#>##<#td#>#", _
"#<#/td#>##<#/tr#>##<#tr#>##<#td nowrap#>#", "#&#nbsp;")
strData = Replace(Replace(Replace(strData, "#&#", Chr(1)), "&", "&"), Chr(1), "&")
strData = Replace(Replace(Replace(strData, "#<#", Chr(1)), "<", "<"), Chr(1), "<")
strData = Replace(Replace(Replace(strData, "#>#", Chr(1)), ">", ">"), Chr(1), ">")
strData = "<td>" & Replace(Left(strData, Len(strData) - 15), "</tr>", "</tr>" & vbCRLF)
Call Response.Write(strData)
Else
Call Response.Write("<td colspan=""" & objRs.Fields.Count & """>No records found.</td></tr>" & vbCRLF)
End If
Call Response.Write("</table>" & vbCRLF)The funny replace stuff is a quick way to get it to render each field as a separate HTML table cell.
Andy
|
|
|
|
|
hi there,
I've got a table like this:
CREATE TABLE [dbo].[one_Messages] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RecipientID] [int] NOT NULL ,
[SenderID] [int] NOT NULL ,
[DateTimeSent] [datetime] NOT NULL ,
[IsRead] [bit] NOT NULL ,
[Body] [varchar] (1024) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Now please consider that I want to allow only 20 Messages per conversation (SenderID/RecipientID combination). In my stored procedure, I'd like to INSERT a new conversation-record first and then DELETE the oldest messages which exceed the number of 20. It's quite hard for me to explain this in english, do you get the point? After every INSERT, I'd like make sure to keep a maximum number of 20 messages and DELETE the rest. How can this be done with a DELETE statement?
Any help is greatly appreceated. Thanks in advance.
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Do the insert then do the delete based on the DateTimeSend, or you could do it on the ID (because it will be ever increasing)
e.g.
DELETE one_Messages
WHERE RecipientID = @RecipientID
AND SenderID = @SenderID
AND ID NOT IN (SELECT TOP 20 ID FROM one_Messages
WHERE RecipientID = @RecipientID
AND SenderID = SenderID
ORDER BY ID DESC)
The inner select gets the most recent 20 items.
Does this help?
|
|
|
|
|
Perfect. Thanks a bunch!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Place the delete code in a trigger.
Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)
http:\\www.frankkerrigan.com
|
|
|
|
|
I am planning on using a CLR stored procedure to encapsulate information in a wrapper object and place this on an MSMQ private queue. My problem is that I can’t add System.Messaging as a reference. I understand that this restriction to ‘approved’ assemblies only can be worked around by creating my assembly as UNSAFE in SQL Server, but does this mean that I have to dynamically load System.Messaging?
|
|
|
|
|
how can i register oracle provider in debug
can any body help me plz.
and when ever i press f5, it says an error like unable to start debugging.
aslam
|
|
|
|
|
|
I have written a program to archive user reports to a temporary area and then deleting them after a set number of days.
This program works fine on our Local Area Network, but it cannot connect to an SQL Server which is in the DMZ. ASP.NET programs do not seem to have any trouble.
My program is written in C++ and Classic ADO.
My connection details are as follows: (written as VB as this is probably the most familiar)
Conn.Mode = adModeReadWrite OR adModeShareExclusive
Conn.CursorLocation = adUseClient
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 128
Conn.Provider = "sqloledb"
Conn.ConnectionString = "Data Source=TheServer;Initial Catalog=TheDB"
Conn.Attributes = 0;
Conn.IsolationLevel = adXactReadCommitted
Conn.Open( , UserNameVar, DBPasswordVar,adConnectUnspecified)
|
|
|
|
|
I'm using VB 2005, and sql databse.
I have a form with 4 textboxes: barcode_textbox, id_textbox, name_textbox, stock_textbox and they are connected to database table called "PRODUCTS"
Now when i add new record, and enter barcode that already exist in database, i want it to only add stock_textbox to that record in database!
How can i do that?
|
|
|
|
|
Do a check to see if the barcode exists, and if it does just update it. You can do this using a SELECT COUNT or IF EXISTS or whatever.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
i using Free SQLyog GUI v5.02 to save data for my asp.net web application. I need to addin rollback feature so while the system is fail during updating,adding and deleting data, the system can back to original(before perform the action).I am fresh for SQLyog, so have any setting at SQLyog for rollback or any idea or technique to perform this feature ? thanks ! 
Best regards,
Chee ken
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
