如何在整数前添加字符

huangapple go评论61阅读模式
英文:

How to prepend character to Integer

问题

我们有一个名为returnNo的字段,当从数据库查询时返回整数值。

整数返回示例为4098

我们想在整数值前添加字符T,然后将该值加1。将附加了此字符并加1的结果插入到数据库中。

当我运行以下代码时,它只返回整数值而没有T字符,并将该值插入数据库。

我做错了什么?

Dim retNo As Integer = 0
Dim return_No As String = ""
Dim dteReturned As String = ""
Try
    Dim Sqls As String = "select MAX(returnNo) returnNo from Trials"
    Dim Sqlsr As String = "select DateReturned from Trials where ownerId='" & Session("rOwnerID") & "' "
    Dim prevretcmd As SqlCommand = New SqlCommand(Sqls)
    prevretcmd.Connection = con
    Dim rdr As SqlDataReader = prevretcmd.ExecuteReader()
    If rdr.Read() Then
        dteReturned = rdr("DateReturned").ToString()
        testReturnNo.Text = rdr("ReturnNo").ToString()
        retNo = Convert.ToInt32(testReturnNo.Text.Replace("T", "")) + 1
        returnNo.Text = "T" + retNo.ToString().PadLeft(1, "0"c)
    End If
    rdr.Close()
Catch
    retNo = 1
    returnNo.Text = retNo.ToString()
End Try
英文:

We have a field named returnNo that returns integer values when queried from the database.

Example of integer returned is 4098.

We would like to add the character T at the beginning of the integer values and then increment the value by 1. The result of appending this character to the integer value and incrementing it by 1 is then inserted into the database.

When I run the code below, it returns only the integer value without the T character and inserts this value to the database.

What am I doing wrong?

Dim retNo As Integer = 0
Dim return_No As String = ""
Dim dteReturned As String = ""
Try
    Dim Sqls As String = "select MAX(returnNo) returnNo from Trials"
    Dim Sqlsr As String = "select DateReturned from Trials where ownerId='" & Session("rOwnerID") & "' "
    Dim prevretcmd As SqlCommand = New SqlCommand(Sqls)
    prevretcmd.Connection = con
    Dim rdr As SqlDataReader = prevretcmd.ExecuteReader()
    If rdr.Read() Then
	dteReturned = rdr("DateReturned").ToString()
	testReturnNo.Text = rdr("ReturnNo").ToString()
	retNo = Convert.ToInt32(testReturnNo.Text.Replace("T", "")) + 1
	returnNo.Text = "T" + retNo.ToString().PadLeft(1, "0"c)
    End If
    rdr.Close()
Catch
    retNo = 1
    returnNo.Text = retNo.ToString()
End Try

答案1

得分: 1

你的代码中有两个完全独立的SQL查询,而你混淆了获取最大值和获取一行数据的目的。

将其拆分为更小的步骤。

以下是应该工作的代码:

Protected Sub Button1_Click(sender As Object, e As EventArgs)
    Dim retNo As Integer = 0
    Dim return_No As String = ""
    Dim dteReturned As String = ""

    Dim cmdSQL As New SqlCommand("select MAX(returnNo) returnNo from Trials")

    Dim rstMAX As DataTable = MyRstP(cmdSQL)
    retNo = rstMAX.Rows(0)(0)

    cmdSQL.CommandText = "select DateReturned from Trials where ownerId=@ownerid"
    cmdSQL.Parameters.Add("@ownerid", SqlDbType.Int).Value = Session("rOwnerID")

    Dim rstTrial As DataRow = MyRstP(cmdSQL).Rows(0)
    dteReturned = rstTrial("DateReturned").ToString()
    testReturnNo.Text = retNo.ToString()
    retNo += 1
    returnNo.Text = "T" & retNo.ToString().PadLeft(6, "0")
End Sub

Public Function MyRstP(cmdSQL As SqlCommand) As DataTable
    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using
    Return rstData
End Function

所以,我们的第一个查询从表中获取最大值。

然后我们的第二个查询根据session()中的id值获取数据库行(日期列)。

我们获取retNo,并在文本框中显示它。

然后我们加上+1,然后在第二个文本框中显示,用"0"填充到6位。

结果如下:

如何在整数前添加字符

所以,在表中找到的最大值是4098,我们加上+1,然后将结果带有"0"的填充放入第二个文本框中。

编辑:保存的数据不是整数,而是带有填充的字符串

好的,所以Max()查询将返回一个字符串,其中包含一个字母、一些"0"和数字。

因此,我们必须修改我们的代码如下:

Dim rstMAX As DataTable = MyRstP(cmdSQL)
Dim retNoText As String = ""
retNoText = rstMAX.Rows(0)(0)
retNo = retNoText.Substring(1) ' 获取第一个字母之后的所有字符

所以,我们获取/抓取最大的字符串,比如T000123,或者K000234,等等。

然后我们忽略第一个字母,然后加上+1。

这意味着你可能会得到这样的结果:

A000123
B000123

嗯,从某种意义上说,我们有重复的数字,因为我们的最大查询将返回B000123,去掉字母B,然后产生一个数字124。

然后我们格式化为T前缀。

T000124

不确定这是否是你想要的,但上面的小改动将以这种方式工作。

英文:

You have 2 100% separate SQL queries here, and you mixing up getting the MAX number vs that of pulling one row.

Break this out into more bite sized steps.

This code should work:

Protected Sub Button1_Click(sender As Object, e As EventArgs)

    Dim retNo As Integer = 0
    Dim return_No As String = ""
    Dim dteReturned As String = ""

    Dim cmdSQL As New _
            SqlCommand("select MAX(returnNo) returnNo from Trials")

    Dim rstMAX As DataTable = MyRstP(cmdSQL)
    retNo = rstMAX.Rows(0)(0)

    cmdSQL.CommandText =
            "select DateReturned from Trials where ownerId=@ownerid"
    cmdSQL.Parameters.Add("@ownerid", SqlDbType.Int).Value = Session("rOwnerID")

    Dim rstTrial As DataRow = MyRstP(cmdSQL).Rows(0)
    dteReturned = rstTrial("DateReturned").ToString()
    testReturnNo.Text = retNo.ToString()
    retNo += 1
    returnNo.Text = "T" & retNo.ToString().PadLeft(6, "0")

End Sub

Public Function MyRstP(cmdSQL As SqlCommand) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

So, our first query gets the max value from the table.

Then our 2nd query gets the database row (date column) based on id value in session().

We get retNo, display it in a text box.

The we add + 1, and then display in the 2nd text box, padded out to 6 "0"s.

The result looks like this:

如何在整数前添加字符

So, the max value found in the table was 4098, and we added + 1, and then placed the result with padding of "0"s into that 2nd text box.

Edit: Data saved is not a integer, but padded string

Ok, so the Max() query going to return a string, one padding with a letter, some "0"'s and then the number.

So, we have to modify our code like this:

    Dim rstMAX As DataTable = MyRstP(cmdSQL)
    Dim retNoText As String = ""
    retNoText = rstMAX.Rows(0)(0)
    retNo = retNoText.Substring(1) ' get all characters AFTER first letter skipped

So, we get/grab the max string, T000123, OR K000234 or whatever.

We then igonre the FIRST letter, and then add + 1

This means that you might have this

  A000123
  B000123

Well, then in a sense we have duplicate numbers, since our max query will return the B000123, stirp off the letter B, and then produce a number of 124.

We then format with a T in front.

 T000124

Note 100% sure if this is what you want, but the above small change will work this way.

huangapple
  • 本文由 发表于 2023年6月2日 11:56:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76387021.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定