英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论