General

The GOTO statement in T-SQL allows you to transfer control to a labeled line of code within a stored procedure or a batch. The labeled line is identified by a unique label name followed by a colon (:). Here is an example of how you can use the GOTO statement in T-SQL:

DECLARE @Value INT = 1

IF @Value = 1
BEGIN
PRINT ‘Value is 1’
GOTO EndOfCode
END

PRINT ‘Value is not 1’

EndOfCode:
PRINT ‘End of code’

In this example, the IF statement checks if the value of the @Value variable is equal to 1. If the value is 1, the PRINT statement outputs “Value is 1” and control is transferred to the labeled line of code identified by EndOfCode using the GOTO statement. The execution continues from that line and the PRINT statement outputs “End of code”.

It is important to note that the use of the GOTO statement is generally discouraged in T-SQL programming as it can make the code difficult to understand and maintain. It is recommended to use structured control flow statements such as IF, WHILE, and FOR instead of GOTO.

declare @UserCode nVarChar(6),@NextID Int=2

SET @UserCode =
RIGHT(‘000000’ +
CAST(
ABS(CAST(CAST(NEWID() AS BINARY(8)) AS BIGINT)) % 1000000
AS VARCHAR(6)
),
6);

Print @Usercode

DECLARE @rtnStr nVarChar(6)

set @rtnStr= RIGHT(‘000000’ +
CAST(
ABS(CAST(CAST(NEWID() AS BINARY(8)) AS BIGINT)) % 1000000
AS VARCHAR(6)
),
6);

Clean the solution: Sometimes, cleaning the solution can resolve performance issues in Visual Studio. To do this, go to Build > Clean Solution.

Reset Visual Studio settings: If your Visual Studio settings have become corrupted, you can reset them to the default settings. To do this, go to Tools > Import and Export Settings > Reset all settings.

Delete the suo file: The suo file is a hidden file that stores Visual Studio settings. Deleting the suo file can help resolve performance issues in Visual Studio. To do this, close Visual Studio, locate the suo file in the solution folder, and delete it.

CREATE TABLE sales (
POSName varchar(255),
BillNo varchar(255),
Date date,
Time datetime,
SubTotal decimal(18,2),
DiscountAmount decimal(18,2),
GrandTotalAmount decimal(18,2),
SettelementMode varchar(255),
TransactionStatus varchar(255)
);

CREATE TABLE taxes (
BillNo varchar(255),
TaxDesciption varchar(255),
TaxAmount decimal(18,2)
);

DECLARE @xml xml = ‘NavaFP01000012022-12-1712:20:43 AM0.00.00.0S C 10 %0.0C.Gst 2.5 %0.0S.Gst 2.5 %0.0NcSale‘;

INSERT INTO sales (POSName, BillNo, Date, Time, SubTotal, DiscountAmount, GrandTotalAmount, SettelementMode, TransactionStatus)
SELECT r.value(‘POSName[1]’, ‘varchar(255)’),
r.value(‘BillNo[1]’, ‘varchar(255)’),
r.value(‘Date[1]’, ‘date’),
r.value(‘Time[1]’, ‘datetime’),
r.value(‘SubTotal[1]’, ‘decimal(18,2)’),
r.value(‘DiscountAmount[1]’, ‘decimal(18,2)’),
r.value(‘GrandTotalAmount[1]’, ‘decimal(18,2)’),
r.value(‘SettelementMode[1]’, ‘varchar(255)’),
r.value(‘TransactionStatus[1]’, ‘varchar(255)’)
FROM @xml.nodes(‘/Records/Record’) AS t(r);

INSERT INTO taxes (BillNo, TaxDesciption, TaxAmount)
SELECT r.value(‘BillNo[1]’, ‘varchar(255)’),
t.value(‘TaxDesciption[1]’, ‘varchar(255)’),
t.value(‘TaxAmount[1]’, ‘decimal(18,2)’)
FROM @xml.nodes(‘/Records/Record’) AS t(r)
CROSS APPLY t.r.nodes(‘Taxes/Tax’) AS x(t);

Calling

Imports System.Data.SqlClient

Module Module1
Sub Main()
Dim con As SqlConnection = New SqlConnection(“Data Source=(local);Initial Catalog=TestDB;Integrated Security=True”)
con.Open()
Dim cmd As SqlCommand = New SqlCommand(“sp_InsertXMLData”, con)
cmd.CommandType = CommandType.StoredProcedure
Dim xmlData As String = “NavaFP01000012022-12-1712:20:43 AM0.00.00.0S C 10 %0.0C.Gst 2.5 %0.0S.Gst 2.5 %0.0NcSale”
cmd.Parameters.AddWithValue(“@xmlData”, xmlData)
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine(reader(0).ToString())
End While
reader.Close()
con.Close()
End Sub
End Module

Alternate

CREATE PROCEDURE usp_InsertRecord (@xmlData XML)
AS
BEGIN
DECLARE @POSName VARCHAR(50)
DECLARE @BillNo VARCHAR(50)
DECLARE @Date DATE
DECLARE @Time TIME
DECLARE @SubTotal DECIMAL(18, 2)
DECLARE @DiscountAmount DECIMAL(18, 2)
DECLARE @GrandTotalAmount DECIMAL(18, 2)
DECLARE @SettelementMode VARCHAR(50)
DECLARE @TransactionStatus VARCHAR(50)

