How to put query in database?

[Wim]

Hi,

I would like to make and put a query in my database by VB.NET code.

Is this possible?

Thanks for your help,

wim

 

[Dominique Vandensteen]

do you mean create a stored procedure?


Dim conn As New SqlConnection(myConnectionString)
conn.Open()
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "CREATE PROCEDURE myNewStoredProcedure AS select * from
mytable"
cmd.ExecuteNonQuery()
conn.Close()


Hope this helps

Dominique

 

[Cor]

Hi Wim,

Something as this
\\\
Dim Conn As New SqlConnection(connString)
Dim myCommand As New SqlCommand("select count(*) from tblMessages", Conn)
myCommand.Connection.Open()
Dim count As Integer = CInt(myCommand.ExecuteScalar())
Conn.Close()
///

I hope this helps?

Cor

 

[Wim]

Hi Cor and Dominique,

I just need to make a new query and save this in an access database....

Is your code doing this?

Regards,

wim

 

[Wim]

the access database is located at
"C:\backup Compaq MV500\TAGS\Access\2003.mdb"

 

[Dominique Vandensteen]

do you want to put your query as data in a table?
if so see code
if put it in access as a stored procedure then no can do...



Imports System.Data.OleDb


dim conn as New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;")
conn.Open()
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "insert into mytable (columnname) values ('the query...')"
cmd.ExecuteNonQuery()
conn.Close()


Dominique

 

[Wim]

No I don't want to put data in the table...

only put a new query in the access database and use it later..

So you think this is impossible??

Regards

wim

 

[Dominique Vandensteen]

you can do something alike stored procedures with access (queries)
but I don't know if you can create them from vb.net code

to call them:

dim conn as New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;")
conn.Open()
Dim cmd As OleDbCommand = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "myquery"


Dominique

 

[Wim]

ok ,thanks a lot

wim

you can do something alike stored procedures with access (queries)
but I don't know if you can create them from vb.net code

to call them:

dim conn as New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;")
conn.Open()
Dim cmd As OleDbCommand = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "myquery"


Dominique

 

[Wim]

Hi Dominique,

I get error "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll" at
conn.open()

what can i do?

Regards,

wim

Imports System.Data.OleDb

Public Class Form1

Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

'

'Form1

'

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(292, 266)

Me.Name = "Form1"

Me.Text = "Form1"

End Sub

#End Region

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim conn As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\backup
Compaq MV500\TAGS\Access\2003.mdb")

conn.Open()

Dim cmd As OleDbCommand = conn.CreateCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "select * from import"

End Sub

End Class

 

[Dominique Vandensteen]

9 out of 10 your db is in use

do a try/catch and check the exception message and stacktrace for more
info...


hope this helps

dominique

 

[Wim]

Hi Dominique,

I tried and got this errors :

Kan het installeerbare ISAM-bestand niet vinden.
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at make_query_20_jan_2004.Form1.Form1_Load(Object sender, EventArgs e) in
C:\Documents and Settings\Eigenaar\Mijn documenten\Visual Studio
Projects\make query 20 jan 2004\Form1.vb:line 63

Any idea?

regards,

Wim



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim conn As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\backup
Compaq MV500\TAGS\Access\2003.mdb")

Try

conn.Open()

Catch ex As Exception

TextBox1.Text = (ex.Message)

TextBox1.Text = TextBox1.Text & (ex.StackTrace)

'conn.Close()

End Try

Dim cmd As OleDbCommand = conn.CreateCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "select * from import"

End Sub

 

[Cor]

Hi Wim,

What is on line 63 it gives me a little bit the idea that you did make a
connection with the designer and that you try to overule that with a new
connection string. (I tried that once and that did work).

But it seems to me that you get an error on line 63 (because when you use
the designer and a form, that is at the beginning of the code).

Just an Idea.

Cor

Kan het installeerbare ISAM-bestand niet vinden.

Are you sure Domenique understand this?

 

[Wim]

Hi Cor,


How can I check if I made a connection with the designer?

ADO.NET and VB.NET It is new to me, so maybe I make basic mistake....

Thanks,

Wim

 

[Wim]

Cor,

In this other program I get also an error at conn.open

