Noob to VWD 2005 - Insert from textbox into SQL DB

I have followed all the tutorials and I think I have a pretty good grasp on how to get info from the SQL database running on my machine in the App Data directory. My question is simple and probably the answer is nested in the 148 pages of this very informative forum.

How do I take the 15 textbox fields worth of data and insert them into the SQL db? I am pretty familiar with general sql concepts (mysql, mssql, postgresql), but I have only been in this IDE for about a week now and I need to get my project up and running quickly. I am just unfamiliar with VB in general, but I do understand the object.method concept.

I gathered that I need to make the insert statement part of the button click "event". I cannot find any good "starter" documentation on getting this process to work. I understand the grid and form and detail view really well and have tested my 2nd app with success, but it requires the db to be populated by a web front end. I have read a few MSDN links but they are not helping much, they just keep linking me back to grid and detal and form views.

Help? Please? Pretty Please?

- Chris

roninuta:

I have followed all the tutorials and I think I have a pretty good grasp on how to get info from the SQL database running on my machine in the App Data directory. My question is simple and probably the answer is nested in the 148 pages of this very informative forum.

How do I take the 15 textbox fields worth of data and insert them into the SQL db? I am pretty familiar with general sql concepts (mysql, mssql, postgresql), but I have only been in this IDE for about a week now and I need to get my project up and running quickly. I am just unfamiliar with VB in general, but I do understand the object.method concept.

I gathered that I need to make the insert statement part of the button click "event". I cannot find any good "starter" documentation on getting this process to work. I understand the grid and form and detail view really well and have tested my 2nd app with success, but it requires the db to be populated by a web front end. I have read a few MSDN links but they are not helping much, they just keep linking me back to grid and detal and form views.

Help? Please? Pretty Please?

- Chris

This link...

http://www.WebLogicArts.com/DemoSqlServerDatabase01.aspx

...is a working sample with source code that shows one simple way to CreateOne/ UpdateOne/ DeleteOne/ RetrieveOne/ RetrieveAll using ASP.NET and SQL Sever 2000.

HTH.

Thank you.

-- Mark Kamoski

|||

Ok, there are two very simple ways you can do this.

1) Using the SQLDataSource object and after creating the connection using the GUI. You can find the SqlDataSource control in the toolbox. Although easy to use I don't like it too much, prefer writing SLQ statements on my own allows for more flexibility. However, to use the SqlDataSource object you are forced to create an SQL statement using the GUI, but this can be countermanded. So what I do is use the SQLDataSource object's SelectCommand and use its SelectCommand method to write my own SQL statement. Basically SQLDataSource1.SelectCommand = "Select * from whatever". To include textboxes, like in a search your SQL statement should look like this

"Select * from Table WHERE Table.Column = '" & textbox1.text & "'"

You can extrapolate to Insert and Update.

2) Another way of using SQL in VWD2005E is to call ADODB objects. I find this the best method to do inserts and such. First you have to make a reference to the DLL, to do this you go to Website->Add Reference click on the COM tab and find Microsoft ActiveX Data Objects, pick one and click OK. To use ADODB you first declare it then set the connection string (I usually copy it from the SQLDataSource Object), and execute your sql command.

Dim Conn as ADODB.Connection

Conn.ConnectionString = "Provider..."

Conn.Execute(InsertCommand)

Holler at me for any clarifications

|||I forgot, for 2 after Dim Conn as ADODB.Connection you have to put Conn = new ADODB.Connection|||You can try this one at your button submit click event using text1 as your control.

Dim datasourceAsNew SqlDataSource()

datasource.ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString").ToString()

datasource.InsertCommandType = SqlDataSourceCommandType.Text

datasource.InsertCommand ="INSERT INTO YourTable (YourTableField) VALUES (@.YourTableField)"

datasource.InsertParameters.Add(

"YourTableField", Text1.Text)

|||You can try this one at your button submit click event using text1 as your control.

Dim datasourceAsNew SqlDataSource()

datasource.ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString").ToString()

datasource.InsertCommandType = SqlDataSourceCommandType.Text

datasource.InsertCommand ="INSERT INTO YourTable (YourTableField) VALUES (@.YourTableField)"

datasource.InsertParameters.Add(

"YourTableField", Text1.Text)

|||

this last example was awesome. It was implistic and really helped. I understand much more now. I new that I was going to have to use the parameters.add functionality. I think I am messing up on my connection string though and it is probably just not set correctly. I get an unhandled exception here is the error message:

System.NullReferenceException was unhandled by user code
Message="Object reference not set to an instance of an object."
Source="App_Web_1cnoj4u7"
StackTrace:
at _Default.SendForm_Click(Object sender, EventArgs e) in D:\Documents and Settings\ronin\My Documents\Visual Studio 2005\WebSites\ADRequest\Default.aspx.vb:line 20
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Here is line 20 - the connection string:

datasource.ConnectionString = ConfigurationManager.ConnectionStrings("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\adrequest.mdf;Integrated Security=True;User Instance=True").ToString()

I am using a built in SQLDB file that comes with VWD2005E, and the file is called adrequest.mdf, what am I missing here.

TIA,

Chris

|||

You are wrong with declaring with your connection string. Your connection string name must be like in my part:

phiwugdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("phiwugConnectionString1").ToString()

To find the name of your connection string, try to do this, add a blank web form on your project, go to database explorer and go to the table of your database, click the table and drag and drop this to the blank web form. You will see that it will create a table on your blank web form and a control name SqlDataSource - SqlDataSource1. Click it then click the arrow next to it and you will see Configure Data Source and click it. Next a wizard will appear. The connection string name that will appear on the drop down list box is the connection string name you will write on it.

Be sure to mark as answer on the post you think that answers your question so that the poster will have credit for it. Don't hesitate to inquire.

Best regards,

|||

This may be a silly question but given this code:

Dim datasource As New SqlDataSource()
datasource.ConnectionString = ConfigurationManager.ConnectionStrings("connection2").ToString()
datasource.InsertCommandType = SqlDataSourceCommandType.Text
datasource.InsertCommand = "INSERT INTO ticket (sr) VALUES (@.srnum)"
datasource.InsertParameters.Add("srnum", TextBoxSRNum.Text)

is the data actually being posted to the DB or is the insert statement just being setup? Do I need to actually execute the statement through another line?

TIA - Chris

|||

Nevermind

I just added datasurce.Insert() to the end of the event - that fixed it - thanks everyone for helping out!

- Chris

|||

You can even add this one and put your datasource.insert() on a structured error handling procedure:

Dim

rowsaffectedAsIntegerTry

rowsaffected = datasource.Insert()

Catch exAs Exception'TODO: Probably write a windows error log

Server.Transfer(

"error.aspx") <--create a web form for error messages/siteFinally

datasource =

Nothing

EndTry

If rowsaffected <> 1Then

Server.Transfer(

"error.aspx") <--create a web form for error messages/siteElse

Server.Transfer(

"success.aspx") <--create a web form for success messages/site

EndIf