— extract values from the XML
SELECT @POSName = T.c.value(‘POSName[1]’, ‘VARCHAR(50)’),
@BillNo = T.c.value(‘BillNo[1]’, ‘VARCHAR(50)’),
@Date = T.c.value(‘Date[1]’, ‘DATE’),
@Time = T.c.value(‘Time[1]’, ‘TIME’),
@SubTotal = T.c.value(‘SubTotal[1]’, ‘DECIMAL(18, 2)’),
@DiscountAmount = T.c.value(‘DiscountAmount[1]’, ‘DECIMAL(18, 2)’),
@GrandTotalAmount = T.c.value(‘GrandTotalAmount[1]’, ‘DECIMAL(18, 2)’),
@SettelementMode = T.c.value(‘SettelementMode[1]’, ‘VARCHAR(50)’),
@TransactionStatus = T.c.value(‘TransactionStatus[1]’, ‘VARCHAR(50)’)
FROM @xmlData.nodes(‘/Record’) T(c)

— insert values into the Sales table
INSERT INTO Sales (POSName, BillNo, Date, Time, SubTotal, DiscountAmount, GrandTotalAmount, SettelementMode, TransactionStatus)
VALUES (@POSName, @BillNo, @Date, @Time, @SubTotal, @DiscountAmount, @GrandTotalAmount, @SettelementMode, @TransactionStatus)

DECLARE @TaxId INT
SELECT @TaxId = SCOPE_IDENTITY()

— insert values into the Taxes table
INSERT INTO Taxes (SalesId, TaxDesciption, TaxAmount)
SELECT @TaxId, T1.c1.value(‘TaxDesciption[1]’, ‘VARCHAR(50)’), T1.c1.value(‘TaxAmount[1]’, ‘DECIMAL(18, 2)’)
FROM @xmlData.nodes(‘/Record/Taxes/Tax’) T1(c1)
END

To set an XML document as a data source in a DataGridView control in VB.NET WinForms, you can use the following steps:

Load the XML data into a DataSet.

Dim ds As New DataSet()
ds.ReadXml(xmlFilePath)

Set the DataSet as the DataSource for the DataGridView control.
DataGridView1.DataSource = ds.Tables(0)

DataGridView1.DataSource = ds.Tables(0)

Optionally, you can customize the appearance of the DataGridView control by setting properties such as ColumnHeadersVisible, SelectionMode, etc.

DataGridView1.ColumnHeadersVisible = True
DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

To load an XML string into a DataSet in VB.NET, you can use the following code:

Dim ds As New DataSet()
Dim xmlString As String = “” & _
“” & _
“Nava” & _
“FP0100001” & _
“2022-12-17” & _
“12:20:43 AM” & _
“” & _
“”

Dim sr As New StringReader(xmlString)
ds.ReadXml(sr)

The StringReader class allows you to read data from a string as if it were a stream of characters. After reading the XML data into a DataSet, you can set it as the data source for a DataGridView control

The error message “Illegal characters in path” usually occurs when the path to a file contains invalid characters.

In this case, it could mean that the path to the XML file contains characters that are not allowed in a file path. For example, the following characters are not allowed in a file path: \ / : * ? ” < > |

To resolve this issue, you can modify the file path to remove the invalid characters. Additionally, you can encode the file path as a URL and then decode it back to a file path to ensure that all characters are valid.

Here’s an example of how to encode and decode a file path in VB.NET:

Dim xmlFilePath As String = “C:\Example\File.xml”
Dim encodedFilePath As String = Uri.EscapeDataString(xmlFilePath)
Dim decodedFilePath As String = Uri.UnescapeDataString(encodedFilePath)

Convert Object To Datatable

Private Sub LoadDataGridView()
Dim recordList As List(Of Record) = GetRecordList() ‘Get a list of Record objects
Dim dt As New DataTable()

‘Add columns to the DataTable
dt.Columns.Add(“POSName”, GetType(String))
dt.Columns.Add(“BillNo”, GetType(String))
dt.Columns.Add(“Date”, GetType(DateTime))
dt.Columns.Add(“Time”, GetType(DateTime))
dt.Columns.Add(“SubTotal”, GetType(Decimal))
dt.Columns.Add(“DiscountAmount”, GetType(Decimal))
dt.Columns.Add(“GrandTotalAmount”, GetType(Decimal))
dt.Columns.Add(“Taxes”, GetType(List(Of Tax)))
dt.Columns.Add(“SettelementMode”, GetType(String))
dt.Columns.Add(“TransactionStatus”, GetType(String))

‘Populate the DataTable with data from the Record objects
For Each rec As Record In recordList
Dim row As DataRow = dt.NewRow()
row(“POSName”) = rec.POSName
row(“BillNo”) = rec.BillNo
row(“Date”) = rec.Date
row(“Time”) = rec.Time
row(“SubTotal”) = rec.SubTotal
row(“DiscountAmount”) = rec.DiscountAmount
row(“GrandTotalAmount”) = rec.GrandTotalAmount
row(“Taxes”) = rec.Taxes
row(“SettelementMode”) = rec.SettelementMode
row(“TransactionStatus”) = rec.TransactionStatus
dt.Rows.Add(row)
Next

‘Set the DataTable as the data source for the DataGridView control
DataGridView1.DataSource = dt
End Sub