|
It's supported 
|
|
|
|
|
Hi,
You can find an example on VB6 and ADO over here[^].
I are Troll 
|
|
|
|
|
Sorry for not to be specific. I want to get the vb6 counterpart for passing dbnull value to database and getting the datareader object (recordset object in case of vb6) and check for dbnull value of one or all of its fields. The following blocks to be specific:
com.Parameters.Add(New SqlParameter("@JoinDate", SqlDbType.DateTime))
If (dtpJoinDate.Checked) Then
com.Parameters("@JoinDate").Value = Convert.ToDateTime(dtpJoinDate.Value)
Else
com.Parameters("@JoinDate").Value = DBNull.Value
End If
If IsDBNull(drd("JoinDate")) Then
'statements.......
'....................
Else
'statements.......
'....................
End If
I want the vb6 counterpart of the upper mentioned blocks (specially the bolded lines) in terms of recordset objects. I'm acquainted in handling recordset objects. Hope this time I'm more specific.
|
|
|
|
|
That would be something along these lines;
Dim MyParam As ADODB.Parameter
If ... Then
Set MyParam = MyCom.CreateParameter("JoinDate", adDate, adParamInput, dtpMyCalendar.value)
Else
Set MyParam = MyCom.CreateParameter("JoinDate", adDate, adParamInput, Noting)
End If
MyCom.Parameters.Append MyPara
Set MyRecordSet = MyCom.Execute
If IsNull(MyRecordSet(k).Value) Then
I are Troll
|
|
|
|
|
Private con As ADODB.Connection
Private Sub cmdOk_Click()
Dim com As ADODB.Command
Dim lngRetVal As Long
On Error GoTo ErrorHandler
'If Not Validate Then Exit Sub
If strItemID = Empty Then
strSql = "Insert Into tblItemList (ItemID,Item,CategoryID,Quantity,Rate,Half,HalfQuantity,HalfRate,ExpiryDate) Values ('" & _
ReplaceQuote(txtItemID.Text) & "','" & ReplaceQuote(txtItem.Text) & "'," & SetDataCombo(cboCategory) & ",'" & _
ReplaceQuote(txtQuantity.Text) & "'," & Val(txtRate.Text) & "," & IIf(chkHalfAvailable.Value, 1, 0) & ",'" & _
ReplaceQuote(txtHalfQuantity.Text) & "'," & Val(txtHalfRate.Text) & ",?)"
Else
strSql = "Update tblItemList Set ItemID='" & ReplaceQuote(txtItemID.Text) & "',Item='" & ReplaceQuote(txtItem.Text) & "'," & _
"CategoryID=" & SetDataCombo(cboCategory) & ",Quantity='" & ReplaceQuote(txtQuantity.Text) & "',Rate=" & _
Val(txtRate.Text) & ",Half=" & IIf(chkHalfAvailable.Value, 1, 0) & ",HalfQuantity='" & ReplaceQuote(txtHalfQuantity.Text) & "'," & _
"HalfRate=" & Val(txtHalfRate.Text) & ",ExpiryDate=?" & " Where ItemID='" & strItemID & "'"
End If
Set com = CreateCommand(strSql)
com.Parameters.Append com.CreateParameter("ExpiryDate", adDate, adParamInput)
If Not IsNull(dtpExpiry.Value) Then
com("ExpiryDate") = CDate(dtpExpiry.Value)
Else
'Using Nothing instead of Null in the following line generates an error- ‘Application uses a value of the wrong type for the current operation’.
com("ExpiryDate") = Null
End If
com.Execute lngRetVal, adCmdText, adExecuteNoRecords
If lngRetVal = 1 Then
Msgbox "Record saved."
Unload Me
End If
CloseConnection
strSql = Empty
Exit Sub
ErrorHandler:
CloseConnection
strSql = Empty
MsgBox "The following error has occurred:" & vbCrLf & Err.Description, vbCritical, "Error"
End Sub
Private Sub OpenConnection()
If con Is Nothing Then
Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB.1;Data Source=MyServer;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB"
End If
End Sub
Private Sub CloseConnection()
If Not con Is Nothing And con.State = adStateOpen Then
con.Close
Set con = Nothing
End If
End Sub
Private Function CreateCommand(strQuery As String) As ADODB.Command
Dim com As New ADODB.Command
OpenConnection
com.ActiveConnection = con
com.CommandText = strQuery
Set CreateCommand = com
End Function
Now about the problem. When I try to save, the system throws an error- 'The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error'.
When I change the database table column data type to datetime and try to save the data from the form, it saves the default datetime value i.e. 31/12/1899 12:00:00 AM irrespective of what I pass through the parameter. Please help me sort out this problem, preferrably with the smalldatetime data type instead of the datetime as my application mainly deals with it. 
|
|
|
|
|
Hi,
Good work, seems you're almost there
What type of database are you using? You might be able to pass a DateTime and to convert that to a SmallDateTime in the SQL-statement itself. That would look something like this for the update-part of the statement;
"HalfRate=" & Val(txtHalfRate.Text) &
",ExpiryDate=convert(smalldatetime,?) " &
" Where ItemID='" & strItemID & "'"
Alternative, you could pass a DateTime to a stored procedure and to convert it to a SmallDateTime within that stored procedure. As a last resort you could pass them as a constant datetime-value, formatting the string by hand. That would resemble this;
"HalfRate=" & Val(txtHalfRate.Text) &
",ExpiryDate=#" & Format(CDate(dtpExpiry.Value), "mm/dd/yy") & "# " &
" Where ItemID='" & strItemID & "'"
You're already using strings to add the other values into the command-object; those would best be parameter-objects (also using CreateParameter), even though that adds to the amount of work. It will make the code easier to maintain in the future.
I are Troll
|
|
|
|
|
I've created a drop down "file" tab and I've added a close file tab the code below is the code I was using but I am getting an error saying Property access must assign to the property or use its value. Can anyone please show me where I've gone wrong in this code. Thank you in advance.
Private Sub CloseTabToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseTabToolStripMenuItem.Click
Dim i As Integer = 1
If Not TabControl1.TabPages.Count = 1 Then
TabControl1.TabPages.RemoveAt(TabControl1.SelectedIndex)
TabControl1.SelectedTab(TabControl1.TabPages.Count - 1)
i = i - 1
End If
End Sub
End Class
|
|
|
|
|
What is this line supposed to do?
TabControl1.SelectedTab(TabControl1.TabPages.Count - 1)
If you are trying to select a tab, I think you have to use this:
TabControl1.SelectTab(TabControl1.TabPages.Count - 1)
|
|
|
|
|
It is meant to close the current open tab
|
|
|
|
|
when I double click on the error it says
TabControl1.SelectedTab(TabControl1.TabPages.Count - 1)
is the part of the code that has the error, it just keeps saying "Property access must assign to the property or use its value"
|
|
|
|
|
John McMahon wrote: Property access must assign to the property or use its value
That tells me that perhaps the code should read something like:
TabControl1.SelectedTab = TabControl1.TabPages.Count - 1
?
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Individuality is fine, as long as we do it together - F. Burns
|
|
|
|
|
I changed Selectedtab to selecttab as you said and it does exactly as I wanted now Thank you so much.
|
|
|
|
|
Hello everyone,
I've got a datagridview in a control which i populate programmatically using values retrieved from a database. Mostly it is fine, however when I set the vale of the cell to be a date i.e "2010-06-10" it transforms it into "10/06/2010 00:00:00". Oh and the type on the database is date...I'm guessing this is what vb is using as the cells format....
I don't mind the date switching around but the addition of the 00:00:00 looks stupid. I know it's probably very simple but I can't find the solution anywhere.
thanks in advance
Chris
modified on Friday, June 11, 2010 11:11 AM
|
|
|
|
|
How are you populating the DataGrid? Are you using a DataSet, DataTable or are you populating manually with a DataReader ? The answer to that question would enable someone to answer properly.
|
|
|
|
|
Hi,
sorry I'm doing it manually using a datareader:
While drgetcusfile.Read()
Dim count As Integer = fieldnostart
Dim rowcount As Integer = 0
grid.Columns.Add("Property", "Property")
grid.Columns.Add("Value", "Value")
While count <= fieldnoend
grid.Rows.Add()
grid.Item(0, rowcount).Value = rows(rowcount)
grid.Item(1, rowcount).Value = drgetcusfile.Item(count)
count += 1
rowcount += 1
End While
the datareader returns 1 row of various different types of data.
thanks,
Chris
|
|
|
|
|
I think in that case you will need to specifically test whether the value is a date using something like this :
While count <= fieldnoend
grid.Rows.Add()
grid.Item(0, rowcount).Value = rows(rowcount)
if drgetcustfile.Item(count).GetType.ToString = "System.DateTime" Then
grid.Item(1, rowcount).Value = Format(drgetcusfile.Item(count),"short date")
Else
grid.Item(1, rowcount).Value = drgetcusfile.Item(count)
End If
End While
This is just off the top of my head. Hope it Helps
Happy Coding
|
|
|
|
|
hi,
thanks for that, I think it would work.....
I figured another way around it when you asked about the method i was using to get the data:
While count <= fieldnoend
grid.Rows.Add()
grid.Item(0, rowcount).Value = rows(rowcount)
Try
grid.Item(1, rowcount).Value = drgetcusfile.GetString(count)
grid.Item(1, rowcount).Value = DateTime.ParseExact(grid.Item(1, rowcount).Value, "yyyy-MM-dd", Nothing).ToShortDateString()
Catch ex As Exception
End Try
count += 1
rowcount += 1
End While
the Datareader.GetString method is what I was looking for to get the exact string on the database, so I can try to parse it into a decent looking date.
Chris
|
|
|
|
|
I disagree with the approach you seem to have taken.
In a database dates and times should be stored in a specialized field type, and not as a string; strings will always lead to confusion, and make for difficult queries. It is the GUI that should take care of formatting, i.e. convert values (datetimes, numbers, whatever) to strings in the way the user wants them to be.
Hence, DateTime field in the database, and an appropriate format for each of the DGV columns.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
The OP has obviously got all his SQL and everything bundled in the UI. While WE know that approach is all wrong, I know if I look back to when I started, my apps were pretty much also unreadaable and unmaintainble, if I look back at them now. 
|
|
|
|
|
I can understand that, but can't approve it, so I felt a need to state so. What the OP does is up to him; he may or may not remember (and understand) my comment at some future occasion.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
yep your right,
all the sql for each of the controls is hard coded into the custom control. I possibly could bundle the sql into the databse (we're using Solid....ever heard of it?) however this has never been done in the application before and it's a 13 year habit I don't want to break. As Uniface, the other platform we have used since 1990, retrieves the data in such an efficiant manner in it's own weird way, starting to add queries into the database now doesn't make sense to us for few a tiny and simple query screens. Where else would you put the sql?
The data is stored in date type fields on the database so i shouldn't really get the string value I guess....
BTW, what is a OP?
Chris
oh and I tried
grid.Columns(0).DefaultCellStyle.Format = "dd/MM/yyyy"
but when the column is used for a actual string value, i.e an order number, I just get dd/MM/yyy although the date is converted properly
Turns out they needed to be date type cells for sorting so used this:
grid.Item(1, rowcount).Value = drgetcusfile.Item(count)
If grid.Item(1, rowcount).Value.GetType.Equals(GetType(Date)) Then
grid.Item(1, rowcount).Style.Format = "dd/MM/yyyy"
End If
:
|
|
|
|
|
It sounds to me as if it is not your typical n-Tier .Net app, and it has been working like that for so many years, why change it ? It may not live up to todays so-called standards, but I've been in this game for too long not to say things won't completely do a 180 degree turnaround in 5 years time.
|
|
|
|
|
private void Form1_Load(object sender, EventArgs e)
{
DataTable datatable = new DataTable();
datatable.Columns.Add("col1",typeof(DateTime));
datatable.Columns.Add("col2", typeof(DateTime));
datatable.Rows.Add(DateTime.Parse("2007/12/31 5:00:11 PM"), DateTime.Parse("2008/01/01 6:00:12 AM"));
datatable.Rows.Add(DateTime.Parse("2007/4/4 1:00:13 AM"), DateTime.Parse("2007/8/8 3:00:14 PM"));
dataGridView1.DataSource = datatable;
dataGridView1.Columns[0].DefaultCellStyle.Format = "dd'/'MM'/'yyyy";
dataGridView1.Columns[1].DefaultCellStyle.Format = "dd'/'MM'/'yyyy hh:mm:ss tt";
}
|
|
|
|
|
oops , just realised this a vb forum... but you get the idea.
|
|
|
|
|
In a DataGridView you can set the default formatting style for each individual column; a statement similar to this C# one would suffice
dgv.Columns[columnNumber].DefaultCellStyle.Format="dd MMM yyyy";
FWIW: You can also set the style for each individual cell, and you can wire a handler to an event that fires whenever any cell needs formatting. So everything is possible!
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
