|
Hello !
If the sql server is on my office's server , and I'm outside , is there any chance to connect to my database using my vb.net program ?
( the server doesn't have a real ip , but a dynamic ip from ISP ).
Thank you !
|
|
|
|
|
If the TCP/IP address changes I take it the name still stays the same? If that is the case then use the DNS name instead of the IP address in the connection string for the server. Of course you need to have a type of a connection that allows you to communicate with the inside network.
For the connection string format refer to https://www.connectionstrings.com/sql-server/[^]
|
|
|
|
|
Sorry , I'm not speaking for a website or a webserver. The server is on my office. In the office I use a ADSL internet connection that use a dynamic IP address. For what "Name" do you speak ?
|
|
|
|
|
So from what I gather, the database server has a fixed IP address inside your network but the IP address for your office that is shown from outside is changing? If that's correct, I believe that you need to contact you ISP and ask if you can get a fixed address etc.
The naming I'm referring to is DNS[^]
|
|
|
|
|
yes you're correct.
Even the ip from ISP is fixed , it's still an static ip address inside the ISP. So it's not a real ip address.
|
|
|
|
|
Also be sure the correct port is open.
BUT that's not really very secure; hosting a web service that connects to the database and having clients access the web service tends to be a better choice.
|
|
|
|
|
How can i host a web service ?
Any example ?
|
|
|
|
|
Dial in on the LAN?
Exposing your DB to the outside is definitely a bad idea.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
ok , but what do you suggest because I need to give users this possibility ?
|
|
|
|
|
Let them access the database over a webservice as PIEBald suggested. Don't make those public/accessible to anyone have some authentication in there.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Get your network admin to set up a VPN[^]. If the public IP address is liable to change, use a Dynamic DNS[^] service to keep a DNS record updated with the correct address.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello ,
I want to ask a question First I'm using VB.NET and I want to know How to Call Stored Procedure parameters in Class ?
What I mean I have a lot of parameters in my Stored Procedure and I want to call it in my form by using that Function but I can't put parameters because it's approximately 15 params.
this my code in class :
Public Function Add_Ne(ExecQ As String) As Boolean
Try
con.Open()
With cmd
.CommandType = CommandType.Text
.CommandText = ExecQ
.Connection = con
' .Parameters.AddWithValue(paramxx, TextN)
.ExecuteNonQuery()
End With
con.Close()
Return True
Catch ex As Exception
MessageBox.Show(ex.Message)
Return False
End Try
End Function
Thanks.
|
|
|
|
|
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?
|
|
|
|
|