Member Avatar for Madaxe

Hello everyone.
This is quite possibly not the place for this but it is more to do with SQL than the other things included so I apologise if I am wrong.

I am looking for some help on a task I have been set.
When I say help, what I actually mean is I need someone to draw the dots that I am supposed to be joining, as this is not my area and I am bumbling through with not much of a clue so do please bear with me on this one...

I have an MS SQL database full of all my customers details including issues they have had in the passed etc. which is linked to a 4th Dimension front end which I use for input.

I want to put a simple form on my website so my customers can go there, fill thier details in (usual stuff: name, address, issue etc.), hit 'Submit' then the information is slammed straight into my database the same as it would usually be done from our 4D application. I would then like it to send an email to me with our reference number (issued by the server) and the details of the call so we can bring it up in our system for editing and contacting the customer obviously.

If someone could point me in the direction of some material to read or even give me some idea of where I should be starting that would be massively appreciated.
I have been told Perl could be the way to go but, as with the other subjects covered above, I lack the know how when it comes to using them.

I have installed ActivePerl on my test server but if anyone knows another, or better way of going about what I am looking to do I would be very interested. I don't mind learning a new language etc. but I don't want to spend hours educating myself only to find there is a much easier way of doing it.

Thank you very much for taking the time to read this and I look forward to seeing back from you.
:)

Appreciated
Madaxe

  • 3 Contributors
  • 9 Replies
  • 229 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for Madaxe

Interesting I will have a look into it.
Thank you for your input cgeier.
Much appreciated.

Member Avatar for cgeier

You should be able to do something similar to below. I've trimmed down the code a little bit, so hopefully I didn't omit anything necessary. But it should give you a jumpstart on how to use ASP to add data to a database.

event.asp

<%@ Language=VBScript %>
<HTML><HEAD><TITLE>Add Event</TITLE>
<BODY>

<TABLE height="100%" cellSpacing=0 cellPadding=0 width=100% align=center border=0>
   <TBODY>

      <form action = "addEvent.asp" method=POST>

       <TR>
         <TD colspan=2>
           <select name="start_month">
             <option value=''></option>
             <option value='January'>January</option> 
             <option value='February'>February</option>
             <option value='March'>March</option> 
             <option value='April'>April</option>
             <option value='May'>May</option>
             <option value='June'>June</option>
             <option value='July'>July</option>
             <option value='August'>August</option>
             <option value='September'>September</option>
             <option value='October'>October</option>
             <option value='November'>November</option>
             <option value='December'>December</option>
           </select>
      </TR>
      
      <TR>
        <TD colspan=2>
          <TEXTAREA NAME="location" COLS=33 ROWS=4></TEXTAREA>
        </TD>
      </TR>

       <TR>
         <TD>
           <input type='submit' value='Add Event'>
            &nbsp;
           <input type='reset' value='Reset'>
         </TD>
       <TR>

     </TBODY>
   </TABLE>
</BODY>
</HTML>

addEvent.asp

<%@ Language=VBScript %>
<HTML><HEAD><TITLE>Add Event</TITLE>
<BODY>
<%

 ' on error resume next

Dim start_month
Dim start_date
Dim location
Dim strConnection

Dim i
Dim field_array(2)
Dim value_array(2)

start_month            = Request.form("start_month")
location                   = Request.form("location")


'strConnection = 

'start_date = start_month & " " & start_day & ", " & start_year 
start_date = start_month

if (start_month =  "") then
      Response.Write "Start date has not been selected."
      Response.Write "Start Date: " & start_date & "<BR>"
      Response.Write "Press the ""Back"" button on your browser to  continue.<BR>"
      Response.End

end if

field_array(0)  = "start_date"
field_array(1)  = "location" 

if (start_date <> "") then
   value_array(0) = chr(35) & start_date & chr(35)
else
   value_array(0) = "NULL"
end if

value_array(1)  = "'" & location & "'"

i=0
for each val in value_array
   if (val = "" or IsNull(val) or val = "''") then
      value_array(i) = "NULL"
   end if
   i = i + 1
next

tblfields = Join(field_array,",")
tblvalues = Join(value_array,",")
call addrecord(strConnection,tblfields,tblvalues)
%>


