Vb6 sybntax counterpart of VB.Net.

priyamtheone

What's the VB6 counterpart of the following codes? To insert a record in database with command object parameters:

dim strSql as string
strSql = "Insert Into tblEmployees (EmpName,DeptartmentID,JoinDate) Values ('" & _
txtName.Text & "'," & Val(cboDepartment.Value) & ",@JoinDate)"
com = CreateCommand(strSql)

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 com.ExecuteNonQuery = 1 Then
MsgBox("Record saved.")
End If

To obtain a record from database:

Dim drd As SqlDataReader
strSql = "Select EmpName,DeptID,JoinDate From tblEmployees Where EmpID=1001"
drd = CreateReader(strSql)
If drd.Read Then
txtName.Text = drd("EmpName")
cboDepartment.Value = drd("DepartmentID")

If IsDBNull(drd("JoinDate")) Then
	dtpJoinDate.ResetText()
	dtpJoinDate.Checked = False
Else
	dtpJoinDate.Value = drd("JoinDate")
End If

End If
drd.Close()

Abhinav S

See here.

My signature "sucks" today

Anshul R

You need to include references to SQL in VB6. But I doubt if it is supported..

Lost User

Hi, You can find an example on VB6 and ADO over here[^].

I are Troll :suss:

Lost User

It's supported :)

priyamtheone

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.

Lost User

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 :suss:

priyamtheone

Thanks Eddy, implemented your idea. My problem is almost solved except a small bug. I have a data entry form with certain fields among which is a datetimepicker. The idea is while inserting or updating records through that form the application will first check whether the datetimepicker is checked. If yes then it'll pass the value of datetimepicker to the command parameter or else it'll pass null value to the command parameter. The corresponding database table column for the datetimepicker field has smalldatetime data type. First take a look into my code:

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 “Reco

Lost User

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 :suss: