[2005] Web (inquiry) form with insert to SQL db

I apologize in advance for not knowing a thing about web apps. I'm completely ignorant of how a web app works, however I have managed to create one from my "programming vs 2005" book that works just peachy. I created a sqldatasource, dragged a grid on the form, a formview on the form, etc., and can get the app to select all the records in a table, let me edit them and add new ones.

Now I just want to create a page where a person can go, enter some info (bound to columns in a sql table?), click a 'submit' button and insert the info into the sql table.

Is this just too big a question? Can you point me to a tutorial that does just this? I am resistant to taking a 101 course on web apps--no time! Plus everything I read says I don't have to know anything about programming!! If I need to, however, I need to do it with VB code.

Thanks for your patience :)

Quote:

---

Originally Posted by vbmom

Plus everything I read says I don't have to know anything about programming!!

---

I don't know what you're reading, but you're going to need atleast some programming knowledge in order to do this. Mostly SQL (insert statement), some VB.net, and some ASP.net. I've done what you're trying to do (set up a form on a website, and transfer whatever information the user inputs into a SQL table), and I'm basically at novice. I'm going to point you to a very useful tutorial I found on the web a few months back, and it basically teaches you how to use parameters with stored procedures. This is what I used as a foundation and I hope it has the same effect on you.

http://www.sqlservercentral.com/colu...procedures.asp

I've been a vb programmer for over 10 years. Useless languages before that. I have written code to create, alter and update SQL 2000 databases using TSQL, stored procedures and XML bulkload. I'm not saying I don't know anything...I just don't know anything about ASP or web programming.

I have been using the book "Programming Visual Baic 2005" by O'Reilly. It's got a chapter on creating web sites with backend sql databases. There's lots of drag and drop and binding controls to the database. I get that.

I'm just wondering if there are like-techniques for creating a sqldatasource, dragging a "formview" or something and letting the page perform a sql row insert when the user clicks a button. I've tried using the little I've learned but I'm not getting it--maybe it just can't be done that way, and I would just like to know that too--too big a question.

Then I really don't know the answer to your question. What I did was simply create a few ASP text boxes, created a "doInsert" function which basically passed the values from the textboxes to a stored procedure as output parameters, and did the insert statement inside the stored procedure (and of course, I added some validation in there as well).

Good luck.

Well, if that's the only way, I'll take it. Would you mind posting your stored proc execution code? I'd just like to know if I use the same object libraries in ASP as I would in a VB program.

There's alot more to these scripts, but I'm giving you a really condensed verison just so I can convey the main idea.

The ASP textboxes:
First Name: <asp:textbox id="txtFirstName" runat="Server" size="18" tabIndex="1" forecolor="blue"></asp:textbox>
Last Name: <asp:textbox id="txtLastName" runat="Server" size="18" tabIndex="2 forecolor="blue"></asp:textbox>

The Namespaces I had to import
<%@ Page Language="VB" Debug="true" autoeventwireup="True" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Windows.Forms" %>
<%@ import Namespace="Microsoft.VisualBasic" %>
<%@ import Namespace="System.Diagnostics" %>
<%@ import Namespace="System.Web.UI.WebControls" %>
<%@ import Namespace="System.Web.UI.HtmlControls" %>
<%@ import Namespace="System.Windows.Forms.UserControl" %>

The doInsert function:

Sub doInsert(obj as Object, E as ImageClickEventArgs)
MyConn = [connection string]
MySQL= New SqlCommand("proc_newWebEntry", MyConn)
MySQL.CommandType = CommandType.StoredProcedure

MySQL.Parameters.Add(New SQLParameter("@FirstName", txtFirstName.text))
MySQL.Parameters.Add(New SQLParameter("@LastName", txtLastName.text))

MyConn.Open()
MySQL.ExecuteNonQuery
MyConn.Close()
End Sub

Stored procedure:

CREATE PROCEDURE proc_newWebEntry(
@FirstName varchar(50)
, @LastName varchar(50)
)

/* This code prevents the same record from being inserted twice
(just in case user refreshes asp page) */

IF NOT EXISTS (SELECT NULL FROM [Internet Usage]
WHERE [First Name]= @FirstName AND
[Last Name] = @LastName )

/* Enter the new record into the database */
Insert into [Internet Usage](
[First Name]
, [Last Name]
) Values (
@FirstName
, @LastName

)
END

RETURN
GO

Also, since I'm helping you, I thought maybe you could help me. I need help dynamically retreiving images from a database. I already have my own thread in these forums posing the detailed question, but no one has been able to fully help me. Please look at it. The link is below:

http://www.vbforums.com/showthread.php?t=460075

Thanks.

Thanks for your code.

I wish I could help you. I saw someone try to lead to the water, but I can't tell if you got close. I get lost with all the open and close brackets. If you need to know how to loop thru a dataset I can give you an example of that, if I try hard.

So did my sample code actually help you? If it did, it would probably make my day, just knowing I helped someone. And yes, please show me how to loop through a dataset. I'm really lost on that particular problem and need it for work.

Thanks.

I wrote this in VS 2005. I already had sql connections and datasets defined using the designer. So I just used what I had but I can't remember how I got them established.

This dataset I'm using below has a few tables from the NorthWind database. I put this code in a button click event:

Dim dr As DataRow
Dim i As Int16
With NorthwindDataSet
For i = 1 To .Customers.Rows.Count
dr = .Customers.Rows(i)
Debug.Print(dr.Item("CompanyName").ToString)
Next
End With

It's probably not what you're looking for. Sorry!

As for your helping me out, I don't know yet. I'm too busy today! I'll let you know though, and I do appreciate anyone who tries and doesn't make me feel stupid. Thanks.

Webwiz: I found a way to program those drag and drop design objects so and wrote code to use a stored proc to insert a row. I probably shouldn't do what I'm doing, but alas, it works. It's just a concept I'm testing anyway.

Firstly, forget about all the web controls that VS 2005 gives you like FormsView, DetailsView, SqlDataSource, AccessDataSource etc., which helps you in creating DB bound app very easily. You will not even learn 2 cents using them.

Like you said you have no idea how web apps work. Then I would suggest you to get ASP.Net books or some good tutorials from asp.net (MS official ASP.Net site).

About your question:

.Net provides you 2 way to look at your DB data:

• in Connected mode - working with 1 row at a time like open db connection, perform action/s, close connection.
• in Disconnected mode (using DataSets) - working with whole table in dataset at a time. Create a dataadapter and fill a dataset, which represents exactly the same copy of your database containing 1 or more datatables.

This itself is needs solid background.

Now, 2 things to consider:

• the web control gridview allows you to edit only 1 row at a time.
• if you know the life cycle of a web page, the last event that is called is Unload, that means if you create a dataset in your web page, then it will be destroyed when the page finish loading. So you cannot edit the dataset and send it to your db server to update the actual database.

So disconnected mode is quite out of question.

Datasets are only used in web apps while binding the data bound controls like Datagrid, gridview, datalist, repeater etc to your database. Create a dataset, and bind it to the control.

Steps involved in Connected mode(I am using SqlClient as example)

• Create SqlConnection object and set its ConnectionString property.
• Create SqlCommand object, set its CommandText property which needs the PL-SQL statements like Update, Delete, Insert, Select queries. Also set its Connection property to the SqlConnection object you just created.
• open the connection using Open() of the SqlConnection object.
• If you are reading values from DB, the create a DataReader object and use the SqlCommand's ExecuteReader() (for reading the entire row).
  The ExecuteScalar() of SqlCommand object will return the value of first column-first row of the DB.
• If you are updating, inserting or deleting records from DB, use SqlCommand object's ExecuteNonQuery().
• Close connection (using Close()) and datareader if necessary.

Steps involved in Disconnected mode

• Create SqlConnection object, like in previous rule.
• Create SqlCommand object, previously.
• Create SqlDataAdapter object, and pass the SqlCommand object in the constructor.
• Create Dataset object.
• Fill the dataset using sqlDataAdapter's Fill() method, passing the dataset object you just created as an argument.

So for your query, like I mentioned before, dataset is out of question for updating, inserting and deleting records. So you will just create SqlConnection, SqlCommand with proper Insert DML command(check MSDN for Parameters class of SqlCommand), open connection, fire ExecuteNonQuery() method and close connection, as webwiz082 showed in his example in post number 6.

Hope it helps you a little.

Harsh:

Thank you for that very nice explanation. What you have explained to me is exactly what I do in a VB6 program now. I was hoping to use drag and drop to eliminate lots of coding like that...hmmm..

I want a disconnected connection to the db--because--I am not displaying a grid and allowing a user to update existing rows. I want to display a form, a bunch of controls a user fills in, then when they click a button, it inserts a single row into a table in my database. So I do not need nor want to be connected to the db--why should my internet surfer keep that connex open?

What I had to do to use the SQLDataSource design object was choose "specify a custom statement or stored procedure" route then it gives you 4 tabs select, delete, update and insert. I had to put a phony select statement, like select * from mytable where 1=2, then point the insert and update settings to my stored procedure (even though I won't use update).

My stored procedure takes 3 parameters and I have textbox entries for each on the form. The click event for the button assigns each textbox value to the sqlDataSource.InsertParameters collection, then calls the sqlDataSource.Insert() method. It seems to be working.

Later I modified the phony select statement to say select col1,col2,col3 from mytable order by id desc and then added a grid so I could see if my inserts were working. The select displays the 3 columns inserted with the textbox entries for the most newly added rows on top.

If you could follow this, do you think it makes sense? Is it a dangerous thing?

Quote:

---

Originally Posted by vbmom

Thank you for that very nice explanation. What you have explained to me is exactly what I do in a VB6 program now. I was hoping to use drag and drop to eliminate lots of coding like that...hmmm..

---

VB6 is not the same as web development. What's good in VB6 may not be best for ASP.NET and vice versa. When you use the drag-and-drop technique, you are blocked from learning the way this all works. As a result, no matter how many times you drag and drop to accomplish a task, you don't understand it and anything slightly more complex becomes a problem. Which is why I'd recommend the coding method rather than drag-and-drop.

Quote:

---

I want a disconnected connection to the db--because--I am not displaying a grid and allowing a user to update existing rows. I want to display a form, a bunch of controls a user fills in, then when they click a button, it inserts a single row into a table in my database. So I do not need nor want to be connected to the db--why should my internet surfer keep that connex open?

---

Use a dataset. Open the connection, fill up the dataset, fill up the connection. The dataset is available to you.

Quote:

---

What I had to do to use the SQLDataSource design object was choose "specify a custom statement or stored procedure" route then it gives you 4 tabs select, delete, update and insert. I had to put a phony select statement, like select * from mytable where 1=2, then point the insert and update settings to my stored procedure (even though I won't use update).

My stored procedure takes 3 parameters and I have textbox entries for each on the form. The click event for the button assigns each textbox value to the sqlDataSource.InsertParameters collection, then calls the sqlDataSource.Insert() method. It seems to be working.

Later I modified the phony select statement to say select col1,col2,col3 from mytable order by id desc and then added a grid so I could see if my inserts were working. The select displays the 3 columns inserted with the textbox entries for the most newly added rows on top.

If you could follow this, do you think it makes sense? Is it a dangerous thing?

---

Fine.