|
Hello,
I'm using:
Microsoft Visual Basic 2008, Professional Edition
I've run into a problem I can't figure out. I'm using VB 2008. I found the following sample code and was attempting to use it but am not understanding how to setup the query, I bolded it below.
I understand I need to go in and set up my own customer query in my database in order to use it like the example below.
Q) Do I setup the query on my table adapter or on the original table in the database (Maybe even some good info on how to setup these queries)?
Q)If I'm understanding this, once I setup the query correctly I should just have to put in a variable and it fill find all row(s) with that parameter that I can delete?
Q) When using the @XXXX in the query, do I have to then use that as my variable to pass the data I'm looking for or can I use and variable?
' Locate the row to delete.
Dim oldRegionRow As NorthwindDataSet.RegionRow
oldRegionRow = NorthwindDataSet._Region.FindByRegionID(5)
' Delete the row from the dataset
oldRegionRow.Delete()
' Delete the row from the database
Me.RegionTableAdapter.Update(Me.NorthwindDataSet._Region)
I was able to setup the following query on my database but per the example, I'm not sure I did it correctly:
SELECT Gene, FindMethod, TDiscovery, TReplicate, AA_A, AA_T, AA_G, AA_C, ImageFileName, Description, SpxReplicationItem, IconFileName, Complete, Image, Icon, Type,
UniqueID, Chromosome, Rarity, ParentGene, Name
FROM PlazimorphGenes
WHERE (Gene = @genename)
I then tried using it as this:
tRow = PlazimorphDBDataSet.PlazimorphGenes.FillByGene(genename)
and got an error that it is not a member of the database.
Thank you for all the help! I've been searching this forum and the web in general and have found the best help here!
modified 1-Dec-12 15:44pm.
|
|
|
|
|
Have you got a link to the code you are trying?
I suspect that you require a Stored Procedure on the Database:
USE BDNAME
GO
CREATE PROCEDURE SelectByGeneName(
@GeneName VarChar(100)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT Gene, FindMethod, TDiscovery, TReplicate, AA_A, AA_T, AA_G, AA_C, ImageFileName, Description, SpxReplicationItem, IconFileName, Complete, Image, Icon, Type,
UniqueID, Chromosome, Rarity, ParentGene, Name
FROM PlazimorphGenes
WHERE Gene = @genename
RETURN;
END
GO
I dont use the automatic binding etc...so I would retreive the data like this:
Public Function GetByGeneName(name as String) As DataTable
Dim cn as New SqlConnection(ConnStr)
Dim cmd as New SqlCommand
Dim dt as New DataTable("PlazimorphGenes")
Try
cn.Open()
With cmd
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "SelectByGeneName"
.Parameters.AddWithValue("@GeneName", name)
Dim da as New SqlDataAdapter(cmd)
da.Fill(dt)
End With
Catch ex as Exception
...
Finally
If cn.State <> ConnectionState.Closed Then cn.Close()
End Try
Return dt
End Function
I don't speak Idiot - please talk slowly and clearly
"I have sexdaily. I mean dyslexia. Fcuk!"
Driven to the arms of Heineken by the wife
|
|
|
|
|
I was attempting to use the sample code above like this:
Dim tRow As PlazimorphDBDataSet.PlazimorphGenesRow
Dim genename As String
genename = MTBGeneLabel.Text.ToString()
tRow = Me.PlazimorphGenesTA.FillByGene(Me.PlazimorphDBDataSet.PlazimorphGenes, genename)
tRow.Delete()
Me.PlazimorphGenesTA.Update(Me.PlazimorphDBDataSet.PlazimorphGenes)
I did go into the database designer and create the specific query on the table. It looks SELECT code section you posted above.
The error I'm getting is specific to this line:
tRow = Me.PlazimorphGenesTA.FillByGene(Me.PlazimorphDBDataSet.PlazimorphGenes, genename)
I'm getting
"Value of type 'Integer' cannot be converted to 'PlazimorphAdmin.PlazimorphDBDataSet.PlazimorphGenesRow'"
I thought Visual Studio would automatically add the stored procedure. Maybe I need to add it as you suggest.
If I change the line to this:
tRow = Me.PlazimorphGenesTA.FillByGene(genename)
I get the following two errors:
"Argument not specified for parameter 'genename' of 'Public Overrideable Overloads Function FillByGene(dataTable as PlazimorphDBDataSet.PlazimorphGenesDataTable, genename as String) As Integer'."
and
"Value of type 'String' cannot be converted to 'PlazimorphAdmin.PlazimorphDBDataSet.PlazimorphGenesDataTable'."
And If I use this version:
tRow = Me.PlazimorphGenesTA.FillByGene(tPlazimorphGenes, genename)
I get
"Value of type 'Integer' cannot be converted to 'PlazimorphAdmin.PlazimorphDBDataSet.PlazimorphGenesRow'"
The query I have attached to the table is this:
SELECT Gene, FindMethod, TDiscovery, TReplicate, AA_A, AA_T, AA_G, AA_C, ImageFileName, Description, SpxReplicationItem, IconFileName, Complete, Image, Icon, Type,
UniqueID, Chromosome, Rarity, ParentGene, Name
FROM PlazimorphGenes
WHERE (Gene = @genename)
I have a solid background in visual basic programming but haven't work this much with a database.
|
|
|
|
|
OK, are you generating the DataSet from the Visual Studio Wizard?
This exposes you to the evils of the BindingSource, TableAdapter, and TableAdapterManager classes. (I and many others here avoid them like the plague).
This may be a usefull article: A Detailed Data Binding Tutorial (CP)[^]
Rolling your own database access is not so hard:
...
Dim cn As New SqlConnection("myConnectionStringHere")
Dim cmd As New SqlCommand()
Try
cn.Open()
With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandTExt = "your SELECT query here"
.Parameters.AddWithValue("@genename", yourGeneNameVariable)
Dim dt As New DataTable("PlazimorphGenes")
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
plazimorphGenesDataGrid.DataSource = dt
End With
...
you can then look after the delete query yourself too
...
Dim dr as DataRow = tRow(0)
Dim queryString as String = "DELETE PlazimorphGenes WHERE Id = @Id"
Dim cn As New SqlConnection("yourConectionString")
Dim cmd As New SqlCommand
Try
cn.Open()
With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandText = queryString
.Parameters.AddWithValue("@Id", CInt(dr("Id")))
.ExecuteNonQuery()
End With
UpdateDataGrid(...)
I don't speak Idiot - please talk slowly and clearly
"I have sexdaily. I mean dyslexia. Fcuk!"
Driven to the arms of Heineken by the wife
|
|
|
|
|
Thank you for the link! Yes, I've used the data wizard. It's been a few years working on such a big database project as a 'hobby project'. Most of the tutorials and examples use it.
I've been able to create a DELETE function on the tables and its working, also a INSERT on.
The next step I've having issue with is filling a combo box with a query. I've added the query to the table but can't get it/figure our how to apply it to filtering data to the combo box. I also tried using the 'Filter' on the Binding source but it doesn't seem to work.
|
|
|
|
|