No error information available: DB_SEC_E_AUTH_FAILED(0x80040E4D).
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at database_20_jan_2004.Form1.btnDatabase_Click(Object sender, EventArgs
e) in C:\Documents and Settings\Eigenaar\Mijn documenten\Visual Studio
Projects\Laptop\database 20 jan 2004\Form1.vb:line 80

Private Sub btnDatabase_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDatabase.Click

Dim ds As New DataSet()

ds = New DataSet("import")

Dim Connectionstring As String

Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;Mode=Share
Deny None;Extended Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine
Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

'Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=C:\TAGS\Access\2003.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet
OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase
Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale
on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False"

Dim connection1 As OleDb.OleDbConnection = New
OleDb.OleDbConnection(Connectionstring)

Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select * from
import")

command1.CommandType = CommandType.Text

Try

connection1.Open()

Catch ex As Exception

txtError.Text = ex.Message & vbCrLf

txtError.Text = txtError.Text & ex.StackTrace

End Try

'command1.Connection = connection1

'Dim OleDbDataAdapter1 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter()

'OleDbDataAdapter1.SelectCommand = command1

'OleDbDataAdapter1.TableMappings.Add("Table", "import")

'OleDbDataAdapter1.Fill(ds)

'Dim datagrid1 As New DataGrid()

'datagrid1.SetDataBinding(ds, "import")

End Sub

 

[Cor]

Hi Wim,

Can you only try this (I am not sure if it is direct the solution)
But than we can see it it connects.

Dim connection1 As OleDb.OleDbConnection = _

New OleDb.OleDbConnection(Connectionstring)

Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select * from
import")

-Delete rows above

command1.CommandType = CommandType.Text

-Delete row above
dim conn as New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;")
Dim OleDbDataAdapter1 As OleDb.OleDbDataAdapter = _
New OleDb.OleDbDataAdapter("Select * from import",conn)
dim ds as new dataset
Try
OleDbDataAdapter1.Fill(ds)

connection1.Open()

-Delete row above

 

[Wim]

Hi Cor,

I tried this code and this didn't give any error's anymore......
Do you now what was wrong? the conn.open? or the OleDbCommand?
What to do to showthe dataset in Datagrid1 on the form?

thanks,

wim
Private Sub btnDatabase_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDatabase.Click

'ds = New DataSet("import")

'Dim Connectionstring As String

'Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;Mode=Share
Deny None;Extended Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine
Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

'Dim connection1 As OleDbConnection = New OleDbConnection(Connectionstring)

'Dim command1 As OleDbCommand = New OleDbCommand("Select * from import")

'command1.CommandType = CommandType.Text

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\backup Compaq MV500\TAGS\Access\2003.mdb;")

Dim oleDbDataAdapter1 As OleDbDataAdapter = New OleDbDataAdapter("select *
from import", conn)

Dim ds As New DataSet()

Try

oledbdataadapter1.Fill(ds)

'conn.Open()

Catch ex As Exception

txtError.Text = ex.Message & vbCrLf

txtError.Text = txtError.Text & ex.StackTrace

End Try

'Try

' connection1.Open()

'Catch ex As Exception

' txtError.Text = ex.Message & vbCrLf

' txtError.Text = txtError.Text & ex.StackTrace

'End Try

'command1.Connection = connection1

'Dim OleDbDataAdapter1 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter()

'OleDbDataAdapter1.SelectCommand = command1

'OleDbDataAdapter1.TableMappings.Add("Table", "import")

'OleDbDataAdapter1.Fill(ds)

'Dim datagrid1 As New DataGrid()

'DataGrid1.SetDataBinding(ds, "import")

End Sub

 

[Cor]

Hi Wim,

I asume it is a winforms datagrid than just

'OleDbDataAdapter1.Fill(ds)
'Dim datagrid1 As New DataGrid()
'DataGrid1.SetDataBinding(ds, "import")

Replace row above by
DataGrid1.datasource =ds.tables(0)

I hope this helps

Cor

 

[Wim]

hi Cor,

thanks this is working good!

Now I need to find out how to copy records from 1 table (table import) to
another table (table dummy) by a query.
The second table must be cleared each time before I run the query.....

The first table (import) has a field "time" and what I want is to select
only those records between time1 and time2.

If possible Time1 and Time2 are date variable that I can give a value by
dtaepicker or textbox ...


I'll buy second book special for ADO.NET because My VB.NET book learns me
little about it..


Thanks again for your help,

Wim