HTTP Post Integration 1

[primagic]

I have never done this before, but was wondering what do I need to google to parse or post information from a URL string to an SQL database using asp.net

 

[jbenson001]

What exactly are you tring to do? Do you want to read a query string and get the values and insert them into a data base?

 

[primagic]

Yes so the URL will have like Firstname="Jack" and so on...

I need to take those values from the URL and insert them into an SQL database

 

[jbenson001]

VB: Request.QueryString("Product")
C#: Request.QueryString["Product"];

 

[jmeckley]

Request.QueryString is a very low level call. it works. and if you developing directly against asp.net it makes sense.

if you are using any of the MVC implementations (built on top of asp.net) you can use conventions to transform the get/post request to an object. for example the uri [tt].../Customer/Add?FirstName=john&LastName=doe[/tt]
would be handled like this

class CustomerControler
{
   [PostAction]
   public void Add(Customer customer)
   {
     //save customer to database
   }
}

class Customer
{
   public string FirstName {get;set;}
   public string LastName {get;set;}
}

in this example the uri Customer/Add will resolve the customer controller and invoke the Add action. the query string will automatically be converted to a Customer object with the properties set for you. PostAction signals the request must be made through POST (not GET), otherwise an exception will be thrown.

something to think about.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[primagic]

I forgot that I had this thread open. I have opened another thread then by mistake.

Ok. I understand using request.querystring to get the values from the posted url -

http://www.domain.com/post.aspx?Name=John&Surname=Smith,

and populate a form fields with those values, then add the record to the database

but

if they are sending through 15 of these a day, how do I get this to run automatically. To check when a new lead has been posted and to submit the form to add to the database.

Do I need to add something to the Page_Load event? But then would the page not need to be opened in a browser?

 

[ca8msm]

On the load of your page, get the values from the querystring and do with them whatever you need to do (i.e. put them in your database). You don't need to do anything else, it has already been ran "automatically".

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[primagic]

Wow. Is it that simple. I actually thought it was more complex.

Thank you for your assistance. I will try it out and come back with any problems.

So just adding request query strings and adding the values to the database this will work everytime the company sends through new information? Apparently there may be 15 posts a day

 

[jmeckley]

the purpose of aspx is to render a webpage for a human to view. In this situation the requests are sent via an automated process. A response, if necessary would only need to contain the result of the result of the request (success/fail) and any data needed for the process to continue; for example returning a confirmation number.

In this instance an webform(aspx) doesn't make much sense and really, only complicates the process. an generic handler(ashx) would be more practical.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[primagic]

Ok. I have the following code that seems to insert the record but with blank information.

Any ideas why?

Imports System.Data.SqlClient
Imports System.Data
Partial Class post
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.TextBox1.Text = Request.QueryString("PROP_DESC")
        Me.TextBox2.Text = Request.QueryString("OCC_STAT")
        Me.TextBox3.Text = Request.QueryString("PURCHASE_PRICE")


        Dim conTest As SqlConnection
        Dim strInsert As String
        Dim cmdInsert As SqlCommand

        conTest = New SqlConnection("Server=*****;UID=**;PWD=***;database=PostTest")
        strInsert = "Insert dbo.Test (Property, Occupation, PurchasePrice) Values (@Property, @Occupation, @PurchasePrice)"
        cmdInsert = New SqlCommand(strInsert, conTest)

        cmdInsert.Parameters.AddWithValue("@Property", Me.TextBox1.Text)
        cmdInsert.Parameters.AddWithValue("@Occupation", Me.TextBox2.Text)
        cmdInsert.Parameters.AddWithValue("@PurchasePrice", Me.TextBox3.Text)
        conTest.Open()
        cmdInsert.ExecuteNonQuery()
        conTest.Close()

    End Sub
End Class

 

[jbenson001]

It seems you are not getting the querystring parameters you expect. The first thing you should do, is take the whole querystring value and insert that into a table, this way you can go back and look at it to make sure they are sending it correctly. In your code, check if the qs values are empty or not, and don't insert if they are.
You need TRY.. CATCH statements in here for error handling and also logging to your DB for debugging.

 

[primagic]

I have it kind of working but it only works when the query string is appended to the url. for some reason it doesnt work when they send it http post.

is there any ports or configuration i need to do on the server (windows server 2003)

 

[ca8msm]

You need to read up on some basic web principles as this is the expected behaviour. GET passes variables via the querystring (so you use Request.QueryString to access them), whereas POST passes them via a form (so you use Request.Form to access them).

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[primagic]

My request now inserts the record into the database however, with the dateofbirth field in the sql table has a datetime fieldtype.

The querystring for Dateofbirth appears like

BIRTH_DATE=25%2F12%2F1956

I get the following error when I run the code

Conversion failed when converting datetime from character string.

Stack Trace:

[SqlException (0x80131904): Conversion failed when converting datetime from character string.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +248
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1357
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +2971052
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +2969457
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +423
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +537
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +203
   posttest.Page_Load(Object sender, EventArgs e) +1261
   System.Web.UI.Control.OnLoad(EventArgs e) +133
   System.Web.UI.Control.LoadRecursive() +66
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2604

 

[jmeckley]

yes, you need to decode the value. something like

var encodedvalue = Request.Params["date"];
var decodedvalue = HttpUtility
.HtmlDecode(encodedvalue);
var date = DateTime.Parse(decodedvalue);

command.AddParameterWithValue("key", date);

side note: params is a collection of all named collections:
server, cookie, form, query string, item. if you don't care where the value it coming from Params is a simple way to get at the data.

Jason Meckley
Programmer

faq855-7190
faq732-7259