Click here to Skip to main content
15,396,794 members
Home / Discussions / Visual Basic
   

Visual Basic

 
AnswerRe: how to make .OBJ 3D model viewer for vb.net 2010 Pin
RedDk9-May-22 10:40
MemberRedDk9-May-22 10:40 
Generalthat link isnt for vs2010 Pin
Android Oyunları22-May-22 5:48
MemberAndroid Oyunları22-May-22 5:48 
Questionhow to view obj with direct x 8 vb6 Pin
Android Oyunları28-Apr-22 7:42
MemberAndroid Oyunları28-Apr-22 7:42 
AnswerRe: how to view obj with direct x 8 vb6 Pin
Richard Andrew x6428-Apr-22 8:01
professionalRichard Andrew x6428-Apr-22 8:01 
GeneralRe: how to view obj with direct x 8 vb6 Pin
Android Oyunları28-Apr-22 8:27
MemberAndroid Oyunları28-Apr-22 8:27 
JokeRe: how to view obj with direct x 8 vb6 Pin
Mycroft Holmes28-Apr-22 11:49
professionalMycroft Holmes28-Apr-22 11:49 
AnswerRe: how to view obj with direct x 8 vb6 Pin
CHill606-May-22 5:10
mveCHill606-May-22 5:10 
QuestionOperator '=' is not defined for type for 'DBNull' and string '1' Pin
LohJane20-Apr-22 23:13
MemberLohJane20-Apr-22 23:13 
Hi VB Experts,

I cannot update my data in the Excel and receive this error
Operator '=' is not defined for type for 'DBNull' and string '1'.
The following is my codes:

