|
I have multiple tables to call. So, I need to have a next button that call's the next row data.
Here's my coding:
Try
cmd.Connection = connection
cmd.CommandText = "SELECT tblExpenses.Date, tblExpenses.Allowance, tblExpenses.Total, tblExpenses.Wallet,"
cmd.CommandText += "tblFoodAndBeverage.Breakfast, tblFoodAndBeverage.Lunch, tblFoodAndBeverage.Dinner,"
cmd.CommandText += "tblFoodAndBeverage.Supper, tblFoodAndBeverage.Snack, tblFoodAndBeverage.Beverage,"
cmd.CommandText += "tblFoodAndBeverage.Grocerries, tblFoodAndBeverage.Sub_Total, tblEntertainment.Video_Rental,"
cmd.CommandText += "tblEntertainment.Cinema, tblEntertainment.Concert, tblEntertainment.Sport,"
cmd.CommandText += "tblEntertainment.Games, tblEntertainment.Club_Bar_Disco, tblEntertainment.Others,"
cmd.CommandText += "tblEntertainment.Sub_Total FROM tblExpenses, tblFoodAndBeverage, tblEntertainment WHERE tblExpenses.Food_And_Beverage=tblFoodAndBeverage.Food_And_BeverageID AND tblExpenses.Entertainment=tblEntertainment.EntertainmentID"
dr = cmd.ExecuteReader
If dr.HasRows Then
While dr.Read
DateTimePicker1.Value = dr.Item(0)
txtAllowance.Text = dr.Item(1)
txtTotalUsage.Text = dr.Item(2)
txtWalletCash.Text = dr.Item(3)
txtBreakFeast.Text = dr.Item(4)
txtLunch.Text = dr.Item(5)
txtDinner.Text = dr.Item(6)
txtSupper.Text = dr.Item(7)
txtSnack.Text = dr.Item(8)
txtBeverage.Text = dr.Item(9)
txtGroceries.Text = dr.Item(10)
txtSubFnB.Text = dr.Item(11)
txtVideo.Text = dr.Item(12)
txtCinema.Text = dr.Item(13)
txtConcert.Text = dr.Item(14)
txtSport.Text = dr.Item(15)
txtGames.Text = dr.Item(16)
txtClubBarDisco.Text = dr.Item(17)
txtEntertainmentOthers.Text = dr.Item(18)
txtSubEntertainment.Text = dr.Item(19)
End While
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
|
|
|
|
|
..I think you need to practice on a simpler project.
You're reading "all" records into the textboxes, only showing the latest record. It's overwriting the values for each record in your database.
Read them into a table or a list, and display them from there. No, I'm not writing code today, too damn hot outside - search for a tutorial.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Okay this is my latest working coding with navigation button but with some un-accurate display.
Public Class DailyExpenses
Dim connection As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim cmd1, cmd2, cmd3, cmd4, cmd5, cmd6, cmd7, cmd8, cmd9 As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer = 0
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If connection.State <> ConnectionState.Open Then
connection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PersonalExpenses.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
connection.Open()
End If
Call Fill_Template()
End Sub
#Region "Fill Data"
Sub Fill_Template()
cmd.CommandText = "SELECT Date,Allowance,Total,Wallet FROM tblExpenses"
cmd.Connection = connection
da.SelectCommand = cmd
da.Fill(ds, "tblExpenses")
If (ds.Tables("tblExpenses").Rows.Count > 0) Then
DateTimePicker1.Value = ds.Tables("tblExpenses").Rows(i)(0).ToString
txtAllowance.Text = ds.Tables("tblExpenses").Rows(i)(1).ToString
txtTotalUsage.Text = ds.Tables("tblExpenses").Rows(i)(2).ToString
txtWalletCash.Text = ds.Tables("tblExpenses").Rows(i)(3).ToString
End If
cmd1.CommandText = "SELECT tblFoodAndBeverage.Breakfast,tblFoodAndBeverage.Lunch,tblFoodAndBeverage.Dinner,"
cmd1.CommandText += "tblFoodAndBeverage.Supper,tblFoodAndBeverage.Snack,tblFoodAndBeverage.Beverage,"
cmd1.CommandText += "Grocerries,Sub_Total FROM tblFoodAndBeverage,tblExpenses WHERE tblFoodAndBeverage.Food_And_BeverageID = tblExpenses.Food_And_Beverage"
cmd1.Connection = connection
da.SelectCommand = cmd1
da.Fill(ds, "tblFoodAndBeverage")
If (ds.Tables("tblFoodAndBeverage").Rows.Count > 0) Then
txtBreakFeast.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(0).ToString
txtLunch.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(1).ToString
txtDinner.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(2).ToString
txtSupper.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(3).ToString
txtSnack.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(4).ToString
txtBeverage.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(5).ToString
txtGroceries.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(6).ToString
txtSubFnB.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(7).ToString
End If
End Sub
#End Region
#Region "Data_Fill"
#End Region
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
If (i <= 0) Then
MsgBox("You are already at first position.")
Else
i = 0
Call Fill_Template()
End If
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If (i <= 0) Then
MsgBox("You are already at first position.")
Else
i = i - 1
Call Fill_Template()
End If
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
Dim RowCount, RowCount1 As String
RowCount = ds.Tables("tblExpenses").Rows.Count - 1
RowCount1 = ds.Tables("tblFoodAndBeverage").Rows.Count - 1
If (i = RowCount) And (i = RowCount1) Then
MsgBox("You are already at Last position.")
Else
i = i + 1
Call Fill_Template()
End If
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
Dim RowCount, RowCount1 As String
RowCount = ds.Tables("tblExpenses").Rows.Count - 1
RowCount1 = ds.Tables("tblFoodAndBeverage").Rows.Count - 1
If (i = RowCount) And (i = RowCount1) Then
MsgBox("You are already at Last position.")
Else
i = ds.Tables("tblExpenses").Rows.Count
i = ds.Tables("tblFoodAndBeverage").Rows.Count
Call Fill_Template()
End If
End Sub
End Class
Problem:
1. When I click next it shows the other rows data correctly but (let's say my tblExpenses has 4 rows of data and my tblFoodAndBeverage has 2 rows of data) then when I click next after the 2nd row data the FoodAndBeverage shouldn't display any data because its null after the 2nd row but it displays duplicate data.
2. My table Expenses actually has a column(Food_And_Beverage(int)) that connects to the table FoodAndBeverage(Food_And_BeverageID). My problem is how to retrieve data based on that 2 relationship.
For example:
tblExpenses(Date(18/8/2012)) has also data on Foods but then (19/8/2012) has its own data in its table but the Foods data is null but still displays other data on that date except for food with blank data.
|
|
|
|
|
Good to see a quick learner
garyu87 wrote: 1. When I click next it shows the other rows data correctly but (let's say my tblExpenses has 4 rows of data and my tblFoodAndBeverage has 2 rows of data) then when I click next after the 2nd row data the FoodAndBeverage shouldn't display any data because its null after the 2nd row but it displays duplicate data.
Are the duplicates already in the set when you execute the Sql statement? Might be easily fixed by changing the "SELECT [fieldnames]" to "SELECT DISTINCT [fieldnames]".
garyu87 wrote: 2. My table Expenses actually has a column(Food_And_Beverage(int)) that connects to the table FoodAndBeverage(Food_And_BeverageID). My problem is how to retrieve data based on that 2 relationship.
For example:
tblExpenses(Date(18/8/2012)) has also data on Foods but then (19/8/2012) has its own data in its table but the Foods data is null but still displays other data on that date except for food with blank data.
Might be solved by 1.
I'd suggest creating a routine that fetches a record from a table, based on the primary key- the number you used to connect the tables. That's usually an INT , a BIGINT or a GUID .
public function GetFoodAndBeverageRecord(PrimaryKey As Integer) as List(Of String)
Dim result as New List(Of Object)
ClearTextBoxes()
Using con As New SqlConnection(connectionstring)
Using cmd As con.CreateCommand
cmd.CommandText = File.Load("GetFoodAndBeverageRecord.sql")
con.Open
using rdr = con.ExecuteReader
if rdr.Read()
for i = 0 to rdr.FieldCount -1
result.Add(rdr.GetField(i)
next
end if
end using
end using
end using
End Function
Private Sub ClearTextBoxes()
txtBoxBla.Text = ""
txtBoxBla2.Text = ""
End Sub
I know it's more efficient (resource-wise) to get the data using a single select-statement, but it's a lot easier to separate the tasks. In this case, it would make it easy to fetch the Expenses. It would "contain" the keys that point to the FoodAndBeverage table, and if the user clicks on a row, I merely have to fetch the key, execute abovementioned method with the key as a parameter, and it'd return a FoodAndBeverage-record WHERE the key equals the passed parameter. That same method also makes programming a forward- and back button easy; it fetches the record with the current id, next that gets called is a method to display it.
A better way would be to return a class, and not a list of values; for that you'd need a FoodAndBeverage class, with public properties for each field in the table. Similar to below;
Public Class FoodAndBeverage
Public Property Id As Integer
Public Property FoodName As String
Public Property Cost As Double
End Class
If the method can return one of those, life becomes easier again; .NET has a lot of support for databinding, and you can throw a BindingList(Of whatever class against a DataGridView, and .NET will do the rest
Hope I didn't overload you with information, just keep the momentum.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Okay thanks.....I'll try it out.
|
|
|
|
|
You're welcome
|
|
|
|
|
i am looking for source code to interface with hp impedance tester 4285A to perform the automated sorting system.
Can vb communicate with tester with only handler interface.(centronic connector)
can anyone help pls.
|
|
|
|
|
You will not "find" source code - it is something that has to be written. If you are looking for somebody to do the work for you, this is the wrong place; here we answer questions on implementation-problems, compiler-errors and runtime-exceptions.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
You're not likely to find ANY source code to do this. What you're asking is such a vertical question that you're probably only going to 5 people on this planet who have done anything close to what you're asking about.
Do yourself a favor. Your time would be better spent researching what it takes to interface with this thing and write the code yourself than it would be trying to find a ready-made solution or other people to write it for you.
|
|
|
|
|
I have a PrintPreviewControl on a form that is opened modally (ShowDialog) from a Main form. (not MDI) The program seems to be trying to return back to the Main form prematurely and causing this error...
'The Undo operation encountered a context that is different from what was applied in the corresponding Set operation. The possible cause is that a context was Set on the thread and not reverted(undone).'
This only happens when I set the PrintPreviewControl.Document. This spawns the 'Generating Preview' window, then crashes the program in the Main form at the ShowDialog statement, which should be 'waiting' for the second form to close. Not sure if it matters, but this is on Win7.
*Update - After several hours of trying to debug this, I changed my default printer from a network printer to my PDF printer and the app works as expected. The network printer works flawlessly from all other programs, so why would I expect it to be the culprit?
"Go forth into the source" - Neal Morse
modified 16-Aug-12 10:37am.
|
|
|
|
|
Is this realted to the default print dialog in any way?
|
|
|
|
|
Hi,
I've a component which is com enabled.
It is working fine in vb6 for my machine and when i try to use the .tlb file on other machine its not able to give the output in vb6 application.
I've tried registering .tlb file using regtlibv12.exe, but couldn't find a way.
Please help me out.
Thanks,
|
|
|
|
|
The .tlb file contains only the Type Library information, the actual code is contained in the .exe or .dll as the case may be. Did you include it in your app?
Karthik Reddy wrote: not able to give the output in vb6 application.
Care to explain?
|
|
|
|
|
you mean to register along with .dll file ?
I tried generating a .tlb file using regasm but it also did not help.
FYI-- I'm unable to execute regsvr32.exe
|
|
|
|
|
What are you trying to do ? Can you explain your problem in detail ?
|
|
|
|
|
Actually I've created a component in .net.
I need to make use of it in vb6. So, for this i've made Com visible and register com interop to true in my component.
I've build my component which has generated a .dll and a .tlb file .
Now the .tlb file is visible in my machine and it works in my application fine and i could see the output of it in vb6.
But when i try to install the .tlb file on other machines, it is installing the tlb file.
But in vb6 application, I'm getting error saying "Automation Error The system cannot find the file specified
".
Its showing like this.
what might be wrong ? Its working fine on my machine but unable to get the output on other machines
|
|
|
|
|
You use REGASM on the .DLL file to register it. You don't register the TLB.
|
|
|
|
|
is it like Regasm myfile.dll /tlb:myfile.dll
or
regasm myfile.dll
|
|
|
|
|
It's the first, but you're allowed to try both.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Command is successful but still unable to get result. Giving error message as "Automation Error The system cannot find the file specified"
|
|
|
|
|
Good huntin'
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Yes all classes have Guid's and also i've set ClassInterface(ClassInterfaceType.None).
yes, i'm on win 7 i've registered to admin account.
I've tried with /codebase option also but it didnot make any difference
I did not install the dependency dll's on admin account.
Do i need to register the .tlb files with regtlibv12.exe ?
I guess inorder to use /codebase option, my dll need to be strongly name, but my dll is not strongly name because my third party dll is not stronly named .
modified 17-Aug-12 2:11am.
|
|
|
|
|
Karthik Reddy wrote: Do i need to register the .tlb files with regtlibv12.exe ?
No. AFAIK, regtlib12 isn't required.
Karthik Reddy wrote: I guess inorder to use /codebase option, my dll need to be strongly name, but my dll is not strongly name because my third party dll is not stronly named .
..there's the problem.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
i have the app i want users to set connection to the database at run time by supplying the data source name,user id and password of the database before the application set. This is what i have done but it seem not to get me the exit output i want.any help?
Public Sub savenewconnection(ByVal id As String, ByVal pass As String, ByVal source As String)
Try
Dim newdbcon As String
Dim oldconnection As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("mydbcon")
Dim dbcon As String = oldconnection.ConnectionString
Dim builder As New OleDbConnectionStringBuilder(dbcon)
builder.Add("Data Source", source)
builder.Add("Password", pass)
builder.Add("User ID", id)
newdbcon = builder.ConnectionString
Dim config As System.Configuration.Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
config.ConnectionStrings.ConnectionStrings("mydbcon").ConnectionString = newdbcon
config.Save(ConfigurationSaveMode.Modified)
ConfigurationManager.RefreshSection("ConnectionStrings")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Subry
End Sub
|
|
|
|
|
Instead of creating a new post, edit your previous post and add your inputs there. Please delete this post.
|
|
|
|
|