SQL-stored procedure

ALTER PROCEDURE [dbo].[spss] 
    -- Add the parameters for the stored procedure here
    @c2 nvarchar(50),
    @c3 nvarchar(50) output
    --DECLARE @oo nchar(20) = 'Transaction1';
AS
BEGIN TRY
BEGIN TRAN m1
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- SET NOCOUNT ON;
    Insert into stu(stu,c1,c2) values(121,'hello',@c2)
    update the set t1='www' where the = 102
    -- Insert statements for procedure here
    set @c3 = @c2
    SELECT * FROM [stu]
    
COMMIT TRAN m1
return 999
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
     ROLLBACK TRAN m1
     -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH

Asp.Net-VB

Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim hh = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        Dim conn As New SqlConnection(hh)
        conn.Open()
        Dim Trans As SqlTransaction = conn.BeginTransaction()
        Dim sql = "Insert into stu(stu,c1,c2) values(null,'hello','kg')"
        Dim cmd As New SqlCommand(sql, conn, Trans)
        sql = "update the set t1='abcd' where the = 102"
        Dim cmd2 As New SqlCommand(sql, conn, Trans)
        Try

            cmd.ExecuteNonQuery()
            cmd2.ExecuteNonQuery()
            Trans.Commit()

        Catch ex As Exception

            Trans.Rollback()

        End Try

        cmd.Dispose()
        cmd2.Dispose()
        conn.Close()
        conn.Dispose()

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sds1 As SqlDataSource = New SqlDataSource
        sds1.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        sds1.SelectCommand = "SELECT * FROM [stu]"
        Dim dv As DataView = sds1.Select(DataSourceSelectArguments.Empty)
        GridView1.DataSource = dv
        GridView1.DataBind()
        sds1.Dispose()

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sds1 As SqlDataSource = New SqlDataSource
        sds1.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        sds1.SelectCommand = "SELECT * FROM [stu]"
        Dim dv As DataView = sds1.Select(DataSourceSelectArguments.Empty)
        Label1.Text = dv.Table.Rows(0)(0)
        GridView1.Rows(0).Cells(2).Text = dv.Table.Rows(0)(1)
        sds1.Dispose()

    End Sub

    Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim jj = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        Dim cn As New SqlConnection(jj)
        Dim cmd As New SqlCommand("SELECT * FROM [stu]", cn)
        cn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dr.Read()
        Label1.Text = dr(1).ToString
        dr.Close()
        cmd.Dispose()
        cn.Close()
        cn.Dispose()


    End Sub

    Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim sds1 As SqlDataSource = New SqlDataSource
        sds1.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        sds1.InsertCommand = "Insert into stu(stu,c1,c2) values(119,@c1,'kg')"
        'sds1.InsertParameters.Add("@c1", SqlDbType.Int)
        sds1.InsertParameters.Add(New Parameter("c1", DbType.String, TextBox1.Text))
        'sds1.InsertParameters("c1").DefaultValue = TextBox1.Text
        sds1.Insert()
        sds1.SelectCommand = "SELECT * FROM [stu]"
        Dim dv As DataView = sds1.Select(DataSourceSelectArguments.Empty)
        GridView1.DataSource = dv
        GridView1.DataBind()
        sds1.Dispose()


        Label1.Text = GridView1.Rows(0).Cells(1).Text
    End Sub

    Protected Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim hh = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        Dim conn As New SqlConnection(hh)
        Dim cmd As New SqlCommand("spss", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@c2", SqlDbType.Text).Value = TextBox1.Text

        Dim out As SqlParameter = cmd.Parameters.Add("@c3", SqlDbType.NVarChar, 50)
        out.Direction = ParameterDirection.Output

        Dim ret As SqlParameter = cmd.Parameters.Add("@ret", SqlDbType.Int)
        ret.Direction = ParameterDirection.ReturnValue
        conn.Open()
        cmd.ExecuteNonQuery()
        Label1.Text = out.Value.ToString
        TextBox1.Text = ret.Value.ToString
        cmd.Dispose()

        conn.Close()
        conn.Dispose()

        Dim sds1 As SqlDataSource = New SqlDataSource
        sds1.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        sds1.SelectCommand = "SELECT * FROM [stu]"
        Dim dv As DataView = sds1.Select(DataSourceSelectArguments.Empty)
        GridView1.DataSource = dv
        GridView1.DataBind()
        sds1.Dispose()
    End Sub

    Protected Sub Button6_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim sds1 As SqlDataSource = New SqlDataSource
        sds1.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        sds1.SelectCommand = "spss"
        sds1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
        sds1.SelectParameters.Add(New Parameter("c2", DbType.String, TextBox1.Text))
        sds1.SelectParameters.Add(New Parameter("c3", DbType.String, "null"))
        'sds1.Select(DataSourceSelectArguments.Empty)
        Dim dv As DataView = sds1.Select(DataSourceSelectArguments.Empty)
        GridView2.DataSource = dv
        GridView2.DataBind()
        sds1.Dispose()
    End Sub

    Protected Sub Button7_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim hh = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        Dim conn As New SqlConnection(hh)
        Dim cmd As New SqlCommand("spss", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@c2", SqlDbType.Text).Value = TextBox1.Text

        Dim out As SqlParameter = cmd.Parameters.Add("@c3", SqlDbType.NVarChar, 50)
        out.Direction = ParameterDirection.Output

        Dim ret As SqlParameter = cmd.Parameters.Add("@ret", SqlDbType.Int)
        ret.Direction = ParameterDirection.ReturnValue
        conn.Open()
        'Dim num As String = cmd.ExecuteScalar
        Dim dvi As SqlDataReader = cmd.ExecuteReader
        dvi.Read()
        dvi.Read()
        'Label1.Text = out.Value.ToString
        'TextBox1.Text = ret.Value.ToString
        Label1.Text = dvi(1).ToString
        cmd.Dispose()

        conn.Close()
        conn.Dispose()

        Dim sds1 As SqlDataSource = New SqlDataSource
        sds1.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        sds1.SelectCommand = "SELECT * FROM [stu]"
        Dim dv As DataView = sds1.Select(DataSourceSelectArguments.Empty)
        GridView1.DataSource = dv
        GridView1.DataBind()
        sds1.Dispose()
    End Sub
End Class

從使用中儲存格後開始尋找含有

Sub Click()
    
moji = "大佑"

'MsgBox "從使用中儲存格後開始尋找含有" & moji & "文字的儲存格"

Set iti = Cells.Find(What:=moji, After:=ActiveCell)
    
If iti Is Nothing Then
MsgBox "找不到您想要找的資料"
Exit Sub
Else
iti.Activate
End If
    
End Sub

VBA 最後一欄

Dim uyt As Integer
uyt = Range("f65536").End(xlUp).Row + 1
WorkSheets("Sheet1").Cells(uyt,"F").Value = "hi"

在最後一欄補上文字

VBA

Sub Click()
    Worksheets("Sheet1").Range("A1").Value = Replace(Worksheets("Sheet2").Range("A1").Value, "年", "")
    Worksheets("Sheet1").Range("B1").Value = Replace(Worksheets("Sheet2").Range("B1").Value, "月", "")
    Worksheets("Sheet1").Range("C1").Value = Replace(Worksheets("Sheet2").Range("A12").Value, "日", "")
    Dim y, m, d, tq, ty As String
    y = Replace(Worksheets("Sheet2").Range("A1").Value, "年", "")
    m = Replace(Worksheets("Sheet2").Range("B1").Value, "月", "")
    If Len(m) = 1 Then
        m = "0" & m
    End If
    d = Replace(Worksheets("Sheet2").Range("A12").Value, "日", "")
    If Len(d) = 1 Then
        d = "0" & d
    End If
    tq = y & m & d
    ty = y & m
    '抓位補風
    Worksheets("Sheet1").Range("D1").Value = Split(Worksheets("Sheet2").Range("C2:AF2").Find(Worksheets("Sheet1").Range("C1").Value).Address(1, 0), "$")
    Dim aa As String
    aa = Worksheets("Sheet1").Range("D1").Value
    Dim j As Integer
    j = 1
    '找莊家號碼列欄
    For i = 3 To Worksheets("Sheet2").Range("A14").Value
        
        With Worksheets("Sheet2")
        If Not .Cells(i, aa).Value = "" Then
            
            Worksheets("Sheet1").Cells(j, 6).Value = .Cells(i, 1).Value & vbCrLf & .Cells(i, 2).Value
            Dim fg As String
            fg = .Cells(i, 2 + d).Value
            '爪爪
            If Not fg = "" Then
                For k = 1 To 7
                If .Cells(i, 2 + d + k).Value = "" Then
                    Dim re As String
                    re = d + k - 1
                    If Len(re) = 1 Then
                        re = "0" & re
                    End If
                    If fg = "8" Then
                    Worksheets("Sheet1").Cells(j, 7).Value = tq & "0800" & "-" & ty & re & Replace(.Cells(i, 2 + d + k - 1).Value, "例", "") & "00"
                    ElseIf fg = "18" Then
                    Worksheets("Sheet1").Cells(j, 7).Value = tq & "1800" & "-" & ty & re & Replace(.Cells(i, 2 + d + k - 1).Value, "例", "") & "00"
                    ElseIf fg = "例" Or fg = "慰" Or fg = "例21" Then
                        For g = 1 To 7
                        
                            
                                If Worksheets("Sheet2").Cells(2, 2 + d - g).Value = 1 And Not Worksheets("Sheet2").Cells(i, 2 + d - g).Value = "" Then
                                Worksheets("Sheet1").Cells(j, 7).Value = ty & "01" & .Cells(i, 2 + d - g).Value & "00" & "-" & ty & re & Replace(.Cells(i, 2 + d + k - 1).Value, "例", "") & "00"
                                Exit For
                                ElseIf .Cells(i, 2 + d - g).Value = "" Then
                                Dim ret As String
                                ret = d - g + 1
                                If Len(ret) = 1 Then
                                ret = "0" & ret
                                
                                Worksheets("Sheet1").Cells(j, 7).Value = ty & ret & .Cells(i, 2 + d - g + 1).Value & "00" & "-" & ty & re & Replace(.Cells(i, 2 + d + k - 1).Value, "例", "") & "00"
                                End If
                                
                                Exit For
                                End If
                                
                                
                                
                                
                            
                            
                        Next
                        
                    End If
                    
                    
                Exit For
                End If
                
                
                Next
                
                
            End If
            
            
            
            j = j + 1
            
            
        End If
        End With
    Next
    
    
    
    
End Sub

VB 資料庫插入並更新語法

Dim hh = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sp").ConnectionString
        Dim conn As New SqlConnection(hh)
        conn.Open()
        Dim Trans As SqlTransaction = conn.BeginTransaction()
        Dim sql = "Insert into stu(stu,c1,c2) values(115,'hello','kg')"
        Dim cmd As New SqlCommand(sql, conn, Trans)
        sql = "update the set t1='rockman' where the = 102"
        Dim cmd2 As New SqlCommand(sql, conn, Trans)
        Try

            cmd.ExecuteNonQuery()
            cmd2.ExecuteNonQuery()
            Trans.Commit()

        Catch ex As Exception

            Trans.Rollback()

        End Try

        cmd.Dispose()
        cmd2.Dispose()
        conn.Close()
        conn.Dispose()