VB
Dim cnn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + str_Database + ";Extended Properties=Excel 12.0;")
        Dim cmd = New OleDbCommand()
        Dim str_SQL As String = ""
        Dim int_Max_SN As Integer

        Try
            If (tb_SN.Text = "") Then
                cnn.Open()
                cmd.Connection = cnn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT IIF(ISNULL(MAX(VAL([S/N]))), 0, MAX(VAL([S/N]))) FROM [Sheet2$]"
                int_Max_SN = cmd.ExecuteScalar()
                cnn.Close()

                tb_SN.Text = int_Max_SN + 1

                str_SQL += "INSERT INTO [Sheet2$] "
                str_SQL += "([S/N], [MPN], [Description], [OEM (Manufacturer)], [MTBF (Hours)], [Failure Rate], [Repair Turn Around Time], [Utilization Rate], [Population], [Spares Required], [Testing], [Price Per Unit], [Total Cost Per Line Item]) "
                str_SQL += "VALUES (" + tb_SN.Text + ", "
                str_SQL += "'" + tb_MPN.Text + "', "
                str_SQL += "'" + tb_Description.Text + "', "
                str_SQL += "'" + tb_OEM.Text + "', "
                If (IsNumeric(tb_MTBF.Text)) Then str_SQL += tb_MTBF.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Failure_Rate.Text)) Then str_SQL += tb_Failure_Rate.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Year_Of_Support.Text)) Then str_SQL += tb_Year_Of_Support.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Utilization_Rate.Text)) Then str_SQL += tb_Utilization_Rate.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Population.Text)) Then str_SQL += tb_Population.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Spares_Required.Text)) Then str_SQL += tb_Spares_Required.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Price_Per_Unit.Text)) Then str_SQL += tb_Price_Per_Unit.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Total_Cost_Per_Line_Item.Text)) Then str_SQL += tb_Total_Cost_Per_Line_Item.Text + ", " Else str_SQL += "null, "
                If (IsNumeric(tb_Total_Uptime.Text)) Then str_SQL += tb_Total_Uptime.Text Else str_SQL += "null "
                str_SQL += ")"

                cnn.Open()
                cmd.Connection = cnn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = str_SQL
                cmd.ExecuteNonQuery()
                cnn.Close()

                LoadRecords()

                MessageBox.Show("New record has been added successfully!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                For int_Row_Index As Integer = 0 To dgv_Excel.Rows.Count - 1
                    If (dgv_Excel.Rows(int_Row_Index).Cells(0).Value = tb_SN.Text) Then
                        dgv_Excel.Rows(int_Row_Index).Cells(1).Value = tb_MPN.Text
                        dgv_Excel.Rows(int_Row_Index).Cells(2).Value = tb_Description.Text
                        dgv_Excel.Rows(int_Row_Index).Cells(3).Value = tb_OEM.Text

                        dgv_Excel.Rows(int_Row_Index).Cells(4).Value = IIf(IsNumeric(tb_MTBF.Text), tb_MTBF.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(5).Value = IIf(IsNumeric(tb_Failure_Rate.Text), tb_Failure_Rate.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(6).Value = IIf(IsNumeric(tb_Year_Of_Support.Text), tb_Year_Of_Support.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(7).Value = IIf(IsNumeric(tb_Utilization_Rate.Text), tb_Utilization_Rate.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(8).Value = IIf(IsNumeric(tb_Population.Text), tb_Population.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(9).Value = IIf(IsNumeric(tb_Spares_Required.Text), tb_Spares_Required.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(10).Value = IIf(IsNumeric(tb_Price_Per_Unit.Text), tb_Price_Per_Unit.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(11).Value = IIf(IsNumeric(tb_Total_Cost_Per_Line_Item.Text), tb_Total_Cost_Per_Line_Item.Text, DBNull.Value)
                        dgv_Excel.Rows(int_Row_Index).Cells(12).Value = IIf(IsNumeric(tb_Total_Uptime.Text), tb_Total_Uptime.Text, DBNull.Value)

                        int_Row_Index += 1
                    End If
                Next

                str_SQL += "UPDATE [Sheet2$] "
                str_SQL += "SET [MPN] = '" + tb_MPN.Text + "', "
                str_SQL += "[Description] = '" + tb_Description.Text + "', "
                str_SQL += "[OEM (Manufacturer)] = '" + tb_OEM.Text + "', "
                If (IsNumeric(tb_MTBF.Text)) Then str_SQL += "[MTBF (Hours)] = " + tb_MTBF.Text + ", " Else str_SQL += "[MTBF (Hours)] = null, "
                If (IsNumeric(tb_Failure_Rate.Text)) Then str_SQL += "[Failure Rate] = " + tb_Failure_Rate.Text + ", " Else str_SQL += "[Failure Rate] = null, "
                If (IsNumeric(tb_Year_Of_Support.Text)) Then str_SQL += "[Repair Turn Around Time] = " + tb_Year_Of_Support.Text + ", " Else str_SQL += "[Repair Turn Around Time] = null, "
                If (IsNumeric(tb_Utilization_Rate.Text)) Then str_SQL += "[Utilization Rate] = " + tb_Utilization_Rate.Text + ", " Else str_SQL += "[Utilization Rate] = null, "
                If (IsNumeric(tb_Population.Text)) Then str_SQL += "[Population] = " + tb_Population.Text + ", " Else str_SQL += "[Population] = null, "
                If (IsNumeric(tb_Spares_Required.Text)) Then str_SQL += "[Spares Required] = " + tb_Spares_Required.Text + ", " Else str_SQL += "[Spares Required] = null, "
                If (IsNumeric(tb_Price_Per_Unit.Text)) Then str_SQL += "[Testing] = " + tb_Price_Per_Unit.Text + ", " Else str_SQL += "[Testing] = null, "
                If (IsNumeric(tb_Total_Cost_Per_Line_Item.Text)) Then str_SQL += "[Price Per Unit] = " + tb_Total_Cost_Per_Line_Item.Text + ", " Else str_SQL += "[Price Per Unit] = null, "
                If (IsNumeric(tb_Total_Uptime.Text)) Then str_SQL += "[Total Cost Per Line Item] = " + tb_Total_Uptime.Text + " " Else str_SQL += "[Total Cost Per Line Item] = null "
                str_SQL += "WHERE VAL([S/N]) = " + tb_SN.Text

                cnn.Open()
                cmd.Connection = cnn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = str_SQL
                cmd.ExecuteNonQuery()
                cnn.Close()

                MessageBox.Show("Record has been updated successfully!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try


modified 21-Apr-22 11:15am.

SuggestionRe: Operator '=' is not defined for type for 'DBNull' and string '1' Pin
Richard Deeming20-Apr-22 23:32
mveRichard Deeming20-Apr-22 23:32 
QuestionRe: Operator '=' is not defined for type for 'DBNull' and string '1' Pin
CHill6021-Apr-22 5:53
mveCHill6021-Apr-22 5:53 
AnswerRe: Operator '=' is not defined for type for 'DBNull' and string '1' Pin
Raphael Adeniji21-Apr-22 6:11
MemberRaphael Adeniji21-Apr-22 6:11 
AnswerRe: Operator '=' is not defined for type for 'DBNull' and string '1' Pin
Richard MacCutchan21-Apr-22 23:22
mveRichard MacCutchan21-Apr-22 23:22 
AnswerRe: Operator '=' is not defined for type for 'DBNull' and string '1' Pin
CMYork0125-Apr-22 4:57
MemberCMYork0125-Apr-22 4:57 
QuestionQuestion about creating dir Pin
User2202219-Apr-22 9:54
MemberUser2202219-Apr-22 9:54 
AnswerRe: Question about creating dir Pin
Dave Kreskowiak19-Apr-22 11:21
mveDave Kreskowiak19-Apr-22 11:21 
QuestionApp.Path & "\" & App.EXEName alternative Pin
Everdome io5-Apr-22 2:08
MemberEverdome io5-Apr-22 2:08 
AnswerRe: App.Path & "\" & App.EXEName alternative Pin
Richard Deeming5-Apr-22 2:25
mveRichard Deeming5-Apr-22 2:25 
GeneralRe: App.Path & "\" & App.EXEName alternative Pin
Everdome io5-Apr-22 5:04
MemberEverdome io5-Apr-22 5:04 
AnswerRe: App.Path & "\" & App.EXEName alternative Pin
Raphael Adeniji17-Apr-22 10:31
MemberRaphael Adeniji17-Apr-22 10:31 
Questionhow to add dynamic image in crystal report from vb6 Pin
Datta Bhor27-Mar-22 7:59
MemberDatta Bhor27-Mar-22 7:59 
AnswerRe: how to add dynamic image in crystal report from vb6 Pin
Raphael Adeniji18-Apr-22 8:40
MemberRaphael Adeniji18-Apr-22 8:40 
GeneralMessage Closed Pin
22-Apr-22 3:01
MemberAman ADMEC22-Apr-22 3:01 
QuestionGoogle Drive API in VB.net check if file is opened Pin
Goldrake_Act23-Mar-22 21:47
MemberGoldrake_Act23-Mar-22 21:47 
Questionsearch for specific word in a text file Pin
Benjamindh19-Mar-22 18:09
MemberBenjamindh19-Mar-22 18:09 
AnswerRe: search for specific word in a text file Pin
Richard MacCutchan19-Mar-22 22:17
mveRichard MacCutchan19-Mar-22 22:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.