Text from TextBox to Database

[SarH]

Hi, I'm totally new to asp.net, I'm using MS visual web developer 2005 express edition. All I want to do is take the text from a few text boxes and store it into a database. I'm using an sql database from within visual web developer. the textboxes are all called TextBox1, TextBox2, etc,,, The table is testInfo, the DB is called TestDB and the fields are Fname, Lname, FavColour, Age. And I have a submit button.

I've tried a few online tutorials but none seem to help, I'd post the code but I'm at work now. I really want to learn how to do this so I can at least expand on it as nothing I've tried is given me any sort of results. I have managed to connect to a Access database in visual basic.net, but this doesnt seem the same. Could someone point me to a very basic tutorial or step by step guide? or just give me any help?

Thanks for your time,
Sarah

[mcmcom]

what language are you working with VB or C# ?

you should start looking at the SqlConnection class and the SqlCommand class. Together they provide the capabilities to connect to an SqlServer from asp.net.

i'll give you a quick example (note this is C# but the classes and methods are the same in both languages) :


//you first create a connection object passing it the connection string
SqlConnection conn = new SqlConnection(myConnectionString); //look up connection strings to see how to format this
//now we need an sql statement
string strSql = "INSERT into myTable(field1, field2) values (@field1, @field2);";
//create a command to perform actions
SqlCommand cmd = new SqlCommand(strSql,conn);
//Add parameters to fill @field1 and @field2
cmd.Parameters.AddWithValue("@field1",this.TextBox1.Text.ToString()); //assuming its a string
cmd.Parameters.AddWithValue("@field2",this.TextBox2.Text.ToString());
//now open the connection and execute the query
try{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex) //catch any exceptions
{
throw(ex);
}


thats basically how it works. but read the MSDN on SqlCommand and SqlConnection they have simple examples that can get you started.

hth,
mcm

[SarH]

Hi thanks for the reply. Here's the code I've been using...
' Create a new Connection and SqlDataAdapter
Dim myConnection as SqlConnection
Dim mySqlDataAdapter as SqlDataAdapter


myConnection = new SqlConnection("server=(local)\VSdotNET;Trusted_Connection=yes;database=northwind")
mySqlDataAdapter = new SqlDataAdapter("Select * from Customers", myConnection)

Dim myDataSet as DataSet = new DataSet()
Dim myDataRow As DataRow

' Create command builder. This line automatically generates the update commands for you, so you don't
' have to provide or create your own.
Dim myDataRowsCommandBuilder as SqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter)

' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
' key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

mySqlDataAdapter.Fill(myDataSet, "Customers")

myDataRow = myDataSet.Tables("Customers").NewRow()
myDataRow("CustomerId") = "NewID"
myDataRow("ContactName") = "New Name"
myDataRow("CompanyName") = "New Company Name"

myDataSet.Tables("Customers").Rows.Add(myDataRow)

Thats the code I'm using straight from the microsoft how to's http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/UpdateDataFromDB.aspx

I'm in work now so it's not the exact code I'm using as I've changed the myConnection = new SqlConnection("server=(local)\VSdotNET;Trusted_Connection=yes;database=northwind") to connect to my data base. I've also changed all the parts that mention the other database to mine and the textbox names ect,,,

I'm in work again now so this is from memory. But one of the errors is here... mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
It says MissingSchemaAction parameter has not been set.
if I comment that part and run it, when I fill in the text boxes and click ok an error comes up saying something about maybe the mySQL server isnt set up for remote use. I'm using a clean install of Visual studio and I assume the server software came with it, any ideas?

If that doesnt explain my problems to well, I'll post a more detailed one when I get home.

Thanks,
Sarah

[shivkumar]

Please try by deleting following line
.............
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
........................
and add the following line
.....................
mysqldataadapter.InsertCommand = (New SqlCommandBuilder(mysqldataadapter)).GetInsertCommand()
............
before

myDataSet.Tables("Customers").Rows.Add(myDataRow)

[SarH]

Hi, I've done that and now I'm getting this error "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections." Any Ideas?

Thanks,
Sarah

edit--- <%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Create a new Connection and SqlDataAdapter
Dim myConnection As Data.SqlClient.SqlConnection
Dim mySqlDataAdapter As Data.SqlClient.SqlDataAdapter


myConnection = New Data.SqlClient.SqlConnection("Data Source=Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\HP_Administrator\My Documents\Visual Studio 2005\WebSites\Test2\App_Data\TestDB.mdf;Integrated Security=True;User Instance=True")
mySqlDataAdapter = New Data.SqlClient.SqlDataAdapter("Select * from TestTBL", myConnection)

Dim myDataSet As Data.DataSet = New Data.DataSet()
Dim myDataRow As Data.DataRow

' Create command builder. This line automatically generates the update commands for you, so you don't
' have to provide or create your own.
Dim myDataRowsCommandBuilder As Data.SqlClient.SqlCommandBuilder = New Data.SqlClient.SqlCommandBuilder(mySqlDataAdapter)

' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
' key & unique key information to be retrieved unless AddWithKey is specified.
'mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

mySqlDataAdapter.Fill(myDataSet, "TestTBL")

myDataRow = myDataSet.Tables("TestTBL").NewRow()
myDataRow("Title") = "TestInformation1"
myDataRow("Description") = "TestInformation2"

mySqlDataAdapter.InsertCommand = (New Data.SqlClient.SqlCommandBuilder(mySqlDataAdapter)).GetInsertCommand()

myDataSet.Tables("TestTBL").Rows.Add(myDataRow)

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>test</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" /></div>
</form>
</body>
</html>
Theres all the code. the error "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections." points at this part mySqlDataAdapter.Fill(myDataSet, "TestTBL") I'm a bit lost now.