|
I’m trying to write a function that returns a datatable. Within the function I’m calling a stored procedure in access. The code looks like this:
Public Shared Function LoadSearchResults(ByVal whereClause As String) As DataTable
Dim SQL As String = " EXECUTE qrySearchResult"
Dim dt As DataTable = New DataTable()
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
dbConnection.Open()
'Dim Cmd As New OleDbCommand(SQL, dbConnection)
Dim Cmd As New OleDbCommand()
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandText = "EXECUTE qrySearchResult"
Cmd.Parameters.Add(Cmd.CreateParameter())
Cmd.Parameters(0).Value = whereClause
'''''dr = Cmd.ExecuteReader()
''''''''''''''''''''
da.SelectCommand = Cmd
da.Fill(dt)
dbConnection.Close()
dbConnection.Close()
Return dt
End Function
How can I accomplish this?
Thanks,
Martin
|
|
|
|
|
Whatr's the problem ?
Why are you closing your connection twice ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
The problem here is that I don't know how to get a datatable from: dr = Cmd.ExecuteReader().
Please ignore the 2nd dbConnection.Close().
|
|
|
|
|
You can try this code to use your datareader to build your datatable
Dim table As New DataTable()
Dim row As DataRow
Dim rows As DataRowCollection
' read table schema from datareader
rows = DataReader.GetSchemaTable.Rows
'If you already know your field names you can use this
table.Clear()
table.Columns.Clear()
' Define the columns.
table.Columns.Add("Last Name", GetType(String))
table.Columns.Add("First Name", GetType(String))
table.Columns.Add("Phone Number", GetType(String))
table.Columns.Add("Email Address", GetType(String))
'Other wise do this
' use schema to create the columns for the new datatable
For Each row In rows
Dim col As New DataColumn()
col.ColumnName = row("ColumnName").ToString
col.Unique = System.Convert.ToBoolean(row("IsUnique"))
col.AllowDBNull = System.Convert.ToBoolean(row("AllowDBNull"))
col.ReadOnly = System.Convert.ToBoolean(row("IsReadOnly"))
col.DataType = Type.GetType(row("DataType").ToString)
table.Columns.Add(col)
Next
While DataReader.Read
row = table.NewRow
Dim col As DataColumn
For Each col In table.Columns
row(col) = DataReader(col.ColumnName)
Next
table.Rows.Add(row)
End While
Return table
Please let me know if this helped you.
|
|
|
|
|
You might also want to download this project and look at it. It has a class that might do what you are looking for.
http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=352&lngWId=10\
|
|
|
|
|
You might also want to download this project and look at it. It has a class that might do what you are looking for.
http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=352&lngWId=10\
|
|
|
|
|
Thanks for all your help. I ended up doing this:
Public Shared Function LoadSearchResults(ByVal whereClause As String) As DataTable
Dim table As DataTable = New DataTable()
table.Columns.Add(New DataColumn("SSN"))
table.Columns.Add(New DataColumn("REFERRED"))
table.Columns.Add(New DataColumn("COMMENTS"))
table.Columns.Add(New DataColumn("ADDITIONAL1"))
table.Columns.Add(New DataColumn("ADDITIONAL2"))
table.Columns.Add(New DataColumn("ADDITIONAL3"))
Dim dr As OleDbDataReader
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
Dim Cmd As New OleDbCommand()
Try
Cmd.Connection = dbConnection
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandText = "qrySearchResult"
Cmd.Parameters.Add(Cmd.CreateParameter())
Cmd.Parameters(0).Value = whereClause
dbConnection.Open()
dr = Cmd.ExecuteReader()
Dim i As Int16
While dr.Read
Dim ar As ArrayList = New ArrayList()
For i = 0 To dr.FieldCount - 1
ar.Add(dr(i))
Next
Dim row As DataRow = table.NewRow()
row.ItemArray = ar.ToArray
table.Rows.Add(row)
End While
dbConnection.Close()
Return table
End Function
and It works fine except that MS Access ignores the where clause and returns all records. Do y ou know what I should do so that the query returs according to the where clause. below it the access query.
SELECT SSN, REFERRED, COMMENTS, Additional1, Additional2, Additional3
FROM MAIN
WHERE @WhereCluase;
|
|
|
|
|
I have a combo box in my form that take values from the other table in the database on form load.
On the Click Property of the Save Button i have put the code to clear the values of all text boxes and Combo Box after saving the data.
I used the following code to clear the combo box:
cboname.listindex = -1
The style of the combo box is set to 2-dropdown.
The data is saved to the database correctly but after that while clearing the text in the combo box it gives the Run Time Error No. 381 i.e. Invalid Property Array Index.
Can any body please tell me that what code should i do to clear those values in the combo box???
|
|
|
|
|
Hi Ali
Firt of all what are you working with windows forms or webforms.
in webforms we have dropdownlist which works similar to combobox to clear a dropdownlist or a combobox on a windows forms tryusing
cboname.items.Clear
Mandar Patankar
Microsoft Certified professional
|
|
|
|
|
I am working on VB 6 Forms. I think we will call them window forms. I dont know but also plz tell me that what should we call them?
Well i have tried the code u told me but its also not working.
it doesnt have that property of Items.
it gives the error of Data Method not found.
|
|
|
|
|
Well, my VB6 is a bit rusty, but IIRC, to clear just the TextBox portion of the ComboBox, set the ListIndex to 1, not -1. To clear out the entire ComboBox, it's ComboBox1.Clear().
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
To clear all items in a combo box, you can use cboName.Clear where cboName is the name of the combo box.
|
|
|
|
|
This seems like it should be simple but I could not find any info anywhere to tell me how to do it.
Below is the code I am using in my datagrid and I need to format the number in this cell with ",".
When 123456789 is displayed (not entered) it needs to look like this 123,456,789
.Add(New System.Windows.Forms.DataGridTextBoxColumn)
With .Item(3)
.MappingName = "DriveSize"
.HeaderText = "Drive Size"
.Width = 115
.NullText = String.Empty
End With
If I try to put the .format member is the above code I get an error telling me it is not a member the datagrid columns style.
I have used the .format before to format money, but I don't know why
it won't work here. Can anyone tell me what I need to do to format this
cell?
|
|
|
|
|
I think your looks for something more like this:
Dim newColumn As New DataGridColumnStyle
With newColumn
.MappingName = "DriveSize"
.HeaderText = "Drive Size"
.Width = 115
.NullText = "String.Empty
.Format = "N"
End With
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Thank you for your response. I had already been trying to use the .format member but with the way I was coding the columns it would not work. I would get an error telling me it was not a member of that object.
I finally tried a different way of coding for the columns (similar to yours) but I had to make other additional changes and I finally got it to work.
I appreciate your help. You made me rethink how I was approaching the column coding.
|
|
|
|
|
Has anyone got a routine to solve an optimization problem using the Simplex method?
Although I am aware of the Process to solve a Standard Maximization Problem using the Simplex Method, I have not even attempted to code it as I imagine that it is available on line.... and hopefully in vb 6 or .net
My application is to optimize the utilization (overall efficiency) of multiple equipment blocks based on block power, within certain constraints (numerical example as follows):
Maximize ntotal = ax + by + cz subject to
x + y + z <= 500
x <= 250
x >= 100
y <= 110
y >= 100
z <= 250
z >= 100
However, the coefficients a, b and c are functions as follows:
a = f(x), b=f(y) and c=f(z).
My planned approach is to use the standard simplex solution and iterate until the differences between two consecutive solutions for x, y and z are less than a defined limit. The values of a, b and c will be determined from a polynmial between each iteration based on the resultant x, y and z.
Finally, although I will use the routine locally (vb), I intended to run the calculation on line on data within a mySQL database using PHP.
Thanks for reading this and I will really appriciate any help!
karmond
|
|
|
|
|
You'd get much better results Googling for "Simplex method optimization".
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I am using the following code from :http://www.kjmsolutions.com/datasetarray.htm[^]
to export data from a datagrid to Excel. The issue I have is that in my grid I have a field which holds the value: 00124544545454545 which represents a code.
Excel displays: 1+32323 a scientific number. I am at a loss on how to force a format to a cell to display this as text. The datagrid can show to datasets that differ in columns and thus it must be dynamic.
Any ideas?
Dim Excel As New Excel.Application
Dim oBook As Excel.Workbook
Dim WSheet As New Excel.Worksheet
Dim rows As Integer = dsDataset.Tables(0).Rows.Count
Dim columns As Integer = dsDataset.Tables(0).Columns.Count
Dim r As Integer
Dim c As Integer
Dim DataArray(rows, columns) As Object
Dim sString As String
WSheet = Excel.Workbooks.Add.Worksheets.Add
Excel.Visible = True
For c = 0 To columns - 1
DataArray(r, c) = dsDataset.Tables(0).Columns.Item(c).ColumnName
For r = 0 To rows - 1
DataArray(r, c) = dsDataset.Tables(0).Rows(r).Item(c)
Next
Next
'Transfer record data from array to Excel
WSheet.Range("A2").Resize(rows, columns).Value = DataArray
Dim columns2 As Integer
Dim columns3 As Integer = 1
For columns2 = 0 To dsDataset.Tables(0).Columns.Count - 1
WSheet.Cells(1, columns3).Value = dsDataset.Tables
(0).Columns(columns2).ColumnName
columns3 = columns3 + 1
Next
Cheers,
Scott
|
|
|
|
|
my mind is slipping; that and I'm new to VB.Net. I declared the array as string and voila!
Cheers,
Scott
|
|
|
|
|
Hi,
My application consists of a page containing textboxes - in some of which you type English and in some, Thai (Thailand's language) - which then gets saved into a MySQL DB. Now when you navigate between these textfields, you've to press Left-Alt+Shift (or whatever key config your system is set to) - to switch back and forth between these two languages. Any of you know a method by which I can make the system switch language on its own - depending on WHICH textfield has focus currently ? That way my user won't have to stop at every field and switch the language manually and would save up ona whole lot of extra keystrokes, besides making the app. really cool  Anybody with any kind of suggestions, most welcome. (Platform: Frontend:VB.NET Client --> Backend: MySQL-4.1.9 on Fedora Core 2)
Thanks a million
PeoPLe aRe cRazY anD tiMeS aRe sTRaNGe, i'M LoCKeD iN tiGhT, i'M OuT oF raNGe, i uSeD t0 caRe BuT - thiNGs haVe chaNGeD.
|
|
|
|
|
Is the machine.config file only available to Asp.Net applications? I am working on a console app and have added a setting to the file, but it isn't pulling using ConfigurationSettings.AppSettings("KeyName").ToString. I would appreciate any thoughts on this. Thank you.
Jason W.
|
|
|
|
|
No it's not.
ConfigurationSettings.AppSettings() does not look in the machine.config file anyway. It will look in the applications config file, which should have the same name as the .EXE file with ".config" appended to it. For example, if your application's .EXE is called MyApp.exe, the corresponding configuration file will be called MyApp.exe.config and reside in the same directory as the .EXE. This is the only file AppSettings will look in.
You can find more on these files on MSDN here:
Machine Configuration Files[^]
Application Configuration Files[^]
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hello! I need convert to(from) Julian Date Format from(to) Calendar Date.
Is there built in algorithm that allow to do this?
Now I do so:
' convert Calendar Date to Julian Date Format
Private Function GetJD(ByVal vDate As Date) As Integer
Dim y As Integer = vDate.Year
Dim m As Integer = vDate.Month
Dim d As Integer = vDate.Day
If m > 2 Then
m = m + 1
Else
y = y - 1
m = m + 13
End If
Dim JulianDate As Integer = Math.Floor(Math.Floor(365.25 * y) + Math.Floor(30.6001 * m) + d + 1720995)
JulianDate += 2 - Math.Floor(0.01 * y) + Math.Floor(0.25 * Math.Floor(0.01 * y))
Return Math.Floor(JulianDate - 0.5)
End Function
' convert Julian Date to Calendar Date Format
Private Function GetCD(ByVal JD As Integer) As Date
Dim g1, g2, g3, g4, g5 As Integer
Dim tmp As Integer = Math.Floor(((JD - 1867216.0) - 0.25) / 36524.25)
g1 = JD + 2 + tmp - Math.Floor(0.25 * tmp)
g2 = g1 + 1524.0
g3 = Math.Floor(6680 + ((g2 - 2439870) - 122.1) / 365.25)
g4 = Math.Floor(g3 * 365.25)
g5 = Math.Floor((g2 - g4) / 30.6001)
Dim d As Integer = Math.Floor(g2 - g4 - Math.Floor(g5 * 30.6001))
Dim m As Integer = Math.Floor(g5 - 1.0)
If m > 12 Then m -= 12
Dim y As Integer = Math.Floor(g3 - 4715.0)
If m > 2 Then y -= 1
If y <= 0 Then y = Abs(y - 1)
Return Date.Parse(d & "/" & m & "/" & y)
End Function
Thanks.
|
|
|
|
|
There's a prebuilt class in VB.NET named JulianCalendar that does exactly what you're looking for. JulianCalendar has several methods that'll take any conventional Gregorian date, convert it to a Julian format and send you back the result in any format you wish. I found an example for this in the MSDN2003 libraries. I'm pasting it here for your reference:
===========================================================
' The following code example displays the values of several components of a DateTime in terms of the Julian calendar.
Imports System
Imports System.Globalization
Public Class SamplesJulianCalendar
Public Shared Sub Main()
' Sets a DateTime to April 3, 2002 of the Gregorian calendar.
Dim myDT As New DateTime(2002, 4, 3, New GregorianCalendar())
' Creates an instance of the JulianCalendar.
Dim myCal As New JulianCalendar()
' Displays the values of the DateTime.
Console.WriteLine("April 3, 2002 of the Gregorian calendar equals the following in the Julian calendar:")
DisplayValues(myCal, myDT)
' Adds two years and ten months.
myDT = myCal.AddYears(myDT, 2)
myDT = myCal.AddMonths(myDT, 10)
' Displays the values of the DateTime.
Console.WriteLine("After adding two years and ten months:")
DisplayValues(myCal, myDT)
End Sub 'Main
Public Shared Sub DisplayValues(myCal As Calendar, myDT As DateTime)
Console.WriteLine(" Era: {0}", myCal.GetEra(myDT))
Console.WriteLine(" Year: {0}", myCal.GetYear(myDT))
Console.WriteLine(" Month: {0}", myCal.GetMonth(myDT))
Console.WriteLine(" DayOfYear: {0}", myCal.GetDayOfYear(myDT))
Console.WriteLine(" DayOfMonth: {0}", myCal.GetDayOfMonth(myDT))
Console.WriteLine(" DayOfWeek: {0}", myCal.GetDayOfWeek(myDT))
Console.WriteLine()
End Sub 'DisplayValues
End Class 'SamplesJulianCalendar
'This code produces the following output.
'
'April 3, 2002 of the Gregorian calendar equals the following in the Julian calendar:
' Era: 1
' Year: 2002
' Month: 3
' DayOfYear: 80
' DayOfMonth: 21
' DayOfWeek: Wednesday
'
'After adding two years and ten months:
' Era: 1
' Year: 2005
' Month: 1
' DayOfYear: 21
' DayOfMonth: 21
' DayOfWeek: Thursday
===========================================================
Hope this helps
PeoPLe aRe cRazY anD tiMeS aRe sTRaNGe, i'M LoCKeD iN tiGhT, i'M OuT oF raNGe, i uSeD t0 caRe BuT - thiNGs haVe chaNGeD.
|
|
|
|
|
want to insert a string in html login screen text box from vb
roy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
