I am going to try to get through this via inline answers:
Rich said:
Thanks. Do you mean explicitly create the command as in
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * from tbl1"
da.Fill(ds, "tbl1")
First, I would not suggest "SELECT *" in any application. There are very few
times when you are actively working with every single row in a table, which
answers your next question. You should only grab the data you need for a
particular function.
but suppose tbl1 contains 100,000 rows?
Answered above. Do not grab all 100,000 rows unless you are really going to
do something with them.
And all I want to do is to insert a
row into tbl1 from the client app?
You can start with a DataSet that matches the table that is empty. Add the
row and then call Update(). If you have the proper INSERT command, it will
see the new row and add it. This is a VERY rough example that assumes a
connection object named conn.
'TODO: Add row here
'This is best done explicitly, column by column
Dim da As newSqlAdapter()
Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES (@col1,
@col2)")
cmd.Parameters.Add(new SqlParameter("@col1", value1))
cmd.Parameters.Add(new SqlParameter("@col2", value2))
conn.Open()
da.Update(dataTableInQuestion)
I am thinking I want to get just the
table without bringing in any rows and then update the table:
Similar to example above. If ALL you are doing is adding a record, then
empty DataSet that matches the table (strongly typed datasets are easier to
use in these cases, as you get dotted notation and Intellisense, but
non-strongly typed will work) and call Update().
da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"
The SQL above will not work. Look at the example I have given.
da.InsertCommand.Parameters.Add(New SqlParameter("@a, SqlDBtype.varchar,
30,
"colA")
This part is fine.
da.InsertCommand.Parameters("@a").Value = "testing"
da.Update(ds, "tbl1")
You have the right idea here as well. The main thing you need to get correct
is the insert statement, which will follow this basic format:
INSERT INTO tableName (column1, column2)
VALUES (value1, value2)
Your basic change, other than using real names
, is to make the values as
parameters @value1, @value2 and create a SQL Parameter for each parameter in
the statement.
I am setting this scenario and ask to make sure I understand you
correctly,
that to get the table structure of "tbl1", in order to insert a row I get
the
structure of the table with da.Fill rather than
da.FillSchema(ds, SchemaType.Source, "tbl1") ?
If you want to get an empty DataSet, without using a strongly typed DataSet,
you can do something like this:
Dim conn As new SqlConnection("connection string here")
Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
Dim da As new SqlDataAdapter(cmd)
Dim ds As new DataSet("DataSet name here")
Try
conn.Open()
da.Fill(ds)
Finally
conn.Dispose()
End Try
A strongly typed DataSet can then be created (to avoid calling the database
for definition alone), by adding the statement:
ds.WriteXml("path to write to here")
You then create a new DataSet in your project, open the XMLyou saved off,
and copy the tables from the one you saved to the new one in your project.
You can then completely avoid filling an empty DataSet for an INSERT.
NOTE: The above steps are migratory. If you want to avoid writing the fill
routine, you can simply create a new DataSet, add a data connection to
Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
Express), should be the same in Visual Studio 2003). You can then drag a
table from the database onto the DataSet and have it automagically create
the table definition.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside of the box!
*************************************************
