How to insert data to a SQL database Using VB

[Newbie19]

I am creating a project that would insert data into a SQL database when the User placed the information into the VB application. Then at a click of a button, it would add this data into the database.

I am wondering what is the exact or direction of the VB code to insert the data into the database.

I have the connectionstring created and I am connected.

Thanks.

[ou812vh]

I'm pretty new to this as well. But I did a program a few months ago and I did it like this. The procedure below collects the data and calls a stored procedure in SQL to put the data where I wanted it.


' Declaring variables for connection string params
Dim sServer As String
Dim sUser As String
Dim sPWD As String
Dim sDatabase As String

' Declaring variables
Dim DBcon As New ADODB.Connection
Dim objcmd As New ADODB.Command
Dim objparameter1 As New ADODB.Parameter
Dim objparameter2 As New ADODB.Parameter
Dim objparameter3 As New ADODB.Parameter
Dim objrs As New ADODB.Recordset

sServer = "ServerName" ' setting up var with data
sDatabase = "DataBaseName"
sUser = "username"
sPWD = "password"

' opening connection
DBcon.ConnectionString = "Provider=sqloledb;" & _
"server=" & sServer & ";database=" & sDatabase & ";Integrated Security=SSPI" 'uid=" & sUser & ";pwd=" & sPWD & ";
DBcon.CursorLocation = adUseServer
DBcon.Open

'Setting up the three parameters for date, time and weights
objparameter1.Direction = adParamInput
objparameter1.Type = adInteger
objparameter1.Size = 3
objparameter1.Value = Text1
objcmd.Parameters.Append objparameter1

objparameter2.Direction = adParamInput
objparameter2.Type = adDBDate
objparameter2.Size = 3
objparameter2.Value = Date
objcmd.Parameters.Append objparameter2

objparameter3.Direction = adParamInput
objparameter3.Type = adDBTime
objparameter3.Size = 3
objparameter3.Value = Time
objcmd.Parameters.Append objparameter3

' Calling up the stored procedure and passing the params
objcmd.ActiveConnection = DBcon
objcmd.CommandType = adCmdStoredProc
objcmd.CommandText = "sp_Line1"
Set objrs = objcmd.Execute

DBcon.Close



Stored procedure in SQL looked like this and the SP name is "sp_Line1"

CREATE PROCEDURE sp_Line1(@objparameter1 datetime, @objparameter2 int, @objparameter3 float)
AS

INSERT into FloatTable1 (DateandTime,TagIndex, Val)
values(@objparameter1,@objparameter2, @objparameter3)
GO


I hope this helps!

[Newbie19]

Thanks for the information, I'll look at it and try and develop a pathway for this project I'm doing.

Thanks.