|
I'm not sure about your question.
Please show your class-definition to make it clearer ...
|
|
|
|
|
That's a Function again
Public Function Add_Ne(ExecQ As String, paramxx As String, TextN As String) As Boolean
Try
con.Open()
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = ExecQ
.Connection = con
.Parameters.AddWithValue(paramxx, TextN)
.ExecuteNonQuery()
End With
If con.State = ConnectionState.Open Then
con.Close()
End If
Return True
Catch ex As Exception
MessageBox.Show(ex.Message)
Return False
End Try
End Function
That's work but for 1 params and 1 textbox I call it in my form by
xo.Add_Ne("INSERT_ACCOUNT", "@AccountID", TXTAccNumber.Text)
And it's work What I mean If I have 15 params Is it true to put 15 params and 15 textbox as string in main of function or there are easier ways?
|
|
|
|
|
Mohamed Hamdy wrote: If I have 15 params Is it true to put 15 params and 15 textbox as string in main
of function Yes.
Why is that a problem?
Mohamed Hamdy wrote: or there are easier ways? Ask someone else to write them.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Mohamed Hamdy wrote: What I mean If I have 15 params Is it true to put 15 params and 15 textbox as string in main of function or there are easier ways?
Basically no. Each parameter need to be given a proper value. This mapping has to be done somehow. Some ORM tools allow different kinds of mappings but in the end a parameter needs a value, regardless how it's done.
Of course you can simplify the situation. If this is for example Windows Forms use for example Tag property and store the name of the database field in it. Then when inserting or updating, loop through the objects in a window and gather the values to proper parameters based on the tag.
Better yet, derive your own object where you define all the properties you need and define necessary values for those and voila you have a dynamic value gathering from UI.
|
|
|
|
|
There are quite some examples on how to execute stored procedures
- Change the commandtype to CommandType.StoredProcedure.
- Change "ExecQ" to "ProcedureName". Assign that to .CommandText.
- Add a Parameter.WithValue for all 15 parameters. (Yes, 15 lines of code).
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All,
i am new in VBA programing,when i run excel 2003 macros in 2010 it will shows run time error Can't find project library.i don't knowit may be problem of library,can any Help to solve this problem.
|
|
|
|
|
Well,
Without the specific error message I can only guess ... try under the Developer tab of the open Excel App opening the Visual Basic (Alt+F11) interface. Make sure the Object Browser is open somewhere too (F2).
Now, under the <all libraries=""> textbox in the browser panel see the "Search" box. Type in any of the keywords found in your suspect procedure and see how they become hierarchically disposed in the "Search Results" window.
If one of them doesn't return any results, then THAT's a missing module. Often the module isn't included in the libraries of the spreadsheet because the reference hasn't been embedded or viewing of the library has been locked by the programmer so you can't see the code. But if it's on the system, it'll be in a list where that dropdown <all libraries=""> can be accessed. Sometimes there'll even be the name of the "missing" library so all you have to do is scroll down in the list of available .ocx, .dll, etc there and tick it to include it.
You can also add libraries that you think might hold functions and methods that would replace the one's that are funky. And you'd do that here.
Hope this works for you.
|
|
|
|
|
Alright, I'm clueless here. This is Day 1.
I'm experimenting with a Data Access Layer DLL, so I can centralize all my database stuff in one location.
So I wrote a dal.dll with a context and Model using Entity Framework 6.0, I'm not sure if I got that right either.
So in another project, I referenced my dal.dll, and experimented with a function to load data in a DBSet of the Model
So I wrote this in my experiment, which is failing by the way. I can't figure out how to write the query, or where to place the query, in the DAL or the project using the DAL,
and how to package the data for use else where. It seems to sort of work, it told me that my database didn't match the model, so I must be connecting.
Public Shared Function load_structure_MovieInfo_Array( _
ByRef m As DbSet(Of Movie),
ByVal sXc As structure_m_Index_QueryString,
ByVal p_Type As mv_shared.MovieType) As Integer
Dim dwExitCode As Integer = 0
Dim context As MovieContext = New MovieContext()
Dim query =
From movies In context.Movies
Where movies.flv = 1
Order By movies.MovieName
Select
MovieID = movies.MovieID,
MovieName = movies.MovieName,
MovieType = movies.MovieType,
MovieDeliveryType = movies.MovieDeliveryType,
MoviePath = movies.MoviePath,
MovieURL = movies.MovieUrl,
MoviePostage = movies.MoviePostage,
MovieThumbnail = movies.MovieThumbnail,
MovieLarge = movies.MovieLarge,
MoviePlaceHolder = movies.MoviePlaceHolder,
MovieBitRate = movies.MovieBitRate,
MovieTime = movies.MovieTime,
MovieSize = movies.MovieSize,
MovieDescription = movies.MovieDescription,
MovieCount = movies.MovieCount,
MovieContributor = movies.MovieContributor,
MoviePostDate = movies.MoviePostDate,
MovieIcon = movies.MovieIcon,
movie_Parameters = movies.Movie_Parameters
m = query.Set(Of Movie)()
Return dwExitCode
|
|
|
|
|
DbSet should only ever be referenced in your DbContext class.
The query is easy enough but I have no idea what you're function header is saying or asking for. So, here's what a query normally looks like:
Public Shared Function GetAllMovies() As IEnumerable(Of MovieItem)
Using context As New MovieContext()
Dim results = From item In context.Movies
Where item.flv = 1
Order By item.MovieName
Select New MovieItem With
{
.MovieId = item.MovieId,
.MovieName = item.MovieName,
...
}
Return results.AsEnumerable()
End Using
End Function
|
|
|
|
|
Thanks Dave! Thats exactly what I wanted to do. Say would it be possible to validate my Context, I may be way off on it.
I get this error after using the iEnumerator(of MovieContext) in your reply:
Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery1[VB$AnonymousType_0 19[System.Int32,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.Int32,System.String,System.DateTime,System.String,System.String]]' to type 'System.Collections.Generic.IEnumerable`1[dataAccessLayer.DAL.MovieContext]'.
Does 19 mean column 19?
Imports System.Data.Entity
Imports System.Data.Entity.ModelConfiguration.Conventions
Imports dataAccessLayer.Models
Namespace DAL
Public Class MovieContext
Inherits DbContext
Public Sub New()
MyBase.New("MovieContext")
Database.SetInitializer(New CreateDatabaseIfNotExists(Of MovieContext))
Entity.Database.SetInitializer(Of MovieContext)(Nothing)
End Sub
Public Property Movies As DbSet(Of Movies)
Public Property Movies_flv As DbSet(Of Movies_flv)
Public Property Movies_h264 As DbSet(Of Movies_h264)
Protected Overrides Sub OnModelCreating( _
ByVal modelBuilder As DbModelBuilder)
modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()
End Sub
End Class
End Namespace
|
|
|
|
|
The "19" doesn't mean anything in this case.
The error says that you're trying to convert an anonymous type (your Select New ...) into an IEnumerable(Of MovieContext). You're saying that the function is returning a list of MovieContext. So, your function header is wrong. You should be returning an IEnumerable of whatever type your Select is projecting into. In my example, I specifically said something like MovieItem, whereas you're returning an anonymous type.
|
|
|
|
|
I didn't pay careful enough attention to your example and just saw what I wanted to see.
I understand what the MovieItem is.
Public Shared Function load_movie_array( _
ByVal pSortOrder As SortOrder,
ByVal pType As MovieType) As IEnumerable(Of MovieItem)
Using context As New MovieContext()
Dim results =
From item In context.Movies
Where item.flv = 1
Order By item.MovieName
Select New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieURL = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieBitRate = item.MovieBitRate,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.movie_Parameters = item.movie_Parameters
}
Return results.AsEnumerable()
End Using
End Function
|
|
|
|
|
Oh, I have to project the results into another object like a class or something, to transport it.
I thought that's what the model was for. Or does the model just works with the context, in order to create or update the database table?
|
|
|
|
|
jkirkerx wrote: I have to project the results into another object like a class or something, to transport it.
You don't have to but it makes your code easier to understand, debug and support.
jkirkerx wrote: I thought that's what the model was for
You can return just the model objects. It appears as though you're just returning every field of the Movie table, so you can just return an IEnumerable(Of Movie) instead of projecting into another object.
Public Shared Function GetAllFlvMovies() As IEnumerable(Of Movie)
Using context As New MovieContext()
Dim results = From m In context.Movies
Where item.flv = 1
Order By m.MovieName
Select m
Return results.AsEnumerable()
End Using
End Function
Projection comes in handy when you are building view model objects. You only put the data you need into the view model object and possibly more data that isn't related to the database to supply your edit controls with.
...
Dim movies = From item In GetAllFlvMovies()
Select New MovieViewModel With
{
Id = item.MovieId,
Name = item.MovieName,
...
}
... send the <code>movies</code> collection to whatever view you need ...
|
|
|
|
|
Thanks Dave, I owe you as well for the price of tuition on schooling me today.
I got more than expected today, and the complete index works, plus I know that my DAL seems to be working as well including the model.
I'm pretty excited right now. I think this DAL experiment is going to work out great in the long run for me, and is the way to go moving forward.
So on to Updates, inserts and deletes, plus joins as well. Then I can get depressed again!
I'll be back.
|
|
|
|
|
|
I'm trying to figure out how to get the count of records. I was just expecting the other fucntion to give me a count as IEnumerable, but it doesn't provide it.
So I wrote this, not sure if it's right, because I get 0 back, and there's 4
Do you think this is because the database was already existing, and it's related to Code First.
On the previous function, I'm not sure if I'm getting data back or not, and not quite sure how to use the returned output.
This calls the previous function in the post above. I didn't want to repost it again.
Dim m_count As Integer = shared_MovieInfo.get_index_count(shared_MovieInfo.MovieType.FLV)
Dim movies As IEnumerable(Of MovieItem)
movies = shared_MovieInfo.load_index_array(shared_MovieInfo.SortOrder.Name, shared_MovieInfo.MovieType.FLV)
So I'm thinking you just use it like this
For Each m As Object In movies
Dim m_MovieName_Encoded As String = System.Web.HttpUtility.HtmlEncode(m.MovieName)
Next
Count Function that I wrote. I tried the shorter method, but the MovieContext wasn't shared, said the compiler.
Not sure what to do with that message, But I get 0 back as a result.
I really wanted to use just 1 function for the data and the count.
Public Shared Function get_index_count( _
ByVal pType As MovieType) As Integer
Dim context As New MoviesContext()
Dim pValue = (From item In context.Movies
Where item.flv = 1
Select item).Count()
Return pValue
End Function
|
|
|
|
|
jkirkerx wrote: So I wrote this, not sure if it's right, because I get 0 back, and there's 4
Wrote what?
jkirkerx wrote: Do you think this is because the database was already existing, and it's related to Code First.
Not a chance.
jkirkerx wrote: On the previous function, I'm not sure if I'm getting data back or not, and not quite sure how to use the returned output.
If the count is 0, you're not getting back any data. The Where clause of your query is probably what's screwing it up.
Quote: For Each m As Object In movies
Dim m_MovieName_Encoded As String = System.Web.HttpUtility.HtmlEncode(m.MovieName)
Next
Uhhhh, what does this have to do with a Count or a list of movies?
jkirkerx wrote: Count Function that I wrote.
Yeah, there's no need for that function at all. Also, you're hitting the database a second time for one value.
I really have no idea what the rest of your code looks like, but, based on what you've posted so far, I think it's a giant over-complicated mess. Sorry, but I'm not following what you're code is doing related to a Movie database and your method and variable names don't make sense to me at all. Also, why do you have 3 different tables of movies? From what I saw in your DbContext class, there should be only one.
|
|
|
|
|
Dave Kreskowiak wrote: Uhhhh, what does this have to do with a Count or a list of movies?
This is just the start of the experiment. This is for a movie index, with page numbers and show selections. I have to calculate the start index and stop index.
Dave Kreskowiak wrote: Do you think this is because the database was already existing, and it's related to Code First.
This is new to me, I read the term a couple of times. I did have to delete a column and recreate it for the "Invalid Column Name" error.
The database is 7 years old, but has been changed 3 times.
Dave Kreskowiak wrote: Uhhhh, what does this have to do with a Count or a list of movies?
That is just a small snippet, a loop that builds the a cell for the movie information.
This is what I get back in the program that calls the function to load the Movie records. I thought I would get a data set back, but it looks like a some sort of query that is ready to go, but just needs to be executed or something.
Sorry I sound so dumb on this, but this is the 2nd time I've done this. the first time, someone else wrote the DAL dll and the database, and I just wrote the Linq for it in a windows app.
Hey thanks for helping me and steering me straight on this. If I could just get something working, even just the count, then everything should come together on the mechanics of it.
{SELECT
[Extent1].[MovieID] AS [MovieID],
[Extent1].[MovieName] AS [MovieName],
[Extent1].[MovieType] AS [MovieType],
[Extent1].[MovieDeliveryType] AS [MovieDeliveryType],
[Extent1].[MoviePath] AS [MoviePath],
[Extent1].[MovieURL] AS [MovieURL],
[Extent1].[MoviePostage] AS [MoviePostage],
[Extent1].[MovieThumbnail] AS [MovieThumbnail],
[Extent1].[MovieLarge] AS [MovieLarge],
[Extent1].[MoviePlaceHolder] AS [MoviePlaceHolder],
[Extent1].[MovieBitRate] AS [MovieBitRate],
[Extent1].[MovieTime] AS [MovieTime],
[Extent1].[MovieSize] AS [MovieSize],
[Extent1].[MovieDescription] AS [MovieDescription],
[Extent1].[MovieCount] AS [MovieCount],
[Extent1].[MovieContributor] AS [MovieContributor],
[Extent1].[MoviePostDate] AS [MoviePostDate],
[Extent1].[MovieIcon] AS [MovieIcon],
[Extent1].[movie_Parameters] AS [movie_Parameters]
FROM [dbo].[MOVIEINFO] AS [Extent1]
WHERE 1 = [Extent1].[flv]
ORDER BY [Extent1].[MovieName] ASC}
|
|
|
|
|
Well I got the count finally.
I misunderstood the Context, Sub New, MyBase.New and put a conn string in it.
Question: I use the connStr below in my original stuff, possible to use that again, or how do you do it?
Public Sub New()
MyBase.New("Data Source=XXXXXXXX; Initial Catalog=XXXXXXX; User ID=XXXXXXX; Password=XXXXX;")
Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Database.SetInitializer(New CreateDatabaseIfNotExists(Of MoviesContext))
Entity.Database.SetInitializer(Of MoviesContext)(Nothing)
End Sub
And I rewrote my function, that I place in the DAL Dll. So now I get the count of 4
Public Shared Function load_index_array( _
ByVal pSortOrder As SortOrder,
ByVal pType As MovieType,
ByRef pResults As IEnumerable(Of MovieItem)) As Integer
Dim context As New MoviesContext()
pResults =
(From item In context.Movies
Select New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieUrl = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.MovieParameters = item.movie_Parameters
}).AsEnumerable
Return pResults.Count()
End Function
|
|
|
|
|
I put the connection string in the App.config/Web.config file, never in the code itself.
An example DbContext I wrote looks like this:
public class MyContext : DbContext
{
public MyContext()
{
}
public MyContext(string connectionStringName)
: base(connectionStringName)
{
}
public override int SaveChanges()
{
ChangeTracker.DetectChanges();
return base.SaveChanges();
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
ConfigureModel(modelBuilder);
base.OnModelCreating(modelBuilder);
}
private void ConfigureModel(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new SomethingConfiguration());
modelBuilder.Configurations.Add(new SomethingElseConfiguration());
modelBuilder.Configurations.Add(new SomeOtherTableConfiguration());
...
}
public DbSet<sometype> SomeTypes { get; set; }
public DbSet<othertype> OtherTypes { get; set; }
public DbSet<contact> Contacts { get; set; }
...
}
}
<div class="signature"><a href="http://www.codeproject.com/scrapbook/ForumGuidelines.asp">A guide to posting questions on CodeProject</a><br><br>Click this: <a href="http://weblogs.asp.net/bleroy/asking-questions-is-a-skill">Asking questions is a skill</a>.
Seriously, do it.<br>
<font color="Blue">Dave Kreskowiak</font></div>
|
|
|
|
|
I used ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
as the conn string in my new in context. But I changed to your version, You must have a good reason for it; I'll learn down the as to why.
So I guess when using context as new context(connStr), using and not Dim in VB
The results I project are lost, because the context is disposed.
Is it good practice to create a new context and just pass it along? with using. I think that would get disposed as well.
OK, Thanks for the example above. I'll move on to the actual queries now.
I figured out that sql parameters are created automatically.
|
|
|
|
|
jkirkerx wrote: I used ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
as the conn string in my new in context. But I changed to your version, You must have a good reason for it; I'll learn down the as to why.
Because DbContext already uses the ConfigurationManager stuff itself. All you need to supply, if anything at all, is the name of the connection string in your App.config/Web.config file. Putting the connection string in the config file is also best practice.
jkirkerx wrote: Is it good practice to create a new context and just pass it along? with using. I think that would get disposed as well.
Best practice for database access is always connect as late as possible, run your queries, and disconnect as early as possible and dispose your objects.
With Entity Framework, that's easy. Create your DbContext instance with a using block and put your query code in there. The using block will automatically Dispose of the context when execution leaves the block.
|
|
|
|
|
Well, I thought IEnumerable would be used for single records as well. Guess Not.
So I put this together, and just used the class straight.
I'm pretty sure my Linq is correct, but MovieRecord class is empty.
Am I even close on this?
Public Shared Function load_movie_record( _
ByVal p_MovieID As Integer,
ByRef pResults As MovieRecord) As Integer
Dim context As New MoviesContext()
pResults =
( _
From m In context.Movies
Join f In context.Movies_flv On m.MovieID Equals f.movieID
Join h In context.Movies_h264 On m.MovieID Equals h.movieID
Where m.MovieID = p_MovieID
Select New MovieRecord With {
.MovieID = m.MovieID,
.MovieName = m.MovieName,
.MovieType = m.MovieType,
.flvID = f.ID,
.flvMovieID = f.movieID,
.flvMovieName = f.flv_movieName,
.h264ID = f.ID,
.h264MovieID = h.movieID,
.h264MovieName = h.h264_movieName,
}).SingleOrDefault()
|
|
|
|
|
If the pResults is null or empty it means your query didn't come back with any records. The value you're passing to the Where clause would be the first thing to check and then see if the record actually exists in the database.
|
|
|
|
|