using SQL query with VB.net

[skarosi]

Hi all
I have a question regarding VB and SQL. I think, and i hope, that it is a simple task, but i just cant get it to work!
I have created a DB in SQL Server, nothing too fancy, just a table and a query for now, and i want to use these in VB. I have managed to accss the table and present the data on the grid, but i just cant get the query to work! i have saved it as a ".sql" file using the query analyser of SQL Server.
the data will also been updated from VB, so a static table will not do the work. Is there a way to enter the SQL code directly in the VB applications, or i have to create a query and access it with VB?
Could anyone pls help me on this one? thanks

 

[Dashley]

skarosi,

You can enbed the Sql into your vbcode or use it as a Sql Server Stored procedure or read it form the file.

To answer you quesiton yes you can assign the sql statement to a value it you code.

Dim mysql as string =("Select firstname from names")

Then from in your app use it to populate a dataset etc.

Dim DA1 As OdbcDataAdapter = New SqlDataAdapter(mysql, myconn)

 

[skarosi]

thanks for the reply Dashley, but i am not so sure what i can do with the code u just provided. where does the sql code goes?
to be honest i havent tried it yet, i will and get back to you.
thanks

 

[TipGiver]

You can create an SQL DataAdapter for your database (invisible to the form) and add a new query (in database). There is by befault one called .fill and .getData that the both take a dataTable. This could work like. sqldataadapter1.fill(dt)
I dont know what is better for you. Till now i do like Dashley suggests. A working example can be:

* ASSUME *
- Database location is C:\db.mdf, using Integrated security.
(if not you must provide in the connectionString the username and password)
- You want the firstname,lastname and address of the people aged (age field) greater than 28.
- The table's name in the database is ContactTbl.

        Dim cnStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\db.mdf";Integrated Security=True;User Instance=True"

        Dim cn As New SqlConnection(cnStr)
        cn.Open()

        Dim qs As String = "SELECT firstname, lastname, address FROM ContactTbl WHERE age > 28"

        Dim da As New SqlDataAdapter(qs, cn)
        Dim dt As New DataTable
        da.Fill(dt)

        ' Release the resources
        cn.Close()
        cn.Dispose()
        da.Dispose()
    End Sub

Hope this helps you!

 

[skarosi]

thanks m8,
i will give it a try on the first chance i get.
cheers