Adding/Updating Sql Records

Updated everettnewell 0 Tallied Votes 537 Views Share

With both subs you must enter the values as you would write them into a sql string i.e. 'mustsurroundstrings'

I'm rather new at VB.Net and SQL but these work for me and are seem pretty efficient. If anyone has a way to improve on them or finds a flaw in them please feel free to email me [snipped].

Public Sub AddRecord(ByVal tbl As String, ByVal fields As String, ByVal values As String)
        Dim con As New SqlConnection
        Dim sqlcmd As SqlCommand
        con.ConnectionString = My.Resources.ConnString
        Try
            con.Open()
            Try
                sqlcmd = New SqlCommand("INSERT INTO [" & tbl & "] (" & fields & ") VALUES(" & values & ")", con)
                Dim check As Integer = sqlcmd.ExecuteReader.RecordsAffected
                If check < 1 Then
                    MsgBox("Record was not added to " & tbl)
                End If
            Catch ex As Exception
                MsgBox(errormsg(ex))
            End Try
        Catch ex As Exception
            MsgBox(errormsg(ex))
        Finally
            con.Close()
        End Try

    End Sub

Public Sub UpdateRecord(ByVal tbl As String, ByVal id As String, ByVal fields As String, ByVal values As String)
        Dim con As New SqlConnection
        Dim sqlcmd As SqlCommand

        con.ConnectionString = My.Resources.ConnString
        Dim multifields() As String = {fields}
        Dim multivalues() As String = {values}

        If InStr(",", fields) Then
            multifields = Split(fields, ",")
        End If
        If InStr(",", values) Then
            multivalues = Split(values, ",")
        End If

        Try
            con.Open()
            Try
                For n As Integer = 0 To multifields.Length - 1
                    If multifields(n) <> "" Then
                        sqlcmd = New SqlCommand("UPDATE [" & tbl & "] SET=[" & multifields(n) & "]=" & multivalues(n) _
                                                & " WHERE ([Id]=" & id & ")", con)
                        Dim check As Integer = sqlcmd.ExecuteReader.RecordsAffected
                        If check < 1 Then
                            MsgBox("Record was not modified")
                        End If
                    End If
                Next
            Catch ex As Exception
                MsgBox(errormsg(ex))
            End Try
        Catch ex As Exception
            MsgBox(errormsg(ex))
        Finally
            con.Close()
        End Try
    End Sub
naeem sikandar 0 Newbie Poster

good ,,,,,,,,,,,, bosss nce program keeeeeeeeeep it
really up .......

awachiraantony 0 Newbie Poster

can someone send me the code for a login form please.am using vb.net and sql.urgent!!!

vishalrane 0 Junior Poster in Training
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        If txtUsername.Text = "" Then
            MsgBox("Please Enter Username")
        ElseIf txtPassword.Text = "" Then
            MsgBox("Please Enter Password")
        End If

        Try
            Dim con As New SqlConnection("Data Source=XXXX-B4C521B850\SQLEXPRESS;Initial Catalog=TT;Integrated Security=True")
            Dim ds1 As New DataSet
           
            Dim da1 As New SqlDataAdapter("select * from Login where UserName='" & Trim(txtUsername.Text) & "'and Password='" & Trim(txtPassword.Text) & "'", con)

            If da1.Fill(ds1) Then

                frmMdi.Show()
                Me.Hide()

            Else
                MsgBox("Invalid Password or Username")

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
adam_k 239 Master Poster

@vishalrane Your login form is pretty easy to bypass.
Change lines 10 - 14 to:

dim dc as sqlcommand 
dc.connection = con

dc.commandtext = "select count(*) from Login where UserName ='" &Trim(txtUsername.Text) & "' and Password='" & Trim(txtPassword.Text) & "'") 
con.open
dim result = dc.executescalar

if result = 1 then

(Written by heart, may contain typos or errors)

This way even if the query doesn't return the results you intend, the form won't show.

If you want to take things seriously get the QUOTENAME function involved when passing username and password.

Maulikpra 0 Newbie Poster

can send me update single row code for vb.net in studio 2010 ...... plzzzzzzzz..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.