Question SQL Databse

[longbow2000]

Hi, my first time using a SQL Database in vb.net 2005. I have added a new item “SQL Database” created Database1.mdf and added a table “Users” with 3 columns UserID, User Name, User Password. I have added data manually in the table “Users” to test.

Connecting via the following code:
Dim myConnection As SqlConnection
myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True; Connect Timeout=30;User Instance=True;")

How do I add data at runtime, i.e. check number of rows, add a new row with a new users’ info (User Name, User Password)? How do I edit a cell value (a password) at runtime?

Thanx in advance.

 

[jmcilhinney]

Follow the Data Walkthroughs link in my signature for some useful instruction an working with data. Just make sure that you're looking at the correct version of the page.

 

[longbow2000]

re SQL Database

I have tried the following, however, after running the command and when goto “Show Table Data” the new row is not added. What am I missing?

Dim myConnection As SqlConnection
myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True; Connect Timeout=30;User Instance=True;")

Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter("Select * from Users", myConnection)

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

Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(mySqlDataAdapter)

mySqlDataAdapter.Fill(myDataSet, "Users")
myConnection.Open()

myDataRow = myDataSet.Tables("Users").NewRow()
myDataRow("UserId") = "3"
myDataRow("User Name") = "Guest2"
myDataRow("User Password") = "65656"

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

myConnection.Close()

 

[jmcilhinney]

You aren't saving anything. You open a connection and retrieve data from the database into a DataTable, then you add a new row to that DataTable, but you never actually save the changes back to the database. I suggest that you read a bit more about the SqlDataAdapter class.