</BODY>
</HTML>

<%


sub addrecord(strConnection,fields,fvalues)
   Dim s_userid,numrecs,db,dbConnection
   Dim s_fn,s_mn,s_ln,bdate,sameuser
   
   set dbConnection = server.CreateObject ("ADODB.Connection")
   dbConnection.ConnectionString = strConnection
   dbConnection.Open

   strSQL = "insert into " & strTableNameB & " "
   strSQL = strSQL & "(" & fields & ") values (" & fvalues & ")"

   set db = dbConnection.Execute(strSQL)
   
   dbConnection.Close

   set db = nothing 
   set dbConnection = nothing


end sub



function checkevent(start_date,start_time,end_time)
   Dim db,dbConnection
   Dim numrecs,bslocation
 
   set dbConnection = server.CreateObject ("ADODB.Connection")
   dbConnection.ConnectionString = strConnection
   dbConnection.Open

   strSQL = "select * from " & strTableNameB
   strSQL = strSQL & " where start_date = "
   strSQL = strSQL & chr(35) & start_date & chr(35)
   strSQL = strSQL & " and start_time = "
   strSQL = strSQL & chr(35) & start_time & chr(35)

   set db = dbConnection.Execute(strSQL)

   numrecs = 0

   Do Until db.EOF
      bslocation = db("location")

      numrecs = numrecs + 1
      db.MoveNext
   loop

   db.Close

   set db = nothing 
   set dbConnection = nothing

   if (numrecs = 0) then
      checkevent= "false"
   else
      checkevent= "true"
   end if

end function
commented: You do not assemble queries dynamically to stop injections -1
commented: right +0
Member Avatar for Madaxe

cgeier I can't thank you enough for that.
When I am back to work on monday I will see how much of this I can make sense of and let you know how I get on.

Your help is appreciated hugely.
All the best

Member Avatar for sknake

Do not do that! Use parameterized SQL to stop SQL injection and help server performance. Assembling the queries dynamically like that is what leads to SQL Injection vulnerabilities in the first place!

This depends a lot on what language you are developing in but here is an ASP.NET/C# example:

private bool InsertRecord(string Loc, string Lab, string Tot)
    {
      const string connStr = @"Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      List<string> Sql = new List<string>();
      Sql.Add("Insert Into Loc_Det (Loc, Lab, Tot)");
      Sql.Add("Select @Loc, @Lab, @Tot");
      Sql.Add("Where NOT EXISTS");
      Sql.Add("(");
      Sql.Add("  Select *");
      Sql.Add("  From Loc_Det (NOLOCK)");
      Sql.Add("  Where Loc_Det.Loc = @Loc and Loc_Det.Lab = @Lab and Loc_Det.Tot = @Tot");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Select CAST(@@ROWCOUNT as int)");
      string query = GetText(Sql);
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Loc", Loc));
          cmd.Parameters.Add(new SqlParameter("@Lab", Lab));
          cmd.Parameters.Add(new SqlParameter("@Tot", Tot));
          return (Convert.ToInt32(cmd.ExecuteScalar()) > 0);
        }
      }
    }
commented: Very helpful example. Thank you! +2
Member Avatar for Madaxe

Hi sknake,
Thank you for the heads up and the code is much appreciated. I will have a look over that.

I have been doing some research thismorning and found that this can also be done using our Frontrange Goldmine system which uses the same SQL Database.
Apparently I can create a form on my site that will, once completed and submitted, send an email to Goldmine which then in turn uses the values in the email to create a customer entry in our database.

The instructions don't go in-depth but does this seem feasable to you, and if so, where would I begin with such a task?

Thank you again for all your help.

Member Avatar for sknake

That all depends on what language you are developing in ... and the question you just asked is getting outside the scope of MSSQL and should be posted in the respective language's forum on Daniweb. If you post the question in the ASP.NET forum then I will likely see you there and answer your question :)

Member Avatar for Madaxe

Very true.
Well the document reads along the lines that Perl is the preferred language so I will have a bit more of a read and if necessary post there.
I will take into account your example above as an option and I thank you for all your help!
I am very greatful.
:)

Member Avatar for sknake

You're welcome

Please mark this thread as solved if you have found a solution to your original question and good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.