How to make multiple queries to the database..

  • Thread starter Thread starter sona
  • Start date Start date
S

sona

Can anyone help?

I am trying to do 2 things within the same sub method.
Firstly, having made the connection to the SQL Server
database and creating a new adapter, i have made a query
to insert values in the database. This works fine.
Now i need to get some data from this database(i.e select
id from ...), but not sure how to make another SQL string
and send it to the same data adapter? i know how to write
the sql command but dont know how to use the existing
connection..

Is it easier to make a seperate sub method to take care
of this? if so, how do i pass parameters?
Am i making any sense? if so, can anyone help?

Thanks.
 
For SQL Serve you can send multiple commands at once.
Just separate them with a semi-colon.

Select * From Table1;Select * From Table2;Select * From Table3;

If you are trying to get back the last IDentity value use:
Select Scope_Identity()
as one of your commands.
 
Dim cn as New SqlConnection("SomeConnectionString")
Dim cmd as New SqlCommand("SELECT * From whatever", cn) 'Probably want to
use Stored Procs though...
Dim da as New SqlDataAdapter(cmd)

Dim ds as New DataSet

da.Fill(ds, "MyFirstTable")

cmd.CommandText = "SELECT * FROM SomeOtherTable"

da.Fill(ds, "MySecondTable")

You can use the same connection and command and just change the command
text. However, in most instances you would probably want to use seperate
DataAdapters, but sticking to the question at hand, this will work for you.
You can also change the connection and use the same command if you had cause
to do so.

When you ask about Parameters, I'm assuming you mean parameters in SQL. For
the sake of argument, just assume you are using SQL Server.

'Pretend this is the sql: SELECT * FROM myTable WHERE Response = @SomeParam
cmd.Parameters.Clear (unless you are just changing the value)
cmd.Parameters.Add("SomeParam", SqlDBType.Varchar).Value = "blah"

ALso, just to note, if you want to use Stored Procs instead, simply Change
the CommandType of the Command Object to StoredProcedure.

HTH,

Bill
 
I usually find it handy to create a stored procedure that does the multiple
selects, thus calling the proc from my code will fill my dataset with two
tables. See [1] for an example

--
Patrik Löwendahl
cshrp.net - " Elegant code by witty programmers "
cornerstone.se - " IT Training for professionals "

[1] http://www.cshrp.net/content.aspx?showID=613
 
You can write multiple selects, separated by a semicolon. You can also have
your insert sproc return the value for you with a Select after the insert.
Take a look at the command object's UpdatedRowSource property.
 
Thanks but what i realy want to do is get some value from
the database, such as a name or sum of prices etc and
display that data as a label. I have already created a
label, know the sql statement but how do i put this data
retrieved as the label content?

am i making sense again?

thanks.
 
Back
Top